Replication with MySQL

Replication with MySQL

Mar 03, 2023

As with PostgreSQL, MySQL also offers high availibilty using asynchronous replication. Very easy to set up. We will go through the primary-replica scenario in this article.

Firstly, you need to restore your dumps onto the replica server in order to obtain relatively recent data.

$ mysql -p database_name < dump_file.sql 

I omit the username because it is assumed that you are logged into the server as the root user, so, it is redundant to supply the root username for mysql.

If you've got multiple databases to restore, as most of us would, you will have to use a bash script to restore each database. Alternitavely, the easier method is to dump all databases on the primary server and restoring in a similar fashion:

$ mysqldump -p --all-databases -f > all_databases.sql
$ scp all_databases.sql IP_OF_REPLICA:/somewhere/all_databases.sql
$ mysql -p < all_databases.sql

Once the restore is complete (this can take some time depending on database sizes and hardware specs), we can now start to replicate. Set the replica server_id in the /etc/my.cnf

server-id=2

If you're going to have multiple replica servers, then you need to set each one's id incrementally, i.e. next replica should be server-id=3 and so forth.

It is also necessary to create a user with replication privileges on the primary server

mysql> grant replication slave on *.* to replica@’%’ identified by ‘password’;

We need to obtain the master status from the primary server with a simply query like so:

mysql> show master status;

Next, on the replica, set the master log file and bin position like so:

mysql> change master to 
->master_host='MASTER_IP',master_user='replica', 
->master_password='password', 
->master_log_file='mysql-bin.000005', 
->master_log_pos=212; //whatever you got from the master status

Now we can start the slave on the replica

mysql> start slave;

Finally, we can check the replication status by running the following on the replica:

mysql> show slave status\G

You should see the SlaveIO and SlaveSQL running  as well as the seconds behind master field. It may take up to a couple of hours to catch up depending on how far behind your replica is on the bin logs.

That should be it!

Thank you for reading I hope you've learnt something from this article.

Until next time.

Enjoy this post?

Buy Alexis Panopoulos a coffee

More from Alexis Panopoulos