Instances of excluding certain libraries when backing up the database with mysqldump

illustrate:

Using mysqldump –all-databases will export all libraries. But if we are doing master-slave, when dumping data from the master database, we do not need or want the information_schema and mysql libraries. If there are few databases, you can export them through /usr/local/mysql/bin/mysqldump -uroot -p --databases db1 db2 > db1db2.sql. However, if there are a lot of data, it will be troublesome to specify it in this way.

MySQL supports ignore-table, but not ignore-database. So if we want to export all libraries except information_schema and mysql, can we only specify databases one by one?

solve:

# 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

In MySQL 5.5, performance_schema was added. 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 or filter out the performance_schema library

# 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 example of excluding certain libraries when backing up the database with mysqldump is all I have to share with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.