mysql 备份 hadoop_MySQL备份与恢复实战

一、什么是备份与增量恢复

(一)什么是全量备份与增量备份

1、全量备份

全量备份就是将数据库中所有数据进行备份。

--1、innodb--备份所有库

mysqldump -uroot -p -S /data/3306/mysql.sock -F --single-transaction -A -B|gzip > /temp/allbak_$(date+%F).sql.gz

--备份单个库

mysqldump -uroot -p -S /data/3306/mysql.sock -F --single-transaction -B crm|gzip > /temp/crmbak_$(date+%F).sql.gz

--2、myisam--备份所有库

mysqldump -uroot -p -S /data/3306/mysql.sock -F --lock-all-tables -A -B|gzip > /temp/allbak_$(date+%F).sql.gz

--备份单个库

mysqldump -uroot -p -S /data/3306/mysql.sock -F --lock-all-tables -B crm|gzip > /temp/crmbak_$(date+%F).sql.gz

2、增量备份

增量备份是上次全量备份后，更新的新数据。对于MySQL来讲，binlog日志就是其增量数据。

(二)增量恢复

1、什么时候需要增量恢复

利用二进制日志以及全备进行恢复的过程就是增量恢复。那么它一般在下面的几种场景中需要使用：

迁移或者升级数据库

增加从库

人为进行DML，DDL语句，比如：drop database db;等，从库也会执行。

当然，对于主从库宕机是不需要进行增量恢复的，如果主库宕机，直接切换到从库即可。

2、增量恢复的必备条件

开启log-bin日志功能(在my.cnf中配置)，主库和从库都要开启该功能

全备文件

全备之后到出问题时刻的增量binlog日志文件

二、备份与增量恢复实战

(一)图解备份与恢复

加入现在有这样一个场景，主库正在稳定的提供写的服务，突然在下午16:00由于员工操作失误导致写的库crm被误删，在16:10分发现问题，然后进行恢复：

问题过程

(1)夜晚0:00数据库做定时备份，并且刷新binlog，由之前的mysql-bin000001刷新为mysql-bin000002，之后所有的数据库更新操作记录在新的binlog文件中。

(2)第二天16::00出现错误操作，删除了crm库，导致后续的数据无法写入。

(3)16:10发现问题，并且进行恢复

恢复过程

(1)导入全备sql文件

(2)恢复0:00-16:00的增量数据

16;00之后由于数据库已经被删除，所以不需要关注这段时间。

(二)实战演练

1、数据准备

--创建数据库

mysql> create database crm default character setutf8 collate utf8_general_ci;

Query OK,1 row affected (0.02sec)--创建表

mysql> create tableuserinfo(-> id int(10) not null primary keyauto_increment,-> username varchar(40) not null

-> ) engine=InnoDB default charset=utf8;

Query OK,0 rows affected (0.05sec)--插入数据

mysql> insert into userinfo(id,username) value(1,'张三'),(2,'李四');

Query OK,2 rows affected (0.01sec)

Records:2 Duplicates: 0 Warnings: 0

--查看数据

mysql> select * fromuserinfo;+----+----------+

| id | username |

+----+----------+

| 1 | 张三 |

| 2 | 李四 |

+----+----------+

2 rows in set (0.00 sec)

2、数据全备

[root@hadoop-slave1 /]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -F -B --master-data=2 crm > /temp/crm_bak.sql

-F刷新binlog此时多生成了一个mysql-bin000007文件，后续的数据库更新操作记录于此

-B指定要备份的数据库crm

--master-data备份文件中记录binlog位置，用于主从同步

3、模拟继续写入数据

在0:00-16:00仍旧有用户写入数据：

--插入数据

[root@hadoop-slave1 /]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "use crm;insert into userinfo(id,username) value(3,'王五'),(4,'赵六');"--查看数据

[root@hadoop-slave1 /]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "use crm; select * fromuserinfo;"+----+----------+

| id | username |

+----+----------+

| 1 | 张三 |

| 2 | 李四 |

| 3 | 王五 |

| 4 | 赵六 |

+----+----------+

4、模拟删除数据库操作

在16:10删除数据库crm：

--删除数据库crm，以及查看数据库是否存在

[root@hadoop-slave1 /]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "drop databasecrm;show databases;"+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| reptest |

| test |

+--------------------+

5、增量恢复

发现问题，然后进行数据库的恢复，注意的是在恢复之前需要停止数据库服务，避免数据库再次被写入，这会给恢复数据造成很大的难度。当然本案例因为是已经删除了数据库，所以不存在再次写入的问题。

检查全备

--检查全备

[root@hadoop-slave1 /]# ll /temp/total672

-rw-r--r--. 1 root root 2197 Sep 14 23:04 crm_bak.sql

刷新binlog并且备份出binlog

首先通过全备，查看备份的位置点，位置点后的就是增量备份在/temp/crm_bak.sql文件中：

--

--Position to start replication or point-in-time recovery from--

--CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=107;

--

--Current Database: `crm`--

找到如上的位置点，增量备份的数据就是这个位置点之后的数据，所以需要备份的增量数据就是mysql-bin.000007文件中的数据。

现在知道的是增量备份的数据是mysql-bin.000007文件中的数据，但是在备份这个增量数据之前，还需要刷新binlog，因为在你备份时可能还会有数据写入，这样就会写入下一个binlog文件中。

--刷新binlog，可以看到多出一个 mysql-bin.000008文件

[root@hadoop-slave1 /]# mysqladmin -uroot -p123456 -S /data/3306/mysql.sock flush-logs[root@hadoop-slave1 /]# ll /data/3306total52...-rw-rw----. 1 mysql mysql 495 Sep 12 16:41 mysql-bin.000004

-rw-rw----. 1 mysql mysql 311 Sep 12 16:51 mysql-bin.000005

-rw-rw----. 1 mysql mysql 886 Sep 14 23:04 mysql-bin.000006

-rw-rw----. 1 mysql mysql 449 Sep 14 23:31 mysql-bin.000007

-rw-rw----. 1 mysql mysql 107 Sep 14 23:31 mysql-bin.000008

...

接下来再备份增量数据mysql-bin.000007：

[root@hadoop-slave1 /]# mysqlbinlog -d crm /data/3306/mysql-bin.000007>/temp/bin.sql

重要的是删除bin.sql中有问题的语句，就是drop crm语句。

增量恢复

此时已经有全备和增量的sql文件了。

[root@hadoop-slave1 /]# ll /temp/total676

-rw-r--r--. 1 root root 1786 Sep 14 23:36 bin.sql

-rw-r--r--. 1 root root 2197 Sep 14 23:04 crm_bak.sql

可进行恢复：

--全备恢复

[root@hadoop-slave1 /]# mysql -uroot -p123456 -S /data/3306/mysql.sock < /temp/crm_bak.sql--增量恢复

[root@hadoop-slave1 /]# mysql -uroot -p123456 -S /data/3306/mysql.sock < /temp/bin.sql--查看

[root@hadoop-slave1 /]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show databases;use crm; select * fromuserinfo;"+--------------------+

| Database |

+--------------------+

| information_schema |

| crm |

| mysql |

| performance_schema |

| reptest |

| test |

+--------------------+

+----+----------+

| id | username |

+----+----------+

| 1 | 张三 |

| 2 | 李四 |

| 3 | 王五 |

| 4 | 赵六 |

+----+----------+