MySQL的备份和恢复

共计 5887 个字符，预计需要花费 15 分钟才能阅读完成。

1. 数据库的备份

备份 = 拷贝加管理，防止数据的丢失同时记录用户的操作记录。其中最有效的备份是对 IT 架构进行备份。

原则：

（1）数据库要定期备份，备份的周期应当根据应用数据系统可承受的恢复时间，而且定期备份的时间应当在系统负荷最低的时候进行。对于重要的数据，要保证在极端情况下的损失都可以正常恢复。

（2）定期备份后，同样需要定期做恢复测试，了解备份的正确可靠性，确保备份的有意义的、可恢复的。

（3）根据系统需要来确定是否采用增量备份，增量备份只需要备份每天的增量数据，备份花费的时间少，对系统负载的压力也小。缺点是恢复的时候需要加载之前所有的备份数据。恢复时间较长。

（4）确保 MySQL 打开了 log-bin 选项，mysql 在做完整恢复或者基于时间点恢复的时候都需要 binlog。

（5）可以考虑异地备份。

2. 逻辑备份和恢复

（1）逻辑备份：也可以成为文件级备份，是将数据库中的数据备份为一个文本文件，而备份大小取决于文件大小。并且该文本文件是可以移植到其他机器上的，甚至是不同硬件结构的机器。

使用 mysqldump 命令生成 insert 语句备份

语法：mysqldump [arguments] >file_name.sql

使用帮助如下：

[root@cai ~]# mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump –help
例：备份所有数据库，语句如下：[root@cai dumpback]# mysqldump -uroot -p123456 --all-databases >/tmp/dumpback/alldb.sql
备份某些数据库，语法如下：[root@cai dumpback]# mysqldump -uroot -p123456 --databases aa >/tmp/dumpback/data_aa.sql
备份某数据库中的表，语句如下：[root@cai dumpback]# mysqldump -uroot -p123456 aa cairui >/tmp/dumpback/aa_cairui.sql
查看备份内容，语句如下：[root@cai dumpback]# more aa_cairui.sql

如何保证数据备份的一致性？有以下两种方法：

①同一时刻取出所有数据

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

例：[root@cai dumpback]# mysqldump -uroot -p123456 --single-transaction aa >/tmp/dumpback/aa_backup.sql

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

通过锁表参数未完成

LOCK-TABLES 每次锁定一个数据库的表，此参数默认为true

LOCK-ALL-TABLES 一次锁定所有的表，适用于 dump 的表分别处于各个不同的数据库中的情况。

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

①通过 select … to outfile from … 命令

参数如下：

fields escaped by [‘name’]：在 sql 语句中需要转义的字符

fields terminated by:设定每两个字段之间的分隔符

fields [optionally] enclosed by‘name’:包装，有optionally数字类型不被包装，否则全包装。

lines terminated by‘name’:行分隔符，即每记录结束时添加的字符。

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

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

用此方法可以生成一个文本数据和一个对应的数据库结构创建脚本，主要参数：

-T,–tab=name

例：导出 sqoop 库的 tb1 表(http://www.linuxidc.com/Linux/2017-08/146186.htm 错误处理方法)

[root@cai ~]# mysqldump -uroot -p123456 -T /tmp sqoop tb1 --fields-enclosed-by=\" --fields-terminated-by=,

（2）逻辑备份的恢复

[root@cai dumpback]# mysql -uroot -p123456 -D cai </tmp/dumpback/aa_cairui.sql

将 aa 底下的 cairui 表恢复到 cai database 下

②纯文本文件的恢复

使用 mysqllimport 工具恢复，此工具可以用于恢复生成 txt 和 sql 两文件，所以保证 txt 文件对应的数据库的表存在。

首先恢复表结构，语句如下：[root@cai tmp]# mysql -uroot -p123456 -D aa </tmp/tb1.sql
恢复数据，语句如下：[root@cai tmp]# mysqlimport -uroot -p123456 aa --fields-enclosed-by=\" --fields-terminated-by=, /tmp/tb1.txt

3. 物理备份和恢复

物理备份比逻辑备份速度要快，分为以下两种：

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

b。热备份：对于不同的存储引擎方法也不同

4. 各种备份与恢复方法的具体实现

（1）利用 select into outfile 实现数据备份与还原

①把需要备份的数据备份出来

mysql> select * from students;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | li   |   36 |
|    2 | wang |   26 |
|    3 | cai  |   12 |

mysql> select * from students where age>30 into outfile '/tmp/students.txt';//将年龄大于 30 的同学的信息备份出来

发现 students。txt 是文本文件，不能直接导入数据库，需要使用 load data infile 恢复到 mysql 服务器端，删除年龄大于 30 的用户，模拟数据被破坏。

mysql> delete from students where age>30;
mysql> load data infile '/tmp/students.txt' into table students;

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

①先给数据库做完全备份

http://www.linuxidc.com/Linux/2017-08/146185.htm（错误解决办法）

[root@cai tmp]# mysqldump -uroot -p123456 --single-transaction --master-data=2 --databases hellodb >/backup/hellodb_'data+%F'.sql

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

mysql> use hellodb;
Database changed
mysql> create table tb1(id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tb1 values(1),(2),(3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

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

④回到服务器端

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

⑤做增量备份

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

⑥再回到服务器

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

⑦导出这次二进制日志

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

⑧先让 mysql 离线

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

⑨模拟数据库破坏

mysql>drop database hellodb;

⑩开始恢复数据

#mysql </backup/hellodb_2013-09-08.sql 导入完全备份文件
#mysql </backup/hellodb_2013-09-08_05.sql 导入增量备份文件
#mysql <hellodb.sql//导入二进制文件

（1 真正生产环境中，应该导出的是整个 mysql 服务器中的数据，而不是单个数据库，因此应该使用 -all-databases。

(2 在导出二进制日志的时候，可以直接复制文件即可，但是要注意的是，备份之前滚动下日志。

（3 利用 LVM 快照实现几乎热备的数据备份与恢复。

1. 优势

（1）快速可靠的进行完全备份

（2）在备份的过程中不影响事务

（3）支持数据流、网络传输、压缩，所以它可以有效地节约磁盘资源和网络带宽。

（4）可以自动备份检验数据的可用性。

a。安装 rabackup

[root@cai tools]# tar zxf percona-xtrabackup-2.4.2-Linux-x86_64.tar.gz 
[root@cai tools]# 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
[root@cai tools]#  echo "export PATH=\$PATH:/application/xtrabackup/bin" >> /etc/profile
[root@cai tools]# . /etc/profile

b。全量备份

mysql> create user 'backup'@'%' identified by '123456'; 创建备份用户
mysql> grant all on *.* to 'backup'@'%'; 授权
mysql> flush privileges; 使生效
mysql> select * from cairui;
+------+------+
| id   | name |
+------+------+
|    1 | li   |
|    2 | wang |
测试的表为 cairui 在 cai 底下

[root@cai backup]# innobackupex –user=root –password=123456 /data/backup/（备份到 backup 下）170511 16:46:14 completed OK!表示成功

MySQL 的备份和恢复

xtrabackup_checkpoints：备份类型、备份状态和LSN（日志序列号）范围信息。

xtrabackup_binlog_info：mysql服务器当前正在使用的二进制文件及备份这一刻为止的二进制日志事件的位置。

xtrabackup_logfile：非文本文件，xtrabackup 自己的日志文件。

backup-my.cnf：备份时数据文件中关于 mysql 的配置。

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

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-08/146184.htm