Detailed analysis of the sample code for excluding certain libraries when backing up the database with mysqldump

The following editor will bring you an example of excluding certain libraries when backing up the database with mysqldump. The editor thinks it is quite good, so I will share it with you now and give it as a reference for everyone. Let’s follow the editor and take a look.

Instructions:

Using mysqldump –all-databases will export all libraries. But if we are a master-slave and dump data from the master library, we do not need or want the information_schema and mysql libraries. If there are few databases, you can also export via /usr/local/mysql/bin/mysqldump -uroot -p --databases db1 db2 > db1db2.sql, but if there is a lot of data, it will be very troublesome to specify like this.

mysql supports ignore-table, but does not have ignore-database, so if you want to export all other libraries except information_schema and mysql library, can you only specify the database one by one?

Solution:

# 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 has been added to mysql5.5. When we perform mysqldump, the following error message will be reported:

mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user ‘root'@'localhost' for table ‘cond_instances' when using 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 或者过滤掉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

The above is the detailed content of Detailed analysis of the sample code for excluding certain libraries when backing up the database with mysqldump. For more information, please follow other related articles on the PHP Chinese website!