A common issue when trying to introduce High Availability is what do to with your database. There are multiple options. The most common would be to load balance over a database cluster and other more out their solutions include dumping the database in a bash script, moving it and restoring it. To be fair, there are no wrongs here. It’s all about your setup and what you need. One of the less common solutions is to Replicate MySQL Database to slaves. MySQL offers two types of replication. Master-Slave Replication (This guide) or Master-Master MySQL Replication.
Replicate MySQL Database
The concept for this is simple. When changes are made to a centralized (Master) database server, those changes are replicated instantly to slave servers. The resolve the single point of failure, you can duplicate the master server and use a Failover IP. Basically, the master needs to run on a highly available service like a cloud.
To get started you will require at least two MySQL servers, set up exactly the same. We’re going to go through the whole setup. It’s going to take about an hour. You can use an NVMe VPS or Business Dedicated Server for this guide.
Master MySQL Server Setup
We have already covered How To Install MySQL 8 on Ubuntu 22 LTS in another blog post so before you follow this guide. Follow that guide. You need to create a Master and as many slaves as you require. Follow the guide exactly. Creating the same database and user on all servers. But remember when running the mysql_secure_installation script, do not disable remote logins.
You should also set up SSH keys so your master can replicate MySQL databases to your slaves without the need for a password.
Configure Master For MySQL Replication
Now that you have your MySQL cluster online we need to configure the master to allow replication of the database you have created to your slaves. MySQL offers two types of replication. The older, binary log file position-based replication and the newer transaction-based replication method where each transaction is assigned a GTID. With the latter, If a slave comes across a transaction that has already been processed, it will skip it. So in this guide, we will be configuring binary log file position-based replication.
In your master, open up the mysqld.cnf file. We need to make the following changes.
nano /etc/mysql/mysql.conf.d/mysqld.cnf bind-address = IPv4 Address server_id = 1 log_bin = /var/log/mysql/mysql-bin.log binlog_do_db = Example
Here you are telling MySQL to use the server’s IPv4 address to allow connections. That’s so slaves can log in to get a list of transactions. You are setting a server-id which must be unique. Use 1. You also specify a location to the bin-log, this should be correct. And lastly, you specify the database name you created when setting up the MySQL server. Save and close the file. Then, restart MySQL.
systemctl restart mysql
When creating the MySQL master you likely created a user and database. That user would be assigned to “localhost” we need to create a new user that specifies the slave IPv4. We then need to grant it replication privileges. Log in as the root user to MySQL and issue the commands. Here you specify the database user and the IP of your slave. You are allowing the database user to connect to the master from the slave IP.
mysql -u root -p CREATE USER 'dbuser'@'Slave-IPv4' IDENTIFIED WITH mysql_native_password BY 'Password'; GRANT ALL ON *.* TO 'dbuser'@'Slave-IPv4'; # You might be able to just grant replication privilages depending on what you # require. GRANT REPLICATION SLAVE ON *.* TO 'dbuser'@'Slave-IPv4'; FLUSH PRIVILEGES;
Setting Binary Log Coordinates
The Binary log is where your slave will get new entries to replicate to its local MySQL database. You can configure this to import existing data in the database to your slaves. But this is outside the scope of this article so we are assuming you have an empty database. So, still in the MySQL console, issue the commands to retrieve the binary log coordinates.
FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; UNLOCK TABLES;
Here is our Binary log information.
So from this, we can disseminate that our log position is 1313. This is the position in the log our slaves will start copying data from. Make a note of the file name (mysql-bin.000001) and the position.
Finally, we need to configure the slave. Login as root and open up the mysqld.cnf file. This is the file were we specified the MySQL servers IPv4.
Again, specify the information. You should just change the IPv4 and the server-id to 2 and the database you are replicating. The database and log-bin would be the same as in the master mysqld.cnf file. The ID must be unique. You must set a relay-log on all slaves.
bind-address = IPv4 Address server-id = 2 log_bin = /var/log/mysql/mysql-bin.log binlog_do_db = Example relay-log = /var/log/mysql/mysql-relay-bin.log
Finally, restart the MySQL service.
systemctl restart mysql
OK. That should be the process of configuring MySQL replication. We have configured a master to allow slaves to log in and read the binary log file. Slaves will then add these changes to their own local database. Let’s test our work!
Testing MySQL Database Replication
Still in the slave, let’s tell it the master IP and some additional settings. Run each command on it’s own!. You are specifying the master IPv4, the user that we created (dbuser) and the password and the binary log coordinates.
mysql CHANGE REPLICATION SOURCE TO SOURCE_HOST='master_server_ip', SOURCE_USER='dbuser', SOURCE_PASSWORD='password', SOURCE_LOG_FILE='mysql-bin.000001', SOURCE_LOG_POS=1313 ;
And now we can start replication.
START REPLICA; SHOW REPLICA STATUS\G;
You should see a message that confirms replication has started. Query OK, 0 rows affected (0.04 sec) Assuming all went well, you should see something like this.
*************************** 1. row *************************** Replica_IO_State: Waiting for source to send event Source_Host: 126.96.36.199 Source_User: dbuser Source_Port: 3306 Connect_Retry: 60 Source_Log_File: mysql-bin.000002 Read_Source_Log_Pos: 157 Relay_Log_File: mysql-relay-bin.000003 Relay_Log_Pos: 373 Relay_Source_Log_File: mysql-bin.000002 Replica_IO_Running: Yes Replica_SQL_Running: Yes
The important bits are Replica_IO_Running and Replica_SQL_Running. Both of these should be yes and your slave should be waiting for the master to update the binary log file. Let’s add some data to the master and see if it’s replicated to the slave.
Add Test Tables To Master Database
On the Master. Log in to MySQL and select the replication database. Switch Example for your replication database.
mysql -u root -p USE Example
Now create a table. This should then be replicated to the slave.
CREATE TABLE dev_table ( example_column varchar(30) );
Check Slave For Replicated MySQL Database Table
Now log back into the slave. Login as root and switch to the replication database. Issue the show tables command and you should see the table we just created in the master.
mysql -u root -p USE Example SHOW TABLES;
That’s it. We are now replicating MySQL databases to a slave. You can replicate to as many slaves as you like and if you are setting up a cluster, you can add High Availability to that cluster by Installing HAproxy on a High Availability server.
How was this article? – Replicate MySQL Database To a cluster
You might also like
More from MySQL/MariaDB
Recently, we went through the process of load balancing HTTPS connections with HAProxy. In this article, we are going to …