mysql的数据备份及恢复

朱雀 2022-06-18 02:09 341阅读 0赞

一、mysql数据库备份的前因后果

1、备份的概念：

将数据收集并保存至另外的多个副本，其目的是将数据还原恢复至备份数据时那个状态。

2、备份数据的原因

1）做灾难恢复使用，要将数据副本做到异地多份备份；

2）数据库数据改动时使用备份；

3）需要对当前服务器做相关测试时使用备份，备份与测试都是最好在服务器访问量最少时进行

3、备份的事先注意事项

1）可以容忍丢失多长时间的数据；

2）恢复数据能在多长时间内完成；

3）是否需要持续提供服务；

4）需要恢复哪些内容，整个服务器的数据库，单个数据库，一个或多个表。

二、数据的备份类型

1、根据是否需要数据库离线可分为

1）冷备：cold backup  备份需要关闭mysql服务或读写请求均不允许；

2）温备：warm backup  **备份的同时，mysql服务在线，只允许读不允许写，在线交易要终止**：

3）热备：hot backup

备份的同时，mysql服务在线，支持读写请求，业务不受影响，但服务器的性能会有所下降。

2、根据要备份的数据范围可分为

1）完全备份：full backup

备份当前状态的整个数据库的数据；

2）增量备份：increment backup

备份基于上次的完全备份或增量备份以来所改变的数据；

3）差异备份：different backup

备份基于上次的完全备份改变了的数据。

3、根据备份数据是否为文件可分为

1）物理备份：直接备份数据库文件

2）逻辑备份：备份表中的数据和库代码

三、备份的对象及备份工具

1、备份对象

1）数据

2）配置文件

3）代码、存储过程、存储函数、触发器等

4）OS相关的配置文件

5）复制相关的配置

6）二进制日志

2、引擎所支持的备份

**MyISAM引擎只支持温备，而InnoDB还支持热备。**

3、备份工具

1）mysqldump，mysql客户端的经典备份工具   ，为逻辑备份工具，备份和恢复比较慢；

2）mylvmdumper，mysqldump升级版

多线程的逻辑备份工具，备份和恢复速度稍快于mysqldump；

3）lvm-snapshot,基于快照卷的备份工具  ，接近于热备的物理备份工具，备份和恢复的速度较快；

4）select ，mysql内置的备份工具  ，逻辑备份工具，速度快于mysqldump；

select into outfile；

load data infile；

5）xtrabackup，由percana提供的免费开源备份工具

为物理备份工具，速度快。

6）mysql hotcopy：几乎冷备的工具，一般不采用，速度慢。

四、数据从备份到恢复的完整流程

1）停止mysql服务；

2）记录服务和配置文件权限；

3）复制备份文件与数据目录；

4）按需调整配置；

5）按需改变文件权限；

6）尝试启动服务；

7）装载逻辑备份；

8）检查和重放二进制日志；

9）确定数据还原正常完成；

10）以完全权限重启服务器。

五、使用mysqldump进行数据备份及恢复

1：为测试的数据库及二进制创建备份目录；

\[root@node1 ~\]\# **mkdir -pv \{/mydata/data,/backup,/var/binlog\}**

mkdir: created directory \`/mydata/data'

mkdir: created directory \`/backup'

mkdir: created directory \`/var/binlog'

2：启动mysqld服务，创建测试数据库，并创建测试数据

\[root@node1 ~\]\# service mysqld restart

Shutting down MySQL....                                    \[  OK  \]

Starting MySQL..                                           \[  OK  \]

\[root@node1 ~\]\#mysql

mysql> create database students;

Query OK, 1 row affected (0.00 sec)

mysql> use students

Database changed

mysql> create table TLtb (Id tinyint unsigned not null primary key auto\_increment,Name char(20) not null unique key,Age tinyint unsigned,Gender char(1) default 'M',Courses char(30) not null);

Query OK, 0 rows affected (0.28 sec)

mysql> insert into TLtb (Name,Age,Gender,Courses) values ('Xu zu',20,'M','Xiao Wuxianggong'),('Qiao Feng',28,'M','Xianglong Shibazhang'),('Duan Fu',23,'M','Liumai Shenjian');

Query OK, 3 rows affected (0.01 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> select \* from TLtb;

\+----+-----------+------+--------+----------------------+

\+----+-----------+------+--------+----------------------+

|  1 | Xu zu     |   20 | M      | Xiao Wuxianggong     |

|  2 | Qiao Feng |   28 | M      | Xianglong Shibazhang |

|  3 | Duan Fu   |   23 | M      | Liumai Shenjian      |

\+----+-----------+------+--------+----------------------+

3 rows in set (0.01 sec)

3：使用mysqldump对测试数据库进行备份，并且滚动二进制日志，记录日志位置；

\[root@node1 ~\]\#** mysqldump --lock-all-tables  --flush-logs --master-data=2 --databases students > /backup/students\_\`date +%F\`.sq**

\[root@node1 ~\]\# cp /var/binlog/mysql-bin.

mysql-bin.000001  mysql-bin.000002  mysql-bin.000003  mysql-bin.000004  mysql-bin.000005  mysql-bin.000006  mysql-bin.index

4：备份二进制日志文件；

\[root@node1 ~\]\# cp /var/binlog/mysql-bin.00000\* /backup/

\[root@node1 ~\]\# ls /backup/

mysql-bin.000001  mysql-bin.000003  mysql-bin.000005  students\_2013-09-30.sql

mysql-bin.000002  mysql-bin.000004  mysql-bin.000006  students\_.sql

5：新增数据库数据进行增量备份，查看当前日志位置；

mysql> create table CDtb (Id tinyint unsigned not null primary key auto\_increment,Name char(20) not null unique key,Age tinyint unsigned,Gender char(1) default 'M',Courses char(30) not null);

Query OK, 0 rows affected (0.14 sec)

mysql> insert into CDtb (Name,Age,Gender,Courses) values ('Yideng Dashi',80,'M','Yiyangzhi'),('Hong Qigong',66,'M','Dagou Bangfa'),('Huang Yaoshi',60,'M','Tanzhi Shengong');

Query OK, 3 rows affected (0.05 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> select \* from CDtb;

\+----+--------------+------+--------+-----------------+

\+----+--------------+------+--------+-----------------+

|  1 | Yideng Dashi |   80 | M      | Yiyangzhi       |

|  2 | Hong Qigong  |   66 | M      | Dagou Bangfa    |

|  3 | Huang Yaoshi |   60 | M      | Tanzhi Shengong |

\+----+--------------+------+--------+-----------------+

3 rows in set (0.01 sec)

mysql> show master status;

\+------------------+----------+--------------+------------------+

\+------------------+----------+--------------+------------------+

| mysql-bin.000006 |      716 |              |                  |

\+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

\[root@node1 ~\]\# less /backup/students\_2013-09-30.sql

\-- CHANGE MASTER TO MASTER\_LOG\_FILE='mysql-bin.000006', MASTER\_LOG\_POS=107;

\[root@node1 ~\]\# mysqlbinlog --start-position=107 /var/binlog/mysql-bin.000006 > /backup/students\_incremental.sql

6：模拟数据库数据损坏；

mysql> insert into CDtb (Name,Age,Gender,Courses) values ('Ou Yangfeng',75,'M','Hamagong');

Query OK, 1 row affected (0.05 sec)

mysql> drop database students;

Query OK, 2 rows affected, 2 warnings (0.13 sec)

mysql> show master status;

\+------------------+----------+--------------+------------------+

\+------------------+----------+--------------+------------------+

| mysql-bin.000006 |     1082 |              |                  |

\+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

7：恢复备份数据：完全备份+增量备份+二进制日志文件；

bac

\[root@node1 ~\]\# **mysqlbinlog  /var/binlog/mysql-bin.000006**

......

\# at 993

\#130930 22:51:40 server id 1  end\_log\_pos 1082  Query   thread\_id=2 exec\_time=0 error\_code=1146

SET TIMESTAMP=1380552700/\*!\*/;

drop database students

\[root@node1 ~\]\# **mysqlbinlog --start-position=716 --stop-position=993 /var/binlog/mysql-bin.000006 > /backup/students\_993.sql**

\[root@node1 ~\]\#**mysqlbinlog /var/binlog/mysql-bin.000006**

......

\# at 993

\#130930 22:51:40 server id 1  end\_log\_pos 1082  Query   thread\_id=2 exec\_time=0 error\_code=1146

SET TIMESTAMP=1380552700/\*!\*/;

drop database students

\[root@node1 ~\]\# **mysqlbinlog --start-position=716 --stop-position=993 /var/binlog/mysql-bin.000006 > /backup/students\_993.sql**

mysql> set global sql\_log\_bin=0;

Query OK, 0 rows affected (0.00 sec)

mysql> source /backup/students\_2013-09-30.sql

mysql> source /backup/students\_incremental.sql

mysql> source /backup/students\_993.sql

8：检测备份的数据是否已经正常恢复。

mysql> set global sql\_log\_bin=1;

Query OK, 0 rows affected (0.00 sec)

mysql> use students;

Database changed

mysql> show tables;

\+--------------------+

| Tables\_in\_students |

\+--------------------+

| CDtb               |

| TLtb               |

\+--------------------+

2 rows in set (0.00 sec)

mysql> select \* from TLtb;

\+----+-----------+------+--------+----------------------+

\+----+-----------+------+--------+----------------------+

|  1 | Xu zu     |   20 | M      | Xiao Wuxianggong     |

|  2 | Qiao Feng |   28 | M      | Xianglong Shibazhang |

|  3 | Duan Fu   |   23 | M      | Liumai Shenjian      |

\+----+-----------+------+--------+----------------------+

3 rows in set (0.00 sec)

mysql> select \* from CDtb;

\+----+--------------+------+--------+-----------------+

\+----+--------------+------+--------+-----------------+

|  1 | Yideng Dashi |   80 | M      | Yiyangzhi       |

|  2 | Hong Qigong  |   66 | M      | Dagou Bangfa    |

|  3 | Huang Yaoshi |   60 | M      | Tanzhi Shengong |

|  4 | Ou Yangfeng  |   75 | M      | Hamagong        |

\+----+--------------+------+--------+-----------------+

4 rows in set (0.00 sec)

六、使用lvm-snapshot进行数据备份及恢复

1、创建LVM逻辑卷并开机自动挂载，并创建mysql数据与二进制日志存放目录；

\[root@localhost ~\]\# **pvcreate /dev/sdb\{1,2\}**

Physical volume "/dev/sdb1" successfully created

Physical volume "/dev/sdb2" successfully created

\[root@localhost ~\]\# **vgcreate vg1 /dev/sdb\{1,2\}**

Volume group "vg1" successfully created

\[root@localhost ~\]\# **lvcreate -L +10G -n lv1 vg1**

Logical volume "lv1" created

\[root@localhost ~\]\# **mke2fs -t ext4 /dev/vg1/lv1**

\[root@localhost ~\]\# **vim /etc/fstab**

/dev/vg1/lv1            /Mydata                 ext4    defaults        0 0

\[root@localhost ~\]\# mount -a

\[root@localhost ~\]\# **mkdir /Mydata/\{data,,binlog\}    /backup1**

\[root@localhost ~\]\# chown -R mysql:mysql /Mydata/\*

2、新建mysql数据库并新增数据；

mysql> create database schooldb;

Query OK, 1 row affected (0.01 sec)

mysql> use schooldb

Database changed

mysql> create table studentstb (Id tinyint unsigned not null primary key auto\_increment,Name char(20) not null unique key,Age tinyint unsigned,Gender char(1) default 'M',Courses char(30) not null);

Query OK, 0 rows affected (0.38 sec)

mysql> insert into studentstb (Name,Age,Gender,Courses) values ('Zhang San',19,'M','Shujujiegou'),('Li Ling',18,'F','Daxueyingyu'),('Wang Wu',20,'M','Dianluyuanli');

Query OK, 3 rows affected (0.09 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> select \* from studentstb;

\+----+-----------+------+--------+--------------+

\+----+-----------+------+--------+--------------+

|  1 | Zhang San |   19 | M      | Shujujiegou  |

|  2 | Li Ling   |   18 | F      | Daxueyingyu  |

|  3 | Wang Wu   |   20 | M      | Dianluyuanli |

\+----+-----------+------+--------+--------------+

3 rows in set (0.03 sec)

3、登录mysql，对所有表加锁，并滚动日志，查看当前日志所在位置；

mysql> **flush tables with read lock;**

Query OK, 0 rows affected (0.00 sec)

mysql> **flush logs;**

Query OK, 0 rows affected (0.04 sec)

mysql> **show master status;**

\+------------------+----------+--------------+------------------+

\+------------------+----------+--------------+------------------+

| mysql-bin.000015 |      107 |              |                  |

\+------------------+----------+--------------+------------------+

1 row in set (0.01 sec)

4、另外启动一个终端，为逻辑卷创建快照卷，做完快照释放施加的锁请求；

\[root@localhost ~\]\#** lvcreate -L 1G -s -p r -n lv1-snap /dev/vg1/lv1**

Logical volume "lv1-snap" created

mysql> **unlock tables;**

Query OK, 0 rows affected (0.00 sec)

5、挂载快照卷，并备份数据，备份完成卸载快照卷并删除；

\[root@localhost ~\]\# cp -rp /mnt/\* /backup1

\[root@localhost ~\]\# umount /mnt/

\[root@localhost ~\]\# **lvremove /dev/vg1/lv1-snap**

Do you really want to remove active logical volume lv1-snap? \[y/n\]: y

Logical volume "lv1-snap" successfully removed

6、停止mysql服务，模拟数据库数据损坏，进行数据恢复；

\[root@localhost ~\]\# service mysqld stop

Shutting down MySQL...                                     \[  OK  \]

\[root@localhost ~\]\# rm -rf /Mydata/\*

\[root@localhost ~\]\# cp -rp /backup1/\* /Mydata/

\[root@localhost ~\]\# service mysqld start

Starting MySQL..                                           \[  OK  \]

7、检测数据恢复是否正常恢复完成。

mysql> show databases;

\+--------------------+

| Database           |

\+--------------------+

| information\_schema |

| hellodb            |

| mysql              |

| performance\_schema |

| schooldb           |

| test               |

| xiaozheng          |

\+--------------------+

7 rows in set (0.01 sec)

mysql> use schooldb

Database changed

mysql> show tables;

\+--------------------+

| Tables\_in\_schooldb |

\+--------------------+

| studentstb         |

\+--------------------+

1 row in set (0.00 sec)

mysql> select \* from studentstb;

\+----+-----------+------+--------+--------------+

\+----+-----------+------+--------+--------------+

|  1 | Zhang San |   19 | M      | Shujujiegou  |

|  2 | Li Ling   |   18 | F      | Daxueyingyu  |

|  3 | Wang Wu   |   20 | M      | Dianluyuanli |

\+----+-----------+------+--------+--------------+

3 rows in set (0.00 sec)

七、使用xtrabackup进行数据备份恢复

1、**下载并安装xtrabackup；**

\[root@localhost ~\]\# ls

anaconda-ks.cfg  install.log         percona-xtrabackup-2.1.4-656.rhel6.x86\_64.rpm

hellodb.sql      install.log.syslog

\[root@localhost ~\]\# **yum -y install percona-xtrabackup-2.1.4-656.rhel6.x86\_64.rpm**

2、**创建有备份权限的数据库用户；**

mysql> create user 'xtrabackup'@'localhost' identified by 'mypass';

Query OK, 0 rows affected (0.09 sec)

mysql> revoke all privileges,grant option from 'xtrabackup'@'localhost';

Query OK, 0 rows affected (0.00 sec)

mysql> grant reload,lock tables,replication client,event on \*.\* to 'xtrabackup'@'localhost';

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

3、**对数据库进行完全备份；**

\[root@localhost ~\]\# **innobackupex --user=xtrabackup --password=mypass /backup2**

........

innobackupex: Backup created in directory '/backup2/2013-09-08\_21-06-19'

innobackupex: MySQL binlog position: filename 'mysql-bin.000017', position 598

130908 21:06:24  innobackupex: Connection to database server closed

130908 21:06:24  innobackupex: completed OK!

4、**关闭mysql服务并模拟数据损坏,并准备一个完全备份（prepare）；**

\[root@localhost ~\]\# service mysqld stop

Shutting down MySQL...                                     \[  OK  \]

\[root@localhost ~\]\# rm -rf /mydata/data/\*

\[root@localhost backup2\]\# innobackupex --apply-log /backup2/2013-09-08\_21-06-19/

........

xtrabackup: starting shutdown with innodb\_fast\_shutdown = 1

130908 21:13:44  InnoDB: Starting shutdown...

130908 21:13:48  InnoDB: Shutdown completed; log sequence number 1626636

130908 21:13:48  innobackupex: completed OK!

5、从一个完全备份中恢复数据,并检测数据库数据是否正常恢复完成。

\[root@localhost ~\]\#** innobackupex --copy-back /backup2/2013-09-08\_21-06-19/**

............

innobackupex: Copying '/backup2/2013-09-08\_21-06-19/ib\_logfile1' to '/mydata/data'

innobackupex: Finished copying back files.

130908 21:21:23  innobackupex: completed OK!

\[root@localhost ~\]\#service mysqld start

\[root@localhost ~\]\# innobackupex --copy-back /backup2/2013-09-08\_21-06-19/

............

innobackupex: Copying '/backup2/2013-09-08\_21-06-19/ib\_logfile1' to '/mydata/data'

innobackupex: Finished copying back files.

130908 21:21:23  innobackupex: completed OK!

\[root@localhost ~\]\# service mysqld start

Starting MySQL..                                           \[  OK  \]

mysql> use schooldb

Database changed

mysql> show tables;

\+--------------------+

| Tables\_in\_schooldb |

\+--------------------+

| studentstb         |

\+--------------------+

1 row in set (0.00 sec)

mysql> select \* from studentstb;

\+----+-----------+------+--------+--------------+

\+----+-----------+------+--------+--------------+

|  1 | Zhang San |   19 | M      | Shujujiegou  |

|  2 | Li Ling   |   18 | F      | Daxueyingyu  |

|  3 | Wang Wu   |   20 | M      | Dianluyuanli |

\+----+-----------+------+--------+--------------+

3 rows in set (0.00 sec)

\--------------------------------------------------------------------

6、在测试数据库中新增数据，实现数据的增量备份；

7、进行第一次增量备份；

8、继续于测试数据库中新增数据，进行第二次增量备份；

9、关闭mysql服务并模拟数据库数据损坏，进行两次增量备份的恢复；

10、检测增量备份的数据恢复是否正常完成。

mysql 热备

在运维中需定期备份mysql，为崩溃后的恢复数据做准备。一般分为冷备和热备，

冷备就是停掉mysql服务，直接cp文件，但是在生产环境中，很 少有机会这样，一般都是在mysql提供服务的时候就进行备份，因此这牵扯到数据一致性的问题。

所以，**在MyISAM存储引擎下**，我们的思路就是：**锁表 –>备份–>解锁**

\# 进入mysql环境 锁表

root@mysql : test > **flush tables with read lock;**\# 在系统环境下 备份

cp -R test /tmp/backup/test\_back

\# 进入mysql环境 解锁

root@mysql : test > **unlock tables;**

mysqlhotcopy 热备工具

当然也可通过mysql自带的工具 mysqlhotcopy 解决，事实上mysqlhotcopy是个perl程序，

**提供MyISAM下的锁表备份解锁操做。因为是perl脚本，所以需要机器上有PERL-DBD 模块**，

不然会报错: Can’t locate DBI.pm in @INC ,安装过程很简单，在这里: perl-DBI连接mysql

mysqlhotcopy也很简单，参数可用 –help 查看

\# 备份test库为新的test\_tmp库，与test同级目录

**mysqlhotcopy  --user=root  --password=root   test   test\_tmp**

\# 备份test库 到 /tmp/ 目录下

**mysqlhotcopy  --user=root  --password=root   test   /tmp/**

\# --checkpoint dbinfo.checkpoint 这个是指定存放操作记录的数据库/表\# --addtodest 增量备份，新的备份自动覆盖掉原来的

**mysqlhotcopy  --user=root  --password=root  --checkpoint=dbinfo.checkpoint  --addtodest  test  /tmp/**

mysqlhotcopy的安装方法如下：

**yum -y install perl perl-DBI**

wget http://down1.chinaunix.net/distfiles/DBD-mysql-3.0002.tar.gz

tar zxvf  DBD-mysql-3.0002.tar.gz

cd DBD-mysql-3.0002

perl Makefile.PL  –mysql\_config=/usr/local/mysql/bin/mysql\_config

make

make test

make install

记录到到数据库的方法：

1、在数据库段分配一个专门用于备份的用户

mysql> grant select,reload,lock tables on \*.\* to 'hotcopyer'@'localhost' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

2、在/etc/my.cnf或者登陆用户的个人主文件.my.cnf里面添加

\[mysqlhotcopy\]

interactive-timeout

user=hotcopyer

password=123456

port=3306

重新加载mysql

3、可以把记录写到专门的表中。具体察看帮助。

mysql> create database hotcopy;

Query OK, 1 row affected (0.03 sec)

mysql> use hotcopy

Database changed

mysql> create table checkpoint(time\_stamp timestamp not null,src varchar(32),dest varchar(60), msg varchar(255));

Query OK, 0 rows affected (0.01 sec)

同时记得给hotcopyer用户权限。

mysql> grant insert on hotcopy.checkpoint to hotcopyer@'localhost';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> quit;

4、备份数据（写入数据库\{备份的结果\}，增量备份）

**/usr/local/mysql/bin/mysqlhotcopy --user=root --password=mysql --checkpoint=hotcopy.checkpoint --addtodest radius /bak**

5、查看

mysql> show databases;

\+--------------------+

| Database           |

\+--------------------+

| information\_schema |

| 1234la             |

| conntrack          |

| hotcopy            |

| mysql              |

| pinphp             |

| radius             |

| test               |

| vod                |

| web                |

\+--------------------+

10 rows in set (0.00 sec)

mysql> show tables;

\+-------------------+

| Tables\_in\_hotcopy |

\+-------------------+

| checkpoint        |

\+-------------------+

1 row in set (0.00 sec)

mysql> select \* from checkpoint;

\+---------------------+--------+---------------+-----------+

| time\_stamp          | src    | dest          | msg       |

\+---------------------+--------+---------------+-----------+

\+---------------------+--------+---------------+-----------+

1 row in set (0.00 sec)

mysql>

一、mysqldump备份结合binlog日志恢复

MySQL备份一般采取全库备份加日志备份的方式，例如每天执行一次全备份，每小时执行一次二进制日志备份。这样在MySQL故障后可以使用全备份和日志备份将数据恢复到最后一个二进制日志备份前的任意位置或时间

1.  binlog日志恢复介绍

·首先要开启binary log功能

通过编辑my.cnf中的log-bin选项可以开启二进制日志；形式如下：

log-bin \[=DIR/\[filename\]\]  （配置文件中只写log\_bin不写后面的文件名和路径时，默认存放在/usr/local/mysql/data目录下，文件名为主机名-bin.000001…命名）

·下面用一个例子来说明binlog日志恢复数据的方法

![wKiom1jT3wGy-QPbAAAKzNvF9o0055.png][]

![wKioL1jT3wKSP453AABGaoC4Tqs732.png][]

查看二进制日志中的事件，默认显示可找到的第一个二进制日志文件中的事件，包含了日志文件名、事件的开始位置、事件类型、结束位置、信息等内容。

语法格式：**SHOW BINLOG EVENTS** \[IN 'log\_name'\] \[FROM pos\] \[LIMIT \[offset,\] row\_count\]

选项解析：

IN 'log\_name'      指定要查询的binlog文件名(不指定就是第一个binlog文件)

FROM pos               指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)

offset           偏移量(不指定就是0),例如为3就会从第3行开始

row\_count          查询总条数(不指定就是所有行)

显示信息中的参数介绍：

Format\_desc  此事件为格式描述事件

Query  为查询事件

BEGIN  为事务开始

Table\_map  为表映射事件

Write\_rows    为我们执行的insert事件

XidXid    时间是自动提交事务的动作

Rotate    为日志轮换事件，是我们执行flush logs开启新日志文件引起的。

COMMIT    为事务的提交

![wKioL1jT3wPzMe12AAA6Ct4daCU520.png][]

![wKiom1jT3wSyVOInAAAlcKNi49w607.png][]

为了便于查看记录了行变化信息的事件在当时具体执行了什么样的SQL语句可以使用mysqlbinlog工具的-v（--verbose）选项，该选项会将行事件重构成被注释掉的伪SQL语句，

如果想看到更详细的信息可以将该选项给两次如-vv，这样可以包含一些数据类型和元信息的注释内容。

执行以下命令，我们可以看到上图中显示的信息：

**mysqlbinlog  -v  /usr/local/mysql/data/mysql-bin.000002**

另外mysqlbinlog和可以通过--read-from-remote-server选项从远程服务器读取二进制日志文件，

这时需要一些而外的连接参数，如-h，-P，-p，-u等，这些参数仅在指定了--read-from-remote-server后有效。

从上图中可以看出delete事件发生position是291，事件结束position是420

恢复流程：**直接用bin-log日志将数据库恢复到删除位置291前,然后跳过故障点,再进行恢复。**下面所有的操作,命令如下：

![wKiom1jT3wTzXFBoAAAP2DI3M\_8562.png][wKiom1jT3wTzXFBoAAAP2DI3M_8562.png]

常见的选项有以下几个：

\--start-datetime

从二进制日志中读取指定时间戳或者本地计算机时间之后的日志事件。

\--stop-datetime

从二进制日志中读取指定时间戳或者本地计算机时间之前的日志事件。

\--start-position

从二进制日志中读取指定position 事件位置作为开始。

\--stop-position

从二进制日志中读取指定position 事件位置作为事件截至。

![wKioL1jT3wTj2mAXAAAZ5DqPp4o506.png][]

删除test数据库，利用binlog恢复数据，完成后发现数据都恢复过来了。

注意：

（1）**在实际生产环境中，如果遇到需要恢复数据库的情况，不要让用户能访问到数据库，以避免新的数据插入进来，以及在主从的环境下，关闭主从。**

（2）以上方法中仅仅利用了binlog来恢复数据，并没有使用到mysqldump全库备份来恢复数据库，所以在恢复数据前要删除数据库。

**2.**利用脚本实现mysql的备份与恢复

（1）mysqldump介绍

mysqldump是mysql用于备份和数据转移的一个工具。它主要产生一系列的SQL语句，可以封装到文件，该文件包含有所有重建你的数据库所需要的 SQL命令如CREATE DATABASE，CREATE TABLE，INSERT等等。可以用来实现轻量级的快速迁移或恢复数据库。

mysqldump 是将数据表导成 SQL 脚本文件，在不同的 MySQL 版本之间升级时相对比较合适，这也是最常用的备份方法。

mysqldump一般在数据量很小的时候（几个G）可以用于备份。当数据量比较大的情况下，就不建议用mysqldump工具进行备份了。

·数据库的导出

导出对象说明：

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

\# mysqldump \[options\] db\_name \[tbl\_name ...\]   //导出指定数据库或单个表

\# mysqldump \[options\] --databases db\_name ...   //导出多个数据库

\#mysqldump \[options\] --all-databases           //导出所有

·数据库的导入

mysql  -uroot  -p  \[options\]  \[db\_name\]  \[tbl\_name\]  <  /路径/备份文件

生产环境中Mysql数据库的备份是周期性重复的操作，所以通常是要编写脚本实现，通过crond计划任务周期性执行备份脚本。

[wKiom1jT3wGy-QPbAAAKzNvF9o0055.png]: /images/20220618/b25af77376474f34a1539953fb0e095c.png
[wKioL1jT3wKSP453AABGaoC4Tqs732.png]: /images/20220618/cf5479ea409041ca94bfb496a6163340.png
[wKioL1jT3wPzMe12AAA6Ct4daCU520.png]: /images/20220618/32c6cd483cfc4b7fa5e120f5c6c28693.png
[wKiom1jT3wSyVOInAAAlcKNi49w607.png]: /images/20220618/349f7af3704b45baab0c7a795d601fb7.png
[wKiom1jT3wTzXFBoAAAP2DI3M_8562.png]: /images/20220618/145514ae6587480591b03146d16d8f87.png
[wKioL1jT3wTj2mAXAAAZ5DqPp4o506.png]: /images/20220618/b30c83014eec42699ef7fbf88e972277.png