CentOS 7.6使用mysqldump备份恢复MariaDB

一、mysqldump简介：

mysqldump是MariaDB自带的客户端逻辑备份工具，通过mysql协议连接至mysqld，将需要备份的数据查询出来，并转换成对应的insert语句，当需要恢复这些数据时，只要执行这些insert语句，适用于数据量较小的场景。

逻辑备份是指备份SQL语句，在恢复时执行备份SQL，从而实现数据库数据的重现。备份完成后生成的SQL文件可以使用文本编辑工具直接修改。

二、mysqldump常用选项：

1、--single-transaction：让备份操作基于“独立的事务”进行，mysqldump自动将备份会话中的事务隔离级别设置为“repeatable read”（可重读），并开启一个事务，而且事务开始的同时创建了快照，直到备份结束时，都不会读取到本事务开始之后提交的任何数据，用于保证InnoDB存储引擎备份数据时的一致性2、--master-data=2：在使用mysqldump进行备份时，会在备份文件中生成对应的change master to语句，此语句记录当前备份时使用的二进制日志文件名称及事件在二进制日志所在的pos位置，但该change master to语句被注释

3、-A | --all-databases：备份所有数据库的所有表，包含建库及use语句

4、-B | --databases DBNAME1 DBNAME2 ...：备份指定的一个或多个数据库中的所有表，包含建库及use语句

5、-d | --no-data：只备份表结构，不备份表数据

6、-E | --events：备份指定数据库相关的所有事件调度器

7、-F | --flush-logs：备份开始前会滚动一次二进制日志，并生成一个新日志文件

8、-l | --lock-tables：备份前锁定指定数据库的所有表

9、-q | --quick：此选项对于转储大表很有用，它强制mysqldump一次从服务器中检索一个表的行，而不是检索整个行集并在将其写出之前在内存中缓冲它

10、-R | --routines：备份指定数据库相关的所有存储过程和存储函数

11、-t | --no-create-info：只备份表数据，不备份表结构

12、--triggers：备份指定表相关的所有触发器，默认已启用该选项

13、-w | --where='where_condition'：按条件进行数据备份

14、-x | --lock-all-tables：备份前锁定所有数据库的所有表

备注：

（1）对于InnoDB存储引擎：支持热备，需要使用--single-transaction选项

（2）对于MyISAM存储引擎：只支持温备，备份时需要将--single-transaction选项替换为-l或-x选项以施加全局读锁锁定备份库，以保证数据的一致性

三、mysqldump使用示例：

1、备份所有库的所有表：

# mysqldump -E -F -R -q --single-transaction --master-data=2 -A -uroot -p > all_`date +%F`.sql

2、备份db1、db2数据库的所有表：

# mysqldump -E -F -R -q --single-transaction --master-data=2 -B db1 db2 -uroot -p > db_`date +%F`.sql

3、备份db1数据库的所有表：

# mysqldump -E -F -R -q --single-transaction --master-data=2 -B db1 -uroot -p > db1_`date +%F`.sql

4、备份db1数据库的tb1：

# mysqldump -E -F -R -q --single-transaction --master-data=2 db1 tb1 -uroot -p > tb1_`date +%F`.sql

备注：恢复时需要先创建db1数据库# mysqladmin -uroot -p create db1

5、备份db1数据库的tb1表中id大于3的记录：

# mysqldump -E -F -R -q --single-transaction --master-data=2 db1 tb1 -w 'id>3' -uroot -p > tb1_`date +%F`.sql

备注：恢复时需要先创建db1数据库# mysqladmin -uroot -p create db1

四、使用mysqldump对db数据库进行全量备份，并通过二进制日志进行时间点恢复：

1、演示环境：

IP	操作系统	数据库版本	安装方式	默认存储引擎	角色
192.168.1.145	CentOS   7.6 x86_64	MariaDB-10.3.15	yum	InnoDB	备份
192.168.1.146	CentOS   7.6 x86_64	MariaDB-10.3.15	yum	InnoDB	恢复

备注：两个节点的服务器时间同步

2、两个节点的server.cnf配置文件如下所示：

# cat /etc/my.cnf.d/server.cnf

[mysqld]

port=3306

socket=/var/lib/mysql/mysql.sock

datadir=/var/lib/mysql

log-error=/var/log/mariadb.log

lower_case_table_names=1

character-set-server=utf8mb4

collation-server=utf8mb4_general_ci

log-bin=mysql-bin

binlog_format=mixed

sync_binlog=1

innodb_file_per_table=1

skip_name_resolve=1

slow_query_log=1

slow_query_log_file=mariadb-slow.log

3、查看192.168.1.145主机的初始二进制日志信息：

# mysql -uroot -p

MariaDB [(none)]> show binary logs;

MariaDB [(none)]> show master status;

4、192.168.1.145主机中创建测试数据库和表：

MariaDB [(none)]> create database db;

MariaDB [(none)]> use db;

MariaDB [db]> create table tb(id int,name varchar(10));

MariaDB [db]> insert into tb values(1,'张三'),(2,'李四'),(3,'王五'),(4,'赵六');

MariaDB [db]> select * from tb;

MariaDB [db]> show master status;

备注：创建测试数据库和表的操作已记入二进制日志中

5、192.168.1.145主机的db数据库通过mysqldump执行全量备份：

# mkdir -pv /backup

# mysqldump -E -F -R -q --single-transaction --master-data=2 -B db -uroot -p > /backup/db_`date +%F`.sql

# less /backup/db_2019-05-20.sql，找到已被注释的CHANGE MASTER TO语句：

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=342;

MariaDB [db]> show binary logs;

MariaDB [db]> show master status;

备注：由于执行mysqldump时附加了-F（--flush-logs）选项，所以二进制日志滚动，并生成了一个新文件mysql-bin.000002，之后引起数据库数据变化的操作都会记录在此二进制日志文件中，并且新的二进制日志的事件位置为385

6、192.168.1.145主机的db数据库在全量备份后执行如下修改操作：

MariaDB [db]> insert into tb values(5,'jack'),(6,'mary');

MariaDB [db]> delete from tb where id=3;

MariaDB [db]> update tb set name='keyso' where id=2;

MariaDB [db]> select * from tb;

MariaDB [db]> show master status;

备注：db数据库的修改操作已记入二进制日志中

7、192.168.1.145主机滚动二进制日志，并通过mysqlbinlog导出sql语句，进行增量备份：

# mysqladmin -uroot -p flush-binary-log

# mysqlbinlog -uroot -p -j 342 /var/lib/mysql/mysql-bin.000002 | less

# mysqlbinlog -uroot -p -j 342 /var/lib/mysql/mysql-bin.000002 > /backup/incre_`date +%F`.sql

备注：-j选项中的342和mysql-bin.000002来自上述CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=342;语句

MariaDB [db]> show binary logs;

MariaDB [db]> show master status;

8、将192.168.1.145主机/backup目录中的db_2019-05-20.sql和incre_2019-05-20.sql复制至192.168.1.146主机/backup目录中：

# scp /backup/*.sql root@192.168.1.146:/backup

9、192.168.1.146主机恢复数据前关闭会话级别二进制日志：

# mysql -uroot -p

MariaDB [(none)]> show variables like 'sql_log_bin';

MariaDB [(none)]> set sql_log_bin=0;

MariaDB [(none)]> show variables like 'sql_log_bin';

10、查看192.168.1.146主机的初始二进制日志信息：

MariaDB [(none)]> show binary logs;

MariaDB [(none)]> show master status;

11、192.168.1.146主机导入全量备份db_2019-05-20.sql：

MariaDB [(none)]> source /backup/db_2019-05-20.sql

MariaDB [(none)]> select * from db.tb;

12、192.168.1.146主机导入增量备份incre_2019-05-20.sql，根据二进制日志进行时间点恢复，将全量备份之后的修改操作进行重放，全部导入操作执行成功后开启会话级别二进制日志：

MariaDB [(none)]> source /backup/incre_2019-05-20.sql

MariaDB [(none)]> select * from db.tb;

MariaDB [(none)]> show master status;

备注：二进制日志中事件位置没有发生变化，说明导入操作没有记入二进制日志中

MariaDB [(none)]> set sql_log_bin=1;

MariaDB [(none)]> show variables like 'sql_log_bin';

备注：下一次的增量备份执行时先滚动二进制日志，然后通过mysqlbinlog命令将mysql-bin.000003中的sql语句导出为.sql文件，复制至192.168.1.146主机进行恢复即可，恢复数据前关闭会话级别的二进制日志

转载于:https://blog.51cto.com/qiuyue/2399999