shell 脚本MySQL备份

1.MySQL备份

1）利用自带工具MySQL dump 实现数据库分表备份。

分库备份

1] 如何获取需要备份的数据库列表

过滤掉上面的database表

将系统自带的名字去掉

[root@RHCSA ~]# mysql -uroot -pOPEBlab123! -N -e 'show databases' | egrep -v 'information_schema |mysql|performance_schema|sys'
|是或者的意思

#定义一个变量等于上面输出的结果
DBS=`mysql -uroot -pOPEBlab123! -N -e 'show databases' | egrep -v 'information_schema |mysql|performance_schema|sys'`
​
[root@RHCSA ~]# echo $DBS
FIRST_DB SECOND_DB exam firstdb homework indext_db information_schema mydb one sqlprogrm test11
​

2]循环遍历

[root@RHCSA ~]# for db in $DBS
> do
> echo 备份 $db
> done
备份 FIRST_DB
备份 SECOND_DB
备份 exam
备份 firstdb
备份 homework
备份 indext_db
备份 information_schema
备份 mydb
备份 one
备份 sqlprogrm
备份 test11
​

3]备份

[root@RHCSA ~]# mysqldump --help | more --查看帮助
​
复制备份
[root@RHCSA ~]# mysqldump -uroot -p'OPEBlab123!' -B $db > $db_$(date +%F).sql
​
​
[root@RHCSA scripts]# vim bak_db_v1.sh +
​
#!/bin/bash
​
#define var
​
DBS=`mysql -uroot -pOPEBlab123! -N -e 'show databases' | egrep -v 'information_schema|mysql|performance_schema|sys'`
​
for db in $DBS
do
   mysqldump -uroot -p'OPEBlab123!' -B $db > ${db}_$(date +%F).sql
done
​
[root@RHCSA scripts]# sh bak_db_v1.sh
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.
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@RHCSA scripts]# ll
total 84
-rw-r--r--. 1 root root   235 Nov  1 11:22 bak_db_v1.sh
-rw-r--r--. 1 root root  4568 Nov  1 11:22 exam_2023-11-01.sql
-rw-r--r--. 1 root root  1462 Nov  1 11:22 firstdb_2023-11-01.sql
-rw-r--r--. 1 root root  1466 Nov  1 11:22 FIRST_DB_2023-11-01.sql
-rw-r--r--. 1 root root  5503 Nov  1 11:22 homework_2023-11-01.sql
-rw-r--r--. 1 root root  2222 Nov  1 11:22 indext_db_2023-11-01.sql
-rw-r--r--. 1 root root   791 Nov  1 11:21 information_schema_2023-11-01.sql
-rw-r--r--. 1 root root 17827 Nov  1 11:22 mydb_2023-11-01.sql
-rw-r--r--. 1 root root  6492 Nov  1 11:22 one_2023-11-01.sql
-rw-r--r--. 1 root root 11240 Nov  1 11:22 SECOND_DB_2023-11-01.sql
-rw-r--r--. 1 root root  3873 Nov  1 11:22 sqlprogrm_2023-11-01.sql
-rw-r--r--. 1 root root  1458 Nov  1 11:22 test11_2023-11-01.sql
​

优化后的脚本

[root@RHCSA scripts]# vim bak_db_v2.sh 
#!/bin/bash
​
#define var
BAK_DIR=/backup/db
CMD_OPT="-uroot -pOPEBlab123!"
EX_DB='information_schema|mysql|performance_schema|sys'
​
DBS=$(mysql ${CMD_OPT} -N -e 'show databases' | egrep -v ${EX_DB})
​
#main program
[ -d ${BAK_DIR} ] || mkdir -p ${BAK_DIR}
for db in $DBS
do
   mysqldump ${CMD_OPT}  -B $db > ${BAK_DIR}/${db}_$(date +%F).sql
done
​

2）分表

[root@RHCSA ~]# mysql -uroot -p'OPEBlab123!' -N -e 'show tables from mydb'
​[root@RHCSA scripts]# vim bak_db_v3.sh 
​
#!/bin/bash
​
#define var
BAK_DIR=/backup/db
CMD_OPT="-uroot -pOPEBlab123!"
db=mydb
TABS=$(mysql ${CMD_OPT} -N -e "show tables from $db")
​
​
#main program
[ -d ${BAK_DIR}/$db ] || mkdir -p ${BAK_DIR}/$db
for tab in $TABS
do
   mysqldump ${CMD_OPT} $db $tab > ${BAK_DIR}/$db/${db}_${tab}_$(date +%F).sql
done
​

3)分库分表

#库的
#!/bin/bash
​
#define var
BAK_DIR=/backup/db
CMD_OPT="-uroot -pOPEBlab123!"
EX_DB='information_schema|mysql|performance_schema|sys'
​
DBS=$(mysql ${CMD_OPT} -N -e 'show databases' | egrep -v ${EX_DB})
​
for db in $DBS
do
   TABS=$(mysql ${CMD_OPT} -N -e "show tables from $db")
   [ -d ${BAK_DIR}/$db ] || mkdir -p ${BAK_DIR}/$db
    mysqldump ${CMD_OPT}  -B $db > ${BAK_DIR}/${db}_$(date +%F).sql
   for tab in $TABS
     do
      mysqldump ${CMD_OPT} $db $tab > ${BAK_DIR}/$db/${db}_${tab}_$(date +%F).sql
     done
  
done

修改后

#!/bin/bash
​
#define var
BAK_DIR=/backup/db
CMD_OPT="-uroot -pOPEBlab123!"
EX_DB='information_schema|mysql|performance_schema|sys'
​
DBS=$(mysql ${CMD_OPT} -N -e 'show databases' | egrep -v ${EX_DB})
​
#main program
for db in $DBS
do
   TABS=$(mysql ${CMD_OPT} -N -e "show tables from $db")
   [ -d ${BAK_DIR}/$db ] || mkdir -p ${BAK_DIR}/$db
    mysqldump ${CMD_OPT}  -B $db > ${BAK_DIR}/$db/${db}_$(date +%F).sql
​
   for tab in $TABS
     do
      mysqldump ${CMD_OPT} $db $tab > ${BAK_DIR}/$db/${db}_${tab}_$(date +%F).sql
     done
​
done

执行

检查是否成功

4）还原删除的数据库表

#查看数据库表的内容
[root@RHCSA ~]# mysql -uroot -pOPEBlab123! -e 'select * from SECOND_DB.student'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+----------+---------+----------+
| stu_id | stu_name | stu_age | class_id |
+--------+----------+---------+----------+
|  10002 | zhangsan |      20 |     1001 |
|  10003 | lisi     |      18 |     1001 |
|  10004 | wangwu   |      19 |     1001 |
+--------+----------+---------+----------+
#删除这个表
[root@RHCSA ~]# mysql -uroot -pOPEBlab123! -e 'drop table SECOND_DB.student'
mysql: [Warning] Using a password on the command line interface can be insecure.
#再次查看证明这个表不存在，已经被删除掉了
[root@RHCSA ~]# mysql -uroot -pOPEBlab123! -e 'select * from SECOND_DB.student'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1146 (42S02) at line 1: Table 'SECOND_DB.student' doesn't exist 

#还原完成
[root@RHCSA ~]# mysql -uroot -pOPEBlab123! SECOND_DB < /backup/db/SECOND_DB/SECOND_DB_student_2023-11-01.sql 
​
[root@RHCSA ~]# mysql -uroot -pOPEBlab123! SECOND_DB（要还原的数据库名字） < /backup/db/SECOND_DB/SECOND_DB_student_2023-11-01.sql （备份路径）