MySQL Replication allows multiple copies of the same database to be on multiple servers at the same time, by automatically copying data from the primary server to the "slave" server.
Thanks to this mechanism all the statements executed on the master, which somehow change the data can be recorded, transmitted to and executed on the replicated databases.
This guide will help you install MySQL on your server, properly configure a replica and verify its operation with test data.
First, connect to your server via an SSH connection. If you haven’t done so yet, following our guide is recommended to connect securely with the SSH protocol. In case of a local server, go to the next step and open your server terminal.
MySQL installation
To set up Master / Slave replication on both servers, install a version of MySQL, then run the following installation procedure on both machines, before proceeding to the next chapter.
Update the distribution repositories, to make sure you download the most up-to-date version of MySQL:
If you have already launched this command before, go to the next step.
Now proceed with the MySQL installation:
$ sudo apt install mysql-server
To make sure that the installation was successful, check the MySQL version:
If the procedure was successfully performed, the installed MySQL version will be printed on the screen.
Proceed with securing MySQL by running a script included in MySQL, which will increase security and limit access to your databases:
$ sudo mysql_secure_installation
At this point a guided procedure will guide you through the configuration of the MySQL security level.
First, you will be asked whether you want to enable the password validation system. If enabled, when setting a user’s password, the password evaluated and if it does not meet the security criteria it is rejected with an error.
Later you will be asked whether you want to change the root password with one of your choice (if you enable the password validation system a password that meets the security criteria has to be entered).
Following several best practices for creating a secure password is recommended. This includes:
-
the use of both upper and lower case letters
-
the use of both letters and numbers
-
the use of non-alphanumeric characters like @ # $% ˆ &!
-
the use of passwords that were never previously used.
Finally choose whether to remove anonymous users, test databases and whether to disable remote login with root user. For an adequate level of security confirming all these changes is recommended.
At this point, confirm the updates of the displayed table of permissions to apply all the new security criteria.
Finally restart the service to apply the changes:
$ sudo service mysql restart
Configuring the Master/Slave replication
After installing on both servers (Master and Slave),proceed by configuring the Master server first.
First, edit the configuration file in /etc/mysql/mysql.conf.d/mysqld.cnf as follows:
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
Since the Slave server will necessarily have to remotely connect to the Master server, you must make sure that the MySQL service can accept external connections, by changing the bind-address as follows:
Once completed, restart the service to apply the changes:
$ sudo service mysql restart
For demonstration and assessment purposes, create a test database on the master server (here called "tutorial") with a table named "numbers" with fictitious data. After completing the configuration procedure check whether all the data entered here are synchronized with the Slave server:
$ sudo mysql -u root -p
mysql> CREATE DATABASE tutorial;
Query OK, 1 row affected (0.00 sec)
mysql> USE tutorial;
Database changed
mysql> CREATE TABLE numbers ( value REAL );
Query OK, 0 rows affected (0.09 sec)
mysql> INSERT INTO numbers VALUES ( 1 ), ( 2 ), ( 3 );
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
So make sure that the values you have just entered are present in the test table:
mysql> SELECT * FROM numbers;
+-------+
| value |
+-------+
| 1 |
| 2 |
| 3 |
+-------+
3 rows in set (0.00 sec)
The numbers table should contain 3 example lines as shown above.
Then proceed by creating access data for the user to be used by the Slave database:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'usr_replica'@'%' IDENTIFIED BY 'SMDipmf#23$42';
mysql> FLUSH PRIVILEGES;
Before going on with the Slave server configuration, the current status of the Master will be printed on the screen and the following information will be saved:
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 1238 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
You will need these data when configuring the Slave server.
Proceed to exit the MySQL shell:
Then save a complete database dump to load it later on the Slave server:
$ sudo mysqldump -u root -p --opt tutorial > slave-init.sql
Enter password:
Transfer the newly created file to the slave machine and proceed with its configuration, modifying the file /etc/mysql/mysql.conf.d/mysqld.cnf:
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
Please note that the server id has been changed using number 2 and also remember to change the bind-address as shown here:
Now restart the Slave to apply the changes:
$ sudo service mysql restart
Now proceed by creating a database with the same name as the Master server:
$ sudo mysql -u root -p
mysql> create database tutorial;
Query OK, 1 row affected (0.00 sec)
mysql> exit
Bye
Now import the newly created backup and start the Slave using the information previously saved by the Master database (MASTER_LOG_FILE and MASTER_LOG_POS):
$ sudo mysql -u root tutorial < /root/slave.sql
$ sudo mysql -u root -p
mysql> CHANGE MASTER TO MASTER_HOST='195.231.4.201', MASTER_USER='usr_replica', MASTER_PASSWORD='SMDipmf#23$42', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 1238;
Query OK, 0 rows affected, 2 warnings (0.10 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
From this moment on, the databases are synchronized real time and you can exit the MySQL shell.
Verifying the correct functioning of replication
To verify the operation of the replication you can try to insert data in the Master and check whether they arrive correctly at the Slave database. Start by entering new data on the Master as follows:
$ sudo mysql -u root -p
mysql> use tutorial;
Reading table information for completion of table and column names
Turn off this feature to get a quicker startup with -A
Database changed
mysql> INSERT INTO numbers VALUES ( 4 ) , ( 5 );
Query OK, 2 rows affected (0.03 sec)
Records: 2
Duplicates: 0
Warnings: 0
Then proceed by querying the Slave database:
$ sudo mysql -u root -p
mysql> use tutorial;
Reading table information for completion of table and column names
Turn off this feature to get a quicker startup with -A
changed
mysql> SELECT * FROM numbers;
+-------+
| value |
+-------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+-------+
5 rows in set (0.00 sec)
As you can see, the new values (4 and 5) have arrived on the Slave database, so the synchronization is working correctly.