12 数据备份
备份介绍
为何要备份数据
在生产环境中，我们数据库可能会遭遇各种各样的不测，从而导致数据丢失，大致可以分为以下几种
硬件故障
软件故障
自然灾害
黑客攻击
误操作（占比最大）
我们要备份什么
数据
二进制日志，innodb事务日志
代码（存储过程、存储函数、触发器、事件调度器）
服务器配置文件
备份的类型
-
冷备
当数据库进行备份时，数据库不能进行读写操作，即数据库要下线
-
温备
不停库、不停服来进行备份，会锁表，阻止用户的写入，即当数据库进行备份时，数据库的读操作可以正常执行，但是不可以执行写操作
-
热备（建议）：不停库、不停服来进行备份，也不会锁表阻止用户的写入，即当数据库进行备份的时候，数据库的读写操作均不是受影响
备份的工具
设计备份策略
备份数据的策略要根据不同的应用场景进行定制, 大致有几个参考数值, 我们可以根据这些数值从而定制符合特定环境中的数据备份策略。
能够容忍丢失多少数据
恢复数据需要多长时间
需要恢复哪一些数据
三种备份策略及应用场景
针对不同的场景下, 我们应该制定不同的备份策略对数据库进行备份, 一般情况下, 备份策略一般为以下三种：
直接cp，tar复制数据库文件(基本上可以忽略这一种了，“手动狗头”)
mysqldump+复制bin logs
lvm2快照+复制bin logs
xtrabackup
以上的几种解决方案分别针对于不同的场景
如果数据量较小, 可以使用第一种方式, 直接复制数据库文件
如果数据量还行, 可以使用第二种方式, 先使用 mysqldump对数据库进行完全备份, 然后定期备份binary log达到增量备份的效果
如果数据量一般, 而又不过分影响业务运行, 可以使用第三种方式, 使用lvm2的快照对数据文件进行备份, 而后定期备份binary log达到增量备份的效果
如果数据量很大, 而又不过分影响业务运行, 可以使用第 四种方式, 使用xtrabackup进行完全备份后, 定期使用xtrabackup进行增量备份或差异备份
备份实战
使用mysqldump + 复制binary logs备份
mysqldump命令使用
语法:
mysqldump -h 服务器 -u 用户名 -p密码 选项与参数 > 备份文件.sql
选项与参数:
-A/--all-databases 所有库
-B/--databases bbs db1 db2 多个数据库
db1 数据库名
db1 t1 t2 db1数据库的表t1、t2
-F 备份的同时刷新bin log
-R 备份存储过程和函数数据（如果开发写了函数和存储过程，就备，没写就不备）
--triggers 备份触发器数据（现在都是开发写触发器）
-E/--events 备份事件调度器
-d 仅表结构
-t 仅数据
--master-data=1
备份文件中 change master语句是没有注释的，默认为1
用于已经制作好了主从，现在想扩展一个从库的时候使用。
如此备份，扩展添加从库时导入备份文件后便不需要再加mater_pos了。
change matser to
master_host='10.0.0.111'
master_user='rep'
master_password=123
master_log_pos=120
master_log_file='master-bin.000001'
--master-data=2
备份文件中change master语句是被注释的
--lock-all-tables, 提交请求锁定所有数据库中的所有表，以保证数据的一致性。
这是一个全局读锁，并且自动关闭--single-transaction和--lock-tables选项，他们是互斥的。
对于支持事务的表，例如InnoDB和BDB，推荐使用--single-transaction选项，因为它根本不需要锁定表。
--single-transaction: 快照备份 （搭配--master-data可以做到热备）
该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎，仅InnoDB。
本选项和--lock-tables选项是互斥的，不能同时存在，因为LOCK TABLES会使任何挂起的事务隐式提交。
# 完整语句
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction > /tmp/full.sql
# 文件太大时可以压缩 gzip ，但是gzip不属于mysql独有的命令，可以利用管道
# --master-data=2 --single-transaction 这两个选项一起使用等于热备
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction | gzip >/tmp/full$(date +%F).sql.gz
# 导出时压缩了，导入时需要解压，可以使用zcat命令，很方便
zcat /tmp/full$(date +%F).sql.gz | mysql -uroot -p123
案例
1. 先打开binlog日志
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log-bin=/service/mysql/mybinlog
binlog_format='row' # (row,statement,mixed)
binlog_rows_query_log_events=on
max_binlog_size=100M
# 重启数据库
[root@db01 ~]# systemctl restart mysql
2. 登录数据库，插入测试数据
mysql> create database db01;
Query OK, 1 row affected (0.00 sec)
mysql> use db01;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.00 sec)
mysql> insert t1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
3. 在命令行执行命令，进行全量备份
[root@db01 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction | gzip > /tmp/full.sql.gz
Warning: Using a password on the command line interface can be insecure.
4. 在命令行执行命令，刷新bin log，便于日后查找(或者直接在上一条sql语句中加上-F参数也可以)
[root@db01 ~]# mysql -uroot -p123 -e "flush logs"
5. 登录数据库，再插入一些数据，模拟增量，这些数据写入了新的binlog
mysql> use db01;
Database changed
mysql> insert t1 values(4),(5),(6);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
模拟数据丢失
mysql> drop database db01;
Query OK, 1 row affected (0.01 sec)
# 查看事件详细信息,我们在做全量备份的时候刷新了bin log日志,所以我们查看的日志文件为mybinlog.000002
mysql> show binlog events in 'mybinlog.000002';
[root@db01 ~]# mysqlbinlog /service/mysql/mybinlog.000002
导出增量备份日志的数据
# 注意:导出bin log时不要加选项--base64-output
[root@db01 ~]# mysqlbinlog /service/mysql/mybinlog.000002 --start-position=192 --stop-position=338 > /tmp/1.sql
恢复数据
1. mysql数据导入时，临时关闭bin log，不要将恢复数据的写操作也记入
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
2. 先恢复全量
# 如果不是压缩包直接使用:source /tmp/full.sql
mysql> system zcat /tmp/full.sql.gz | mysql -uroot -p123
Warning: Using a password on the command line interface can be insecure.
3. 再恢复增量
mysql> source /tmp/1.sql;
4. 开启二进制日志，恢复数据的写操作也记入bin log文件
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
LVM备份
1. 添加硬盘; 这里我们直接实现SCSI硬盘的热插拔, 首先在虚拟机中添加一块硬盘, 无需重启
echo '- - -' > /sys/class/scsi_host/host0/scan
echo '- - -' > /sys/class/scsi_host/host1/scan
echo '- - -' > /sys/class/scsi_host/host2/scan
2. 创建逻辑卷
pvcreate /dev/sdb
vgcreate vg1 /dev/sdb
lvcreate -n lv1 -L 5G vg1
3. 格式化制作文件系统并挂载到指定目录下
mkfs.xfs /dev/mapper/vg1-lv1
# 将/var/lib/mysql的文件都暂时移动到/opt目录下
mv /var/lib/mysql/* /opt/
# 挂载
[root@db01 ~]# mount /dev/mapper/vg1-lv1 /var/lib/mysql
# 移动回去
4. 先打开binlog日志
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log-bin=/service/mysql/mybinlog
binlog_format='row' # (row,statement,mixed)
binlog_rows_query_log_events=on
max_binlog_size=100M
# 重启数据库
[root@db01 ~]# systemctl restart mysql
5. 往数据库内插入测试数据
create database db01;
use db01;
create table t1(id int);
insert t1 values(1),(2),(3);
全备
mysql> FLUSH TABLES WITH READ LOCK; # 锁定所有表
Query OK, 0 rows affected (0.00 sec)
[root@node1 lvm_data]# lvcreate -L 1G -s -n lv1_from_vg1_snap /dev/vg1/lv1 # 创建快照卷
# 刷新bin log日志，便于日后查找
[root@db01 ~]# mysql -uroot -p123 -e "flush logs"
mysql> UNLOCK TABLES; # 解锁所有表
Query OK, 0 rows affected (0.00 sec)
[root@node1 lvm_data]# mkdir /snap1 # 创建文件夹
[root@node1 lvm_data]# mount -o nouuid /dev/vg1/lv1_from_vg1_snap /snap1
[root@localhost snap1]# cd /snap1/
[root@localhost snap1]# tar cf /tmp/mysqlback.tar *
[root@localhost snap1]# umount -l /snap1/
[root@localhost snap1]# lvremove vg1/lv1_from_vg1_snap
增备
模拟数据丢失
use db01;
create table t2(id int);
insert t2 values(1),(2),(3);
drop database db01;
# 查看事件详细信息,我们在做全量备份的时候刷新了bin log日志,所以我们查看的日志文件为mybinlog.000002
mysql> show binlog events in 'mybinlog.000002';
[root@db01 ~]# mysqlbinlog /service/mysql/mybinlog.000002
导出增量备份日志的数据
# 注意:导出bin log时不要加选项--base64-output
[root@db01 ~]# mysqlbinlog /service/mysql/mybinlog.000002 --start-position=120 --stop-position=468 > /tmp/1.sql
1. 先恢复全量
[root@db01 ~]# tar -xf /tmp/mysqlback.tar -C /service/
2. 再恢复增量
# mysql数据导入时，临时关闭bin log，不要将恢复数据的写操作也记入
mysql> set sql_log_bin=0;
mysql> source /tmp/1.sql;
# 开启二进制日志，恢复数据的写操作也记入bin log文件
mysql> set sql_log_bin=1;
Xtrabackup备份
Xtrabackup是由percona提供的mysql数据库备份工具，据官方介绍，这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。特点：
-
备份过程快速、可靠；
-
备份过程不会打断正在执行的事务；
-
能够基于压缩等功能节约磁盘空间和流量；
-
自动实现备份检验；
-
还原速度快；
使用xtrabackup使用InnoDB能够发挥其最大功效, 并且InnoDB的每一张表必须使用单独的表空间, 我们需要在配置文件中添加 innodb_file_per_table = ON 来开启。
安装脚本，当然你也可以一个个执行
#!/usr/bin/env bash
cd /opt
wget https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install percona-release-latest.noarch.rpm -y
yum install percona-xtrabackup-24 -y
全备
# 创建备份目录
[root@db01 ~]# mkdir /backup
# 全备
[root@db01 ~]# innobackupex --user=root --password=xxx! /backup/full
# 查看
[root@db01 ~]# cd /backup/
[root@db01 backup]# ls
full
[root@db01 backup]# cd full/
[root@db01 full]# ls
2021-07-21_20-31-12
# 去掉时间戳
[root@db01 full]# innobackupex --user=root --password=xxx! --no-timestamp /backup/full
[root@db01 full]# ls
backup-my.cnf mysql xtrabackup_checkpoints
egon_test performance_schema xtrabackup_info
ib_buffer_pool sys xtrabackup_logfile
ibdata1 xtrabackup_binlog_info
# 补充：关于备份目录下新增的文件说明，可用cat命令查看 xtrabackup_checkpoints 存储系统版本号，增备的时候会 用到xtrabackup_info 存储UUID，数据库是由自己的UUID的， 如果相同，做主从会有问题 xtrabackup_logfile 就是redo
增备
--incremental:开启增备功能
--incremental-basedir:上一次备份的路径
# 增备
innobackupex --user=root --password=xxx! --no-timestamp --incremental --incremental-basedir=/backup/full /backup/xtra
# 查看
[root@db01 ~]# cd /backup/
[root@db01 backup]# ls
full xtra
# 查看是否衔接
[root@db01 backup]# cat full/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 5908495
last_lsn = 5908504
compact = 0
recover_binlog_info = 0
flushed_lsn = 5908504
[root@db01 backup]# cat xtra/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 5908495
to_lsn = 5908495
last_lsn = 5908504
compact = 0
recover_binlog_info = 0
flushed_lsn = 5908504
案例--备份
# 开启binlog
vim /etc/my.cnf
[mysqld]
server_id=1 -- 服务ID,主从库必须不一样,必须指定
log-bin=/service/mysql/mybinlog -- 此变量用于控制是否开启二进制日志，而且这是一个只读变量，默认值为OFF
binlog_format='row' -- (row,statement,mixed)，不建议随意去修改binlog工作模式
binlog_rows_query_log_events=on -- 打开才能查看详细记录，默认为off
max_binlog_size=100M -- 设置单个二进制日志文件的最大大小，超出100M。MySQL默认会新建一个文件记录日志
# 你权限有问题自己解决
# 重启mysql
systemctl restart mysqld
# 插数据
create database egon_test;
use egon_test;
create table employee(id int not null unique auto_increment,name varchar(20) not null,sex enum('male','female') not null default 'male', age int(3) unsigned not null default 28,hire_date date not null,post varchar(50),post_comment varchar(100),salary double(15,2),office int, depart_id int);#插入记录#三个部门：教学，销售，运营
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values('egon','male',18,'20170301','God',7300.33,401,1),('alex','male',78,'20150302','teacher',1000000.31,401,1),('wupeiqi','male',81,'20130305','teacher',8300,401,1),('yuanhao','male',73,'20140701','teacher',3500,401,1),('liwenzhou','male',28,'20121101','teacher',2100,401,1),('jingliyang','female',18,'20110211','teacher',9000,401,1),('jinxin','male',18,'19000301','teacher',30000,401,1);
# 第一次全备
innobackupex --user=root --password=xxx! --no-timestamp /backup/full
# 周一继续插数据
use egon_test;
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values('egon','male',18,'20170301','God',7300.33,401,1),('alex','male',78,'20150302','teacher',1000000.31,401,1),('wupeiqi','male',81,'20130305','teacher',8300,401,1),('yuanhao','male',73,'20140701','teacher',3500,401,1),('liwenzhou','male',28,'20121101','teacher',2100,401,1),('jingliyang','female',18,'20110211','teacher',9000,401,1),('jinxin','male',18,'19000301','teacher',30000,401,1);
# 周一增备
innobackupex --user=root --password=xxx! --no-timestamp --incremental --incremental-basedir=/backup/full /backup/xtra
# 周二继续插数据
use egon_test;
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values('egon','male',18,'20170301','God',7300.33,401,1),('alex','male',78,'20150302','teacher',1000000.31,401,1),('wupeiqi','male',81,'20130305','teacher',8300,401,1),('yuanhao','male',73,'20140701','teacher',3500,401,1),('liwenzhou','male',28,'20121101','teacher',2100,401,1),('jingliyang','female',18,'20110211','teacher',9000,401,1),('jinxin','male',18,'19000301','teacher',30000,401,1);
# 周二增备
innobackupex --user=root --password=xxx! --no-timestamp --incremental --incremental-basedir=/backup/xtra /backup/xtra1
# 周三插入数据
use egon_test;
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values('egon','male',18,'20170301','God',7300.33,401,1),('alex','male',78,'20150302','teacher',1000000.31,401,1),('wupeiqi','male',81,'20130305','teacher',8300,401,1),('yuanhao','male',73,'20140701','teacher',3500,401,1),('liwenzhou','male',28,'20121101','teacher',2100,401,1),('jingliyang','female',18,'20110211','teacher',9000,401,1),('jinxin','male',18,'19000301','teacher',30000,401,1);
# 然后数据库没了
# bomb 纱卡拉卡
[root@db01 mysql]# rm -rf /var/lib/mysql
# 恢复思路
1. 停业务,挂维护页 2. 查找可用备份并处理备份:full+inc1+inc2 3. 找到binlog中: inc2 到 故障时间点的binlog 4. 恢复全备+增量+binlog 5. 验证数据 6. 起业务,撤维护页
案例--恢复
# 整理full
[root@db01 backup]# innobackupex --apply-log --use-memory=3G --redo-only /backup/full
# 合并周一的增量到full并整理
[root@db01 backup]# innobackupex --apply-log --use-memory=3G --redo-only --incremental-dir=/backup/xtra /backup/full
# 合并周二的增量到full并整理
[root@db01 backup]# innobackupex --apply-log --use-memory=3G --redo-only --incremental-dir=/backup/xtra1 /backup/full
# 整理full
[root@db01 backup]# innobackupex --apply-log --use-memory=3G /backup/full
# 查看周三的binlog_info
[root@db01 backup]# cat xtra1/xtrabackup_binlog_info
mybinlog.000001 4350
[root@db01 backup]# mysqlbinlog /data/binlog/mybinlog.000001 -- start-position=4350 > /backup/binlog.sql # 起始点选择上面的
# 授权
chown -R mysql.mysql /var/lib/mysql
systemctl start mysqld
source /backup/binlog.sql
数据库的快速导入和导出
在公司中，如果运营或者产品手里有几千万甚至几亿条数据，要求你将其导入数据中，请问如何做？
如果你依据运营或产品交给你的数据文件直接使用insert语句，一行一行地批量插入，那至少需要1-2天时间才能插入完毕，此时我们可以用load data infile语句。 load data infile语句可以从一个文本文件中，将数据以很高的速度读入一个表中。MySQL官方文档也说明了，该方法比一次性插入一条数据性能快20倍。此外，mysql也支持快速导出语句select into outfile 。
使用MySQL的select into outfile、load data infile快速导出导入数据，12G的数据导出用时3分钟左右，导入用时4分钟左右（执行时间根据机器的配置会有所不同，不具有参考价值）。
快速导出
语法：
select ... into outfile 导出文本文件
要想导出成功，需要设置安全目录才行
vim /etc/my.cnf
[mysqld]
secure-file-priv=/tmp # 指定导出的目录
SELECT * FROM db1.t1
INTO OUTFILE '/tmp/db1_t1.txt'
FIELDS TERMINATED BY ',' -- 定义字段分隔符
OPTIONALLY ENCLOSED BY '"' -- 定义字符串使用什么符号括起来
LINES TERMINATED BY '\n'; -- 定义换行符
快速导入
mysql> delete from t1;
mysql> create table new_t1(表结构与文件中数据保持一致);
mysql> LOAD DATA INFILE '/tmp/db1_t1.txt'
INTO TABLE new_db.new_t1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
花里胡哨导出
mysql -u root -p123 -e 'select * from db1.t1' > /tmp/db1_t1.txt
mysql -u root -p123 --xml -e 'select * from db1.t1' > /tmp/db1_t1.xml -- xml文件跨平台性
mysql -u root -p123 --html -e 'select * from db1.t1' > /tmp/db1_t1.html
案例
# 建表
create database db01;
use db01;
create table employee(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum("male","female") not null default "male",
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int,
depart_id int
);
# 插入数据
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
("sun","male",18,"20170301","猥琐欲为",1000.22,401,1),
("haha","male",78,"20150302","teacher",10000.31,401,1),
("xixi","male",81,"20130305","teacher",8300.31,401,1),
("dudu","male",72,"20150312","teacher",3100,401,1);
# 快速导出数据
# 修改配置文件,指定导出的目录
vim /etc/my.cnf
[mysqld]
secure-file-priv=/tmp
# 重启数据库
systemctl restart mysql
# 导出数据
mysql> SELECT * FROM db01.employee
-> INTO OUTFILE '/tmp/employee.txt'
-> FIELDS TERMINATED BY ','
-> OPTIONALLY ENCLOSED BY '"'
-> LINES TERMINATED BY '\n';
-> LINES TERMINATED BY '\n';
Query OK, 4 rows affected (0.00 sec)
# 查看导出的数据
cat /tmp/employee.txt
# 1,"sun","male",18,"2017-03-01","猥琐欲为",\N,1000.22,401,1
# 2,"haha","male",78,"2015-03-02","teacher",\N,10000.31,401,1
# 3,"xixi","male",81,"2013-03-05","teacher",\N,8300.31,401,1
# 4,"dudu","male",72,"2015-03-12","teacher",\N,3100.00,401,1
# 快速导入数据
# 创建表，表结构与数据保持一致
create table test(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum("male","female") not null default "male",
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int,
depart_id int
);
# 导人数据
mysql> LOAD DATA INFILE '/tmp/employee.txt'
-> INTO TABLE db01.test
-> FIELDS TERMINATED BY ','
-> OPTIONALLY ENCLOSED BY '"'
-> LINES TERMINATED BY '\n';
Query OK, 4 rows affected (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
# 查看导入的新表数据
mysql> select * from db01.test;
+----+------+------+-----+------------+--------------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+------+-----+------------+--------------+--------------+----------+--------+-----------+
| 1 | sun | male | 18 | 2017-03-01 | 猥琐欲为 | NULL | 1000.22 | 401 | 1 |
| 2 | haha | male | 78 | 2015-03-02 | teacher | NULL | 10000.31 | 401 | 1 |
| 3 | xixi | male | 81 | 2013-03-05 | teacher | NULL | 8300.31 | 401 | 1 |
| 4 | dudu | male | 72 | 2015-03-12 | teacher | NULL | 3100.00 | 401 | 1 |
+----+------+------+-----+------------+--------------+--------------+----------+--------+-----------+
4 rows in set (0.00 sec)
