Description
All libraries are exported using mysqldump–all-databases. But when it comes to getting the data out of the main repository, we don't need or want to information_schema and MySQL libraries. With fewer databases, you can also /usr/local/mysql/bin/mysqldump -uroot -p --databases db1 db2 > db1db2.sql
export it this way, but if you have more data, it's cumbersome to specify.
MySQL is supported ignore-table, but there is no ignore-database, so to export all the other libraries except INFORMATION_SCHEMA and MySQL library, can you only specify database?
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
Performance_schema is added to the mysql5.5, and when we do mysqldump, we report the following error message:
Mysqldump:got error:1142:select,lock tabl command denied to user ' root ' @ ' localhost ' for table ' cond_instances ' when USI ng LOCK TABLES
We can add parameter –skip-lock-tables to the mysqldump, such as
# mysqldump -uroot- p --skip-lock-tables
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
Mysqldump exclude some libraries when backing up a database