MySQL Bugs: #44864: dump only views

Bug #44864	dump only views
Submitted:	14 May 2009 9:01	Modified:	14 May 2009 9:03
Reporter:	Susanne Ebrecht	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S4 (Feature request)
Version:	5.0, 5.1, 6.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Imagine a huge instances (let us say 200 schemas with 100 tables and lots of views, triggers, routines each) needs to be dumped because of backup or upgrade reasons.

You want to use mysqldump here by storing in a file.

Of course you don't want to get all in only one file.

By running it twice: once time with --no-create-info and the other time with --no-data you will get two files: one without data and one with only data.

But that is also to complex. You want a dump without data but you want to get a separate file for triggers, a separate file for routines and a separate file for views.

For triggers and routines this also is possible by using:
mysqldump --no-create-info --no-data --triggers -A (for only triggers)
mysqldump --no-create-info --no-data -R -A (for only routines)

What is missing?

Missing is a possibility to dump only views.

Something like:
mysqldump --no-create-info --no-data --views -A

How to repeat:
It is a feature request

Suggested fix:
Add an option that allows to switch on/off Views that is on by default.

There is bug #44939 which shows a problem with current behavior.

(Also me to be a necromancer for a moment :-) )

There is a simple way to do this.  Simply specify only the names of the view in the mysqldump comment and then create statements will be in the dump file.

For example:

mysqldump --user=root --single-transaction --quick DB1 user_view client_view any_view > /mnt/backup/DB1-views.sql

will create a dump file that contains the sql to recreate the 3 views mentioned.

Maybe someone more proficient and experienced in mysql than me can create a function to create the mysqldump statement by reading the names of the views from the database.

Actually, this command will list all the views in a database.

mysql -uroot -sNe "SELECT TABLE_NAME,' ' FROM information_schema.tables WHERE TABLE_TYPE LIKE 'VIEW' AND TABLE_SCHEMA = 'DB1';"

Use that to construct a mysqldump command.

I have created a workaround to achieve this:

#!/bin/bash
set $var1 = /usr/bin/mysql -uroot -sNe "SELECT TABLE_NAME,' ' FROM information_schema.tables WHERE TABLE_TYPE LIKE 'VIEW' AND TABLE_SCHEMA = 'DB1';" | sed -r 's/\s+//g' | sed -z -e 's/\n/ /g'
/usr/bin/mysqldump -uroot --no-data DB1 $var1 > /mnt/backup/DB1-views.sql

# We can take backup of only view by below command-

mysql -Nse"SET SESSION group_concat_max_len = 100000000; SELECT concat(table_schema,' ',group_concat(table_name order by table_name SEPARATOR ' ')) FROM information_schema.views WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys') group by table_schema order by table_schema" | while read MYVIEW; do echo "use "${MYVIEW} | awk ' { print $1 " "$2";"} ' >> /root/temp/only_view_dump.sql && mysqldump --single-transaction -d ${MYVIEW} >> /root/temp/only_view_dump.sql; done