mysqldump shouldn't dump information_schema or performance_schema
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"
(from redmine: created on 2016-02-21)