Source-Replica Tutorial

Last Update: May 2026

Exercise 5

In this exercise you will: Prepare the backup, start MySQL on DB2 and connect the replica to the source (mysql-db1).

• 5.1 - Decompress the backup

  Recall that we used the --compress option to take our backup. Before we can do anything else to our DB2 instance, we have to decompress the backup.

  # cd /var/lib/mysql
  # xtrabackup --decompress --remove-original --parallel 4 --target-dir ./
  

• 5.2 - Prepare the backup

  When you take a backup of a running MySQL instance, there are usually transactions in-flight. Xtrabackup records these transactions while taking the snapshot of your data on-disk. We must now apply all of those changes that happened during the backup process to the actual data files.

  In your MySQL-DB2 terminal window, you should still be inside your MySQL $DATADIR. We can now issue the following command:

  # xtrabackup --prepare --use-memory 2G --target-dir ./
  

  • --prepare - This option tells xtrabackup that we want to apply any in-flight transactional changes.
  • --use-memory 2G - Increasing the amount of memory available to xtrabackup can significantly speed up this process. If there is nothing else running on your system, you may increase this to 10/20/50GB.

  This is a two-stage process that xtrabackup handles for you. Stage 1 applies all of the transactional information to the datafiles and stage 2 creates new logfiles. You should see “completed OK!” again when both steps are completed. The backup is now prepared and ready to be used.

• 5.3 - Change ownership

  Presumably, you ran all of this as root. Thus we need to change the ownership of all the MySQL files otherwise it will not start.

  # chown -R mysql:mysql ./*
  

• 5.4 - Start MySQL

  We can now start MySQL.

  # systemctl start mysql
  

  MySQL should now be running without any errors.

• 5.5 - Create a replication user

  On your MySQL-DB1 instance, we need to create a user that can connect and receive binlog events. This user needs the REPLICATION SLAVE privilege. Log in to MySQL and execute the following DDL statement.

  mysql> CREATE USER 'replication'@'10.11.%' IDENTIFIED BY 'myPassword1#';
  mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'10.11.%';
  

  You can substitute any username, hostname and password you wish.

• 5.6 - Connect the replica instance and start replication

  Now that the user is created, go back to your MySQL-DB2 terminal and log in to MySQL. We now need to tell this instance to become a replica of DB1. We do that with the CHANGE MASTER command.

  mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST='db1-TX', SOURCE_USER='replication',
      SOURCE_PASSWORD='myPassword1#', SOURCE_LOG_FILE='master-bin.0000001',
      SOURCE_LOG_POS=107, SOURCE_SSL=1;
  

  To determine the values for SOURCE_LOG_FILE and SOURCE_LOG_POS, scroll up in your terminal window a bit. They will have been printed out at the end of the --prepare execution from exercise 5. You can also cat xtrabackup_binlog_info to find this info. SSL is required when using the new caching_sha2 authentication in MySQL 8.0.

  You can now start the replication process.

  mysql> START REPLICA;

  You can now see the replica running with other information:

  mysql> SHOW REPLICA STATUS\G
  

• 5.7 - Woops!

  Doesn’t look like our replica is running; we have an error! One thing we forgot to do is change the server_id of the replica; it cannot be the same id as the master. Edit /etc/my.cnf and change the server_id to anything other than 0 and it’s current value.

  You will have to restart MySQL for this to take effect.

  After you’ve restarted MySQL, log back in and run another

  mysql> SHOW REPLICA STATUS\G
  

  If you see Replica_SQL_Running: Yes and Replica_IO_Running: Yes, congratulations! You’ve set up a replica instance!