I have a Percona MySQL 5.7 Master/slave setup at the moment where the install was performed using RPMs. I stood up a 3 node Percona 8 cluster on a new set of servers, which is the architecture that we want to move to. I used mysqldump to export the 5.7 DB and then import it to the v8 cluster. That process took about 30 hours, which is not acceptable in terms of down time. Another option I was considering was stopping the v5.7 DB and using rsync to copy over the data directory to the v8 server, then starting up the v8 server. In my mind, this would be effectively doing an in-place upgrade. Is my thinking wrong? Would this be a supported method of upgrading?
Hi @ultrapain,
mysqldump is horrible for restore, as you discovered. If you want to do logical backup/restore, check out mydumper/myloader. mysqldump is single-threaded and works on 1 table at a time. mydumper is multithreaded and can work in parallel on multiple tables, even breaking apart larger tables into smaller work units. Tar the backup directory, copy it to node1, un-tar, and use myloader to load the data. All 3 nodes now have the same data.
Another ‘best practice’ recommendation would be to perform an upgrade-in-place of your 5.7 replica to 8.0. Simply stop mysql, remove the 5.7 RPMs, install the 8.0 RPMS, and start mysql. This will upgrade mysql on restart. Replication is 100% compatible this way. After this is done, use Xtrabackup to perform a physical backup of the replica. Shutdown all 3 PXC nodes. Restore this backup to node1 of your PXC, and bootstrap it. Then erase node2, and node3 and start them one at a time (ie: let node2 finish and come online before starting node3). They will SST a complete copy from node1 automatically. All 3 nodes now have the same data.
Take your pick, but rsync is never a good idea in my experience.
Thanks sir! I always appreciate your very thorough and detailed responses! I’m going to give mydumper/myloader a shot today to gauge performance. Much appreciated!
You can consider installing mysql 5.7 on the new server, then joining the cluster, waiting for data synchronization, separating this node, then upgrading to mysql 8, and then creating a new cluster with the shortest downtime.