MySQL数据库的备份与恢复（6）——编写一个mysqldump分表备份脚本
编写一个脚本，用于备份某个数据库中所有的数据表，每个数据表生成一个sql文件。
step1、取出hist数据库包含的所有表的名称
[root@Mysql11 tmp
]
mysql:
[Warning
] Using a password on the
command line interface can be insecure.
+----------------+
| Tables_in_hist
|
+----------------+
| course
|
| dept
|
| score
|
| stu
|
| t1
|
+----------------+
step2、过滤掉标题行
[root@Mysql11 tmp
]
mysql:
[Warning
] Using a password on the
command line interface can be insecure.
course
dept
score
stu
t1
step3、编写脚本
vim backup_tables.sh
脚本的内容如下：
for tablename
in `mysql -uroot -p123456 hist -e "show tables;"|grep -Evi "table"`
do
mysqldump -uroot -p123456 --events hist
$tablename|gzip > /tmp/
${tablename}_bak.sql.gz
done
step4、为脚本增加可执行权限
[root@Mysql11 tmp
]
/tmp
[root@Mysql11 tmp
]
[root@Mysql11 tmp
]
总用量 4
-rwxr-xr-x. 1 root root 184 7月 2 15:37 backup_tables.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.
mysqldump:
[Warning
] Using a password on the
command line interface can be insecure.
[root@Mysql11 tmp
]
backup_tables.sh course_bak.sql.gz dept_bak.sql.gz score_bak.sql.gz stu_bak.sql.gz t1_bak.sql.gz
