Mysql的备份和恢复

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 '
'
-> from cai limit 50;

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

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

-T,--tab=name

例：导出sqoop库的tb1表(http://blog.itpub.net/31386161/viewspace-2130313/错误处理方法)

[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.cnblogs.com/kerrycode/p/4565669.html（错误解决办法）

[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快照实现几乎热备的数据备份与恢复。

重要：使用xtrabackup做备份恢复

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!表示成功

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