Project

General

Profile

Bug #11148

mysqldump shouldn't dump information_schema or performance_schema

Added by LeLutin over 1 year ago.

Status:
New
Priority:
Normal
Assignee:
-
Category:
-
Target version:
-
Start date:
02/20/2016
Due date:
% Done:

0%

QA Check:

Description

The mysql handler when using mysqldump, with the "databases" setting set to "all", currently dumps all databases. This includes the meta-databases "information_schema" and "performance_schema".

I don't know how mysqlhotcopy works so I'm not sure if there's something to be done for this method, too.

There's currently no way of excluding databases by name. But also, keeping sql dumps of those databases is completely useless since they're only information that mysql keeps in memory about different settings / performance values.

I've also seen some mysql servers fail to dump information_schema saying that it had exhausted memory:

Warning: mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE random list of files from the FS here FROM `INNODB_BUFFER_PAGE_LRU`': Out of memory (Needed 3578512 bytes) (5)

It would be great to filter those two databases out of the db list when running mysqldump. for example:

--- mysql.orig    2016-02-20 14:31:25.601574577 -0500
+++ mysql    2016-02-20 14:52:32.685574577 -0500
@@ -238,14 +238,14 @@
    then
       if [ $usevserver = yes ]
       then
-         debug 'set -o pipefail ; echo show databases | $VSERVER $vsname exec su $user -c \"$MYSQL $defaultsfile\" | grep -v Database'
-         databases=`set -o pipefail ; echo 'show databases' | $VSERVER $vsname exec su $user -c "$MYSQL $defaultsfile" | grep -v Database`
+         debug 'set -o pipefail ; echo show databases | $VSERVER $vsname exec su $user -c \"$MYSQL $defaultsfile\" | grep -v Database | grep -v '^\(information\|performance\)_schema$''
+         databases=`set -o pipefail ; echo 'show databases' | $VSERVER $vsname exec su $user -c "$MYSQL $defaultsfile" | grep -v Database | grep -v '^\(information\|performance\)_schema$'`
          if [ $? -ne 0 ]
          then
             fatal "Authentication problem, maybe user/password is wrong or mysqld is not running?" 
          fi
       else
-         databases=$(set -o pipefail ; su $user -c "$MYSQL $defaultsfile -N -B -e 'show databases'" | sed 's/|//g;/\+----/d')
+         databases=$(set -o pipefail ; su $user -c "$MYSQL $defaultsfile -N -B -e 'show databases'" | sed 's/|//g;/\+----/d;/^\(information\|performance\)_schema$/d')
          if [ $? -ne 0 ]
          then
             fatal "Authentication problem, maybe user/password is wrong or mysqld is not running?" 
@@ -257,15 +257,6 @@
    do
       DUMP_BASE="$MYSQLDUMP $defaultsfile $sqldumpoptions" 

-      case "$db" in
-         information_schema)
-            DUMP_BASE="${DUMP_BASE} --skip-lock-tables" 
-            ;;
-         performance_schema)
-            DUMP_BASE="${DUMP_BASE} --skip-lock-tables --skip-events" 
-            ;;
-      esac
-
       # Dumping structure and data
       DUMP="$DUMP_BASE $ignore $db" 

Also available in: Atom PDF