编写脚本,使用mysqldump实现分库分表备份
[root@localhost test]# vim s1.sh
#!/bin/bash
user=root #数据库用户
pw=12345 #数据库密码
backup=/backup #备份目录
[ -d $backup ] || mkdir $backup #判断目录是否存在并创建
mysql -u$user -p$pw -e 'show databases' -N | egrep -v 'information_schema|my|sql|performance_schema|sys' > dname # 将数据库名放入dname中
while read db #读取数据库
do
mysqldump -u$user -p$pw $db > $backup/$db.sql #备份数据库
mysql -u$user -p$pw -e "show tables from $db" -N > tname #将数据库表名放入tname中
mkdir $backup/$db #创建数据库表目录
while read tb #读取数据库表
do
mysqldump -u$user -p$pw $db $tb > $backup/$db/$tb.sql #分别备份数据库表
done < tname
rm -rf dname tname
done < dname
[root@localhost test]# tree /backup/
/backup/
├── db
│ └── tb_employee.sql
├── db1
│ └── tb_dept.sql
├── db1.sql
├── db.sql
├── school
│ ├── score.sql
│ └── student.sql
└── school.sql
3 directories, 7 files
验证
[root@localhost test]# mysql -uroot -p12345 -e 'select * from school.score'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+--------+-----------+-------+
| id | stu_id | c_name | grade |
+----+--------+-----------+-------+
| 1 | 901 | 计算机 | 98 |
| 2 | 901 | 英语 | 80 |
| 3 | 902 | 计算机 | 65 |
| 4 | 902 | 中文 | 88 |
| 5 | 903 | 中文 | 95 |
| 6 | 904 | 计算机 | 70 |
| 7 | 904 | 英语 | 92 |
| 8 | 905 | 英语 | 94 |
| 9 | 906 | 计算机 | 90 |
| 10 | 906 | 英语 | 85 |
+----+--------+-----------+-------+
#删除表score
[root@localhost test]# mysql -uroot -p12345 -e 'drop table school.score'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost test]# mysql -uroot -p12345 -e 'show tables from school'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+
| Tables_in_school |
+------------------+
| student |
+------------------+
#还原表score
[root@localhost test]# mysql -uroot -p12345 school < /backup/school/score.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost test]# mysql -uroot -p12345 -e 'select * from school.score'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+--------+-----------+-------+
| id | stu_id | c_name | grade |
+----+--------+-----------+-------+
| 1 | 901 | 计算机 | 98 |
| 2 | 901 | 英语 | 80 |
| 3 | 902 | 计算机 | 65 |
| 4 | 902 | 中文 | 88 |
| 5 | 903 | 中文 | 95 |
| 6 | 904 | 计算机 | 70 |
| 7 | 904 | 英语 | 92 |
| 8 | 905 | 英语 | 94 |
| 9 | 906 | 计算机 | 90 |
| 10 | 906 | 英语 | 85 |
+----+--------+-----------+-------+
[root@localhost test]#