MySQL数据备份及恢复（1）

水深无声 2023-06-29 10:33 34阅读 0赞

### 前言 ###

MySQL备份一般采用全库备份加日志备份的方式，根据业务的需要，可以采用每周日凌晨1点进行完全备份以及每小时进行一次增量备份，这样在MySQL故障后可以使用完全备份和日志备份尽可能的去恢复最完整的数据。

### 一、binlog日志恢复 ###

MySQL的二进制日志记录着该数据库所有增删改的操作日志（前提是需要自己开启binlog），还包括了这些操作的执行时间，binlog的使用场景无外乎就是主从同步以及恢复数据库。开启binlog功能，需要编辑MySQL的主配置文件，如下：

#### 1、查看二进制功能是否开启（如下，值为OFF，则表示未开启）： ####

![MySQL数据备份及恢复（1）][MySQL_1]

#### 2、开启二进制日志功能： ####

[root@mysql ~]#  vim /etc/my.cnf    #在mysqld字段下写入下面配置，以便开启二进制日志并指定二进制文件名
    #开启二进制日志，需要指定server-id，否则服务将会启动失败
    log-bin=/usr/local/mysql/data/bin_log
    server-id=1
    [root@mysql ~]# systemctl restart mysqld
    #重启后，将在指定的目录下生成两个文件，如下：
    [root@mysql data]# pwd
    /usr/local/mysql/data
    [root@mysql data]# ls | grep bin_log
    bin_log.000001   #每次重启mysql服务或执行flush logs命令，都会生成一个新的这样的文件，依次为000001、000002......
    bin_log.index       #这个文件存储所有二进制文件的索引

开启二进制日志功能后，所有增删改的操作都会记录到二进制日志文件当中，注意，是增删改的操作，不包括查操作。

#### 3、确定二进制日志功能已开启 ####

![MySQL数据备份及恢复（1）][MySQL_1 1]

#### 4、执行增删改以便测试bin\_log是否有记录 ####

mysql> reset master;            #清空所有的二进制文件，从00001开始
    
    mysql> create database test_db1;       
    mysql> use test_db1;
    mysql> create table tb1(id int primary key auto_increment,name varchar(20));
    
    mysql> insert into tb1(name) values('zhangsan');
    mysql> insert into tb1(name) values('lisi');
    #重新开始一个新的日志文件再执行操作。注意，此时上面所有的操作写入的是第一个二进制日志文件
    mysql> flush logs;
    mysql> delete from tb1 where name='lisi';    
    mysql>  insert into tb1(name) values('tom');

#### 5、MySQL中查看二进制日志文件及文件内容 ####

**查看二进制日志文件：**  
![MySQL数据备份及恢复（1）][MySQL_1 2]

**查看二进制日志文件内容：**

完整的命令格式如下：

SHOW BINLOG EVENTS[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
    # in：指定要查看的二进制文件；
    # from：指定从哪个“pos”位置开始查看
    # limit：限制返回的行数，offset是指跳过多少行再显示

注：如果不指定二进制文件名，那么默认显示第一个二进制日志文件中的事件，文件内容中包含了日志文件名、事件的开始位置、事件类型、结束位置、信息等内容。

![MySQL数据备份及恢复（1）][MySQL_1 3]

> 其他命令：
> 
>  *  show master logs：也是查看二进制日志文件；
>  *  PURGE BINARY LOGS：用于删除二进制文件；

例子：

*  PURGE BINARY LOGS TO 'mysql-bin.00010'; \#把这个文件之前的其他文件都删除掉
 *  PURGE BINARY LOGS BEFORE '2016-08-28 22:46:26'; \#把指定时间之前的二进制文件删除了

#### 6、使用mysqlbinlog本地查看二进制日志内容 ####

[root@mysql data]# pwd
    /usr/local/mysql/data
    [root@mysql data]# mysqlbinlog bin_log.000001
    #使用-v选项可以查看出日志文件中的详细信息，两个v可以查看出更详细的信息，但是三个vvv也不会有什么作用了
    [root@mysql data]# mysqlbinlog -v bin_log.000001 
    [root@mysql data]# mysqlbinlog -vv bin_log.000001

#### 7、通过二进制日志恢复数据 ####

假设在开始删除lisi记录的那条sql语句是误操作，现在要通过二进制日志来恢复数据。

**1）**首先需要找到删除lisi记录的sql语句在二进制日志中的位置，每条sql语句都是一个事务，所以需要从其begin到commit，才算是完整的sql语句。如下：  
![MySQL数据备份及恢复（1）][MySQL_1 4]

从上面可以看出，delete事件发生position是219，事件结束是393。

**2）**事件恢复流程：直接用bin-log日志将数据库恢复到删除位置219前，然后跳过故障点，再进行恢复下面所有的操作，具体恢复流程如下：

**导出相关binlog文件（将二进制文件转换为sql语句生成新的文件）：**

[root@mysql data]# mysqlbinlog bin_log.000001 > /tmp/01.sql
    [root@mysql data]# mysqlbinlog --stop-position=219 bin_log.000002 > /tmp/219.sql
    [root@mysql data]# mysqlbinlog --start-position=393 bin_log.000002 > /tmp/393.sql

上述指令中，第一条比较好理解，无非就是使用msyqlbinlog查看第一个二进制文件，并生成新文件，后面两条指令呢，--stop-postition意思是查看时到219这个位置不查看，一直到393才又开始接着查看。最后的结果就是新生成的文件中不会包含删除lisi记录的sql语句。

**3）**删除数据库

mysql> drop database test_db1;

**4）**利用binlog恢复数据

[root@mysql data]# mysql -uroot -p123.com < /tmp/01.sql   #恢复第一个日志文件
    [root@mysql data]# mysql -uroot -p123.com < /tmp/219.sql   #恢复第二个
    [root@mysql data]# mysql -uroot -p123.com < /tmp/393.sql    #恢复第三个

**5）**确定数据已恢复

![MySQL数据备份及恢复（1）][MySQL_1 5]

### 二、mysqldump备份工具 ###

mysqldump是mysql用于备份和数据转移的一个工具。主要产生一系列的SQL语句，可以封装到文件，该文件包含有所有重建数据库所需要的 SQL命令，如CREATE DATABASE，CREATE TABLE，INSERT等等。可以用来实现轻量级的快速迁移或恢复数据库。 mysqldump 是将数据表导成 SQL 脚本文件，在不同的 MySQL 版本之间升级时相对比较合适，这也是最常用的备份方法。 mysqldump一般在数据量很小的时候（几个G）可以用于备份。当数据量比较大的情况下，就不建议用mysqldump工具进行备份了。

mysqldump可以针对单个表、多个表、单个数据库、多个数据库、所有数据库进行导出的操作。

**mysqldump使用示例：**

#### 1、备份某一个表 ####

[root@mysql backup]# mysqldump -u root -p123.com mysql user > mysql-user.sql          #备份mysql库中的user表
    [root@mysql backup]# ls                  #查看备份文件
    mysql-user.sql

#### 2、恢复mysql数据库中的user表 ####

[root@mysql backup]# mysql -u root -p123.com mysql < mysql-user.sql

#### 3、备份mysql库 ####

[root@mysql backup]# mysqldump -u root -p123.com --databases mysql > mysql.sql            #备份mysql库
    
    [root@mysql backup]# ls             #查看备份文件
    mysql.sql

#### 4、恢复mysql库 ####

[root@mysql backup]# mysql -u root -p123.com < mysql.sql

#### 5、备份所有的库（当导出的数据量较大时，可以添加“--opt”选项以优化执行速度） ####

[root@mysql backup]# mysqldump -u root -p123.com --opt --all-databases > all-data.sql         #备份所有库
    
    [root@mysql backup]# ls              #查看备份文件
    all-data.sql

[MySQL_1]: https://s1.51cto.com/images/blog/202001/10/e393c219a3e762fdc1c4157370f97876.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL_1 1]: https://s1.51cto.com/images/blog/202001/10/f488845b9e3c5e4c10c150e6b1f1f93d.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL_1 2]: https://s1.51cto.com/images/blog/202001/10/83827a23fcf2ddd4aaa87448f4939324.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL_1 3]: https://s1.51cto.com/images/blog/202001/10/b93618ae389fd80080c234acab4ab7fa.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL_1 4]: https://s1.51cto.com/images/blog/202001/10/3f383639fd5247f79135161c0d04b2c9.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL_1 5]: https://s1.51cto.com/images/blog/202001/11/7bebc48c31f8dbf87b1f61e61ab3c0cb.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=