mysqldump Exclude instances of certain libraries when backing up databases

Description:

Using mysqldump all-databases exports all libraries. However, if we are master-slave, we don't need or want information_schema and mysql libraries when exporting data from the main library dump. In the case of few databases, it can also be accessed through/usr/local/mysql/bin/mysqldump-uroot-p-databases db1 db2 > db1db2. sql is exported in this way, but if there is a lot of data, it is troublesome to specify it.

mysql supports ignore-table, but there is no ignore-database, so to export all the libraries except information_schema and mysql, can only specify database one by one?

Resolve:

# mysql -e "show databases;" -uroot -p| grep -Ev "Database|information_schema|mysql|test" | xargs mysqldump -uroot -p --databases > mysql_dump.sql

Appendix:

Appendix 1: mysqldump: Got error: 1142: SELECT, LOCK TABL command denied to user 'root' @ 'localhost' for table 'cond_instances' when using LOCK TABLES

performance_schema is added to mysql5.5. When we perform mysqldump, we will report the following error message:

mysqldump: Got error: 1142: SELECT, LOCK TABL command denied to user 'root' @ 'localhost' for table 'cond_instances' ES90using LOCK TABLES

We can add the parameter skip-lock-tables to mysqldump, such as

# mysqldump -uroot -p --skip-lock-tables performance_schema > performance_schema. sql or filter out the library performance_schema

# mysql -e "show databases;" -uroot -p| grep -Ev "Database|information_schema|mysql|test|performance_schema" | xargs mysqldump -uroot -p --databases > mysql_dump.sql