dasini.net – Diary of a MySQL expert

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:

server-id 
log-bin 
binlog_format 
gtid-mode=ON
enforce-gtid-consistency

1st Replication Method – mysqldump

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

mysqldump -u dumpUser -p --routines --events --triggers --single-transaction --set-gtid-purged=ON --all-databases > backup.sql

Once this is finished, if we do :

grep PURGED backup.sql

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:

mysql -u root -p -e "source path/backup.sql"

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

CHANGE MASTER TO MASTER_HOST='primary_IP' ,MASTER_USER='user_to_connect',MASTER_PASSWORD='******', MASTER_AUTO_POSITION=1;
start slave;

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:

FLUSH TABLES WITH READ LOCK;
rsync -axvz /datadir/* root@replica:/datadir

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:

 systemctl start mysqld
 mysql -uroot -p
 CHANGE MASTER TO MASTER_HOST='primary_IP' ,MASTER_USER='user_to_connect',MASTER_PASSWORD='******',    MASTER_AUTO_POSITION=1;
 start slave;

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

unlock tables;

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:

mysqlbackup -u backupUser -p --backup-dir=/tmp --backup-image=- --compress backup-to-image | ssh mysql@replica_host "/bin/mysqlbackup --backup-dir=/tmp --datadir=path_of_data --log-bin=binlog_path --relay-log=relay_log_path --uncompress --backup-image=- copy-back-and-apply-log"

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:

 reset slave;
 reset master;
 set global.gtid_purged=@@ (this is the command inside the backup_gtid_purged.sql)
 CHANGE MASTER TO MASTER_HOST='primary_IP' ,MASTER_USER='user_to_connect',MASTER_PASSWORD='******', MASTER_AUTO_POSITION=1;
 start slave;

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:

INSTALL PLUGIN clone SONAME 'mysql_clone.so';
CREATE USER clone_user@'%' IDENTIFIED BY 'xxxxxx';
GRANT BACKUP_ADMIN, CLONE_ADMIN ON *.* to clone_user@'%';

Verify that plugin is indeed active:

SELECT PLUGIN_NAME, PLUGIN_STATUS FROM information_schema.PLUGINS WHERE PLUGIN_NAME = 'clone';
+-------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------+---------------+
| clone       | ACTIVE        |
+-------------+---------------+

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

SET GLOBAL clone_valid_donor_list = 'Primary_host_IP:port';

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:

CLONE INSTANCE FROM 'clone_user'@'Primary_host_IP':port IDENTIFIED BY 'xxxxxx';

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

CHANGE MASTER TO MASTER_HOST='primary_IP' ,MASTER_USER='user_to_connect',MASTER_PASSWORD='******', MASTER_AUTO_POSITION=1;
start slave;

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

• MySQL Enterprise Backup:
  • https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/mysqlbackup.html
• mysqldump:
  • https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html
• The Clone Plugin:
  • https://dev.mysql.com/doc/refman/8.0/en/clone-plugin.html
• Setting Up Replication Using GTIDs:
  • https://dev.mysql.com/doc/refman/8.0/en/replication-gtids-howto.html

Filed under:
Backup / Restore, MySQL by Anastasia Papachristopoulou