How to exclude a specified database when Mysql is backed up _mysql

When using the mysqldump command backup,--all-databases can back up all the databases. You can also exclude established tables by using ignore-table. However, Mysqldump has no parameters to exclude the database.

When there are fewer databases to back up, you can do so by mysqldump-uroot-p123456--databases db1 DB2 db3 > Mysqldump.sql.

But if there are dozens of databases, it's tiring and low. The solution is still there, look at the following:

"The following demo uses the MySQL username root, password 123456"

Mysql-uroot-p123456-e ' Show databases ' | Grep-e-V "database|information_schema|mysql|test" |xargs mysqldump-uroot-p123456--databases > Mysqldump1.sql

Unfortunately, it was wrong to perform the backup times on mysql5.5.

Check the data, found that is due to 5.5 after the MySQL Performance_schema library caused. Then we skip the library during backup, and the following 2 options are optional:

1. Mysql-uroot-p123456-e ' Show databases ' | Grep-e-V "database|information_schema|mysql|test" |xargs mysqldump-uroot-p123456--skip-lock-tables Performance_ Schema--databases > 2.sql

2. Mysql-uroot-p123456-e ' Show databases ' | Grep-e-V "database|information_schema|mysql|performance_schema" |xargs mysqldump-uroot-p123456--databases > 3. Sql

I prefer the second way.

Here's a copy of the MySQL script that I've written, executed once every night. Writing is simpler and the statements are not optimized.

#!/bin/bash
# description:backup MySQL data
# author:lee
file= ' mysql_ ' date +%f '
backdir= ' Mysqlbackup "
conf_file="/etc/my.cnf "
mysql-uroot-e ' show databases ' |egrep-v" database|information_schema| Performance_schema "\ |xargs mysqldump-uroot--databases > $BACKDIR/$FILE. SQL && CP $CONF _file $BACKDIR/$FILE . cnf \
&& echo-e "Machine:oracle\nip:172.16.10.12\nstatus:mysql is backup complete" | \ mail-s "MySQL Bac Kup Complete "lee@126.com

The code in the diagram below looks more clearly written.

Let me introduce you to the following MySQL exclude specified tables when backing up a database using mysqldump

 #!/bin/sh for J. ' Mysql-uroot-e ' use spservice; Show TABLES |
    Grep-v Tables ' do case $j in mo_log|mt_log|report_info):;; 
* Mysqldump-uroot--DEFAULT-CHARACTER-SET=GBK--opt spservice $j >> $j. sql Esac done >> table name is not fixed #!/bin/sh For j in ' mysql-uroot-e ' use spservice; Show TABLES | Grep-v Tables ' Do with I ' $@ ' do if [$j!= $i];then mysqldump-uroot--DEFAULT-CHARACTER-SET=GBK--opt Spservice $j >> $j. sql fi done >> More concise method #!/bin/sh for J in ' mysql-uroot-e ' use spservice; Show TABLES | Grep-v Tables ' do echo $@ | Grep-wq "\< $j \>" If [$?-ne 0];then mysqldump-uroot--default-character-set=gbk--opt spservice $j >> ; $j. sql Fi >> use method sh exclude.sh mo_log mt_log report_log #定义一个shell数组 tables= (mo_log mt_log report_log) SH exc lude.sh ${tables[@]}