Using mysqldump to back up multiple libraries

mysqldump backs up multiple libraries at a time

There are 20 + databases in a database instance, and 10 + of them need to be backed up in this backup. Use mysqldump to directly back up and step on a warning and an error.

Database version: 5.7.26

The process is as follows:

action1

mysqldump -hrm-2ze04c849v9m32bzj.mysql.rds.aliyuncs.com  -uadminroot  -p'BDm4w%qqGf3Zx!J5' --single-transaction --quick --databases db1 db2 db3 db4 ... > /tmp/test.sql

Warning:

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
mysqldump: Couldn't execute 'SELECT COLUMN_NAME,                       JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')                FROM information_schema.COLUMN_STATISTICS                WHERE SCHEMA_NAME = 'activity' AND TABLE_NAME = 'scores';': Unknown table 'column_statistics' in information_schema (1109)

reason:

After GTID is 5.6, global transaction ID (GTID) is added to strengthen the database's primary and secondary consistency, fault recovery, and fault tolerance. Official: A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (master) So it may be because it is unique in one database, but it may be repeated when importing other databases. All will have a reminder.

You can set it by adding two parameters -- set gtid purged = off or - gtid mode = off.

action2

mysqldump -hrm-2ze04c849v9m32bzj.mysql.rds.aliyuncs.com  -uadminroot  -p'BDm4w%qqGf3Zx!J5' --set-gtid-purged=OFF --single-transaction --quick --databases db1 db2 db3 db4 ... > /tmp/test.sql

report errors:

mysqldump: Couldn't execute 'SELECT COLUMN_NAME,                       JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')                FROM information_schema.COLUMN_STATISTICS                WHERE SCHEMA_NAME = 'activity' AND TABLE_NAME = 'scores';': Unknown table 'column_statistics' in information_schema (1109)

reason:

The new version of mysqldump has a new flag enabled by default, which is disabled by -- column statistics = 0.

action3

mysqldump -hrm-2ze04c849v9m32bzj.mysql.rds.aliyuncs.com  -uadminroot  -p'BDm4w%qqGf3Zx!J5' --column-statistics=0 --set-gtid-purged=OFF --single-transaction --quick --databases db1 db2 db3 db4 ... > /tmp/test.sql

success!

Tags: Database mysqldump MySQL SQL

Posted on Tue, 26 May 2020 07:57:51 -0700 by ponies3387