mysql数据库备份

数据备份

（1）数据丢失可能的原因

• 硬件故障
• 软件故障
• 自然灾害
• 黑客攻击
• 误操作（比例最大）

（2）备份数据需要考虑

• 能够容忍丢失多少数据
• 恢复数据需要多长时间
• 需要恢复哪一些数据

1. 备份类型

热备份—> 备份时，数据库读写操作均不受影响
温备份—> 备份时，数据库可读不可写
冷备份—> 备份时，数据库不可读不可写，数据库要下线
注：进行备份时，需要考虑数据库存储引擎是否支持此操作
MyISAM 不支持热备份
InnoDB 支持热备，温备，冷备
物理备份—> 通过tar,cp等命令打包复制数据库的数据文件达到备份的效果
逻辑备份—> 通过特定工具从数据库中导出数据并另存备份，会丢失数据的精确度；备份的是建表，建库，插入等操作执行的sql语句，适用于中小型数据库，效率相对较低；

（1）逻辑备份的优缺点

优点

• 在备份速度上两种备份取决于不同的存储引擎
• 物理备份的还原速度快，但是物理备份的最小力度只能做到表
• 逻辑备份保存的结果通常是纯ascii的，所以可以使用文本处理工具来处理
• 逻辑备份有非常强的兼容性，物理备份则对版本要求非常高
• 逻辑备份也对保持数据的安全性有保证

缺点

• 逻辑备份要对RDBMS 产生额外的压力，而裸备份无压力
• 逻辑备份的结果可能比源文件大。所以可以进行压缩
• 逻辑备份可能会丢失浮点数的精度信息

（2）物理备份

• 直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复带走异构系统如Windows

2.备份内容

• 数据文件日志文件（事务日志，二进制日志）
• 存储过程，存储函数，触发器
• 配置文件（十分重要，每个配置都要备份）
  -用于实现数据库备份的脚本。数据库自身清理的crontab 等

2.1 确定备份内容

• 数据
• 二进制 InnoDB事务日志
• 代码（存储过程，存储函数，触发器，时间调度器）
• 服务器配置文件
  注：若要使用二进制日志进行逻辑备份，需要在配置文件中开启二进制日志。

2.2 确定备份工具

• mysqldump：逻辑备份工具，适用于所有的存储引擎，支持温备，完全备份，部分备份
• cp,tar 等归档复制工具；物理备份工具，适用于所有的存储引擎，冷备，完全备份，部分备份
• lvm2 snashot ：几乎热备，接触文件系统管理工具进行备份
• mysqlhotcopy：仅支持MsISAM存储引擎
• xtrabackup：非常强大的InnoDB/XtraDB 热备工具，支持完全被分，增量备份

2.3 备份策略

（1）直接cp，tar复制数据库文件
数据量小
（2）mysqldump+复制BIN LOGS
数据量中等，可以进行完全备份，定期备份
（3）lvm2快照+复制BIN LOGS
数据量一般，而不过分影响业务运行
（4）xtrabackup
数据量大，而不过分影响业务运行，定期增量备份或差异备份
注：以上几种方案分别针对不同场景

• 数据量较小：可以使用第一种方式，直接复制数据库文件
• 数据量还行，可以使用第二种方式，先mysqldump对数据库进行完全备份，然后定期备份Binary_log达到增量备份的效果
• 数据量一般，而又不影响业务运行：可以使用第三种方式，使用lvm2的快照对数据文件进行备份，而后对Binary_log文件进行备份达到增量备份的效果。
• 数据量很大，而又不过分影响业务运行，可以使用第四种方式，使用xtrabackup进行完全备份后。定期使用xtrabackup进行增量备份。

3. 模拟全量与二进制增量备份数据破坏后恢复示例

3.1 数据库备份

准备备份内容

# （1） 备份指定库，表
# 备份上图中的test库
[root@mysql-server ~]# mysqldump -uroot -pABC123.com --single-transaction --master-data=2 test > /tmp/test_`date +%F`.sql
# 备份及压缩
[root@mysql-server ~]# mysqldump -uroot -pABC123.com --single-transaction --master-data=2 test | gzip > /tmp/test_`date +%F`.gz
mysqldump: [Warning] Using a password on the command line interface can be insecure.
# 查看
[root@mysql-server ~]# ll /tmp/test_2020-08-03.*
-rw-r--r--. 1 root root  798 8月   3 15:29 /tmp/test_2020-08-03.gz
-rw-r--r--. 1 root root 2020 8月   3 15:26 /tmp/test_2020-08-03.sql

# （2） 全量备份，备份时刷新日志文件
[root@mysql-server ~]# mkdir /backup
[root@mysql-server ~]# mysqldump -uroot -pABC123.com --routines --events --triggers --master-data=2 --flush-logs --all-databases > /backup/all_`date +%F`.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-server ~]# ll /backup/
总用量 1016
-rw-r--r--. 1 root root 1037139 8月   3 15:32 all_2020-08-03.sql

3.2 数据库恢复

# 先创建一个库
[root@mysql-server ~]# mysql -uroot -pABC123.com -e 'create database test1'
mysql: [Warning] Using a password on the command line interface can be insecure.
# 将刚才备份的test数据库内容导入test1中
[root@mysql-server ~]# mysql -uroot -pABC123.com test1 < /tmp/test_2020-08-03.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

3.3 增量备份

前提条件：my.cnf（数据库配置文件）中要开启mysql log-bin日志功能
在数据库配置文件中的[msyqld]中插入
log-bin=mysql-bin #可以加路径，但是目录要真实存在。
重启mysql
查看数据库是否开启了二进制日志打印功能；

此时显示开启；

3.3.1 mysqlbinlog增量恢复方式

基于时间点恢复：

（1）指定开始时间到结束时间
myslbinlog mysqlbin.000011 --start-datetime='2020-08-04 00:02:24' stop-datetime='020-08-04 00:39:22'  time.sql
（2）指定开始时间到文件结束
myslbinlog mysqlbin.000011 --start-datetime='2020-08-04 00:02:24'  esen -r time.sql
（3）从文件开头到指定结束时间
myslbinlog mysqlbin.000011  --stop-datetime='2020-08-04 00:39:22'  esen -r time.sql

基于位置点的增量恢复

（1）指定开始位置到结束位置
myslbinlog mysqlbin.000011 --start-position=4 --stop-position=558 -r pos.
（2）指定开始位置到文件结束
myslbinlog mysqlbin.000011 --start-position=4 -r pos.sql
（3）从文件开始位置到指定结束位置
myslbinlog mysqlbin.000011 --stop-position=558 -r pos.sql

3.3.2 实验环境准备

（1）先做一个完全备份

[root@mysql-server ~]# mysqldump -uroot -pABC123.com --default-character-set=gbk --single-transaction -F -B test1 |gzip > /backup/test1_`date +%F`.sql.gz
mysqldump: [Warning] Using a password on the command line interface can be insecure.

（2）准备数据库和表

（3）模拟凌晨全备份

[root@mysql-server ~]# date -s '20200804'
2020年 08月 04日 星期二 00:00:00 CST
[root@mysql-server ~]# mysqldump -uroot -pABC123.com --default-character-set=utf8 --single-transaction -F -B test2 | gzip > /backup/test2_`date +%F`.sql.gz
mysqldump: [Warning] Using a password on the command line interface can be insecure.

（4）备份后继续插入数据

mysql> insert into test2_1 values
    -> (55,"zhangsanfeng",100),
    -> (66,"zhangwuji",101);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

（5）模拟用户破坏数据

mysql> drop database test2;
Query OK, 1 row affected (0.03 sec)

（6）尝试恢复数据

方法1：找出出现问题的二进制日志文件，恢复到出现问题之前时

注：需要锁定数据库
a. 检查凌晨备份

[root@mysql-server ~]# ll /backup/test2_2020-08-04.sql.gz
-rw-r--r--. 1 root root 815 8月   4 00:02 /backup/test2_2020-08-04.sql.gz

b. 检查全备后的binlog

[root@mysql-server ~]# ll /var/lib/mysql/mysql-bin.*
-rw-r-----. 1 mysql mysql  179 7月  21 12:20 /var/lib/mysql/mysql-bin.000001
-rw-r-----. 1 mysql mysql  492 7月  22 14:30 /var/lib/mysql/mysql-bin.000002
-rw-r-----. 1 mysql mysql  156 7月  22 15:17 /var/lib/mysql/mysql-bin.000003
-rw-r-----. 1 mysql mysql  179 7月  22 15:44 /var/lib/mysql/mysql-bin.000004
-rw-r-----. 1 mysql mysql  203 7月  22 16:43 /var/lib/mysql/mysql-bin.000005
-rw-r-----. 1 mysql mysql 1048 8月   3 00:40 /var/lib/mysql/mysql-bin.000006
-rw-r-----. 1 mysql mysql  930 8月   3 15:32 /var/lib/mysql/mysql-bin.000007
-rw-r-----. 1 mysql mysql 1800 8月   3 15:45 /var/lib/mysql/mysql-bin.000008
-rw-r-----. 1 mysql mysql 1119 8月   4 00:01 /var/lib/mysql/mysql-bin.000009
-rw-r-----. 1 mysql mysql  358 8月   4 00:02 /var/lib/mysql/mysql-bin.000010
-rw-r-----. 1 mysql mysql  665 8月   4 00:39 /var/lib/mysql/mysql-bin.000011

c. 立即刷新并备份出binlog

[root@mysql-server ~]# mysqladmin -uroot -pABC123.com flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-server ~]# cp /var/lib/mysql/mysq-bin.* /root/mysql-bin

注：根据时间点及前一个binlog可以知道发现问题时刻前binlog日志
d. 检查二进制日志中可能出现的问题，恢复binlog生成sql语句

[root@mysql-server mysql-bin]# mysqlbinlog mysql-bin.000010 >> bin.log
[root@mysql-server mysql-bin]# mysqlbinlog mysql-bin.000011 >> bin.log
[root@mysql-server mysql-bin]# mysqlbinlog mysql-bin.000012 >> bin.log

注：检查为mysql-bin.000011日志中出现的问题
e. 恢复凌晨备份

[root@mysql-server mysql-bin]# cd /backup/
[root@mysql-server backup]# ls
all_2020-08-03.sql  test1_2020-08-03.sql.gz  test2_2020-08-04.sql.gz
[root@mysql-server backup]# gunzip test2_2020-08-04.sql.gz
[root@mysql-server backup]# ls
all_2020-08-03.sql  test1_2020-08-03.sql.gz  test2_2020-08-04.sql
[root@mysql-server backup]# mysql -uroot -pABC123.com -e 'create database test3'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-server backup]# mysql -uroot -pABC123.com test3 < test2_2020-08-04.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

f. 恢复增量备份

[root@mysql-server mysql-bin]# mysqlbinlog mysql-bin.000010 > test3.sql
[root@mysql-server mysql-bin]# mysql -uroot -pABC123.com < test3.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

注：上面为分步恢复；也可以如下，将二进制日志文件导入一个文件后恢复
先找出凌晨时刻的二进制日志文件，然后找出出问题时刻的二进制文件

注：此次找到问题出处为 mysql-bin.000009

# 单库
[root@mysql-server mysql-bin]# mysqlbinlog -d test2 mysql-bin.000009 --start-position=344 > test2-bin.sql
[root@mysql-server mysql-bin]# mysqlbinlog -d test2 mysql-bin.000009 --start-position=233 > test2-bin.sql
[root@mysql-server mysql-bin]# mysqlbinlog -d test2 mysql-bin.000010 >> test2-bin.sql
WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead.

[root@mysql-server mysql-bin]# mysql -uroot -pABC123.com test3 < test2-bin.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1007 (HY000) at line 24: Can't create database 'test2'; database exists
# 此时报错，因为我们刚才恢复了test2库，现在再次删掉即可


mysql> drop database test2;
Query OK, 1 row affected (0.02 sec)

[root@mysql-server mysql-bin]# mysql -uroot -pABC123.com test3 < test2-bin.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

方法2：在增量备份时，将二进制文件转化成sql语句，然后删除其中的错误操作行，修改。sql，在导入即可

# 再次删除test2数据库
mysql> drop database test2;
Query OK, 1 row affected (0.01 sec)

[root@mysql-server mysql-bin]# mysqlbinlog mysql-bin.000011 > 11.sql
[root@mysql-server mysql-bin]# vim 11.sql
# 删除刚才指其中的drop database test2;
[root@mysql-server mysql-bin]# mysql -uroot -p < test2-bin.sql
Enter password:

3.4 自动化备份

使用脚本实现自动化备份

（1）编写脚本

[root@mysql-server ~]# vim mysql_back.sh
#!/bin/bash
back_dir=/backup/
back_file=`date +%F`_all.sql
user=root
pass=ABC123.com

if [ ! -d /backup ]
then
    mkdir -p /backup
fi

# 备份并截断日志
mysqldump -u$user -p$pass --lock-all-tables --routines --events --triggers --master-data=2 --flush-logs --all-databases > $back_dir$back_file

# 只保留最近一个月的备份
find $back_dir -mtime +30 -exec rm -rf {} \;

（2）测试：

[root@mysql-server ~]# chmod a+x mysql_back.sh
[root@mysql-server ~]# chattr +i  mysql_back.sh
[root@mysql-server ~]# sh mysql_back.sh
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-server ~]# ll /backup/
总用量 1016
-rw-r--r--. 1 root root 1039014 8月   4 02:11 2020-08-04_all.sql

（3）配置计划任务

[root@mysql-server ~]# crontab -e
# everyday backup all databases of mysql
0 2 * * * ./etc/profile;sh /root/mysql_back.sh

3.5 使用物理备份方式复制数据文件，在恢复

cp ；tar
注：此种方法只能用于同种安装方式的mysql服务器

（1） 停止服务

[root@mysql-server ~]# systemctl stop mysqld
[root@mysql-server ~]# netstat -tunalp |grep 3306
[root@mysql-server ~]#

（2） 备份数据文件

[root@mysql-server ~]# cd /var/lib/mysql
[root@mysql-server mysql]# mkdir -p /server/backup
[root@mysql-server mysql]# tar czf /server/backup/all.`date +%F`.tar.gz *

（3）将备份文件拷贝到目标服务器 192.168.10.102

[root@mysql-server mysql]# scp -p /server/backup/all.2020-08-04.tar.gz  192.168.10.102:/tmp

（4）目标服务器停止服务

[root@server02 ~]# systemctl stop mysqld

（5） 解压文件只目标服务器数据文件夹

[root@mysql01 ~]# tar xf /tmp/all.2020-08-04.tar.gz -C /var/lib/mysql/

（6）修改权限

[root@server02 ~]# chown -R mysql:mysql /usr/local/mysql/data/

（7）启动目标服务器测试

[root@mysql01 ~]# systemctl start mysqld

------------------------------------------------------------------------------------------------------- 返回目录