Personal blog of Yzmir Ramirez

When we need to upgrade from MySQL 5.7 to MySQL 8, we could choose to do an in-place upgrade or set up another MySQL 8 server(s) from the existing running MySQL 5.7 replica.

This article will explain how to set up MySQL 8 as a replica from an existing MySQL 5.7 server with Percona XtraBackup.

In my lab, we have two test nodes:

PRIMARY:  mysql57 (192.168.56.111)    with   Percona Server 5.7 and  xtrabackup 2.4 installed
REPLICA:  mysql8    (192.168.56.113)   with  Percona Server 8  and   xtrabackup 8.0 installed

0. Pre-flight check with MySQL upgrade checker utility

Let’s run the MySQL upgrade checker utility to verify whether MySQL 5.7 server instances are ready for an upgrade.

MySQL  localhost  JS > util.checkForServerUpgrade('root@localhost:3306', {"password":"####", "targetVersion":"8.0.32", "configPath":"/etc/my.cnf"})

The MySQL server at localhost:3306, version 5.7.26-29-log - Percona Server
(GPL), Release 29, Revision 11ad961, will now be checked for compatibility
issues for upgrade to MySQL 8.0.32...
1) Usage of old temporal type
  No issues found
.....
 20) Tables recognized by InnoDB that belong to a different engine
   No issues found
 21) Issues reported by 'check table x for upgrade' command
   No issues found
22) New default authentication plugin considerations
   Warning: The new default authentication plugin 'caching_sha2_password' offers
     more secure password hashing than previously used 'mysql_native_password'
    ....
 23) Columns which cannot have default values
   No issues found
....
 25) Check for orphaned routines in 5.7
   No issues found
 Errors:   0
 Warnings: 5
 Notices:  0

For details about MySQL Upgrade Checker,  please refer to Upgrading to MySQL 8? Meet the MySQL Shell Upgrade Checker Utility or mysql-shell-utilities-upgrade for details.

First, let’s try streaming copy from mysql57 to mysql8 server as this does not need additional storage.

1. On the replica mysql8 server, run:

[root@mysql8 ~]# nc -l -p 2222 | unpigz -c | xbstream -x -C /var/lib/mysql

The above command would be there to receive the backup until the stream copy was done.

2. On primary server mysql5.7, stream copy to mysql8 server:

[root@mysql57 ~]# xtrabackup --stream=xbstream --parallel=4 /tmp | pigz -c --fast | nc -w 2 192.168.56.113 2222
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --server-id=1 --log_bin=mysql-bin --parallel=4
xtrabackup: recognized client arguments:
230228 22:58:57 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".
230228 22:58:57  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
230228 22:58:58  version_check Connected to MySQL server
230228 22:58:58  version_check Executing a version check against the server...
230228 22:58:58  version_check Done.
230228 22:59:11 [00]        ...done
230228 22:59:11 [00] Streaming <STDOUT>
230228 22:59:11 [00]        ...done
xtrabackup: Transaction log of lsn (31871969) to (31872026) was copied.
Shutting down plugin 'keyring_file'
230228 22:59:12 completed OK!

3. Prepare the backup on MySQL 8 with Percona XtraBackup 8.0, but you will get this error – [Xtrabackup] Unsupported redo log format 1] because of the version incompatible issue.

xtrabackup --prepare  --use-memory=3G --apply-log-only --target-dir=/var/lib/mysql
[root@mysql8 mysql]# xtrabackup --prepare  --use-memory=3G --apply-log-only --target-dir=/var/lib/mysql
xtrabackup: [Warning] option 'innodb_undo_tablespaces': unsigned value 0 adjusted to 2.
2023-02-28T23:05:23.993903-05:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1
2023-02-28T23:05:23.994329-05:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --prepare=1 --use-memory=3G --apply-log-only=1 --target-dir=/var/lib/mysql
xtrabackup version 8.0.32-25 based on MySQL server 8.0.32 Linux (x86_64) (revision id: 14f007fb)
2023-02-28T23:05:23.994404-05:00 0 [Note] [MY-011825] [Xtrabackup] cd to /var/lib/mysql/
2023-02-28T23:05:24.001029-05:00 0 [Note] [MY-011825] [Xtrabackup] This target seems to be not prepared yet.
2023-02-28T23:05:24.003369-05:00 0 [ERROR] [MY-011825] [Xtrabackup] Unsupported redo log format 1
2023-02-28T23:05:24.003425-05:00 0 [ERROR] [MY-011825] [Xtrabackup] This version of Percona XtraBackup can only perform backups and restores against MySQL 8.0 and Percona Server 8.0, please use Percona Xtrabackup 2.4 for this database.
[root@mysql8 mysql]#

4, You need to uninstall percona-xtrabackup-80 and install percona-xtrabackup-24 on the MySQL 8 server.

sudo yum remove percona-xtrabackup-80
sudo yum list | grep percona
sudo  yum install percona-xtrabackup-24

[root@mysql8 mysql]# xtrabackup --version
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup version 2.4.27 based on MySQL server 5.7.40 Linux (x86_64) (revision id: aae8e82)
[root@mysql8 mysql]#

Then, prepare the backup on the mysql8 server,

[root@mysql8 mysql]# xtrabackup --prepare  --use-memory=3G --apply-log-only --target-dir=/var/lib/mysql
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=1
xtrabackup: recognized client arguments: --prepare=1 --use-memory=3G --apply-log-only=1 --target-dir=/var/lib/mysql
xtrabackup version 2.4.27 based on MySQL server 5.7.40 Linux (x86_64) (revision id: aae8e82)
xtrabackup: cd to /var/lib/mysql/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
…….
InnoDB: xtrabackup: Last MySQL binlog file position 234, file name mysql-bin.000029
InnoDB: xtrabackup: Last MySQL binlog file position 234, file name mysql-bin.000029

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 31872035
InnoDB: Number of pools: 1
Shutting down plugin 'keyring_file'
230228 23:27:52 completed OK!
[root@mysql8 mysql]#

5. You could start the MySQL service on MySQL 8 server now, and MySQL 8 will upgrade for you.

You will get the server upgrade from ‘50700’ to ‘80031’ completed, as below:

[root@mysql8 lib]# chown -R mysql:mysql /var/lib/mysql
[root@mysql8 lib]# systemctl start mysql


2023-03-01T02:38:23.887913Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.31-23) initializing of server in progress as process 6660
2023-03-01T02:38:23.939215Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-03-01T04:28:38.397836Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.31-23) starting as process 8229
2023-03-01T04:28:38.530856Z 0 [Warning] [MY-010075] [Server] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 89c92c7e-b7e9-11ed-9369-080027f01654.
2023-03-01T04:28:38.749996Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.

2023-03-01T04:34:02.516924Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.31-23) starting as process 8388
2023-03-01T04:34:02.827172Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2023-03-01T04:34:02.827297Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-03-01T04:34:47.547671Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-03-01T04:35:10.890987Z 3 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2023-03-01T04:35:17.864546Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80031' started.
2023-03-01T04:35:41.732506Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80031' completed.
2023-03-01T04:35:52.304549Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-03-01T04:35:52.974222Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/lib/mysql/mysqlx.sock

6. You could set up replication with the steps below.

[root@mysql8 mysql]# cat /var/lib/mysql/xtrabackup_binlog_info
mysql-bin.000029	234	2fb728db-eb40-11eb-96c5-080027b6340a:1-78,
30b9be00-c21a-11ec-8eb2-0800275411eb:1
[root@mysql8 mysql]#
mysql8>STOP REPLICA;
mysql8>RESET MASTER;
mysql8>SET GLOBAL gtid_purged='2fb728db-eb40-11eb-96c5-080027b6340a:1-78,30b9be00-c21a-11ec-8eb2-0800275411eb:1';
mysql8>CHANGE REPLICATION SOURCE  TO
        SOURCE_HOST = '192.168.56.111',
        SOURCE_PORT = 3306,
        SOURCE_USER = 'repl',
        SOURCE_PASSWORD = ########',
        SOURCE_AUTO_POSITION = 1;
mysql8>START REPLICA;
mysql8> SHOW REPLICA STATUSG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.56.111
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000029
      Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            …
           Retrieved_Gtid_Set: 2fb728db-eb40-11eb-96c5-080027b6340a:79
            Executed_Gtid_Set: 2fb728db-eb40-11eb-96c5-080027b6340a:1-79,
30b9be00-c21a-11ec-8eb2-0800275411eb:1
                Auto_Position: 1

For now, we have set up the replication, and you could uninstall Percona XtraBackup 2.4 and reinstall Percona XtraBackup 8.0 for future backups on the MySQL 8 server.

If you feel that’s annoying, you could choose to prepare the backup first on the mysql57 server, but this might need additional storage. Please see below.

#1 Take the backup on mysql5.7

xtrabackup   --parallel=4 --backup   --socket=/var/lib/mysql/mysql.sock  --target-dir=/opt/mysql57backup/

xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --server-id=1 --log_bin=mysql-bin --parallel=4
xtrabackup: recognized client arguments: --user=root --password=* --backup=1 --socket=/var/lib/mysql/mysql.sock --target-dir=/opt/mysql57backup/
230301 13:43:05  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock' as 'root'  (using password: YES).
230301 13:43:05  version_check Connected to MySQL server
230301 13:43:05  version_check Executing a version check against the server...
….
Using server version 5.7.26-29-log
xtrabackup version 2.4.24 based on MySQL server 5.7.35 Linux (x86_64) (revision id: b4ee263)
........
230301 13:43:25 Executing UNLOCK TABLES
230301 13:43:25 All tables unlocked
230301 13:43:25 [00] Copying ib_buffer_pool to /opt/mysql57backup/ib_buffer_pool
230301 13:43:25 [00]        ...done
230301 13:43:25 Backup created in directory '/opt/mysql57backup/'
MySQL binlog position: filename 'mysql-bin.000029', position '408', GTID of the last change '2fb728db-eb40-11eb-96c5-080027b6340a:1-79,
30b9be00-c21a-11ec-8eb2-0800275411eb:1'
230301 13:43:26 completed OK!

#2. Prepare the backup on mysql57, then transfer to the mysql8 server.

xtrabackup --prepare   --socket=/var/lib/mysql/mysql.sock  --target-dir=/opt/mysql57backup/

xtrabackup version 2.4.24 based on MySQL server 5.7.35 Linux (x86_64) (revision id: b4ee263)
xtrabackup: cd to /opt/mysql57backup/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(31872026)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_hom
....
InnoDB: 5.7.35 started; log sequence number 31872533
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: page_cleaner: 1000ms intended loop took 12196ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 31872552
Shutting down plugin 'keyring_file'
230301 13:47:16 completed OK!

Then transfer to mysql8:

[root@mysql8 lib]# chown -R percona:percona /var/lib/mysql/
[root@mysql57 ~]# rsync -avpP -e ssh /opt/mysql57backup/   /var/lib/mysql/
percona@192.168.56.113:/var/lib/mysql/
percona@192.168.56.113's password:
sending incremental file list
./
backup-my.cnf
            515 100%    0.00kB/s    0:00:00 (xfr#1, to-chk=443/445)
ib_buffer_pool
            686 100%  669.92kB/s    0:00:00 (xfr#2, to-chk=442/445)
ib_logfile0
     50,331,648 100%    1.87MB/s    0:00:25 (xfr#3, to-chk=441/445)
ib_logfile1
     50,331,648 100%   16.09MB/s    0:00:02 (xfr#4, to-chk=440/445)
......................

 #3: Start MySQL service, and it will upgrade for you.

[root@mysql8 lib]# chown -R mysql:mysql /var/lib/mysql
[root@mysql8 lib]# systemctl start mysql

….
2023-03-01T19:12:38.752622Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.31-23) starting as process 21608

2023-03-01T19:12:40.212880Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2023-03-01T19:12:40.220504Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-03-01T19:17:48.087404Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.

2023-03-01T19:18:10.663895Z 3 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2023-03-01T19:18:13.814450Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80031' started.
2023-03-01T19:18:57.570769Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80031' completed.
………
2023-03-01T19:19:06.029463Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.31-23'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Percona Server (GPL), Release 23, Revision 71449379.

#4. Set up replication, similar to step 6 above:

[root@mysql57 ~]# cat  /opt/mysql57backup/xtrabackup_binlog_info
mysql-bin.000029	408	2fb728db-eb40-11eb-96c5-080027b6340a:1-79,30b9be00-c21a-11ec-8eb2-0800275411eb:1
[root@mysql57 ~]#


mysql8>STOP REPLICA;
mysql8>RESET MASTER;
mysql8>SET GLOBAL gtid_purged='2fb728db-eb40-11eb-96c5-080027b6340a:1-79,30b9be00-c21a-11ec-8eb2-0800275411eb:1';
mysql8>	CHANGE REPLICATION SOURCE  TO
        SOURCE_HOST = '192.168.56.111',
        SOURCE_PORT = 3306,
        SOURCE_USER = 'repl',
        SOURCE_PASSWORD = ########',
        SOURCE_AUTO_POSITION = 1;
mysql8>START REPLICA;
mysql8> SHOW REPLICA STATUSG

*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.56.111
                  Master_User: repl
                  Master_Port: 3306
        ……….
        Relay_Master_Log_File: mysql-bin.000029
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

Conclusion

You could either use Percona XtraBackup 2.4 to prepare the MySQL 5.7 backup on MySQL 8 servers after the stream copied, or use Percona XtraBackup 2.4 and prepare the backup on MySQL 5.7 first and then transfer to MySQL 8. Finally, let MySQL 8 binary upgrade it automatically for you.

I hope this is helpful for you to upgrade from MySQL 5.7 to MySQL 8  with Percona XtraBackup!

Percona XtraBackup is a free, open source, complete online backup solution for all versions of Percona Server for MySQL and MySQL. It performs online non-blocking, tightly compressed, highly secure backups on transactional systems so that applications remain fully available during planned maintenance windows.

Try Percona XtraBackup today!

Percona XtraBackup is an open source hot backup utility for MySQL-based servers. To take consistent and hot backup, it uses various locking methods, especially for non-transactional storage engine tables. This blog post discusses the cause and possible solution for queries with ?Waiting for table flush state in processlist when taking backups using Percona XtraBackup. Only MySQL 5.7 version is affected by this, as per my tests.

Type of locks taken by Percona XtraBackup

Before discussing the main issue, let’s learn about the type of locks used by Percona XtraBackup to take consistent backups. Percona XtraBackup uses backup locks as a lightweight alternative to FLUSH TABLES WITH READ LOCK. This feature is available in Percona Server for MySQL 5.6+. MySQL 8.0 allows acquiring an instance-level backup lock via the LOCK INSTANCE FOR BACKUP statement.

Locking is only done for MyISAM storage engine tables and other non-InnoDB tables after Percona XtraBackup finishes backing up all InnoDB/XtraDB data and logs. With backup locks, Percona XtraBackup uses LOCK TABLES FOR BACKUP automatically to copy non-InnoDB data and avoid blocking DML queries that modify InnoDB tables.

So for Percona Server for MySQL 5.7 and Percona XtraDB Cluster 5.7, it uses the following locking command while performing MyISAM and other non-InnoDB tables:

Executing LOCK TABLES FOR BACKUP...
Starting to backup non-InnoDB tables and files

For upstream MySQL 5.7 versions, it uses the following locking commands:

Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
Executing FLUSH TABLES WITH READ LOCK...
Starting to backup non-InnoDB tables and files

MySQL 8.0 uses the LOCK INSTANCE FOR BACKUP command.

Queries with ?Waiting for table flush state in processlist output while XtraBackup is running, this issue is visible in upstream MySQL 5.7 versions only; Percona Server for MySQL/Percona XtraDB Cluster/MySQL 8.0 will not have this issue since they use a different locking mechanism.

Root cause

When MySQL has a long-running query for a table/s, running XtraBackup will run FLUSH NO_WRITE_TO_BINLOG TABLES and all types of new queries on a table where a long-running query is running will have Waiting for table flush state in processlist.

This will be resolved after the long-running query is finished, and the next backup will complete successfully.

Test case example:

CREATE TABLE `joinit` (
`i` int(11) NOT NULL AUTO_INCREMENT,
`s` varchar(64) DEFAULT NULL,
`t` time NOT NULL,
`g` int(11) NOT NULL,
PRIMARY KEY (`i`)
) ENGINE=innodb;

Create table joinit_new like joinit;

INSERT INTO joinit VALUES (NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )));
INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
33554618 rows

#Start sysbench load on the database:

$ sysbench /usr/share/sysbench/oltp_read_write.lua --mysql_storage_engine=innodb --table-size=1000 --tables=5 --mysql-db=test --mysql-host=127.0.0.1 --mysql-user=msandbox --mysql-password=msandbox --threads=4 --time=0 --report-interval=1 --events=0 --db-driver=mysql run

Session 1: Ran the following query:

mysql  > select distinct i from joinit for update;

mysql > show processlist;
+-----+----------+-----------+-------+---------+------+--------------+------------------------------------------+
| Id  | User     | Host      | db    | Command | Time | State        | Info                                     |
+-----+----------+-----------+-------+---------+------+--------------+------------------------------------------+
|  23 | msandbox | localhost | test1 | Query   |    6 | Sending data | select distinct i from joinit for update |
|  76 | msandbox | localhost | NULL  | Query   |    0 | starting     | show processlist                         |
| 171 | msandbox | localhost | test1 | Sleep   |   57 |              | NULL                                     |
| 207 | msandbox | localhost | test  | Sleep   |  135 |              | NULL                                     |                             |

| 243 | msandbox | localhost | test  | Execute |    0 | starting     | COMMIT                                   |
| 244 | msandbox | localhost | test  | Execute |    0 | starting     | COMMIT                                   |
| 245 | msandbox | localhost | test  | Execute |    0 | starting     | COMMIT                                   |
| 247 | msandbox | localhost | NULL  | Sleep   |    3 |              | NULL                                     |
+-----+----------+-----------+-------+---------+------+--------------+------------------------------------------+
9 rows in set (0.00 sec)

#Start XtraBackup:

xtrabackup --user=msandbox --password=msandbox  --backup --target-dir=~/backup 

Copying ./mysql/help_relation.ibd to /home/lalit/backup/mysql/help_relation.ibd
       ...done
>> log scanned up to (13605240561)
Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
>> log scanned up to (13605923511)
>> log scanned up to (13606547653)
>> log scanned up to (13607307899)

Session 2: while session1 long-query is running:

mysql > insert into joinit_new (SELECT * from joinit);

Session 3: while session1 long-query is running:

mysql > select count(*) from test1.joinit;

As we can see from the example, all new queries on joinit table are in the Waiting for table flush state. Queries on other tables are not affected.

mysql [localhost] {msandbox} ((none)) > show processlist;
+-----+----------+-----------+-------+---------+------+-------------------------+-----------------------------------------------+
| Id  | User     | Host      | db    | Command | Time | State                   | Info                                          |
+-----+----------+-----------+-------+---------+------+-------------------------+-----------------------------------------------+
|  23 | msandbox | localhost | test1 | Query   |   22 | Sending data            | select distinct i from joinit for update      |
|  76 | msandbox | localhost | NULL  | Query   |    0 | starting                | show processlist                              |

| 171 | msandbox | localhost | test1 | Query   |    7 | Waiting for table flush | insert into joinit_new (SELECT * from joinit) |
| 207 | msandbox | localhost | test  | Query   |    3 | Waiting for table flush | select count(*) from test1.joinit            |
| 242 | msandbox | localhost | test  | Execute |    0 | starting                | COMMIT                                        |
                                     |
| 245 | msandbox | localhost | test  | Execute |    0 | closing tables          | SELECT c FROM sbtest5 WHERE id=?              |
| 247 | msandbox | localhost | NULL  | Query   |   11 | Waiting for table flush | FLUSH NO_WRITE_TO_BINLOG TABLES               |
+-----+----------+-----------+-------+---------+------+-------------------------+-----------------------------------------------+
9 rows in set (0.00 sec)

Once the long query is completed, we don’t see the Waiting for table flush state in processlist, and backup will continue.

>> log scanned up to (13619342387)
>> log scanned up to (13619630994)
 Executing FLUSH TABLES WITH READ LOCK...
>> log scanned up to (13619733489)
 >> log scanned up to (13619737067)

mysql > show processlist;
+-----+----------+-----------+-------+---------+------+------------------------------+-----------------------------------------------+
| Id  | User     | Host      | db    | Command | Time | State                        | Info                                          |
+-----+----------+-----------+-------+---------+------+------------------------------+-----------------------------------------------+
|  23 | msandbox | localhost | test1 | Sleep   |  184 |                              | NULL                                          |
|  76 | msandbox | localhost | NULL  | Query   |    0 | starting                     | show processlist                              |
| 171 | msandbox | localhost | test1 | Query   |  169 | Sending data                 | insert into joinit_new (SELECT * from joinit) |                                     |

| 242 | msandbox | localhost | test  | Execute |  131 | Waiting for global read lock | UPDATE sbtest3 SET k=k+1 WHERE id=?           |
| 243 | msandbox | localhost | test  | Execute |  131 | Waiting for global read lock | UPDATE sbtest2 SET k=k+1 WHERE id=?           

| 247 | msandbox | localhost | NULL  | Query   |  131 | Waiting for global read lock | FLUSH TABLES WITH READ LOCK                   |
+-----+----------+-----------+-------+---------+------+------------------------------+-----------------------------------------------+
9 rows in set (0.00 sec)

mysql > show processlist;
+-----+----------+-----------+-------+---------+------+----------+------------------+
| Id  | User     | Host      | db    | Command | Time | State    | Info             |
+-----+----------+-----------+-------+---------+------+----------+------------------+
|  23 | msandbox | localhost | test1 | Sleep   |  256 |          | NULL             |
|  76 | msandbox | localhost | NULL  | Query   |    0 | starting | show processlist |
| 171 | msandbox | localhost | test1 | Sleep   |  241 |          | NULL             |
| 207 | msandbox | localhost | test  | Sleep   |  237 |          | NULL             |
+-----+----------+-----------+-------+---------+------+----------+------------------+
4 rows in set (0.00 sec)

Writing /home/lalit/backup/backup-my.cnf
        ...done
 Writing /home/lalit/backup/xtrabackup_info
        ...done
xtrabackup: Transaction log of lsn (13596553991) to (13619761343) was copied.
completed OK!

Possible solutions

• – Wait for the long-running query to finish.
• – Killing blocking queries.

List of options that you can use with Percona XtraBackup:

• xtrabackup –ftwrl-wait-timeout (seconds) – how long to wait for a good moment. Default is 0, not to wait.
• xtrabackup –ftwrl-wait-query-type – which long queries should be finished before FLUSH TABLES WITH READ LOCK is run. Default is ALL.
• xtrabackup –ftwrl-wait-threshold (seconds) – how long the query should be running before we consider it long running and potential blocker of global lock.
• xtrabackup –kill-long-queries-timeout (seconds) – how much time we give for queries to complete after FLUSH TABLES WITH READ LOCK is issued before start to kill. Default if 0, not to kill.
• xtrabackup –kill-long-query-type – which queries should be killed once kill-long-queries-timeout has expired. The default is SELECT.

Note: Killing select queries is OK but for DML/DDL it could lead to data inconsistency) so better to retry backup later/during non-peak hours.

References:

How Percona XtraBackup works (8.0)

How Percona XtraBackup works (2.4)

Improved FLUSH TABLES WITH READ LOCK handling

Amazon instance profiles are used to pass IAM roles to an EC2 instance. This IAM role can be queried using EC2 instance metadata to access an S3 bucket. Please check Amazon’s Official Documentation for more information.

Today we are happy to announce that starting with Percona XtraBackup 8.0.31-24, xbcloud can read instance metadata and fetch credentials from an instance profile, utilizing it to authenticate against an S3 bucket. Xbcloud is a tool part of Percona XtraBackup and allows you to upload and download backups to Amazon S3 storage.

How it works

xtrabackup ... | xbcloud put --storage=s3 --s3-bucket=bucket-name backup-name

221121 13:16:26 Using instance metadata for access and secret key
221121 13:16:26 xbcloud: Successfully connected.

221121 13:04:52 xbcloud: S3 error message: The provided token has expired.
221121 13:04:52 xbcloud: Sleeping for 2384 ms before retrying test/mysql.ibd.00000000000000000002 [1]
221121 13:05:09 xbcloud: successfully uploaded chunk: test/mysql.ibd.00000000000000000002, size: 5242923
. . .
221121 13:05:19 xbcloud: Upload completed.

Summary

Percona xbcloud can now utilize an instance profile allowing users to automate access to AWS S3 buckets by removing the need of access/secret-key being provided as parameters or configuration.

Percona XtraBackup is a free, open source, complete online backup solution for all versions of Percona Server for MySQL and MySQL. It performs online non-blocking, tightly compressed, highly secure backups on transactional systems so that applications remain fully available during planned maintenance windows.

Download Percona XtraBackup

Recently, we performed a database engine major version upgrade in one of our customers’ environments from MySQL 5.7.26 to 8.0.27. After this version upgrade, we experienced issues with backups and replication for one of the nodes.

In this article, I will explain these issues in detail and recommend a way to take backups from a replication environment.

To begin with, we upgraded all the database nodes from 5.7.26 to 8.0.27 and as a recommended way we have a backup set-up from one of the replica nodes. Physical backups are being taken using Percona XtraBackup (PXB) so it does not lock the database during the backup.

With MySQL 5.7, a backup was taken using PXB 2.4. Due to the new data dictionaries, redo log and undo log in MySQL 8.0, we also upgraded PXB to 8.0.27 to avoid compatibility issues, after upgrading the database to 8.0.

Before we discuss our original issue in detail, let us look at some of the options that should be used with PXB when you are taking backups from a replication environment to ensure consistency. These options are being used in this case as well.

–slave-info

It prints and stores the binary log position of the source server which can be useful to set up new replicas from the source.

–safe-slave-backup

This option is useful to handle temporary tables on replicas and when used it stops the SQL thread and waits until there are no temporary tables before taking a backup.

–lock-ddl

This option blocks all the DDL operations on replicas when a backup is running, so any DDL events do not corrupt the backups. Any DML events continue to occur, and only DDL events are blocked.

Now coming back to our original issue, after the database upgraded successfully to 8.0.27, we noticed several issues related to backups. One of the major issues that we noticed was replication failure on the replica node during the backup.

We identified below things to make sure that nothing changed after the upgrade:

1. No change in the backup command
2. All the tables were InnoDB as before and no engine change was performed

Further, we noticed that whenever PXB starts taking backups, the replication stops on the backup server. Upon checking further, we noticed that only the SQL thread gets stopped when the backup starts, and the IO thread keeps running. This pointed us to the fact that this replication break is happening because of PXB running with the –safe-slave-backup option, which is a recommended way of taking backups from the replica node as mentioned earlier.

However, we noticed that in the 5.7 environment, the backup was also running with the –safe-slave-backup option, and backups were running fine at that time and replication also had no issues. So with this major version upgrade, the only change we did with respect to the backup was upgrading the PXB packages to 8.0.27 from PXB 2.4.20.

In order to get more insights, I did a simple test and performed the backup using PXB 8.0.27 and PXB 2.4.26. During this test, I noticed that with PXB 8.0, –safe-slave-backup stops the SQL thread right after the backup starts (even before copying InnoDB files). In this test scenario, a complete backup takes around 20 seconds to finish and the SQL thread was stopped for the entire period of time.

2022-09-09T11:15:00.668080-00:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.27

**2022-09-09T11:15:00.717674-00:00 0 [Note] [MY-011825] [Xtrabackup] Slave open temp tables: 0

2022-09-09T11:15:00.720502-00:00 0 [Note] [MY-011825] [Xtrabackup] Slave is safe to backup.**

2022-09-09T11:15:00.720573-00:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP 

...

...

...

2022-09-09T11:15:01.248442-00:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./db/t.ibd to /root/backups/db/t.ibd

2022-09-09T11:15:01.980691-00:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (64948804922)

...

...

**2022-09-09T11:15:19.589799-00:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH TABLES WITH READ LOCK...**

**2022-09-09T11:15:19.590675-00:00 0 [Note] [MY-011825] [Xtrabackup] Starting to backup non-InnoDB tables and files**

...

...

2022-09-09T11:15:20.610477-00:00 0 [Note] [MY-011825] [Xtrabackup] Finished backing up non-InnoDB tables and files

2022-09-09T11:15:20.676140-00:00 0 [Note] [MY-011825] [Xtrabackup] Stopping log copying thread at LSN 64948804922

2022-09-09T11:15:20.728679-00:00 0 [Note] [MY-011825] [Xtrabackup] Executing UNLOCK INSTANCE

2022-09-09T11:15:20.729034-00:00 0 [Note] [MY-011825] [Xtrabackup] Executing UNLOCK TABLES

2022-09-09T11:15:20.729254-00:00 0 [Note] [MY-011825] [Xtrabackup] All tables unlocked

**2022-09-09T11:15:20.729268-00:00 0 [Note] [MY-011825] [Xtrabackup] Starting slave SQL thread**

...

...

2022-09-09T11:15:22.099206-00:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

However, with the PXB 2.4 version, the SQL thread gets stopped only while copying the non-InnoDB files, and when it takes a backup of InnoDB files SQL thread was not stopped. So for the entire duration of 20 seconds of backup, the SQL thread was stopped only for four seconds.

Using server version 5.7.26

/usr/bin/xtrabackup version 2.4.26 based on MySQL server 5.7.35 Linux (x86_64) (revision id: 19de43b)

...

...

220909 11:20:21 [01] Copying ./db/t.ibd to /root/backups/db/t.ibd

220909 11:20:22 >> log scanned up to (3991557882)

...

220909 11:20:35 >> log scanned up to (3991557882)

220909 11:20:35 [01]        ...done

...

...

**220909 11:20:36 Slave open temp tables: 0

220909 11:20:36 Slave is safe to backup.**

220909 11:20:36 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...

220909 11:20:36 Executing FLUSH TABLES WITH READ LOCK...

**220909 11:20:36 Starting to backup non-InnoDB tables and files**

...

...

220909 11:20:40 Finished backing up non-InnoDB tables and files

...

...

**220909 11:20:40 Executing UNLOCK TABLES

220909 11:20:40 All tables unlocked

Starting slave SQL thread**

...

...

220909 11:20:40 completed OK!

The PXB safe-slave-backup option is designed to ensure consistent backups of a database replica. In versions prior to 8.0.22, this option would stop the SQL slave thread after backing up InnoDB tables and before copying non-InnoDB data files. However, this could result in a corrupt backup if a replicated DDL (Data Definition Language) statement was executed on the backup replica during the backup process.

To address this issue, the behavior of the safe-slave-backup option was changed in version 8.0.22. Now, when this option is specified, the “STOP SLAVE SQL_THREAD” command is issued at the beginning of the backup process. This prevents any replicated DDL statements from being executed during the backup, ensuring a consistent and reliable backup.

This is the reason that after upgrading the PXB packages to 8.0, replication was getting stopped when the backup process started and it stopped the SQL thread even though all the tables were InnoDB.

Conclusion

As of Percona XtraBackup 8.0.22-15.0, using a safe-slave-backup option stops the SQL replica thread before copying the InnoDB files. This behavior prevents any replicated DDL statements from being executed during the backup, ensuring a consistent and reliable backup.

Percona XtraBackup is a free, online, open source, complete database backup solution for all versions of Percona Server for MySQL and MySQL.

Try Percona XtraBackup Today

Taking a MySQL backup using Percona XtraBackup (PXB) consists of basically two steps: 1) take the backup and 2) prepare the backup.

Briefly speaking, taking a backup means that PXB will copy all of the files from your instance and transfer them to another location. While it does the copy, it spawns a thread that will monitor the InnoDB redo log (WAL/transaction log) and store a copy of all the new redo log entries generated by the server during the backup.

Before restoring the backup into a new instance, users have to prepare the backup. This operation is the same as the crash recovery steps that the MySQL server does after a server crash.

It consists of reading all the redo log entries into memory, categorizing them by space id and page id, reading the relevant pages into memory, and checking the LSN number on the page and on the redo log record. If the LSN from the redo log is more recent than the one read from the page, we need to apply the redo log change to the page.

Memory usage

Percona XtraBackup/MySQL server utilizes InnoDB Buffer Pool memory to perform this operation. Memory for 256 pages is reserved for loading the pages into the buffer pool, while the remaining memory is utilized for hashing/categorizing the redo log entries.

This is controlled by the PXB parameter –use-memory . The more memory you have for this parameter the better. In case the available memory on the buffer pool is not enough, the work will have to be performed in multiple batches. After each batch, the memory structures have to be freed in order to make room for the next batch.

This has a huge performance impact as an InnoDB page holds data from multiple rows. If a change on the same page happens on a different batch, that page will have to be fetched and evicted multiple times.

Motivation

In regards to increasing the memory available for prepare phase we identified two challenges that motivated us to enhance this aspect of the software:

1. Not every user knows/reads the manual to understand what each parameter does and knows about them. We have seen a countless number of times users complaining about PXB being slow and the solution was to increase –use-memory as the user did not know about it and was using the default value, causing the prepare to require a huge amount of batches to complete.
2. Even knowing about how to tune PXB memory, there is no simple way to set –use-memory to the required memory for that particular prepare. The formula to get the amount of memory required depends on various factors such as the number of redo log entries, the number of entries per page, and so on. For example, x records on the same page versus x records on different pages will have different memory requirements.

Smart Memory Estimation

We are glad to announce Percona XtraBackup Smart Memory Estimation as of the release of Percona XtraBackup 8.0.30.

PXB has extended the crash recovery logic to extract the formula used to allocate memory.

Now, during the backup phase, while PXB is copying the redo log entries, it will compute the required memory for prepare. Not only that, but it will also take into consideration the number of InnoDB pages that will be required to be fetched from the disk. If enough memory is available for parsing the redo logs in one go, we also increase the 256 frames limit.

With this information gathered during the backup, PXB will check the server’s available free memory and will use up to –use-free-memory-pct of that memory for prepare.

This new feature will be released as Tech Preview to give users the chance to test it and provide feedback. –use-free-memory-pct will be released as 0% (disabled) during the duration of the tech preview. The aim is to make it enabled by default at 50% once we make the feature GA, which will allow PXB to use up to 50% of free memory to complete the prepare process as fast as possible.

Users will have the ability to adjust PXB memory allowance from 0 to 100%.

Benchmarks

To compare the new default we ran three backups, using sysbench with 16, 32, and 64 tables containing 1M rows each.

We used an ec2 c4.8xlarge instance (36 vCPUs / 60G memory / General Purpose SSD (gp2))

During each –backup we ran the below sysbench:

sysbench --db-driver=mysql --db-ps-mode=disable --mysql-user=sysbench --mysql-password=sysbench --table_size=1000000 --tables=${NUM_OF_TABLES} --threads=24 --time=0 --report-interval=1 /usr/share/sysbench/oltp_write_only.lua run

Each –prepare operation was run three times and the best time was extracted.

Please note: The purpose of this experiment is to show what the new default will look like once this feature becomes generally available.

The above table shows the amount of memory required by PXB with –use-free-memory-pct=50, the size of the PXB log file (relo log entries copied during the backup), and the size of the resulting backup folder. Operations done without Smart Memory Estimation used the default of 128M for the buffer pool.

16 tables result – prepare time dropped to ~5.7% of the original time. An improvement in recovery time of about 17X.

32 tables result – prepare time dropped to ~8,2% of the original time. An improvement in recovery time of about 12X.

64 tables result – prepare time dropped to ~9.9% of the original time. An improvement in recovery time of about 10X.

Summary

As we can see, the better results come from the smaller datasets, as the chances are that the same page will be present in multiple redo log records, causing it to be fetched into multiple different batches, however, the bigger the dataset bigger is the impact in recovery time, for example, 64 tables time dropped from 35 minutes and 28 seconds to only three minutes and 31 seconds.

As mentioned before, at the moment this feature will remain Tech Preview for a few releases, and users are welcome to use it via –use-free-memory-pct parameter and provide feedback.

Having a backup of your database is like insurance, you have to pay a monthly price to ensure you have a service available when you need to. When talking about backups, the storage required to keep your backups is what comes into factor when talking about price, the bigger your backup, or the bigger the retention period, the more it will cost.

Compressing your backups is a common practice to reduce this cost. Currently, Percona XtraBackup (PXB) has support for two compression algorithms: quicklz (which is an abandoned project and will soon be deprecated in PXB) and LZ4.

Today we are glad to introduce support for a new compression algorithm in Percona XtraBackup 8.0.30 – Zstandard (ZSTD).

Zstandard is a fast lossless compression algorithm, targeting real-time compression scenarios at zlib-level and better compression ratios.

Usage

A new value has been added to the xtrabackup –compress option. Passing –compress=zstd to PXB will make it use the new Zstandard algorithm to run compression.

Compression can work in parallel by adjusting –parallel=X for parallel file copy and –compress-threads=x for parallel compression of those files.

For decompression either on PXB or xbstream the usage remains the same. Users only need to pass –decompress and the tool will use the same algorithm used for compression.

Please note that as in qpress and LZ4, you will require the ZSTD client to run the –decompress operation.

Compress

xtrabackup --backup --compress=zstd --parallel=16 --compress-threads=8 --target-dir=/backup

Decompress

xtrabackup --decompress --parallel=16 --target-dir=/backup

Testing

To test how ZSTD compares with LZ4 and uncompressed backups, we set up a test environment using an AWS EC2 instance c5.4xlarge (16 CPUs and 32G of RAM ) with data and backup going to the same disk partition, an EBS IO2 with 10K provisioned IOPS SSD.

For each round of tests, we created a set of 12 tables with 40M rows resulting in a 109G dataset.

We ran five rounds of tests creating a new database with the same amount of data on each round.

Percona XtraBackup was invoked using –parallel=16 –compress-threads=8 .

Size of resulting backup (full, LZ4, and ZSTD), time to run the backup (full, LZ4, and ZSTD), and time to decompress the resulting backup (LZ4 and ZSTD).

The second round of tests explored how the two algorithms and uncompressed backups perform when uploading the data to S3. For this round, we ran two tests.

The first one was to take the backup again and upload it to S3, time to complete was measured.

The second part of the test was to download the backup from S3 and in the case of uncompressed backups, just store it on disk, and for compressed backup, we were first decompressing it and then storing it on disk.

On all tests, ZSTD compression level of one was used.

Results

Summary

As we can see from the test results above, ZSTD not only overcame LZ4 results on all tests, but it also brought backup size to half of its original size.

When streaming is added to the mix is when we see the biggest difference between both algorithms, with ZSTD overcoming LZ4 with an even bigger margin.

This can bring users and organizations a huge amount of savings in backup storage, either on-premises or especially in the cloud – where we are charged for each GB of storage we use.

Learn more about Percona XtraBackup

Oracle’s MySQL 8.0.29 release extended the support for ALTER TABLE … ALGORITHM=INSTANT to 1) allow users to instantly add columns in any position of the table, and 2) instantly drop columns. As part of this work, the InnoDB redo log format has changed for all DML operations on the server. This new redo log format introduced a design flaw that can cause data corruption for tables with INSTANT ADD/DROP COLUMNS.

The corruption happens when InnoDB crash recovery takes place. InnoDB applies redo logs at startup. Percona XtraBackup copies the redo log during backup and applies it as part of the –prepare step to bring the backup to a consistent state.

Percona fixed the corruption issue and several other issues with the INSTANT ADD/DROP column feature in the upcoming Percona Server for MySQL 8.0.29 (check PS-8291 /  PS-8292 / PS-8303 for more details) we also raised and provided patches for those issues as a contribution to Community MySQL (see 107613 / 107611 / 107854 for details). Percona XtraBackup 8.0.29 can take backups of Percona Server for MySQL 8.0.29 with tables that have INSTANT ADD/DROP COLUMNS. However, the current version of Community MySQL 8.0.29 still has this flaw, making it unsafe to take backups.

It is impossible for XtraBackup to deal with the corrupted redo log generated by Community MySQL 8.0.29 and, for this reason, XtraBackup 8.0.29 version will not take backups if it detects tables with INSTANT ADD/DROP columns and will create an error with a list of the affected tables and provide instructions to convert them to regular tables.

Please avoid ALTER ADD/DROP COLUMN without an explicit ALGORITHM=INPLACE. The default ALGORITHM is INSTANT, so ALTER TABLE without the ALGORITHM keyword uses the newly added INSTANT algorithm. For example:

Working:

ALTER TABLE tb1 ADD/DROP COLUMN [...], ALGORITHM=INPLACE/COPY;

Not Working:

ALTER TABLE tb1 ADD/DROP COLUMN [...], ALGORITHM=INSTANT;
ALTER TABLE tb1 ADD/DROP COLUMN [...], ALGORITHM=DEFAULT;
ALTER TABLE tb1 ADD/DROP COLUMN [...];

If you already have such tables (see below on how to find such tables), users are advised to run OPTIMIZE TABLE against these tables before taking backups.

Find all tables with INSTANT ADD/DROP COLUMNS:

mysql> SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE TOTAL_ROW_VERSIONS > 0;
+---------+
| NAME    |
+---------+
| test/t1 |
| test/t2 |
| test/t3 |
+---------+
3 rows in set (0.02 sec)

If this query shows an empty result set, you are all good. Percona XtraBackup will take backups of your MySQL 8.0.29 servers. If not, please run OPTIMIZE TABLE on the list of tables before taking a backup.

Percona XtraBackup error message

If Percona XtraBackup detects that MySQL 8.0.29 server has tables with instant add/drop columns, it aborts with the following error message

2022-07-01T15:18:35.127689+05:30 0 [ERROR] [MY-011825] [Xtrabackup] Found tables with row versions due to INSTANT ADD/DROP columns
2022-07-01T15:18:35.127714+05:30 0 [ERROR] [MY-011825] [Xtrabackup] This feature is not stable and will cause backup corruption.
2022-07-01T15:18:35.127714+05:30 0 [ERROR] [MY-011825] [Xtrabackup] Please check https://docs.percona.com/percona-xtrabackup/8.0/em/instant.html for more details.
2022-07-01T15:18:35.127723+05:30 0 [ERROR] [MY-011825] [Xtrabackup] Tables found:
2022-07-01T15:18:35.127730+05:30 0 [ERROR] [MY-011825] [Xtrabackup] test/t1
2022-07-01T15:18:35.127737+05:30 0 [ERROR] [MY-011825] [Xtrabackup] test/t2
2022-07-01T15:18:35.127744+05:30 0 [ERROR] [MY-011825] [Xtrabackup] test/t3
2022-07-01T15:18:35.127752+05:30 0 [ERROR] [MY-011825] [Xtrabackup] Please run OPTIMIZE TABLE or ALTER TABLE ALGORITHM=COPY on all listed tables to fix this issue.

Summary

Algorithm INSTANT is the new default in 8.0.29. If you do not specify an algorithm, all ALTER TABLE ADD/DROP COLUMN statements will use the default algorithm. 

The INSTANT algorithm is considered unstable at this point.

Percona XtraBackup will refuse to take backups from MySQL 8.0.29 tables that have been modified using this algorithm. Running OPTIMIZE TABLE on affected tables will bring them back to a safe state.

Percona XtraBackup is able to take backups seamlessly from Percona Server for MySQL, as the corruption issues have been fixed in the upcoming release of Percona Server for MySQL 8.0.29

Earlier last week, Oracle released their Q2 releases series. Unlike previous releases, backward compatibility has now been broken with previous versions of MySQL.

MySQL 8.0.29 extended the support for the online DDL algorithm INSTANT. Prior to 8.0.29 only adding columns to the end of the table was supported.

In 8.0.29, this functionality was extended to allow the INSTANT algorithm the ability to add columns in any position of the table as well to drop columns. This new functionality required the redo log version to increase and new redo log types to be added, thus making it incompatible with older versions of the MySQL server and also older versions of Percona Xtrabackup. Please note that an in-place minor version downgrade of the server is also not supported.

We are currently working on making Percona Xtrabackup compatible with those changes. Until then users relying on Percona Xtrabackup for backups are advised to not upgrade the MySQL server to 8.0.29.

KMIP (Key Management Interoperability Protocol) is an open standard developed by OASIS (Organization for Advancement of Structured Information Standards) for the encryption of stored data and cryptographic key management.

Percona Server for MySQL 8.0.27 and Percona XtraBackup 8.0.27 now include a KMIP keyring plugin to enable the exchange of cryptographic keys between a key management server and the database for encryption purposes. The procedure to use them with HashiCorp Vault Enterprise is described below.

Install Hashicorp Vault Enterprise

We will first install Hashicorp Vault Enterprise on Ubuntu Linux “Bionic” and then enable the KMIP secrets engine. The KMIP secrets engine is only available with the Enterprise version of HashiCorp Vault, hence a valid license for it is required.

Add HashiCorp repository and install enterprise vault package:

curl -fsSL https://apt.releases.hashicorp.com/gpg | sudo apt-key add -
sudo apt-add-repository "deb [arch=amd64] https://apt.releases.hashicorp.com $(lsb_release -cs) main"
sudo apt-get update && sudo apt-get install vault-enterprise

Export the license as an environment variable:

export VAULT_LICENSE=XXXX

Create a configuration file to be used with the vault, vault_config.hcl:

disable_mlock = true
default_lease_ttl = "24h"
max_lease_ttl = "24h"
storage "file" {
  path    = "/home/manish.chawla/vault/data"
}

listener "tcp" {
  address     = "127.0.0.1:8200"
  tls_cert_file = "/home/manish.chawla/test_mode/certificates/vault.crt"
  tls_key_file = "/home/manish.chawla/test_mode/certificates/vault.key"
}

Note: Vault root certificates and key need to be created separately and are not covered here.

Start vault server with the configuration file:

vault server -config=$HOME/vault_config.hcl 2>&1 &

Note: To configure and start the vault using systemd, refer to the instructions here.

Initialize the vault:

vault operator init -address=https://127.0.0.1:8200

This will generate five unseal keys and the initial root token.

Unseal Key 1: rf4C6gY87tN0UkJbJY96Aq4+Zext1YqgwaDnm+0gBBAH
Unseal Key 2: f9KA7EdlF391cIUzDqaS1N21JjML/36sZFl1x/OfCPn4
Unseal Key 3: 8Nh3EFYF7S0S9qqzdUIilPPRZRmWaDG+3El4rr4FmZNX
Unseal Key 4: iJaCKBIzxulLvA/6vbF3fRK1RXVZ0zLEZoVdlv/s13Sc
Unseal Key 5: rA4pwT6EZLwmVXPQJfU9fjgeGwPaHl260qM9CVNiUw13

Initial Root Token: s.WXEZ26Yb3MtvzbvNMMIG8bve

Unseal the vault.

Use any three unseal keys to unseal the vault. Three keys are required to unseal the vault.

vault operator unseal -address=https://127.0.0.1:8200 rf4C6gY87tN0UkJbJY96Aq4+Zext1YqgwaDnm+0gBBAH
Key                Value
---                -----
Seal Type          shamir
Initialized        true
Sealed             true
Total Shares       5
Threshold          3
Unseal Progress    1/3
Unseal Nonce       aa1a25c8-aa90-49b3-b127-875524de38f8
Version            1.9.3+ent
Storage Type       file
HA Enabled         false

vault operator unseal -address=https://127.0.0.1:8200 f9KA7EdlF391cIUzDqaS1N21JjML/36sZFl1x/OfCPn4
Key                Value
---                -----
Seal Type          shamir
Initialized        true
Sealed             true
Total Shares       5
Threshold          3
Unseal Progress    2/3
Unseal Nonce       aa1a25c8-aa90-49b3-b127-875524de38f8
Version            1.9.3+ent
Storage Type       file
HA Enabled         false

vault operator unseal -address=https://127.0.0.1:8200 8Nh3EFYF7S0S9qqzdUIilPPRZRmWaDG+3El4rr4FmZNX
Key             Value
---             -----
Seal Type       shamir
Initialized     true
Sealed          false
Total Shares    5
Threshold       3
Version         1.9.3+ent
Storage Type    file
Cluster Name    vault-cluster-7d9b43de
Cluster ID      c047dcb5-5038-5a29-f4af-47c1ad560f9c
HA Enabled      false

The vault is unsealed.

To use the vault in any terminal, run:

export VAULT_ADDR=https://127.0.0.1:8200
export VAULT_TOKEN=s.WXEZ26Yb3MtvzbvNMMIG8bve
export VAULT_CACERT=/home/manish.chawla/test_mode/certificates/root.cer

Configure KMIP Secrets Engine in Vault

Enable KMIP secrets engine:

vault secrets enable kmip
Success! Enabled the kmip secrets engine at: kmip/

View the secrets list:

vault secrets list
Path          Type         Accessor              Description
----          ----         --------              -----------
cubbyhole/    cubbyhole    cubbyhole_ec12856f    per-token private secret storage
identity/     identity     identity_12d9670d     identity store
kmip/         kmip         kmip_5fb3d4c6         n/a
sys/          system       system_1733eece       system endpoints used for control, policy and debugging

Change the kmip server listening address and port:

vault write kmip/config listen_addrs=0.0.0.0:5696
Success! Data written to: kmip/config

Note: Here kmip is the default path of the secret engine and not the type of the engine.

By default, the kmip generates certificates in EC(Elliptic Curve). We need RSA for MySQL, so specify the certificate type (tls_ca_key_type) and bits (tls_ca_key_bits) to configure the kmip server.

vault write kmip/config tls_ca_key_type="rsa" tls_ca_key_bits=2048
Success! Data written to: kmip/config

vault read kmip/config
Key                            Value
---                            -----
default_tls_client_key_bits    256
default_tls_client_key_type    ec
default_tls_client_ttl         336h
listen_addrs                   [0.0.0.0:5696]
server_hostnames               [localhost]
server_ips                     [127.0.0.1 ::1]
tls_ca_key_bits                2048
tls_ca_key_type                rsa
tls_min_version                tls12

The KMIP secrets engine uses scopes to partition object storage into multiple named buckets. Within a scope, roles can be created with a set of allowed operations that the particular role can perform.

Create a scope:

vault write -f kmip/scope/my-service
Success! Data written to: kmip/scope/my-service

Create a role within the scope, specifying the set of operations to allow or deny.

vault write kmip/scope/my-service/role/admin operation_all=true
Success! Data written to: kmip/scope/my-service/role/admin

Client Certificate Generation for the scope and role created above.

Retrieve the generated CA certificate:

vault read kmip/ca

Copy and save the CA certificate as ca.pem.

Generate a certificate in PEM format, and save it in a JSON file named credential.json.

vault write -format=json \
    kmip/scope/my-service/role/admin/credential/generate \
    format=pem > credential.json

Extract the certificate from the credential.json using jq tool and save it in a file named cert.pem.

jq -r .data.certificate < credential.json > cert.pem

Extract the private key from the credential.json using jq tool and save it in a file named key.pem.

jq -r .data.private_key < credential.json > key.pem

The KMIP configuration is now complete.

Percona Server for MySQL 8.0.27 Configuration for KMIP

This section describes the KMIP configuration in Percona Server for MySQL. KMIP is configured as a component in Percona Server for MySQL.

Create the global manifest file(mysqld.my) in the mysqld installation directory.

{
  "components": "file://component_keyring_kmip"
}

Create the global configuration file, component_keyring_kmip.cnf in the directory, where the component_keyring_kmip library resides.

{ "path": "/home/manish.chawla/keyring_kmip", "server_addr": "0.0.0.0", "server_port": "5696", "client_ca": "/home/manish.chawla/cert.pem", "client_key": "/home/manish.chawla/key.pem", "server_ca": "/home/manish.chawla/ca.pem" }

Note: SElinux/AppArmor rules may have to be adjusted, so that Percona Server for MySQL and Percona XtraBackup can access the certificates.

Initialize and start mysqld with encryption options(add in my.cnf): 

--innodb-undo-log-encrypt --innodb-redo-log-encrypt --binlog-encryption --default-table-encryption=ON --log-replica-updates --gtid-mode=ON --enforce-gtid-consistency --binlog-format=row --source-verify-checksum=ON --binlog-checksum=CRC32 --table-encryption-privilege-check=ON

Check the KMIP component status:

8.0.27>SELECT * FROM performance_schema.keyring_component_status;
+---------------------+------------------------------+
| STATUS_KEY          | STATUS_VALUE                 |
+---------------------+------------------------------+
| Component_name      | component_keyring_kmip       |
| Author              | Percona Corporation          |
| License             | GPL                          |
| Implementation_name | component_keyring_kmip       |
| Version             | 1.0                          |
| Component_status    | Active                       |
| Server_addr         | 0.0.0.0                      |
| Server_port         | 5696                         |
| Client_ca           | /home/manish.chawla/cert.pem |
| Client_key          | /home/manish.chawla/key.pem  |
| Server_ca           | /home/manish.chawla/ca.pem   |
| Object_group        | <NONE>                       |
+---------------------+------------------------------+

Create some encrypted tables and add data in the Percona Server for MySQL.

Backup and Restore of Percona Server for MySQL 8.0.27 Using Percona XtraBackup 8.0.27

This section describes the procedure for taking backup and restore of Percona Server for MySQL 8.0.27 when the KMIP component is enabled and the KMIP vault server is running. Percona XtraBackup reads the KMIP configuration in Percona Server for MySQL automatically, and it is not required to pass this information separately.

Take full backup:

xtrabackup --user=backup --password=* --backup --target-dir=backup_directory

Prepare full backup:

xtrabackup --prepare --target_dir=backup_directory

Stop Percona Server for MySQL and move the data directory to another location. Disable SElinux/AppArmor before restoring the backup.

Restore full backup:

xtrabackup --copy-back --target-dir=backup_directory

Change the ownership of the copied files in the Percona Server for MySQL data directory to the MySQL user.

Start Percona Server for MySQL and check the data. Enable SElinux/AppArmor, if disabled previously.

A little bit ago, I released a blog post comparing the backup performance of different MySQL tools such as mysqldump, the MySQL Shell feature called Instance Dump, mysqlpump, mydumper, and Percona XtraBackup. You can find the first analysis here:

Backup Performance Comparison: mysqldump vs. MySQL Shell Utilities vs. mydumper vs. mysqlpump vs. XtraBackup

However, we know the backups are just the first part of the story. What about the restore time? And which tool performs better for the complete operation (backup+restore)?

Let’s see the results and the conclusion in the following sections.

Benchmark Results

I ran the benchmark on an m5dn.8xlarge instance, with 128GB RAM, 32 vCPU, and two io1 disks of 600GB (one for backup and the other one for MySQL data) with 5000 provisioned IOPS. The MySQL version was 8.0.26 and configured with 89Gb of the buffer pool, 20Gb of redo log, and a sample database of 96 GB (more details below).

When we sum the backup time and the restore time, we can observe the results in the chart below:

And if we analyze the chart without mysqldump to have a better idea of how the other tools performed:

The backup size created by each tool:

Note that the backup size of XtraBackup (without compression) is the size of the datadir without the binary logs. Next, we can see the backup time:

And the restore time:

Analyzing The Results

When we sum backup and restore times, we observe that the fastest tool is Percona XtraBackup. The main point of XtraBackup is not even the speed but its capacity to perform PITR backups. Also, the tool supports compression and encryption.

We can also observe that mydumper/myloader and MySQL Shell utilities produce good results in both phases. The difference from Xtrabackup is that both tools perform logical backups, which means that these tools connect to MySQL and extract the data to dump files. Because they have to extract data from MySQL, these tools are more sensitive for the MySQL configuration and backup/restore parametrization. For example, MyDumper/MyLoader has some extra options that can improve the backup and restore performance, such as --rows, --chunk-filesize, and --innodb-optimize-keys.

Note that  XtraBackup, MyDumper, and mysqldump support stream restore, reducing overall timing to perform the backup and restore operation. 

The tool that has the most inconsistent behavior is mysqlpump where the tool can make speedy backups, but the restore performance is terrible since it is single-threaded the same way as mysqldump. 

Based on the tests, we can observe that compression, TLS, socket, or TCP/IP do not significantly impact the time needed to perform the whole operation. Because there is no significant impact, tools that can perform compression and use TLS like MySQL Shell, mydumper/myloader, and XtraBackup have a good advantage since their backups are safer and use less disk space (less disk space = fewer costs). The trade-off between the features of these tools and the time spent to backup and restore the database is something that all DBAs should evaluate.

And to answer some questions/comments about this topic:

The difference you see between MySQL Shell and mydumper can be explained by the use of SSL in one and clear transfer in the other. Encryption has a cost, unfortunately. 

A: Indeed, SSL has a cost. However, when we put the security benefits of the SSL and consider the whole process, it is a small cost (in the same way as compression).

Does XtraBackup support ZSTD? 

A: At this moment, no. However, there is a feature request for this (you can follow the JIRA ticket to receive updates about it):

https://jira.percona.com/browse/PXB-2669

Is there any difference substituting mysqldump | gzip with a different compression tool?

A: The difference is neglectable piping with gzip or sending the uncompressed dump to the disk. The mysqldump tool is the most inefficient option due to its single-thread nature, severely impacting performance. Because of its single-thread nature, the tool cannot extract maximum performance from hardware resources (in particular I/O).

How is the performance impact on MySQL when running the backups?

A: Unfortunately, I did not measure this. Based on my experience, there is a dedicated replica server for backup most of the time. If the MySQL community is interested in this test, I can write another post about this (leave in the comments your opinion). 

It is possible to squeeze more juice from MySQL in the restore phase. We can take some actions like disabling the binary log and making asynchronous writes. You can check the advice (pros and cons) in these two blog posts:

https://www.percona.com/blog/2020/05/14/tuning-mysql-innodb-flushing-for-a-write-intensive-workload/

https://www.percona.com/blog/2014/05/23/improve-innodb-performance-write-bound-loads/

To conclude, this blog post is intended to give an overall idea of how these tools perform. I tried to stick with the default options of each tool (except the number of threads) to keep the test as fair as possible. Also, time is not the only thing that companies consider to adopt a backup method (security, encryption, and data protection are very important). In my daily tasks, I use mydumper/myloader and XtraBackup because I’m more familiar with the commands, and I have used them for a long time. However, I would advise keeping an eye on the MySQL Shell utilities since it is becoming a fascinating tool to perform many tasks (backup and restore have excellent results).

Hardware and Software Specs

These are the specs of the benchmark:

• 32 CPUs
• 128GB Memory
• 2x io1 disks 600 GB with 5000 IOPS each
• Centos 7.9
• MySQL 8.0.26
• MySQL shell 8.0.26
• mydumper 0.11.5 – gzip
• mydumper 0.11.5 – zstd
• Xtrabackup 8.0.26

Useful Resources

Finally, you can reach us through the social networks, our forum, or access our material using the links presented below:

• Blog 
• Solution Briefs
• White Papers
• Ebooks
• Technical Presentations archive
• Videos/Recorded Webinars
• Forum
• Knowledge Base (Percona Subscriber exclusive content)