MySQL的备份和恢复

物理备份和恢复

物理备份比逻辑备份速度要快，分为以下两种：
a. 冷备份：这种方式是最直接的备份方式，就是首先停掉数据库服务，然后cp数据文件，恢复时停止mysql，先进行操作系统级别恢复文件，然后重启mysql服务，使用mysqlbinlog工具恢复自备份以来的所有binlog。这种方式虽然简单，而且对所有引擎支持，但是有一个非常大的弊端就是需要关闭数据库服务。在当前的大多数信息系统都是不允许长期时间停机的。
b. 热备份：对于不同的存储引擎方法也不同

备份原则：

1. 数据库要定期备份，备份的周期应当根据应用数据系统可承受的恢复时间，而且定期备份的时间应当在系统负荷最低的时候进行。对于重要的数据，要保证在极端情况下的损失都可以正常恢复。
2. 定期备份后，同样需要定期做恢复测试，了解备份的正确可靠性，确保备份的有意义的、可恢复的。
3. 根据系统需要来确定是否采用增量备份，增量备份只需要备份每天的增量数据，备份花费的时间少，对系统负载的压力也小。缺点是恢复的时候需要加载之前所有的备份数据。恢复时间较长。
4. 确保MySQL打开了log-bin选项，mysql在做完整恢复或者基于时间点恢复的时候都需要binlog。
5. 可以考虑异地备份。

mysqldump命令生成insert语句备份

# 备份所有数据库
mysqldump -uroot -p123456 --all-databases >/tmp/dumpback/alldb.sql
#  备份某些数据库
mysqldump -uroot -p123456 --databases aa >/tmp/dumpback/data_aa.sql
# 备份某数据库中的表
mysqldump -uroot -p123456 aa cairui >/tmp/dumpback/aa_cairui.sql

查看备份一致性

# 查看备份内容
more aa_cairui.sql

保证数据备份的一致性,有以下两种方法：
①同一时刻取出所有数据

# 对于事务支持的存储引擎，如innodb或者bdb等，可以通过控制将整个备份过程在同一事物中，使用 -single-transaction test>test_backup.sql选项
mysqldump -uroot -p123456 --single-transaction aa >/tmp/dumpback/aa_backup.sql

②数据库中的数据处于静止状态

# 通过锁表参数未完成
# LOCK-TABLES 每次锁定一个数据库的表，此参数默认为true
# LOCK-ALL-TABLES 一次锁定所有的表，适用于dump的表分别处于各个不同的数据库中的情况。

生成特定格式的纯文本文件备份

①通过select … into outfile from … 命令

# fields escaped by [‘name’]：在sql语句中需要转义的字符
# fields terminated by:设定每两个字段之间的分隔符
# fields [optionally] enclosed by ‘name’:包装，有optionally数字类型不被包装，否则全包装。
# lines terminated by ‘name’:行分隔符，即每记录结束时添加的字符。

select * into outfile '/tmp/tb1.txt'
-> fields terminated by ','
-> optionally enclosed by '"'
-> lines terminated by '\n'
-> from cai limit 50;



select * from students;
+------+------+------+
| id | name | age |
+------+------+------+
# 将年龄大于30的同学的信息备份出来
select * from students where age>30 into outfile '/tmp/students.txt';
# 导出txt文本文件，不能直接导入数据库
# 需要使用load data infile恢复到mysql服务器端。
# 删除原来数据
delete from students where age>30;
# 导入数据
load data infile '/tmp/students.txt' into table students;

②通过mysqldump工具命令导出文本。

# 用此方法可以生成一个文本数据和一个对应的数据库结构创建脚本，主要参数：-T,--tab=name
mysqldump -uroot -p123456 -T /tmp sqoop tb1 --fields-enclosed-by=\" --fields-terminated-by=,

逻辑备份的恢复

# 将aa底下的cairui表恢复到cai database下
mysql -uroot -p123456 -D cai

纯文本文件的恢复

# 使用mysqllimport工具恢复，此工具可以用于恢复生成txt和sql两文件，所以保证txt文件对应的数据库的表存在。
# 恢复表结构
mysql -uroot -p123456 -D aa
# 恢复数据
mysqlimport -uroot -p123456 aa --fields-enclosed-by=\" --fields-terminated-by=, /tmp/tb1.txt

备份策略：完全备份+增量备份+二进制日志

①先给数据库做完全备份

mysqldump -uroot -p123456 --single-transaction --master-data=2 --databases hellodb >/backup/hellodb_'data+%F'.sql

②回到mysql服务器端更新数据

use hellodb; 
create table tb1(id int);
insert into tb1 values(1),(2),(3);

③先查看完全备份文件里面记录的位置

cat hellodb_data+%F.sql |less--CHANGE MASTER TO MASTER_LOG_FILE='cai-bin.000001', MASTER_LOG_POS=107; # 记录了二进制日志的位置

④回到服务器端

# 显示此时的二进制日志的位置，从备份文件里边记录的位置到我们此时的位置即为增量的部分。
show master status;
+----------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| cai-bin.000001 | 394 | | |
+----------------+----------+--------------+------------------+

⑤做增量备份

 mysqlbinlog --start-position=107 --stop-position=394 /application/mysql/data/cai-bin.000001 >/backup/hellodb_'data+$F_%H'.sql

⑥再回到服务器

insert into tb1 values(4),(5);
drop database hellodb;

⑦导出这次二进制日志

# 查看删除操作时二进制日志的位置
mysqlbinlog --start-position=394 /application/mysql/data/cai-bin.000001 
mysqlbinlog --start-position=394 --stop-position=587 /application/mysql/data/cai-bin.000001 >/tmp/hellodb.sql # 导出二进制日志

⑧先让mysql离线

set sql_log_bin=0;# 关闭二进制日志
flush logs; # 滚动下日志

⑨模拟数据库破坏

drop database hellodb;

⑩开始恢复数据

1. 真正生产环境中，应该导出的是整个mysql服务器中的数据，而不是单个数据库，因此应该使用-all-databases。
2. 在导出二进制日志的时候，可以直接复制文件即可，但是要注意的是，备份之前滚动下日志。
3. 利用LVM快照实现几乎热备的数据备份与恢复。
4. 重要：使用xtrabackup做备份恢复
   • 快速可靠的进行完全备份
   • 在备份的过程中不影响事务
   • 支持数据流、网络传输、压缩，所以它可以有效地节约磁盘资源和网络带宽。
   • 可以自动备份检验数据的可用性。

# 安装rabackup
tar zxf percona-xtrabackup-2.4.2-Linux-x86_64.tar.gz
mv percona-xtrabackup-2.4.2-Linux-x86_64 /application/percona-xtrabackup2.4.2[root@cai tools]#ln -s /application/percona-xtrabackup2.4.2/ /application/xtrabackup
echo "export PATH=\$PATH:/application/xtrabackup/bin" >> /etc/profile
./etc/profile

# 全量备份
create user 'backup'@'%' identified by '123456'; # 创建备份用户
grant all on *.* to 'backup'@'%'; # 授权
flush privileges; # 使生效

select *from cairui;
+------+------+
| id | name |
+------+------+
| 1 | li |

测试的表为cairui 在cai底下

innobackupex --user=root --password=123456 /data/backup/(备份到backup下)170511 16:46:14 completed OK!表示成功

• xtrabackup_checkpoints：备份类型、备份状态和LSN(日志序列号)范围信息。
• xtrabackup_binlog_info：mysql服务器当前正在使用的二进制文件及备份这一刻为止的二进制日志事件的位置。
• xtrabackup_logfile：非文本文件，xtrabackup自己的日志文件。
• backup-my.cnf：备份时数据文件中关于mysql的配置。

delete from bb where age >30;
# xtrabackup的增量备份代码如下：
innobackupex--user=root --password=123456 --incremental /data/backup/--incremental-basedir=/data/backup/2017-05-11_16-57-24