From version 2.4.8, xtrabackup introduces a new option --lock-ddl-per-table, the purpose of this option has been detailed here. However, this option causes xtrabackup not to use FLUSH TABLES WITH READ LOCK any more, and wrong binlog information saved in file xtrabackup_binlog_info. Since the binlog position isn’t right, the backup is not consistent.

Let me show you the code.

First, let’s see what this option does.

backup_mysql.cc#L30

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
void
mdl_lock_table(ulint space_id)
{
MYSQL_RES *mysql_result = NULL;
MYSQL_ROW row;
char *query;

mutex_enter(&mdl_lock_con_mutex);

xb_a(asprintf(&query,
"SELECT NAME FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES "
"WHERE SPACE = %lu", space_id));

mysql_result = xb_mysql_query(mdl_con, query, true);

while ((row = mysql_fetch_row(mysql_result))) {
char *lock_query;
char table_name[MAX_FULL_NAME_LEN + 1];

innobase_format_name(table_name, sizeof(table_name), row[0]);

msg_ts("Locking MDL for %s\n", table_name);

xb_a(asprintf(&lock_query,
"SELECT * FROM %s LIMIT 1",
table_name));

xb_mysql_query(mdl_con, lock_query, false, false);

free(lock_query);
}

mysql_free_result(mysql_result);
free(query);

mutex_exit(&mdl_lock_con_mutex);
}

This option makes sure that before copying a ibd file, a MDL lock is acquired against that table. It blocks DDL opertion but NOT DML. The lock will be released at the end of the backup.

Second, the other effect of this option.

When finished backup innodb tables, non-InnoDB tables backup will be started. xtrabackup will use FLUSH TABLES WITH READ LOCK to backup non-InnoDB tables. And this lock is issued in function lock_tables-maybe:

backup_mysql.cc#L1064

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
bool
lock_tables_maybe(MYSQL *connection)
{
if (tables_locked || opt_lock_ddl_per_table) {
return(true);
}

if (have_backup_locks) {
return lock_tables_for_backup(connection);
}

if (have_lock_wait_timeout) {
/* Set the maximum supported session value for
lock_wait_timeout to prevent unnecessary timeouts when the
global value is changed from the default */
xb_mysql_query(connection,
"SET SESSION lock_wait_timeout=31536000", false);
}
...

msg_ts("Executing FLUSH TABLES WITH READ LOCK...\n");

if (opt_kill_long_queries_timeout) {
start_query_killer();
}

if (have_galera_enabled) {
xb_mysql_query(connection,
"SET SESSION wsrep_causal_reads=0", false);
}

xb_mysql_query(connection, "FLUSH TABLES WITH READ LOCK", false);

if (opt_kill_long_queries_timeout) {
stop_query_killer();
}

tables_locked = true;

return(true);
}

As you can see, the first if in this function is if (tables_locked || opt_lock_ddl_per_table). This means if you use option --lock-ddl-per-table, true will be returned immediately. All the code rest in this function will be ignored. The sql FLUSH TABLES WITH READ LOCK will not be executed. So client can still write to the database.

I changed the this logic, removed opt_lock_ddl_per_table in the firstif.

1
2
3
4
5
...
if (tables_locked) {
return(true);
}
...

Build the program and test. In the bakcup log, I found the expected message:

1
2
3
4
5
190512 13:43:55 [02]        ...done
190512 13:43:56 >> log scanned up to (4343106730677)
190512 13:43:56 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
190512 13:43:56 Executing FLUSH TABLES WITH READ LOCK...
190512 13:43:56 Starting to backup non-InnoDB tables and files

These messages(Executing FLUSH TABLES WITH READ LOCK...) should be printed at the end of backuping InnoDB tables and before start to backup non-InnoDB tables.

When using the official binary xtrabackup, there’re no these messages printed out(with --lock-ddl-per-table)! The steps of issuing FLUSH TABLES WITH READ LOCK are skipped.

So I think this is a bug. Even in the newest version (v2.4.14), the problem still exists. Click for the code.