Setting up Replication with various methods for MySQL 8

December 10, 2020
Irish Castle and mountains by Olivier DASINI

In the world of the Databases, one of the most important value that we are all trying to achieve is High Availability. Not to loose our valuable data or in case one server fails to always have another to step in and take control.

So, it is critical to have along with our primary database, other identical instances, the replicas.

On this blog post, we will see the most famous ways to set up our replication.

MySQL offers a variety of options to achieve replication set up. Here we are going to review how to create a replica based on GTID replication by using one of the following methods:

  • mysqldump
  • rsync
  • MySQL Enterprise Backup (MEB)
  • Clone plugin

MySQL Shell, using the utilities, is another method. Please see this article.

Before we initiate any process we need to make sure that on our my.cnf, primary & replica, have set the following parameters:

1st Replication Method – mysqldump

So, starting with mysqldump option we need to make sure that we have enabled gtid-purged:

Once this is finished, if we do :

We will receive the corresponding gtid that is needed for setting up our replica. The backup.sql file should be moved to the replica, a simple scp should do.

Moving along to the replica, we perform the following command:

After this import is completed, we simple need to execute the following:

If both the above commands are executed with no issue, perform a show slave status\G to verify that both Slave_IO_Running and Slave_SQL_Running are set to yes.

2nd Replication Method – rsync

When using the rsync option, the database on the replica must not be running. And on the primary db we will need to have a lock on the tables. As soon as the tables are locked we may proceed with rsyncing the data.

So:

Once the copy is completed, we will need to remove the auto.cnf (as this will give the same UUID and we will not be able to set up our replica) and we will also remove mysql.sock.

The next steps will be to start our database and set it up as a replica:

And of course do not forget to reverse the lock command on the primary:

3rd Replication Method – MEB

MEB, MySQL Enterprise Backup, is a commercially licensed backup utility for MySQL databases. One of the fastest and easiest way to set up your replication while primary db is up and running.

Please note that the db on the replica should not be running and the corresponding paths should be empty, as this may cause confusion. For example, imagine having forgotten a binlog file and binlog_index to trying to read from erroneous binlogs. This will lead to having the replication broken.

So, for mysqlback & restore we can perform:

In one command we are sending our data to the replica as well.

Once, this is completed we go into the /tmp folder in the primary node and inside the meta folder, we take note of the command inside the backup_gtid_purged.sql file. This is needed for setting up our replica:

Our replica is up and running with no trouble.

4th Replication Method – clone plugin

Last but not least we have the clone method. Clone is initiated at 8.0.17 version and it is a really useful tool.

First thing we need to do is to install the clone plugin on both the primary and wannabe replica as well as the user with the proper grants:

Verify that plugin is indeed active:

And we will also set up our donor on the replica:

Now we need to log in to the replica with the clone_user we created on the above-mentioned steps and execute the following command:

Once this is completed, we log out from clone_user and log in with root and set up our replica:

Please note that the master_user that is being used to set up our replica in all the methods needs to have access on our replica and have the replication_slave privilege.

Conclusion

To sum up, MySQL offers a variety of ways to set up tour replication, what you will choose depends on your needs for speed, performance and down time.

References

6 Responses to “Setting up Replication with various methods for MySQL 8”

  1. You can forget about mysqldump and focus also on MySQL Shell Dump & Load utilities 😉

  2. Anastasia gave us a feedback of the tools she has used so far.
    But I do agree, MySQL Shell Dump & Load utilities will become the new standard 🙂

  3. You are both absolutely right 🙂 We should prepare a demo with MySQL Shell! Thank you for the idea!

  4. I would like to clear my doubts in MySQL 8.0+ doesn’t allow on replica to drop MySQL system database when restoring sql dump taken through —all-databases .your blog is how many types we can setup replication

  5. Hey, MySQL system database indeed cannot be deleted in MySQL 8+. That’s why option –databases should be used instead of add-drop databases. In case MySQL system db needs to be deleted due to corruption, then this should be done from the operating system and then you will need to initialize MySQL once again. Option –all-databases also doesn’t drop the database, it has the command “CREATE DATABASE IF NOT EXISTS”. I hope this helps shed some light, if not please feel free to reach out once again!

  6. […] one of our previous articles – Setting up Replication with various methods for MySQL 8 – we reviewed how to create a replica with multiple tools. Now, it is time to perform the […]