You can mirror your MySQL Server database to other MySQL Database servers automatically by using the replication feature of MySQL. You will learn about the basics of MySQL replication and the configuration of a replicated MySQL database environment.
This is an introduction on how to set up this in Ubuntu. Tested on Ubuntu 18.04 and 16.04.
There is a master server (in this example: 192.168.0.1) which does have our database that will be replicated (mirrored). The master server saves all the executed database commands ("CREATE", "INSERT", "UPDATE",..) into its binary log file (mysql-bin.log).
The slave server/s (in this example: 192.168.0.10) are connected to the master server. The slave server reads the binary log file of the master server and copies the data from there to the relay log file (mysql-relay-bin) of the slave server. The events and commands of the relay log file are then run step by step on the database server of the slave server.
There can be several slave servers, but only one master server. The slave servers do not have writing privileges to the database of the master server, but only replication privileges.
Usage
A replicated database server can be used for example to load balance your database. Load balancing in MySQl can be realized with the proxy software "HaProxy".
Your HaProxy proxy server is connected to all the used database servers. Access to the database server is done through "HaProxy" which then connects you to a database server that is running.
Requests to the database server are distributed to different configured running database servers. The proxy server does check also, if your MySQL Servers are running by using the module "mysql-check".
This tutorial however covers only the creation of a replicated database server environment. You can then add additionally a load balancing proxy or you can use the slave database server as a backup to restore your database data faster.
Note: Please check if the MySQL port 3306 is opened in all of the used database servers (Firewall settings).
1. Install MySQL on both the master server and the slave server
sudo apt-get install mysql-client mysql-server
Do not forget to add also a secure root password.
sudo mysql_secure_installation
2. Setting up the MySQL server on the master server
2.1 Configuration of the MySQL Server
Open your MySQL configuration file:
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
Set in "bind-address" your public IP address of the master server or "0.0.0.0".
bind-address = 192.168.0.1
Set the "server-id" to "1".
server-id = 1Add also the paths to the files relay_log, relay_log_index, log_bin and log_bin_index:
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index
If you want do replicate only a certain database, then add this with your database name:
binlog_do_db = YOUR_DATABASE_NAMEIf you want to exclude a database from replication, then add this with your database name:
binlog_ignore_db = YOUR_DATABASE_NAME
2.2 Adding a user that is used for the replication
Login into the MySQL server of your master server.
sudo mysql -u root -pCreate a new user in MySQL that is used for the replication of this database server. The user should be referenced with the public ip address of the slave server. In this example it is the IP address "192.168.10".
CREATE USER 'replicator'@'192.168.0.10' IDENTIFIED BY 'REPLICATOR_PASSWORD';Add the following privileges for the new user:
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'192.168.0.10';FLUSH PRIVILEGES;These are the privileges of the MySQL user that does do the replication of your database. If you use a load balancer with an CMS software such as Joomla, then you should set a read only privilege for the used databases in your slave server.
If you want to replicate a database that is already created and used, then please change to that database and lock that database with this command:
USE MY_DATABASE;
FLUSH TABLES WITH READ LOCK;If you are done with setting up the database replication server, then do not forget to unlock your database with this command: UNLOCK TABLES;
Check now the status of your database master server and note the first two values ("File" and "Position").
SHOW MASTER STATUS\GThe two first values are later needed when we set up the slave server.
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.001202
Position: 1017657
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)In this example they are "mysql-bin.001202" and "1017657".
Create a dump of your database server.
sudo mysqldump -u root -p --add-drop-database --all-databases > /database_master_dump.sql
If you want to replicate only a certain (running) database, then create a dump of that certain database with this command:
sudo mysqldump -u root -p --single-transaction --databases MY_DATABASE > /database_master_dump.sql
3. Setting up the MySQL server on the slave server
3.1 Configuration of the MySQL Server
Open your MySQL configuration file.
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnfSet in "bind-address" your public IP address of the slave server or "0.0.0.0":
bind-address = 192.168.0.10
Set the "server-id" to "2":
server-id = 2
Add also the paths to the files relay_log, relay_log_index, log_bin and log_bin_index:
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index
Restart the MySQL server.
sudo systemctl restart mysql
Login into your MySQL server.
sudo mysql -u root -pStop the slave server.
STOP SLAVE;
Run this command with the values of the MySQL configuration of the master server, that were shown in Step 2 of this tutorial.
CHANGE MASTER TO MASTER_HOST = '192.168.0.1', MASTER_USER ='replicatior', MASTER_PASSWORD ='REPLICATOR_PASSWORD', MASTER_LOG_FILE = 'mysql-bin.001202', MASTER_LOG_POS = 1017657;In this example the IP address "192.168.0.1" is the public IP of the master server. The "MASTER_USER" and "MASTER_PASSWORD" are the credentials of the user that was created in the master server for the database replication.
"MASTER_LOG_FILE" and "MASTER_LOG_POS" are values that we got from following command that we run on the MySQL server in the master server: SHOW MASTER STATUS\G
Don't forget to import the created MySQL dump of the master database server into the database server of the slave server. Example command:
mysql -u root -p < /database_master_dump.sql
4. Starting the master and slave server
Now start the slave server.
START SLAVE;Login into the MySQL server in your master server and start as well the master server.
mysql -u root -pSTART MASTER;
5. Check the status of your master and slave server
Status of the slave server:
SHOW SLAVE STATUS\G
Status of the master server:
SHOW MASTER STATUS\G
This was an introduction into MySQL replication. You can also add additional slave servers by repeating the steps from 2.1 to 4.
More about MySQL Replication:
https://dev.mysql.com/doc/refman/8.0/en/replication.html