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:
|
1 |
PRIMARY: mysql57 (192.168.56.111) with Percona Server 5.7 and xtrabackup 2.4 installed<br>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.
|
1 |
<b>MySQL localhost JS > util.checkForServerUpgrade('root@localhost:3306', {"password":"####", "targetVersion":"8.0.32", "configPath":"/etc/my.cnf"})</b><br><br>The MySQL server at localhost:3306, version 5.7.26-29-log - Percona Server<br>(GPL), Release 29, Revision 11ad961, will now be checked for compatibility<br>issues for upgrade to MySQL 8.0.32...<br>1) Usage of old temporal type<br> No issues found<br>.....<br> 20) Tables recognized by InnoDB that belong to a different engine<br> No issues found<br> 21) Issues reported by 'check table x for upgrade' command<br> No issues found<br>22) New default authentication plugin considerations<br> Warning: The new default authentication plugin 'caching_sha2_password' offers<br> more secure password hashing than previously used 'mysql_native_password'<br> ....<br> 23) Columns which cannot have default values<br> No issues found<br>....<br> 25) Check for orphaned routines in 5.7<br> No issues found<br> Errors: 0<br> Warnings: 5<br> 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:
|
1 |
[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:
|
1 |
[root@mysql57 ~]# xtrabackup --stream=xbstream --parallel=4 /tmp | pigz -c --fast | nc -w 2 192.168.56.113 2222<br>xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --server-id=1 --log_bin=mysql-bin --parallel=4<br>xtrabackup: recognized client arguments:<br>230228 22:58:57 innobackupex: Starting the backup operation<br>IMPORTANT: Please check that the backup run completes successfully.<br> At the end of a successful backup run innobackupex<br> prints "completed OK!".<br>230228 22:58:57 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).<br>230228 22:58:58 version_check Connected to MySQL server<br>230228 22:58:58 version_check Executing a version check against the server...<br>230228 22:58:58 version_check Done.<br>230228 22:59:11 [00] ...done<br>230228 22:59:11 [00] Streaming <STDOUT><br>230228 22:59:11 [00] ...done<br>xtrabackup: Transaction log of lsn (31871969) to (31872026) was copied.<br>Shutting down plugin 'keyring_file'<br>230228 22:59:12 completed OK!<br> |
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.
|
1 |
xtrabackup --prepare --use-memory=3G --apply-log-only --target-dir=/var/lib/mysql<br>[root@mysql8 mysql]# xtrabackup --prepare --use-memory=3G --apply-log-only --target-dir=/var/lib/mysql<br>xtrabackup: [Warning] option 'innodb_undo_tablespaces': unsigned value 0 adjusted to 2.<br>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<br>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<br>xtrabackup version 8.0.32-25 based on MySQL server 8.0.32 Linux (x86_64) (revision id: 14f007fb)<br>2023-02-28T23:05:23.994404-05:00 0 [Note] [MY-011825] [Xtrabackup] cd to /var/lib/mysql/<br>2023-02-28T23:05:24.001029-05:00 0 [Note] [MY-011825] [Xtrabackup] This target seems to be not prepared yet.<br><strong>2023-02-28T23:05:24.003369-05:00 0 [ERROR] [MY-011825] [Xtrabackup] Unsupported redo log format 1<br>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.<br>[root@mysql8 mysql]#<br></strong> |
4, You need to uninstall percona-xtrabackup-80 and install percona-xtrabackup-24 on the MySQL 8 server.
|
1 |
sudo yum remove percona-xtrabackup-80<br>sudo yum list | grep percona<br>sudo yum install percona-xtrabackup-24<br><br>[root@mysql8 mysql]# xtrabackup --version<br>xtrabackup: recognized server arguments: --datadir=/var/lib/mysql<br>xtrabackup version 2.4.27 based on MySQL server 5.7.40 Linux (x86_64) (revision id: aae8e82)<br>[root@mysql8 mysql]#<br> |
Then, prepare the backup on the mysql8 server,
|
1 |
[root@mysql8 mysql]# xtrabackup --prepare --use-memory=3G --apply-log-only --target-dir=/var/lib/mysql<br>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<br>xtrabackup: recognized client arguments: --prepare=1 --use-memory=3G --apply-log-only=1 --target-dir=/var/lib/mysql<br>xtrabackup version 2.4.27 based on MySQL server 5.7.40 Linux (x86_64) (revision id: aae8e82)<br>xtrabackup: cd to /var/lib/mysql/<br>xtrabackup: This target seems to be not prepared yet.<br>InnoDB: Number of pools: 1<br>…….<br>InnoDB: xtrabackup: Last MySQL binlog file position 234, file name mysql-bin.000029<br>InnoDB: xtrabackup: Last MySQL binlog file position 234, file name mysql-bin.000029<br><br>xtrabackup: starting shutdown with innodb_fast_shutdown = 1<br>InnoDB: Starting shutdown...<br>InnoDB: Shutdown completed; log sequence number 31872035<br>InnoDB: Number of pools: 1<br>Shutting down plugin 'keyring_file'<br>230228 23:27:52 completed OK!<br>[root@mysql8 mysql]#<br> |
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:
|
1 |
[root@mysql8 lib]# chown -R mysql:mysql /var/lib/mysql<br>[root@mysql8 lib]# systemctl start mysql<br><br><br>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<br>2023-03-01T02:38:23.939215Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.<br>2023-03-01T04:28:38.397836Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.31-23) starting as process 8229<br>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.<br>2023-03-01T04:28:38.749996Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.<br><br>2023-03-01T04:34:02.516924Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.31-23) starting as process 8388<br>2023-03-01T04:34:02.827172Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.<br>2023-03-01T04:34:02.827297Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.<br>2023-03-01T04:34:47.547671Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.<br>2023-03-01T04:35:10.890987Z 3 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.<br>2023-03-01T04:35:17.864546Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80031' started.<br>2023-03-01T04:35:41.732506Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80031' completed.<br>2023-03-01T04:35:52.304549Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.<br>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<br> |
6. You could set up replication with the steps below.
|
1 |
[root@mysql8 mysql]# cat /var/lib/mysql/xtrabackup_binlog_info<br>mysql-bin.000029 234 2fb728db-eb40-11eb-96c5-080027b6340a:1-78,<br>30b9be00-c21a-11ec-8eb2-0800275411eb:1<br>[root@mysql8 mysql]#<br>mysql8>STOP REPLICA;<br>mysql8>RESET MASTER;<br>mysql8>SET GLOBAL gtid_purged='2fb728db-eb40-11eb-96c5-080027b6340a:1-78,30b9be00-c21a-11ec-8eb2-0800275411eb:1';<br>mysql8>CHANGE REPLICATION SOURCE TO<br> SOURCE_HOST = '192.168.56.111',<br> SOURCE_PORT = 3306,<br> SOURCE_USER = 'repl',<br> SOURCE_PASSWORD = ########',<br> SOURCE_AUTO_POSITION = 1;<br>mysql8>START REPLICA;<br>mysql8> SHOW REPLICA STATUSG<br>*************************** 1. row ***************************<br> Slave_IO_State: Waiting for source to send event<br> Master_Host: 192.168.56.111<br> Master_User: repl<br> Master_Port: 3306<br> Connect_Retry: 60<br> Master_Log_File: mysql-bin.000029<br> Slave_IO_Running: Yes<br> Slave_SQL_Running: Yes<br> …<br> Retrieved_Gtid_Set: 2fb728db-eb40-11eb-96c5-080027b6340a:79<br> Executed_Gtid_Set: 2fb728db-eb40-11eb-96c5-080027b6340a:1-79,<br>30b9be00-c21a-11ec-8eb2-0800275411eb:1<br> Auto_Position: 1<br> |
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
|
1 |
xtrabackup --parallel=4 --backup --socket=/var/lib/mysql/mysql.sock --target-dir=/opt/mysql57backup/<br><br>xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --server-id=1 --log_bin=mysql-bin --parallel=4<br>xtrabackup: recognized client arguments: --user=root --password=* --backup=1 --socket=/var/lib/mysql/mysql.sock --target-dir=/opt/mysql57backup/<br>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).<br>230301 13:43:05 version_check Connected to MySQL server<br>230301 13:43:05 version_check Executing a version check against the server...<br>….<br>Using server version 5.7.26-29-log<br>xtrabackup version 2.4.24 based on MySQL server 5.7.35 Linux (x86_64) (revision id: b4ee263)<br>........<br>230301 13:43:25 Executing UNLOCK TABLES<br>230301 13:43:25 All tables unlocked<br>230301 13:43:25 [00] Copying ib_buffer_pool to /opt/mysql57backup/ib_buffer_pool<br>230301 13:43:25 [00] ...done<br>230301 13:43:25 Backup created in directory '/opt/mysql57backup/'<br>MySQL binlog position: filename 'mysql-bin.000029', position '408', GTID of the last change '2fb728db-eb40-11eb-96c5-080027b6340a:1-79,<br>30b9be00-c21a-11ec-8eb2-0800275411eb:1'<br>230301 13:43:26 completed OK!<br> |
#2. Prepare the backup on mysql57, then transfer to the mysql8 server.
|
1 |
xtrabackup --prepare --socket=/var/lib/mysql/mysql.sock --target-dir=/opt/mysql57backup/<br><br>xtrabackup version 2.4.24 based on MySQL server 5.7.35 Linux (x86_64) (revision id: b4ee263)<br>xtrabackup: cd to /opt/mysql57backup/<br>xtrabackup: This target seems to be not prepared yet.<br>InnoDB: Number of pools: 1<br>xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(31872026)<br>xtrabackup: using the following InnoDB configuration for recovery:<br>xtrabackup: innodb_data_home_dir = .<br>xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend<br>xtrabackup: innodb_log_group_hom<br>....<br>InnoDB: 5.7.35 started; log sequence number 31872533<br>xtrabackup: starting shutdown with innodb_fast_shutdown = 1<br>InnoDB: page_cleaner: 1000ms intended loop took 12196ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)<br>InnoDB: FTS optimize thread exiting.<br>InnoDB: Starting shutdown...<br>InnoDB: Shutdown completed; log sequence number 31872552<br>Shutting down plugin 'keyring_file'<br>230301 13:47:16 completed OK!<br> |
Then transfer to mysql8:
|
1 |
[root@mysql8 lib]# chown -R percona:percona /var/lib/mysql/<br>[root@mysql57 ~]# rsync -avpP -e ssh /opt/mysql57backup/ /var/lib/mysql/<br>[email protected]:/var/lib/mysql/<br>[email protected]'s password:<br>sending incremental file list<br>./<br>backup-my.cnf<br> 515 100% 0.00kB/s 0:00:00 (xfr#1, to-chk=443/445)<br>ib_buffer_pool<br> 686 100% 669.92kB/s 0:00:00 (xfr#2, to-chk=442/445)<br>ib_logfile0<br> 50,331,648 100% 1.87MB/s 0:00:25 (xfr#3, to-chk=441/445)<br>ib_logfile1<br> 50,331,648 100% 16.09MB/s 0:00:02 (xfr#4, to-chk=440/445)<br>......................<br> |
#3: Start MySQL service, and it will upgrade for you.
|
1 |
[root@mysql8 lib]# chown -R mysql:mysql /var/lib/mysql<br>[root@mysql8 lib]# systemctl start mysql<br><br>….<br>2023-03-01T19:12:38.752622Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.31-23) starting as process 21608<br><br>2023-03-01T19:12:40.212880Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.<br>2023-03-01T19:12:40.220504Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.<br>2023-03-01T19:17:48.087404Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.<br><br>2023-03-01T19:18:10.663895Z 3 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.<br>2023-03-01T19:18:13.814450Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80031' started.<br>2023-03-01T19:18:57.570769Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80031' completed.<br>………<br>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.<br> |
#4. Set up replication, similar to step 6 above:
|
1 |
[root@mysql57 ~]# cat /opt/mysql57backup/xtrabackup_binlog_info<br>mysql-bin.000029 408 2fb728db-eb40-11eb-96c5-080027b6340a:1-79,30b9be00-c21a-11ec-8eb2-0800275411eb:1<br>[root@mysql57 ~]#<br><br><br>mysql8>STOP REPLICA;<br>mysql8>RESET MASTER;<br>mysql8>SET GLOBAL gtid_purged='2fb728db-eb40-11eb-96c5-080027b6340a:1-79,30b9be00-c21a-11ec-8eb2-0800275411eb:1';<br>mysql8> CHANGE REPLICATION SOURCE TO<br> SOURCE_HOST = '192.168.56.111',<br> SOURCE_PORT = 3306,<br> SOURCE_USER = 'repl',<br> SOURCE_PASSWORD = ########',<br> SOURCE_AUTO_POSITION = 1;<br>mysql8>START REPLICA;<br>mysql8> SHOW REPLICA STATUSG<br><br>*************************** 1. row ***************************<br> Slave_IO_State: Waiting for source to send event<br> Master_Host: 192.168.56.111<br> Master_User: repl<br> Master_Port: 3306<br> ……….<br> Relay_Master_Log_File: mysql-bin.000029<br> Slave_IO_Running: Yes<br> Slave_SQL_Running: Yes<br> |
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.
Resources
RELATED POSTS
