How to Replicate MySQL Database to Another Server

MySQL Replication allows you to easily copy database from one server to another. MySQL supports different kind of replication such as master-slave, master-master and group replication. MariaDB also supports multi-master replication. In this article, we will look Master-Slave replication in MySQL and learn how to replicate MySQL database in Linux. You can use these steps to replicate MySQL database in Ubuntu, Debian, CentOS, Fedora, Red hat and other types of Linux.

How to Replicate MySQL Database

Here are the steps to replicate MySQL database. For our setup, we will need a master database (IP – 54.24.32.12) and a slave database (IP – 45.12.21.23). We will replicate database named exampledb from master to slave. We have assumed that you have MySQL installed on both these servers, and you have root privileges for both of them. Else you can install MySQL with following command

$ sudo apt-get install mysql-server mysql-client

Bonus Read : Top MySQL Workbench Alternatives

1. Edit Master Configuration file

Open terminal on master database’s server and run the following command

$ sudo vi /etc/mysql/my.cnf

By default, remote connections are disabled in MySQL. We need to allow remote connection from slave to master for replication. So we uncomment the following two lines to allow remote connections, by adding # at their beginning, as shown below

#skip-networking
#bind-address=127.0.0.1

Add or uncomment the following lines in [mysqld] block to look like the following.

[mysqld]
server-id=1
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=exampledb

Let us look at each of the above line. The first line server-id specifies a unique ID number for master. It needs to be a positive number from 1 to 2^32.

log-bin specifies the log file location that will be populated by MySQL with details of replication.

binlog-do-db indicates the name of database that needs to be replicated.

Restart MySQL Server to apply changes

$ sudo service mysql restart

Log into MySQL as root user

$ sudo mysql -u root -p

and run the following command

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 |   exampledb  |                  |
+------------------+----------+--------------+------------------+

Please note the above details, we will need them later in Step #3.

Bonus Read : How to Enable SSL/TLS in MySQL

2. Create Replication User

Log into MySQL server on master.

$ sudo mysql -u root -p

You will be prompted for root password.

After logging into MySQL, run the following commands to create a remote user slave_user, and grant it replication permissions for all databases. Please replace 45.12.21.23 below with your slave server’s IP, and replace $password with a suitable password as per your requirement.

mysql> CREATE USER slave_user@45.12.21.23; 
mysql> GRANT REPLICATION SLAVE ON *.* TO slave_user@45.12.21.23
       IDENTIFIED BY '$password';
mysql> FLUSH PRIVILEGES;

Please add an inbound firewall rule for port 3306 and allow 45.12.21.23. This remote user needs to connect to master database for replication to happen.

Bonus Read : How to Change Collation of All Tables in MySQL

3. Edit Slave Configuration file

Open terminal on slave database’s server and run the following command

$ sudo vi /etc/mysql/my.cnf

Add the following lines under [mysqld] to look like

[mysqld]
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = exampledb

In the first line, we assign server ID of 2 to slave server. Next couple of lines specify locations of relay log and replication log files. Last line specifies the database to be replicated.

Restart MySQL Server and log into MySQL

$ sudo service mysql restart
$ sudo mysql -u root -p

Run the following commands to create an empty database on slave.

mysql> CREATE DATABASE exampledb; 
mysql> USE exampledb;

Load data from master database to populate slave database

mysql> LOAD DATA FROM MASTER;

Exit MySQL.

Bonus Read : Top Database Blogs to Follow

4. Initialize Replication

While you are logged into MySQL, run the following commands to initiate replication process. Replace parts in bold with your values.

mysql> SLAVE STOP;
mysql> CHANGE MASTER TO MASTER_HOST='54.24.32.12',
       MASTER_USER='slave_user', 
       MASTER_PASSWORD='<password>', 
       MASTER_LOG_FILE='mysql-bin.000001', 
       MASTER_LOG_POS=107;

MASTER_HOST – IP address or hostname of the master (54.24.32.12).
MASTER_USER – slave user we created in step #2.
MASTER_PASSWORD – password of slave user we crated in step #2.
MASTER_LOG_FILE – file MySQL gave back in step #1 when you ran
SHOW MASTER STATUS
MASTER_LOG_POS – position MySQL gave back when you ran SHOW MASTER STATUS in step #1

Finally, we start the slave to begin replication of MySQL database.