MySQL binlogdump_mysqldump+binlog的备份与恢复和xtrabackup备份工具

Dear 丶 2022-10-25 00:57 147阅读 0赞

数据备份类型

按业务划分：可分为完全备份，增量备份，差异备份。

完全备份：就是对整个数据库的数据和数据结构进行备份，好处是很直观，容易被人理解。不足之处：由于每天都对系统进行完全备份，因此在备份数据中大量是重复的，这些重复数据占用空间，增加成本，备份量大，所需时间长。

增量备份：就是每次备份的数据只是相当于上一次备份后增加和修改过的数据。优点：没有重复的数据，节省空间，缩短备份时间。缺点：发生灾难时，恢复数据麻烦。另外这种备份的可靠性也差，在这种备份下，各备份数据间的关系就像链子一样，一环套一环，其中任何一个备份数据出了问题都会导致整条链子脱节。

差异备份：就是每次备份的数据是相对于上一次全备份之后新增加的和修改过的数据。

数据备份方式：可分为热备，温备，冷备。

热备份：是指在数据库运行中直接备份，对正在运行的数据库没有任何影响。

冷备份：是指在数据库停止的情况下进行的备份，这种备份最为简单，一般只需要拷贝相关的数据库物理文件即可。

温备份：备份同样是在数据库运行时进行，但是会对当前数据库的操作有所影响，例如加一个全局读锁以保证备份数据的一致性。

备份流程图

![3e00ff6edafd19601a3648cb099e75cc.png][]

mysql备份工具介绍

mysqldump:逻辑备份工具，适用于所有存储引擎，可用于温备，能实现完全备份，部分备份，对IonoDB存储引擎支持热备；

cp,tar等文件系统工具：物理备份工具，适用于所有存储引擎，用于冷备，能实现完全备份，部分备份；

lvm2的快照：备几乎热，借助于文件系统工具实现物理备份。

mysqlhotcopy:几乎冷备，仅适用于myisam存储引擎；

mysqldump+binlog实现数据库的备份与恢复

1.准备备份目录\[root@centos7 ~\]\# mkdir  -p  /backup/binlog

2.准备备份的数据库及表MariaDB \[(none)\]> create database datas;

Query OK, 1 row affected (0.05 sec)

MariaDB \[(none)\]> use datas;

Database changed

MariaDB \[datas\]> create table tb1 (id int,name char(20));

Query OK, 0 rows affected (0.41 sec)

MariaDB \[datas\]> desc tb1;

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

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

| id    | int(11)  | YES  |     | NULL    |       |

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

MariaDB \[datas\]> insert into tb1 values (11,'haha')

\-> ;

Query OK, 1 row affected (0.03 sec)

MariaDB \[datas\]> select \* from tb1;

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

| id   | name |

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

|   11 | haha |

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

1 row in set (0.00 sec)

3.对数据库进行完整备份\[root@centos7 ~\]\# mysqldump --all-databases --lock-all-tables --flush-log --master-data=2 > /backup/\`date +%F\_%T\`-all.sql

\--lock-all-tables:锁定所有表

\--flush-logs:锁定表之后执行flush logs命令

\--master-data=\{0|1|2\}

0:不记录

1：记录change master to语句；此语句未被注释

2：记录为注释语句

4.向表中插入数据MariaDB \[datas\]> show master status;

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

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

| mysql-bin.000007 |      245 |              |                  |

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

1 row in set (0.00 sec)

MariaDB \[datas\]> insert into tb1 values (12,'hehe'),(13,'yaya');

Query OK, 2 rows affected (0.23 sec)

Records: 2  Duplicates: 0  Warnings: 0

MariaDB \[datas\]> select \* from tb1;

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

| id   | name |

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

|   11 | haha |

|   12 | hehe |

|   13 | yaya |

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

3 rows in set (0.00 sec)

5.进行增量备份，备份二进制日志\[root@centos7 mysql\]\# mysqlbinlog --start-position=245 --stop-position=451 mysql-bin.000008> /backup/binlog/\`date +%F\_%T\`.sql

\[root@centos7 mysql\]\# cd  /backup/binlog/

\[root@centos7 binlog\]\# ls

2017-12-03\_16:43:29.sql

6.继续插入数据，在没备份的情况下删除数据库，模拟误操作。MariaDB \[datas\]> insert into tb1 values (16,'yuyu');

Query OK, 1 row affected (0.00 sec)

MariaDB \[datas\]> drop database datas;

Query OK, 1 row affected (0.09 sec)

7.恢复数据，由于在最后没有备份就删除了数据库，所以我们首先需要保护最后的二进制日志，查看删除之前的position值。\[root@centos7 binlog\]\# cd  /var/lib/mysql/

\[root@centos7 mysql\]\# mysqlbinlog  mysql-bin.000008

/\*!50530 SET @@SESSION.PSEUDO\_SLAVE\_MODE=1\*/;

/\*!40019 SET @@session.max\_insert\_delayed\_threads=0\*/;

/\*!50003 SET @OLD\_COMPLETION\_TYPE=@@COMPLETION\_TYPE,COMPLETION\_TYPE=0\*/;

DELIMITER /\*!\*/;

\# at 4

\#171203 16:35:29 server id 2  end\_log\_pos 245     Start: binlog v 4, server v 5.5.52-MariaDB created 171203 16:35:29 at startup

\# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/\*!\*/;

BINLOG '

UbcjWg8CAAAA8QAAAPUAAAABAAQANS41LjUyLU1hcmlhREIAbG9nAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAABRtyNaEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAikwNAA==

'/\*!\*/;

\# at 245

\#171203 16:36:38 server id 2  end\_log\_pos 314     Query    thread\_id=2    exec\_time=0    error\_code=0

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

SET @@session.pseudo\_thread\_id=2/\*!\*/;

SET @@session.foreign\_key\_checks=1, @@session.sql\_auto\_is\_null=0, @@session.unique\_checks=1, @@session.autocommit=1/\*!\*/;

SET @@session.sql\_mode=0/\*!\*/;

SET @@session.auto\_increment\_increment=1, @@session.auto\_increment\_offset=1/\*!\*/;

/\*!\\C utf8 \*//\*!\*/;

SET @@session.character\_set\_client=33,@@session.collation\_connection=33,@@session.collation\_server=8/\*!\*/;

SET @@session.lc\_time\_names=0/\*!\*/;

SET @@session.collation\_database=DEFAULT/\*!\*/;

BEGIN

/\*!\*/;

\# at 314

\#171203 16:36:38 server id 2  end\_log\_pos 424     Query    thread\_id=2    exec\_time=0    error\_code=0

use \`datas\`/\*!\*/;

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

insert into tb1 values (14,'hehe'),(15,'yaya')

/\*!\*/;

\# at 424

\#171203 16:36:38 server id 2  end\_log\_pos 451     Xid = 10

COMMIT/\*!\*/;

\# at 451

\#171203 16:54:34 server id 2  end\_log\_pos 520     Query    thread\_id=3    exec\_time=0    error\_code=0

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

BEGIN

/\*!\*/;

\# at 520

\#171203 16:54:34 server id 2  end\_log\_pos 618     Query    thread\_id=3    exec\_time=0    error\_code=0

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

insert into tb1 values (16,'yuyu')

/\*!\*/;

\# at 618

\#171203 16:54:34 server id 2  end\_log\_pos 645     Xid = 20

COMMIT/\*!\*/;

\# at 645

\#171203 16:54:56 server id 2  end\_log\_pos 728     Query    thread\_id=3    exec\_time=0    error\_code=0

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

drop database datas

/\*!\*/;

DELIMITER ;

\# End of log file

ROLLBACK /\* added by mysqlbinlog \*/;

/\*!50003 SET COMPLETION\_TYPE=@OLD\_COMPLETION\_TYPE\*/;

/\*!50530 SET @@SESSION.PSEUDO\_SLAVE\_MODE=0\*/;

将最后的二进制日志备份\[root@centos7 mysql\]\# mysqlbinlog --start-position=520 --stop-position=645  mysql-bin.000008> /backup/binlog/\`date +%F\_%T\`.sql

8.导入之前的所有备份\[root@centos7 ~\]\# mysql

\[root@centos7 ~\]\# mysql

9.查看数据库及数据MariaDB \[datas\]> select \* from tb1;

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

| id   | name |

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

|   11 | haha |

|   14 | hehe |

|   15 | yaya |

|   12 | hehe |

|   13 | yaya |

|   16 | yuyu |

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

6 rows in set (0.00 sec)

数据已经全部恢复了。

xtrabackup的数据库备份与恢复

xtrabackup是由percona提供的mysql数据库备份工具，是一款开源能够对innodb和xtradb数据库进行热备的工具。

特点：

(1)备份过程快速，可靠。

(2)备份过程不会打断正在执行的事务

(3)能够基于压缩等功能节约磁盘空间和流量

(4)自动实现备份检验

(5)还原速度快

安装\[root@centos7 ~\]\# yum install xtrabackup

完全备份\[root@centos7 backup\]\# innobackupex --user=root /backup/

如果想要给备份的用户设置权限，可以创建最小权限的备份用户MariaDB \[(none)\]> grant reload,lock tables,replication client on \*.\* to 'backuser'@'localhost' identified by '123456';

Query OK, 0 rows affected (0.07 sec)

MariaDB \[(none)\]> flush privileges;

Query OK, 0 rows affected (0.04 sec)

增量备份

每个InnoDB的页面都会包含一个存储信息，每当相关的数据发生改变，相关的页面的存储就会自动增长。这正是InnoDB表可以进行增量备份的基础，即innobackupex通过备份上次完全备份之后发生改变的页面来实现。

innobackupex命令会在备份目录中创建一个新的以时间命名的目录以存放所有的增量备份数据，另外，在执行过增量备份之后再一次进行增量备份时，其--incremental-basedir应该指向上一次的增量备份所在的目录。

注：增量备份仅能应用于InniDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。

添加数据MariaDB \[datas\]> insert into tb1 values (17,'laoshi');

Query OK, 1 row affected (0.00 sec)

MariaDB \[datas\]> insert into tb1 values (18,'jiangchen');

Query OK, 1 row affected (0.00 sec)

MariaDB \[datas\]> select \* from tb1;

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

| id   | name      |

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

|   11 | haha      |

|   14 | hehe      |

|   15 | yaya      |

|   12 | hehe      |

|   13 | yaya      |

|   16 | yuyu      |

|   17 | laoshi    |

|   18 | jiangchen |

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

8 rows in set (0.00 sec)

增量备份\[root@centos7 2017-12-03\_18-08-41\]\# innobackupex --user=root --incremental /backup --incremental-basedir=/backup/2017-12-03\_18-08-41

\[root@centos7 backup\]\# ls

2017-12-03\_18-08-41  2017-12-03\_18-36-10

数据恢复准备

一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态“准备”(prepare)增量备份与整理完全备份有着一些不同，尤其要注意的是：

(1)需要在每个备份(包括完全和各个增量备份)上，将已经提交的事务进行“重放”。“重放”之后

，所有的备份数据将合并到完全备份上。

(2)基于所有的备份将未提交的事务进行“回滚”。

数据恢复准备：执行操作\[root@centos7 backup\]\# innobackupex --apply-log --redo-only /backup/2017-12-03\_18-08-41/

接着执行增量\[root@centos7 backup\]\# innobackupex --apply-log --redo-only /backup/2017-12-03\_18-08-41/  --inctrmental-dir=/backup/2017-12-03\_18-36-10

/backup/2017-12-03\_18-08-41/(完全备份的目录)

/backup/2017-12-03\_18-36-10(第一次增量所在的目录)

恢复阶段，还原数据\[root@centos7 mysql\]\# innobackupex --copy-back /backup/2017-12-03\_18-08-41/

\[root@centos7 mysql\]\# chown -R mysql.mysql  /var/lib/mysql

\[root@centos7 mysql\]\# systemctl restart mariadb

[3e00ff6edafd19601a3648cb099e75cc.png]: /images/20221024/c8c6ad52acec425eab88761b580aa231.png