MySQL数据库的备份与恢复（5）——编写一个简单的mysqldump分库备份脚本
编写一个脚本，用于备份除了information_schema和performance_schema数据库之外的所有数据库，每个数据库生成一个sql文件。
step1、取出MySQL包含的所有数据库名称
[root@Mysql11 tmp
]
mysql:
[Warning
] Using a password on the
command line interface can be insecure.
+--------------------+
| Database
|
+--------------------+
| information_schema
|
| hist
|
| mysql
|
| performance_schema
|
| sys
|
| wanggx
|
+--------------------+
step2、过滤掉系统数据库和标题行
[root@Mysql11 tmp
]
mysql:
[Warning
] Using a password on the
command line interface can be insecure.
hist
mysql
sys
wanggx
step3、编写脚本
vim backup.sh
脚本的内容如下：
for dbname
in `mysql -uroot -p123456 -e "show databases;"|grep -Evi "database|infor|perfor"`
do
mysqldump -uroot -p123456 --events -B
$dbname|gzip > /tmp/
${dbname}_bak.sql.gz
done
step4、为脚本增加可执行权限
[root@Mysql11 tmp
]
/tmp
[root@Mysql11 tmp
]
[root@Mysql11 tmp
]
[root@Mysql11 tmp
]
总用量 4
-rwxr-xr-x. 1 root root 184 7月 2 15:09 backup.sh
step5、执行脚本，查看执行结果
[root@Mysql11 tmp
]
mysql:
[Warning
] Using a password on the
command line interface can be insecure.
mysqldump:
[Warning
] Using a password on the
command line interface can be insecure.
mysqldump:
[Warning
] Using a password on the
command line interface can be insecure.
mysqldump:
[Warning
] Using a password on the
command line interface can be insecure.
mysqldump:
[Warning
] Using a password on the
command line interface can be insecure.
[root@Mysql11 tmp
]
backup.sh hist_bak.sql.gz mysql_bak.sql.gz sys_bak.sql.gz wanggx_bak.sql.gz
