Losing a MySQL database may be terrifying and give you nightmares; however, there are multiple ways to restore data on a MySQL server. Some ways work well and can be quite fast when your database has a couple of GBs of data and not a lot of indexes.
Things start to be more complicated and longer when you have 100s of GB of data. Unfortunately, using the good old mysqldump doesn't cut it anymore.
In this post, we will perform a "hot" MySQL data backup on Ubuntu 18.04. A "hot" backup means that it's performed while MySQL is still processing reads and writes from your application. We can use the Percona XtraBackup backup tool to achieve this. When the backup is completed, we can restore the MySQL data on the second replica server.
The same processes could also be used to restore the data on a new master database.
Go on the Percona installation page for Xtrabackup on Debian or Ubuntu to get the latest instructions for version 2.4.
Once Xtrabackup is installed, you will need to install the qpress compression utility tool that Xtrabackup requires to compress and decompress the backup.
both$ sudo apt-get install qpress
You should install these tools on both your master and replica servers.
On your master server, run the following commands to prepare the backup process.
Create a directory for the backup data. Use /var/backups/mysql. You should create this directory on both servers.
both$ mkdir -p /var/backups/mysql
Next, run the Xtrabackup backup tool. There are multiple options to pass to this tool. Here's the link for the complete list of options.
Here's the list of options you should pass:
master$ xtrabackup --backup --user=MYSQL_USER --password=MYSQL_PASSWORD --stream=xbstream --parallel=2 --compress-threads=2 --compress --target-dir=/var/backups/mysql > /var/backups/mysql/backup.xbstream
Now that you have your backup file with the name backup.xbstream, it's time to copy the file over to your replica server. For this, use scp. You could also use rsync. If your server has password authentication disabled, you will need to create a public and private key to allow the master to communicate with the replica. Otherwise, scp will prompt you to enter your user password.
master$ scp /var/backups/mysql/backup.xbstream [email protected]_IP:/var/backups/mysql
Next, extract the data from the backup file you just copied to your replica server. On the replica server, run the following command:
replica$ xbstream -x -C /var/backups/mysql < /var/backups/mysql/backup.xbstream
Next, you need to decompress the files created by xtrabackup.
Here's the list of options we will pass to xtrabackup:
replica$ xtrabackup --decompress --parallel=6 --remove-original --target-dir=/var/backups/mysql
In order for your data to be consistent and ready to be used on your replica, you need to prepare it.
Here's the list of options we will pass to xtrabackup:
replica$ xtrabackup --prepare --use-memory=12G --parallel=6 --target-dir=/var/backups/mysql
Next, to copy the new backup data to MySQL's data folder, you need to stop MySQL from running and delete the data inside the folder.
replica$ systemctl stop mysql
replica$ rm -r -f /var/lib/mysql/ # or use mv if you want to keep the data.
Next, it's time to move your prepared files to the MySQL data folder. Xtrabackup knows where your MySQL data folder is by reading your my.cnf configuration file.
Here's the list of options we will pass to Xtrabackup:
replica$ xtrabackup --copy-back --parallel=6 --target-dir=/var/backups/mysql
You could use --move-back instead of --copy-back if you don't want to save your backup data. Data will be moved instead of copied.
As the files were copied by your connected user, some might not be owned by the MySQL user. This command will change the files' ownership to mysql.
replica$ chown -R mysql:mysql /var/lib/mysql
replica$ systemctl start mysql
Now that the backup data is in place, it's probably out-of-date with your master server. During the backup process, Xtrabackup saved the binlog file name and position. It can now be used to start the replication at the right position. To get this information, run the following command.
replica$ cat /var/lib/mysql/xtrabackup_info
In the output, you should see binlog_pos. The output is the following:
binlog_pos = filename 'mysql-bin.000001', position '154'
Use this information to start the replication. Before you do this on the replica, ensure that the user on the master server used for the replication as access to the replication grant. If not, here's a SQL command to run on your master server.
GRANT REPLICATION SLAVE ON *.* TO 'replication-user'@'REPLICA_IP' IDENTIFIED BY 'PASSWORD_TO_CHANGE';
On the replica server, open a MySQL console and execute this statement with the binlog information obtained earlier.
CHANGE MASTER TO MASTER_HOST='REPLICA_IP', MASTER_USER='replication-user', MASTER_PASSWORD='PASSWORD_TO_CHANGE', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 154;
Once that's done, you can start the replication.
START SLAVE;
You should check that everything is operating properly with the following command:
SHOW SLAVE STATUS;
Confirm that both Slave_IO_Running and Slave_SQL_Running are running (the value should be "Yes"). MySQL will pick up the new changes from the master database. The Seconds_Behind_Master means that MySQL is currently catching up with the changes from master and once it's done the value should be 0.
If you used the --copy-back option, you still have the folder /var/backups/mysql full of data used for the backup. Once you confirm that your replica is running well, you can delete the data inside this folder.
both$ rm -r -f /var/backups/mysql/
You can run this command on both your master and replica servers.
And there you go! Your MySQL replica is now up-to-date with your master database.
December 2019
E-book
Discover all you need to know about business texting
Download now