15

I am trying to backup my entire database along with the views. I am going to use mysqldump command. I was wondering if that will copy all the views or I will have to use separate command for it.

Please suggest me an answer. Any help is greatly appreciated.

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
vinny
  • 565
  • 2
  • 11
  • 19

2 Answers2

15

There is no specific option for dumping views.

You can try the following:

mysqldump -h... -u... -p... --all-databases --routines --triggers --no-data > /root/MySQLDBSchema.sql
grep "CREATE ALGORITHM" /root/MySQLDBSchema.sql

You should be able to see the views. This indicates that when you dump databases, the view comes with it.

Another stunt you can try, just to get the views only, is this:

mysql -uroot -prootpass AN -e"select concat('SHOW CREATE VIEW ',table_schema,'.',table_name,';') from information_schema.views" | sed 's/;/\\G/g' | mysql --uroot -prootpass > /root/MySQLDBViews.sql

Give it a Try !!!

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
4

The best answer for this is probably this one by olliiiver which I have slightly modified. It specifically exports a drop table query and a create or replace view query for every view in the system, without needing to mess around with any tables. The drop table query is often important, as mysqldump apparently likes to recreate views as tables unless you include every view in its own --ignore-table parameter, which would be annoying. It uses INFORMATION_SCHEMA.

mysql --skip-column-names --batch \
  -e "SELECT CONCAT('DROP TABLE IF EXISTS ', TABLE_SCHEMA, '.', TABLE_NAME, \
  '; CREATE OR REPLACE VIEW ', TABLE_SCHEMA, '.', TABLE_NAME, ' AS ', \
  VIEW_DEFINITION, '; ') AS q FROM INFORMATION_SCHEMA.VIEWS"

If you want to turn something like this into a shell script, I also recommend using the --defaults-extra-file=CREDSFILENAME parameter, so that you do not need to specify user/pass in the script.

The credentials file looks like this:

[client]
username=YourUsernameHere
password=YourPasswordHere
RedScourge
  • 141
  • 1