关于MySQL 5.7.26备份报错解决

原创

雨夜v1

修改于 2021-03-14 17:16:25

1.6K0

修改于 2021-03-14 17:16:25

文章被收录于专栏：雨夜-Elasticsearch成长专栏

前言

今天同事过来跟我说有个备份失败了，让我帮忙看看。经过检查，感觉是DDL导致的备份失败。

具体报错

InnoDB: An optimized (without redo logging) DDLoperation has been performed. All modified pages may not have been flushed to the disk yet.

问题解决过程

为了解决问题，找了一些文章，其中社区的一篇博客说明的很详细。

解决这个问题，Percona XtraBackup官方提供了三个选项 xtrabackup --lock-ddl, xtrabackup --lock-ddl-timeout, xtrabackup --lock-ddl-per-table。

MySQL 5.7 redo logs会跳过一些DDL,Percona XtraBackup 监测到redo log 有跳过时，它会中止备份以防止创建一个损坏的备份，所以上面的问题也就出现了。

官方解释了，MySQL 5.7 redo log会跳过一些DDL的这个功能是一个新的功能，这个新功能叫"Sorted Index Builds",具体解释就是，索引创建的时候分成三个阶段:

There are three phases to an index build. In the first phase, the clustered index is scanned, and index entries are generated and added to the sort buffer. When the sort buffer becomes full, entries are sorted and written out to a temporary intermediate file. This process is also known as a “run”. In the second phase, with one or more runs written to the temporary intermediate file, a merge sort is performed on all entries in the file. In the third and final phase, the sorted entries are inserted into the B-tree.

第一阶段，扫描clustered index，生成索引项并添加到sort buffer。当sort buffer满时，索引项将被排序并写入临时文件。这个过程也被称为 "run"；
第二阶段，一个或多个线程写入临时文件或对sort buffer的索引项进行合并排序；
第三阶段，排序完后的索引项插入B-tree。

在引入Sorted Index Builds构建之前，使用insert API将索引项一次一个记录插入到B-tree中。这种方法包括打开B-tree游标找到插入位置，然后使用乐观的插入方式将索引项插入到B-tree页面中。如果由于page也满了导致插入失败，则会进行悲观的插入，这种创建索引会导致page的不断分裂和合并，并且这个过程还需要找到也得插入位置，是成本很高的，因此引入了Sorted Index Builds。

官当在“Sorted Index Builds and Redo Logging”这里提到

在使用Sorted Index方式创建索引的时候会关闭掉Redo Logging，他是通过检查点的方式来保证创建索引的高可用性。检查点强制将所有脏页写入磁盘，在索引创建期间， page cleaner会定期的刷新脏页以保证检查点的快速前推。通常情况下，只有当干净页面的数量低于设置的阈值时，page cleaner才回去刷新脏页，但是在创建索引的过程中， page cleaner会快速刷新，来减少检查点的开销。

也正因为这个创建索引的时候关闭了Redo log,所以才会出现我们备份的问题。

InnoDB: An optimized (without redo logging) DDLoperation has been performed. All modified pages may not have been flushed to the disk yet.

为了避免该问题官方提供的解决方案就是加上--lock-ddl

模拟对应场景

1. 创建测试表

sysbench /usr/share/sysbench/oltp_read_only.lua --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-socket=/appdata/mysql/mysql.sock --mysql-user=root --mysql-password="paic1234A?" --mysql-db=db1  --table-size=2000000  prepare


sysbench /usr/share/sysbench/oltp_read_only.lua --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-socket=/appdata/mysql/mysql.sock --mysql-user=root --mysql-password="paic1234A?" --mysql-db=db2 --table-size=2000000  --threads=50 prepare

2.发起DDL

vim table1.sql
use db1 ;drop table if exists db1.sb1;
use db1 ;create table sb1 like select id,c from sbtest1 where id < 150000;
use db1 ;create unique index ix on sb1 (id);

vim table2.sql
use db2 ;drop table if exists db2.sb1;
use db2 ;create table sb1 as select id,c from sbtest1 where id < 150000;
use db2;create unique index ix on sb1 (id);

测试脚本
vim test.sh
#!/bin/bash
#Author zxb
#Date 2021/03/14
mysql -uroot -p"paic1234A?" < table.sql
sleep 1
mysql -uroot -p"paic1234A?" <  table1.sql

执行方式

while true; do sh  test.sh; done

3.发起备份

xtrabackup --defaults-file=/etc/my.cnf \
--user=root --password='paic1234A?'  --target-dir=/appdata/backup/  \
--backup --host=127.0.0.1 --port=3306 --binlog-info=AUTO  --parallel 4 \
--check-privileges --no-version-check --lock-ddl

4.备份的结果如下:

Shell
210314 11:16:56 Executing LOCK TABLES FOR BACKUP...
xtrabackup: Transaction log of lsn (2808294311) to (2808304872) was copied.
210314 11:20:42 completed OK!

根据官方说明--lock-ddl只适用于Percona Server的MySQL服务器，如果像MariaDB是不适用的。产生的报错如下:

021-03-14 12:08:32 ERROR    FULL BACKUP FAILED!
021-03-14 12:08:37 ERROR    170726 12:08:32 Connecting to MySQL server host: 127.0.0.1, user: msandbox, password: set, port: 10207, socket: /tmp/mysql_sandbox10207.sock
Using server version 10.2.7-MariaDB
021-03-14 12:08:32 Error: LOCK TABLES FOR BACKUP is not supported.

所以官方更推荐使用--lock-ddl-per-table，这个参数使用任何server，可以从以下例子看出来

Table: CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT);
 
  Cases:
 
  connection 1:
 
  - BEGIN; SELECT * FROM sb1 LIMIT 1; <--- MDL
  
  connection 2:
 
  - UPDATE sb1 SET c = '288' WHERE id = 34;    <--- completes OK
 
  connection 3:
 
  - CREATE INDEX sb1_1 ON sb1 (c(10));         <--- WAITING for MDL
 
  connection 2:
 
  - UPDATE sb1 SET c = '288' WHERE id = 34;    <--- WAITING for MDL
 
  connection 1:
 
  - COMMIT;
 
  connection 2 and 3 are able to complete now

如上，第一个会话持有MDL锁，后面的会话就不能够进行DDL操作了，这样就实现了，备份不被打断了。

复现方式如下：

mysql> use db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE `sb1` (
    ->   `id` int(11) NOT NULL DEFAULT '0',
    ->   `c` char(120) NOT NULL DEFAULT '',
    ->   UNIQUE KEY `ix` (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into sb1  select id,c from sbtest1 where id < 150000;
Query OK, 21428 rows affected (0.31 sec)
Records: 21428  Duplicates: 0  Warnings: 0

mysql> select count(*) from sb1;
+----------+
| count(*) |
+----------+
|    21428 |
+----------+
1 row in set (0.01 sec)

mysql> select * from sb1 limit 3;
+----+-------------------------------------------------------------------------------------------------------------------------+
| id | c                                                                                                                       |
+----+-------------------------------------------------------------------------------------------------------------------------+
|  5 | 83868641912-28773972837-60736120486-75162659906-27563526494-20381887404-41576422241-93426793964-56405065102-33518432330 |
| 12 | 38014276128-25250245652-62722561801-27818678124-24890218270-18312424692-92565570600-36243745486-21199862476-38576014630 |
| 19 | 33973744704-80540844748-72700647445-87330233173-87249600839-07301471459-22846777364-58808996678-64607045326-48799346817 |
+----+-------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

产生MDL锁后，在进行备份

 xtrabackup --defaults-file=/etc/my.cnf \
--user=root --password='paic1234A?'  --target-dir=/appdata/backup/  \
--backup --host=127.0.0.1 --port=3306 --binlog-info=AUTO  --parallel 4 \
--check-privileges --no-version-check --lock-ddl-per-table

发起备份后，执行connection 2和connection 3的操作

然后看到的现象如下：

mysql> show processlist;
mysql> show processlist;
+-----+-------------+-----------------+------+---------+------+--------------------------------------------------------+------------------+
| Id  | User        | Host            | db   | Command | Time | State                                                  | Info             |
+-----+-------------+-----------------+------+---------+------+--------------------------------------------------------+------------------+
| 4   | system user |                 | NULL | Connect | 3415 | executing                                              | NULL             |
| 13  | root        | localhost       | db1  | Querry  |   26 | Waiting for table metadata lock                        | CREATE INDEX sb1 |
_1 ON sb1 (c(10))      |
| 12  | root        | localhost       | db1  | Query   |    6 | Waiting for table metadata lock                        | UPDATE sb1 SET c |
 = '288' WHERE id = 34 |
| 327 | root        | 127.0.0.1:53538 | NULL | Sleep   |    8 |                                                        | NULL             |
| 328 | root        | 127.0.0.1:53540 | NULL | Sleep   |    2 |                                                        | NULL             |
+-----+-------------+-----------------+------+---------+------+--------------------------------------------------------+------------------+
5 rows in set (0.00 sec)

当然也是可以备份成功的

aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-532879'
210314 16:37:35 [00] Writing /appdata/backup/backup-my.cnf
210314 16:37:35 [00]        ...done
210314 16:37:35 [00] Writing /appdata/backup/xtrabackup_info
210314 16:37:35 [00]        ...done
210314 16:37:35 Unlocking MDL for all tablesxtrabackup: Transaction log of lsn (18210684206) to (18210684215) was copied.
210314 16:37:35 completed OK!