Tutorials > How to configure a MySQL Slave Database

How to configure a MySQL Slave Database

Published on: 18 January 2020

Database MySQL

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:

$ sudo apt update

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:

$ mysqld --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:

bind-address            = 0.0.0.0

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:

mysql> exit
	Bye

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:

bind-address            = 0.0.0.0

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.