签到成功

知道了

CNDBA社区CNDBA社区

MariaDB 的逻辑备份与恢复

2018-02-09 01:17 3639 0 原创 MySQL
作者： dave
本文链接：http://www.cndba.cn/dave/article/2650

1 MariaDB 的逻辑备份

1.1 Mysqldump 工具说明

mysqldump是MariaDB自带的逻辑备份工具。它的备份原理是，通过协议连接到MariaDB数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的insert语句，当我们需要还原这些数据时，只要执行这些insert语句，即可将对应的数据还原。

http://www.cndba.cn/dave/article/2650

mysqldump的优点：http://www.cndba.cn/dave/article/2650

可以直接使用文本处理工具处理对应的备份数据，因为备份数据已经被mysqldump转换为了对应的insert语句，可以借助文件系统中的文本处理工具对备份数据进行直接处理。

mysqldump的缺点：

http://www.cndba.cn/dave/article/2650

无法备份索引等信息（因为它只是单纯的将需要备份的数据查询出来，还原后需要重建索引）
当数据为浮点类型时，会出现精度丢失

mysqldump的备份过程属于逻辑备份，备份速度、恢复速度与物理备份工具相比较慢，而且mysqldump备份的过程是串行化的，不能并行的进行备份。

1.2 Mysqldump的常用选项

关于mysqldump的完整选项，可以参考命令的help：

[root@www.cndba.cn/dave ~]# mysqldump –help
1.2.1 选项：—master-data
--master-data[=#]   This causes the binary log position and filename to be appended to the output. If equal to 1, will print it as a CHANGE MASTER command; if equal to 2, that command will be prefixed with a comment symbol. This option will turn --lock-all-tables on, unless --single-transaction is specified too (in which case a global read lock is only taken a short time at the beginning of the dump; don't forget to read about --single-transaction below). In all cases, any action on logs will happen at the exact moment of the dump. Option automatically turns --lock-tables off.

当数据库开启了二进制日志，在使用mysqldump进行备份时，可以使用—master-data选项，标记备份开始时binlog所对应的position（位置）。

关于MariaDB 二进制日志的说明可以参考：
MariaDB 二进制日志
http://www.cndba.cn/dave/article/2645

—master-data选项有3个可用值，0、 1 、2。

  1. 设置为0：表示在使用mysqldump进行备份时，不记录对应二进制日志文件位置，这也是默认值。
  2. 设置为1：表示在使用mysqldump进行备份时，记录对应二进制日志文件位置，当设置为1后，则会在备份文件中生成对应的”CHANGE MASTER TO”语句，此语句中标明了备份开始时二进制日志的前缀名以及其所处的position(位置)。
  3. 设置为2：表示在使用mysqldump进行备份时，记录对应二进制日志文件的位置，如果将此选项的值设置为2，则会在备份文件中生成对应的”CHANGE MASTER TO”语句，此语句中标明了备份开始时二进制日志的前缀名以及其所处的position(位置)，但是”CHANGE MASTER TO”语句将会被注释，所以这里只是单纯的记录备份时的二进制日志文件位置。
1.2.2 选项：—flush-logs
   -F, --flush-logs    Flush logs file in server before starting dump. Note that if you dump many databases at once (using the option -databases= or --all-databases), the logs will be flushed for each database dumped. The exception is when using --lock-all-tables or --master-data: in this case the logs will be flushed only once, corresponding to the moment all tables are locked. So if you want your dump and the log flush to happen at the same exact moment you should use --lock-all-tables or --master-data with --flush-logs.

备份开始时滚动一次二进制日志，这样保证从对应的二进制日志文件的开头部分开始重放日志。

1.2.3 其他常用选项

—routines选项：表示备份时，存储过程和存储函数也会被备份。
—triggers选项：表示备份时，触发器会被备份。
—events选项：表示备份时，事件表会被备份。

1.3 使用mysqldump命令生成INSERT语句
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| cndba              |
| information_schema |
| mysql              |
| performance_schema |
| world              |
+--------------------+
5 rows in set (0.00 sec)

—备份所有数据库

[root@www.cndba.cn/dave mysql]# mysqldump -u root -p --all-databases > /tmp/alldb.sql
Enter password: 
[root@www.cndba.cn/dave mysql]# ll /tmp/alldb.sql
-rw-r--r-- 1 root root 722671 Feb  8 18:43 /tmp/alldb.sql

或者：
[root@www.cndba.cn/dave mysql]# mysqldump -u root -p --flush-logs --master-data=2 --single-transaction --routines --triggers --events --all-databases  > alldb.sql

—备份某些数据库

[root@www.cndba.cn/dave mysql]# mysqldump -u root -p --databases cndba > /tmp/cndba.sql
Enter password: 
[root@www.cndba.cn/dave mysql]# ll /tmp/cndba.sql 
-rw-r--r-- 1 root root 2005 Feb  8 18:45 /tmp/cndba.sql
[root@www.cndba.cn/dave mysql]# cat /tmp/cndba.sql |more
-- MySQL dump 10.16  Distrib 10.2.12-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: cndba
-- ------------------------------------------------------
-- Server version    10.2.12-MariaDB-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

备份某数据库中的表http://www.cndba.cn/dave/article/2650

mysql> use cndba
Database changed
mysql> show tables;
+-----------------+
| Tables_in_cndba |
+-----------------+
| dave            |
+-----------------+
1 row in set (0.00 sec)

[root@www.cndba.cn/dave mysql]# mysqldump -u root -p cndba dave > /tmp/dave.sql
Enter password: 
[root@www.cndba.cn/dave mysql]# ll /tmp/dave.sql 
-rw-r--r-- 1 root root 1865 Feb  8 18:48 /tmp/dave.sql
[root@www.cndba.cn/dave mysql]# cat /tmp/dave.sql 
-- MySQL dump 10.16  Distrib 10.2.12-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: cndba
-- ------------------------------------------------------
-- Server version    10.2.12-MariaDB-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `dave`
--

DROP TABLE IF EXISTS `dave`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `dave` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `dave`
--

LOCK TABLES `dave` WRITE;
/*!40000 ALTER TABLE `dave` DISABLE KEYS */;
INSERT INTO `dave` VALUES (1,'dave'),(2,'oracle'),(2,'oracle');
/*!40000 ALTER TABLE `dave` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2018-02-08 18:48:01
[root@www.cndba.cn/dave mysql]#
1.4 备份的一致性

在备份期间要保证数据备份的一致性，可以通过以下两种方法做到：

1.4.1 同一时刻取出所有数据

对于事务支持的存储引擎，如Innodb ，可以通过控制将整个备份过程在同一个事务中，使用“—single-transaction”选项。

例如：

# mysqldump --single-transaction test > test_backup.sql

因为当使用—single-transaction后，此时—lock-all-tables被置为false了，innodb是用snapshot来保证一致性，但是myisam并不适用于—single-transaction的情况。

1.4.2 数据库中的数据处于静止状态

通过锁表参数
—lock-tables 每次锁定一个数据库的表，此参数是默认为true；
—lock-all-tables 一次锁定所有的表，适用于dump的表分别处于各个不同的数据库中的情况。

2 MaraiDB 的逻辑备份恢复

2.1 mysqldump 恢复

mysqldump 可以生成sql，txt等格式的文件。 我们上面小节演示的是生成sql的文件，这个文件就是insert的语句，所以可以直接执行，直接使用mysql命令导入或者在mysql命令行里执行source 来完成恢复。 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| cndba              |
| information_schema |
| mysql              |
| performance_schema |
| world              |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database cndba;
Query OK, 1 row affected (0.00 sec)

mysql> create database cndba;
Query OK, 1 row affected (0.00 sec)

[root@www.cndba.cn/dave mysql]# mysql -u root -p cndba < /tmp/cndba.sql
Enter password: 
[root@www.cndba.cn/dave mysql]#

mysql> use cndba;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_cndba |
+-----------------+
| dave            |
+-----------------+
1 row in set (0.00 sec)

mysql -u root -p cndba < /tmp/cndba.sql

Mysql>source /tmp/cndba.sql

这里只是简单做了一个数据的导出导入，数据只能恢复到备份的时刻，在实际生产中，我们更多的是需要在此基础上进行二进制日志的恢复。

2.2 利用二进制日志推进数据

准备测试数据http://www.cndba.cn/dave/article/2650

mysql> use cndba
Database changed
mysql> create table oracle(id int,name varchar(100));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into oracle values(1,'http://www.cndba.cn/dave');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |    746734 |
| mysql-bin.000002 |       722 |
| mysql-bin.000003 |    728910 |
| mysql-bin.000004 |      3196 |
+------------------+-----------+
4 rows in set (0.00 sec)

全备数据库cndba

http://www.cndba.cn/dave/article/2650 

[root@www.cndba.cn/dave data]# rm -rf /tmp/cndba.sql
[root@www.cndba.cn/dave data]# mysqldump -u root -p --flush-logs --master-data=2 --single-transaction --routines --triggers --events cndba > /tmp/cndba.sql
Enter password: 
[root@www.cndba.cn/dave data]# ll /tmp/cndba.sql 
-rw-r--r-- 1 root root 2796 Feb  8 19:16 /tmp/cndba.sql
[root@www.cndba.cn/dave data]#

刷新日志flush logs 

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |    746734 |
| mysql-bin.000002 |       722 |
| mysql-bin.000003 |    728910 |
| mysql-bin.000004 |      3243 |
| mysql-bin.000005 |       432 |
| mysql-bin.000006 |       432 |
| mysql-bin.000007 |       385 |
+------------------+-----------+
7 rows in set (0.00 sec)

生成一个新的二进制日志，记录备份后的DML操作，方便后续恢复

进行新的DML操作

mysql> insert into oracle values(2,'http://www.ahdba.com');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

用全备恢复数据库http://www.cndba.cn/dave/article/2650

注意：恢复前，先flush logs，将利用全备恢复的信息记录到新的日志中。假如不进行该步骤，利用全备恢复的信息会记录到当前的二进制日志（mysql-bin.0000076）中，在后续恢复二进制日志mysql-bin.0000076时，会执行全备恢复的步骤，相当于没有恢复该二进制日志。

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 |      385 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

恢复数据库：http://www.cndba.cn/dave/article/2650

mysql> drop table oracle;
Query OK, 0 rows affected (0.01 sec)

[root@www.cndba.cn/dave data]# mysql -u root -p cndba < /tmp/cndba.sql
Enter password: 
[root@www.cndba.cn/dave data]#

mysql> select * from oracle;
+------+--------------------------+
| id   | name                     |
+------+--------------------------+
|    1 | http://www.cndba.cn/dave |
+------+--------------------------+
1 row in set (0.00 sec)

利用二进制日志恢复

[root@www.cndba.cn/dave data]# mysqlbinlog --no-defaults /mysql/data/mysql-bin.000007 | mysql -u root -p
Enter password: 
[root@www.cndba.cn/dave data]#

这里是直接将mysqlbinlog抽取的日志直接传入mysql执行了。也可以先将结果保存到sql文件，在source执行这个文件。 

[root@www.cndba.cn/dave data]# mysqlbinlog --stop-position=254 /mysql/data/ mysql-bin.000007 > /tmp/binlog.sql

验证，成功利用二进制日志推进数据，要做到完全恢复，重点就是定位到准确的二进制文件

mysql> select * from oracle;
+------+--------------------------+
| id   | name                     |
+------+--------------------------+
|    1 | http://www.cndba.cn/dave |
|    2 | http://www.ahdba.com     |
+------+--------------------------+
2 rows in set (0.00 sec)

3 加快逻辑恢复速度的方法

对于innodb 的数据库，可以通过对以下参数的调整来加速恢复的速度。

log-bin=OFF

在恢复时可以关闭二进制日志，避免不必要的IO。

sync_binlog=0

如果关闭了二进制日志，则这个选项不调整。
但如果因为特殊原因不能关闭二进制日志时，可以考虑减少binlog的fsync来减少磁盘IO压力。

Innodb_buffer_pool_size 尽可能大

尽可能大的配置Innodb_buffer_pool_size 来保证更多的脏页能够存在于BP中，增大潜在的写入合并的可能性，从而减少了磁盘的IO。

Innodb_logfile_size=1G 或更大

增大redolog的体积可以推迟blocking checkpoint发生的时间，也一定程度缓解adaptive flush的刷写频率。调整这个参数对于恢复表体积远大于Innodb Buffer Size时非常有用。

Innodb_doublewrite=OFF

由于不存在宕机风险（即使宕机，也就是重新再恢复一次），所以doublewrite也可以不需要了。

Innodb_flush_log_at_trx_commit=0

同上，由于不存在宕机风险，无需那么卖力的刷写redo log。

Innodb_flush_neighbors=1http://www.cndba.cn/dave/article/2650

由于逻辑导入更多的是顺序写入，打开flush neighbor以后不单能把IO pattern更贴近顺序。同时，innodb的内部逻辑也会把多个page合并成一次IO进行提交，刷写性能更高。http://www.cndba.cn/dave/article/2650

版权声明：本文为博主原创文章，未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的：当他回首往事的时候，他不会因为虚度年华而悔恨，也不会因为碌碌无为而羞耻；这样，在临死的时候，他就能够说：“我的整个生命和全部精力，都已经献给世界上最壮丽的事业....."

  • 2296
    原创
  • 3
    翻译
  • 582
    转载
  • 198
    评论
  • 访问：8501357次
  • 积分：4477
  • 等级：核心会员
  • 排名：第1名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ