MySQL备份策略详解-优快云博客

本文链接：https://blog.youkuaiyun.com/n_u_l_l_/article/details/103091342

就是为了防止数据损坏和丢失，定期备份，多次保存，坏掉的数据还可以通过备份的来修复。

备份的时间？
都是在用户使用很少的时候，不能能趁着使用高峰期备份。这时数据变动很大

备份的内容？
全库
单库

备份策略？
完全备份：将所有的数据原封不动的备份一次。
优点：恢复方便
缺点：备份时间长，浪费存储空间

适用于数据量小的场景。

差异备份：以完全备份为基准，只备份不一样的数据

增量备份：以上一次备份为基准，只备份不一样的数据

如果数据量大，可以考虑使用增量或差异备份。

如果完全备份节点后，第一天使用差异备份，第二天再使用差异备份，这时，第一天的数据丢失，直接把第二天的恢复回去就可以了。

如果完全备份节点后，第一天使用增量备份，第二天在使用增量备份，这时，第三天的崩掉了，要先恢复第一天的，再恢复第二天的，才能保证不丢失。

mysql的备份：

自带的备份工具-mysqldump
只支持完全备份

第三方备份工具-xtrbackup
支持完全备份和增量备份

mysqldump备份：
1. 全库

[root@localhost data]# mysqldump -u root -p --all-databases > /tmp/all`date "+%Y-%m-%d"`.sql
Enter password:

[root@localhost tmp]# ls
all2019-11-15.sql

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2                |
| db3                |
| db400              |
| gsc1               |
| mysql              |
| performance_schema |
| sys                |
| t2                 |
+--------------------+
10 rows in set (0.00 sec)
mysql> drop database db400;
mysql> drop database t2;
mysql> drop database db3;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2                |
| gsc1               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

删库了,先不要着急跑。

[root@localhost tmp]# mysql -u root -p < /tmp/all2019-11-15.sql 
Enter password:

恢复。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2                |
| db3                |
| db400              |
| gsc1               |
| mysql              |
| performance_schema |
| sys                |
| t2                 |
+--------------------+
10 rows in set (0.00 sec)

发现库又回来了。

2. 单库

[root@localhost ~]# mysqldump -u root -p db1 > /tmp/sqls/db1`date "+%Y-%m-%d"`.sql
Enter password: 
将db1 这个库被分成.sql文件 名字是db1加“时间戳”

数据库中有这么多表：

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| animal1       |
| animal2       |
| dept          |
| emp           |
| likesomething |
| serce         |
| shop          |
| student       |
| t1            |
| t2            |
| t3            |
| t4            |
| t6            |
| t7            |
| xuesheng      |
+---------------+
删到就剩这么多。
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| dept          |
| likesomething |
| serce         |
| student       |
| t2            |
| t3            |
| t4            |
| t6            |
| t7            |
| xuesheng      |
+---------------+

然后恢复：

单库恢复时要指定数据库。如果没有这个库的话要创建新的库才能添加。

[root@localhost sqls]# mysql -u root -p db1 < /tmp/sqls/db12019-11-16.sql 
Enter password:

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| animal1       |
| animal2       |
| dept          |
| emp           |
| likesomething |
| serce         |
| shop          |
| student       |
| t1            |
| t2            |
| t3            |
| t4            |
| t6            |
| t7            |
| xuesheng      |
+---------------+
15 rows in set (0.00 sec)

然后就恢复回来了。

3. 单表

[root@localhost sqls]# mysqldump -u root -p db1 animal1 > /tmp/sqls/animal1`date "+%Y-%m-%d".sql`
Enter password:

备份 db1 库的animal1表。

mysql> select * from animal1;
+------+------+-----------+
| id   | name | city      |
+------+------+-----------+
| 1000 | cat  | beijing   |
| 1001 | dog  | chongqing |
| 1002 | pig  | shanghai  |
| 1003 | fish | liaoning  |
+------+------+-----------+

mysql> delete  from animal1 where id=1000 or id=1001;

mysql> select * from animal1;
+------+------+----------+
| id   | name | city     |
+------+------+----------+
| 1002 | pig  | shanghai |
| 1003 | fish | liaoning |
+------+------+----------+

[root@localhost sqls]# mysql -u root -p db1 < /tmp/sqls/animal12019-11-16.sql 
Enter password:

mysql> select * from animal1;
+------+------+-----------+
| id   | name | city      |
+------+------+-----------+
| 1000 | cat  | beijing   |
| 1001 | dog  | chongqing |
| 1002 | pig  | shanghai  |
| 1003 | fish | liaoning  |
+------+------+-----------+

但是这个的恢复只能恢复这个表，别的不能恢复。

4. 多库

多库的备份要加上一个选项 -B 这样才能识别成多个库，不然的话第二个库会被识别成第一个库的中的表，如果没有与库同名的表，就报错，就算不报错，也不符合我们的需求。

[root@localhost sqls]# mysqldump -u root -p -B db1 mysql > /tmp/sqls/db1-mysql`date "+%Y-%m-%d"`.sql
Enter password:

[root@mysql-bin ~]# mysql -u root -p < /tmp/mysql_db2.sql
Enter password:

多库的恢复也不用指定数据库。

5. 多表

[root@localhost sqls]# mysqldump -u root -p db1 animal1 animal2 emp > /tmp/sqls/a1-a2-e`date "+%Y-%m-%d"`.sql
Enter password:

备份

[root@localhost sqls]# mysql -u root -p db1 < /tmp/sqls/a1-a2-e2019-11-16.sql 
Enter password:

恢复

**注：在备份时要注意一点，外键，以及表的联系。**如果备份时，没有备份外键的依赖或者主键，会导致无法使用，不能添加和修改。说简单点就是两个表是有联系的，你只备份了一个，删除2个表再恢复时，确实能够恢复，只能恢复1个表，但是这个表不能用，因为表不全，没有联系，添加、修改等操作会报错。

备份的选项：
–lock-all-tables 备份的同时，给表加锁
–default-character-set=utf8 指定字符集

[root@mysql-bin ~]# mysqldump -u root -p --all-databases --lock-all-tables --default-character-set=utf8 > /tmp/all.sql 
Enter password:

一些注意的点：

" > " 这个符号都不陌生吧，重定向。备份时确是用了这个东西，会清空之前的文件的所有内容。

而备份时，这个符号是可以换的，比如换成“ >> ”追加。

[root@localhost sqls]# ls -lh db1-mysql2019-11-16.sql 
-rw-r--r--. 1 root root 783K Nov 16 10:01 db1-mysql2019-11-16.sql
//之前已经备份过一次了，看一下这个文件，783k。

[root@localhost sqls]# mysqldump -u root -p -B db1 mysql >> /tmp/sqls/db1-mysql`date "+%Y-%m-%d"`.sql
Enter password: 
[root@localhost sqls]# ls -lh db1-mysql2019-11-16.sql 
-rw-r--r--. 1 root root 1.6M Nov 16 10:03 db1-mysql2019-11-16.sql
//这时用>> 追加，再次查文件看到是1.6M，文件变大了

[root@localhost sqls]# mysqldump -u root -p -B db1 mysql > /tmp/sqls/db1-mysql`date "+%Y-%m-%d"`.sql
Enter password: 
[root@localhost sqls]# ls -hl db1-mysql2019-11-16.sql 
-rw-r--r--. 1 root root 783K Nov 16 10:03 db1-mysql2019-11-16.sql
//这个 再次重定向，又变成783k了
证明这个符号和重定向一样。

备份出的.sql文件为什么能回复数据呢？
为什么单库，单表，多表要指定数据库呢？
从sql文件来看一看。

sql!文件的的行很多，所以看截图吧
在这里插入图片描述可以看到，他能恢复的原理就是将数据库作成一条条的SQL语句，每一个库，每一个表，每一条数据都是SQL语句。这个感觉挺狗。。。。。。。。。。。没有想象中那么复杂。

上图中是全库的备份。

可以看到，创建数据库 db1，删除表 animal1 ，创建表animal1 。等等的。

就是用这东西来恢复的。

在看一个单表的备份：

在这里插入图片描述
这里没有创建数据库的语句，所以不能直接备份，而是要有指定数据库才能使用。

不过也可以看到lock table ‘animal1’ write；这个是上写锁，恢复时要加锁，防止数据异常。

不过也比较好，从这个SQL文件也可以学到很多技巧性东西。

一般来说mysql的备份会备份到3个位置：
1、备份到本机，2、备份到同局域网内的另一台主机，用NFS来连接，2、备份到跨机房跨网段的另一台备份机上，回传公司。

用脚本和计划任务来进行操作。

要想将备份出来的文件发送到远程的另一个机房，要经过防火墙，不能直接访问到，所以要做端口绑定，将备份机和防火墙的端口绑定。这样访问防火墙的IP就是访问这台公司备份机。

非交互式远程拷贝。
可以用sshpass。

#!/bin/bash

#mysql 备份脚本

mysql_backup="/tmp/mysql_backup"
date=`date "+%Y-%m-%d"`
nfs_backup="/tmp/nfs_mysql_backup"
nfs_ip="172.16.12.15"

#本机备份
if [ -d $mysql_backup ]
then
    echo "ERROR: File is here !!!"
else
    mkdir $mysql_backup -p
    mysqldump -u root -p000000 --all-databases > $mysql_backup/all-${date}.sql
    mysqldump -u root -p000000 db2 > $mysql_backup/db2-${date}.sql
fi

gzip ${mysql_backup}/*
find ${mysql_backup} -mtime +7 -name "*.gz" -type f -exec -rm -f {} \;
#同机房备份NFS
if [ -d $nfs_backup ]
then
    echo "ERROR: File is here !!!"
    /bin/cp ${mysql_backup}/*.gz ${nfs_backup}
else
    mkdir $nfs_backup
    mount ${nfs_ip}:/NFS-share ${nfs_backup}
fi
find ${nfs_backup} -mtime +7 -name "*.gz" -type f -exec -rm -f {} \;
#远程访问
if [ $UID -eq 0 ]
then
sshpass -p 000000 scp -o StrictHostkeyChecking=no ${mysql_backup}/*.gz 172.16.12.15:/tmp/mysql_backup
else
    echo "permission deiny"
fi

其实这个写的还不够严谨，应该全用变量的。

find 找到7天以前的，并删除它。

StrictHostkeyChecking=no应用在脚本当中，避免使用域名链接服务器的时候，检查knows_hosts文件。

然后把这个添加到计划任务里就可以了。

[root@localhost 1119mysql]# crontab -e
[root@localhost 1119mysql]# crontab -l
30 2 * * * /tmp/gsc-test/1119mysql/mysqlbackup1.sh

半夜两点半执行