MySQL Bugs: #3228: mysqldump should have a exclude

Bug #3228	mysqldump should have a exclude
Submitted:	18 Mar 2004 11:55	Modified:	8 Feb 2007 19:25
Reporter:	daniel poelzleithner	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I use mysqldump --all-databases to backup my local databases. I use a search engine for my documentations which also uses mysql, but it's useless to back the index up. A --execlude-database option would be nice to skip some databases which should not be backuped. This would be easier than using shell scripts for this.

How to repeat:
Use mysqldump --all-databases

--exclude-table(s) also comes to mind here ...

--exclude-table(s) would certainly be a big help, I have a database with a lot of tables, I want to back them all up apart from one, having to specify all the tables apart from the one I don't want seems daft.

Even though this has been requested or discussed for at least three years, I still don't see that option even in 5.1.14. Is anyone working on it?

Thank you!

Sjeh

Thank you for the reasonable feature request.

Option --ignore-table has already implemented.

I would like to see this option implemented too.

It would look something like:
mysqldump --all-databases --exclude-database MyTempDB

The reason is that I have a database with a large number of tables that are temporary in nature, and do not need to be part of the backup.  It would not be practical to use the exclude tables option for that many tables.

Without the option, I will have to write a script to list all the databases individually, except the one I want.  Having the option would be easy to add, and would save time for me and many others.

Another way to implement this feature is to allow exclude-tables to handle globs.  For example:
mysqldump --all-databases --exclude-table MyTempDB.*
This would exclude all the tables in MyTempDB.
However, it is probably more user-friendly to have a simple, --exclude-database option.

Patch against 5.6 tree:

=== modified file 'client/client_priv.h'
--- client/client_priv.h        revid:andrei.elkin@oracle.com-20120419163009-cnx30q4zdcrgzf7p
+++ client/client_priv.h        2012-04-19 21:49:10 +0000
@@ -58,7 +58,8 @@
   OPT_USE_THREADS,
   OPT_IMPORT_USE_THREADS,
   OPT_MYSQL_NUMBER_OF_QUERY,
-  OPT_IGNORE_TABLE,OPT_INSERT_IGNORE,OPT_SHOW_WARNINGS,OPT_DROP_DATABASE,
+  OPT_IGNORE_TABLE,OPT_IGNORE_DATABASE,OPT_INSERT_IGNORE,
+  OPT_SHOW_WARNINGS,OPT_DROP_DATABASE,
   OPT_TZ_UTC, OPT_CREATE_SLAP_SCHEMA,
   OPT_MYSQLDUMP_SLAVE_APPLY,
   OPT_MYSQLDUMP_SLAVE_DATA,

=== modified file 'client/mysqldump.c'
--- client/mysqldump.c  revid:andrei.elkin@oracle.com-20120419163009-cnx30q4zdcrgzf7p
+++ client/mysqldump.c  2012-04-19 21:33:44 +0000
@@ -191,6 +191,7 @@
                                   "", compatible_mode_names, NULL};

 HASH ignore_table;
+HASH ignore_database;

 static struct my_option my_long_options[] =
 {
@@ -356,6 +357,10 @@
    "be specified with both database and table names, e.g., "
    "--ignore-table=database.table.",
    0, 0, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
+  {"ignore-database", OPT_IGNORE_DATABASE,
+   "Do not dump the specified database. To specify more than one database to ignore, "
+   "use the directive multiple times, once for each database.",
+   0, 0, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
   {"include-master-host-port", OPT_MYSQLDUMP_INCLUDE_MASTER_HOST_PORT,
    "Adds 'MASTER_HOST=<host>, MASTER_PORT=<port>' to 'CHANGE MASTER TO..' "
    "in dump produced with --dump-slave.", &opt_include_master_host_port,
@@ -842,6 +847,12 @@
       exit(EX_EOM);
     break;
   }
+  case (int) OPT_IGNORE_DATABASE:
+  {
+    if (my_hash_insert(&ignore_database, (uchar*)my_strdup(argument, MYF(0))))
+      exit(EX_EOM);
+    break;
+  }
   case (int) OPT_COMPATIBLE:
     {
       char buff[255];
@@ -918,6 +929,9 @@
   if (my_hash_init(&ignore_table, charset_info, 16, 0, 0,
                    (my_hash_get_key) get_table_key, my_free, 0))
     return(EX_EOM);
+  if (my_hash_init(&ignore_database, charset_info, 16, 0, 0,
+                   (my_hash_get_key) get_table_key, my_free, 0))
+    return(EX_EOM);
   /* Don't copy internal log tables */
   if (my_hash_insert(&ignore_table,
                      (uchar*) my_strdup("mysql.apply_status", MYF(MY_WME))) ||
@@ -1439,6 +1453,8 @@
   my_free(opt_password);
   if (my_hash_inited(&ignore_table))
     my_hash_free(&ignore_table);
+  if (my_hash_inited(&ignore_database))
+    my_hash_free(&ignore_database);
   if (extended_insert)
     dynstr_free(&extended_row);
   if (insert_pat_inited)
@@ -4123,6 +4139,12 @@
   return 0;
 }

+/* Return 1 if we should copy the database */
+
+my_bool include_database(const uchar *hash_key, size_t len)
+{
+  return ! my_hash_search(&ignore_database, hash_key, len);
+}

 static int dump_all_databases()
 {
@@ -4145,6 +4167,9 @@
     if (is_ndbinfo(mysql, row[0]))
       continue;

+    if (!include_database((uchar*) row[0], strlen(row[0])))
+      continue;
+
     if (dump_all_tables_in_db(row[0]))
       result=1;
   }
@@ -4170,6 +4195,9 @@
       if (is_ndbinfo(mysql, row[0]))
         continue;

+    if (!include_database((uchar*) row[0], strlen(row[0])))
+      continue;
+
       if (dump_all_views_in_db(row[0]))
         result=1;
     }
@@ -5454,6 +5482,7 @@
   compatible_mode_normal_str[0]= 0;
   default_charset= (char *)mysql_universal_client_charset;
   memset(&ignore_table, 0, sizeof(ignore_table));
+  memset(&ignore_database, 0, sizeof(ignore_database));

   exit_code= get_options(&argc, &argv);
   if (exit_code)

Leith you patch is NOT in 5.6!  there is still no OPT_IGNORE_DATABASE ...

14 YEARS, Wow, next year I'll celebrate the quinceañeras for this thread.

There is still no way to exclude or ignore databases from a mysqldump --all-databases backup.

I need this --ignore-database option

You can now use mysqlpump for this:

https://dev.mysql.com/doc/refman/8.0/en/mysqlpump.html#option_mysqlpump_exclude-databases