I apologize for the machine translation. I am not a robot )
We have a question about upgrading MySQL 5.7 to 8.0
We updated all the replicas using the Percona xtrabackup tool, which essentially makes a file copy of the master databases
and then adds a bin-log position to it. Works great, put in a new Percona 8, fill in the files, start replication
from the position. Now it’s a question of updating the master. The plan was also, stop it, make a copy,
restore it on a new machine with new os and new Mysql 8. Give the machine the ip of the old master and run it.
I don’t fully understand how replication will start in this case. After all, in theory the replicas will
look for something like vm-db1-bin.003543 pos 46550466, and the new master will start new bins first.
There is an option to create a new master as a replica of the existing one. Switch slaves to it and then
make it a master again. In this case please advise how to switch slaves without
How do I switch the slaves without backing them up? There just volumes colossal and the second reason, in some replicas
already added a lot of other databases on the same MySQL instances.
1 Like
Hello, if you create a new master then you will need to stop all replicas at a known position relative to the existing master and then run the change master statement to re-point them to the new master. Also take a look at Orchestrator it could help you with all the server movements.
1 Like
Thank you for your answer, please tell me if I understand correctly.
I stop the master, look at the position, for example vm-db1-bin.003552 | 118618270
I see that the slave has the same position, I stop replication on the slave servers.
I transfer the database using xrtabackup to a new machine. I launch it.
It will create new bin-logs like og-db1-prod-bin.000003 | 156
Then I reset slave all on replicas.
Then CHANGE MASTER TO MASTER_HOST = ‘new’, MASTER_USER = ‘USER’, MASTER_PASSWORD = ‘pass’, MASTER_LOG_FILE = ‘og-db1-prod-bin.000003’, MASTER_LOG_POS = 156;
And then start replication.
1 Like
If you are using xtrabackup to clone the primary and don’t reset master, then the clone will resume writing to the same binlog file name and position so you can repoint replicas to the new hostname without altering the position on them.
1 Like
Thank you for your help. Your answers have given me a boost in the right direction. I managed to completely migrate all my servers from Percona 5.7 to Percona 8. I set up backups on the new servers and there was one issue that I would really like to solve to the end.
After the backup is completed, I see a file created by the utility, for example, log-bin-00222.bin This number corresponds to my numbering on the server, but on some servers I use a time lag when replicating. And when restoring, I would like to start / continue replication from an earlier log. Can you advise me. how can I properly merge this file created by the backup utility with the file that remains in the system and is naturally larger. Sorry for the vagueness, maybe I didn’t describe it clearly.
1 Like
Hmm I don’t quite understand the scenario, but if you take a backup of a delayed replica the process is the same. The file storing the coordinates should be used to run the change master command and make the server start replicating from the proper position.
1 Like
I’ll try to restate it.
So the wizard works, it writes in log-bin0003.bin e.g. from position 10 000 and will finish at position 30 000.
During the backup utility xtrabackup creates its own, new log-bin0003.bin and always with position 156.
I understand that I have to start replication from this file and this position.
But the lagging replica is at an earlier binlog position than at the time of the backup. And in the newly created log-bin0003.bin there is no previous information.
For some reason, on version 5.7, the xtrabackup utility did not create a log-bin file.
And in the file xtrabackup_binlog_info the position in the original file log-bin0003.bin was indicated
I just saved another folder with all the log-bins when I backed up. For example 0001 - 0003.
After the first start, after recovery, Mysql simply created file 0004, and therefore my replicas continued to work, no matter where they were at the time of the backup.
Maybe in the new version of the utility to specify some parameters which I have not considered?
Or how do I know at what position the backup was made in the original log-bin file still working?
1 Like
I found in the backup logs the places I’m talking about.
220117 22:58:23 Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS
220117 22:58:23 Selecting LSN and binary log position from p_s.log_status
220117 22:58:23 [00] Copying /mnt/db/bin-logs/vm-db1-bin.003645 to /mnt/backup/data/vm-db1-bin.003645 up to position 156
220117 22:58:23 [00] …done
220117 22:58:23 [00] Writing /mnt/backup/data/vm-db1-bin.index
220117 22:58:23 [00] …done
220117 22:58:23 [00] Writing /mnt/backup/data/xtrabackup_binlog_info
220117 22:58:23 [00] …done
I probably don’t quite understand the logic of the utility.
If I understand correctly, in version 8.0 it doesn’t just point to a location in the existing bin-log file, it creates a new one. And in this case, when restoring the backup, those replicas that are lagged will not be able to “seamlessly”, not even notice the restoration of the Wizard, as it was before ?
1 Like
Hello, which wizard are you referring to? Let me try to answer your question. The backup is like a snapshot in a specific point in time; this point in time corresponds to the binlog coordinates that you find in xtrabackup_binlog_info file. So if you want to use this backup to create new replicas, you can start replication from the position in the xtrabackup_binlog_info.
More info about this: Working with Binary Logs - Percona XtraBackup
Hope that helps
2 Likes
There are different ways to upgrade MySQL 5.7 to 8.0. You can use the upgrade in-place or even create a replication slave in the new version, so you can promote it later.
But before upgrading, step 0 must be backing up your data. The backup should include all the databases including the system databases. So, if there is any issue, you can rollback asap.
Another option, depending on the available resources, can be creating a cascade replication MySQL 5.7 → MySQL 8.0 → MySQL 5.7, so after promoting the new version, if something went wrong, you can promote the slave node with the old version back. But it could be dangerous if there was some issue with the data, so the backup is a must before it.
For any method to be used, it’s necessary a test environment to verify that the application is working without any issue using the new MySQL 8.0 version.
1 Like