What is MySQL replication?

MySQL replication is a process that allows data in one or more MySQL database servers, called slaves, to automatically remain in sync with one master MySQL database server. Replication can be very convenient as it enables scalability by spreading out read access to multiple servers, facilitates data backup, and allows data analysis on the slave without accessing the master server. In this shot, we will go over the steps required to replicate data from a master database to a single slave database.

Prerequisites

This shot assumes the following:

• The user has sudo privileges
• MySQL is set up on the machine
• Two servers (the master and the slave) are set and running on two different IP addresses

Configuring the master database

First, we need to open up the MySQL configuration file on the master server using the following command on the terminal:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

You will be prompted to enter your root password before the configuration file opens up. Scroll down until you reach the following line:

bind-address        = 127.0.0.1

Here, change the standard IP address to the IP address of the master server.
Next, proceed further down the configuration file and make sure the following lines are uncommented:

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = include_database_name

You can choose a server-id other than 1 as long as the selected id is unique and does not match any other server-id in the replication group. Replace include_database_name with any database you want to replicate on the slave server. For our example, let’s use a database called repl_database. If you want to replicate more than one database, repeat the line binlog_do_db = include_database_name for all of the databases you want. Now, we are done making changes to the configuration file, so we will save and exit the file before refreshing MySQL with the following command:

sudo service mysql restart

The next commands will be fun on the MySQL shell. We will fire up the shell using the command below:

mysql -u root -p

Before proceeding further, we need to grant privileges to the slave databases. We will use the command in the following format to name our slave database and set up its password:

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';

The command will follow this:

FLUSH PRIVILEGES;

For the next few steps, we will need to open a new window or tab alongside the one already running. In that new tab, we will run the following MySQL commands:

USE repl_database;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

The SHOW MASTER STATUS; command above will display a table showing the position of the database the slave would copying from. Open a new window once again and type the following command in the terminal to export your database:

mysqldump -u root -p --opt repl_database > repl_database.sql

Now, return to the previous window that is running the MySQL shell and run the following commands to finish configuring the master database:

UNLOCK TABLES;
QUIT;

Configuring the slave database

Next, we need to configure the slave database. For this, we will log into our slave server, run the MySQL shell, and create the database that we will be replicating from the master using the commands below:

mysql -u root -p
CREATE DATABASE repl_database;
EXIT;

Next, we want to import the database the master exported with the command:

mysql -u root -p repl_database < /path/to/repl_database.sql

Now, we are in a position to configure the slave server in a similar way to how we configured the master. Once again we will open the configuration file with:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Now, make sure the following changes are made in the configuration file:

server-id               = 2
relay-log               = /var/log/mysql/mysql-relay-bin.log
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = repl_database

Once again we will restart MySQL:

sudo service mysql restart

Now, we can enable replication using the MySQL shell. Start up the shell with mysql -u root -p and type the following command by replacing the fields within asterisks with your respective data in string form:

CHANGE MASTER TO MASTER_HOST= *Your master IP address*,MASTER_USER= *Your master user*, MASTER_PASSWORD= *Your master password*, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  107;

Finally, we can activate the slave server and view the replication details with the commands:

START SLAVE;
SHOW SLAVE STATUS\G

In case you encounter an issue, try:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START; 

You have now successfully replicated data from a MySQL master database to a slave database!