How to Setup MySQL 5.7 Replication on Ubuntu 16.04
Tags: database, ubuntu, linux, replication
Published: 2020-01-11 15:51:41

## Setting up MySQL 5.7 master-slave replication on Ubuntu 16.04.
This article focus on how to setup database replication between two MySQL 5.7 servers on Ubuntu 16.04 LTS server. Database replication is mostly used for backups, improved availability and some also use it to run analytic queries and reports.

If you don’t already have MySQL 5.7 installed on your server, the following command will install MySQL 5.7 on your server since Ubuntu 16.04 LTS repositories come with MySQL 5.7.
```
sudo apt-get install mysql-server
```
Having setup the two identical servers running MySQL, which can communicate with each other on a private network and that the servers have the IPs:
```
master IP: 10.10.10.146
slave IP:  10.10.10.147
```
## Replication Master Setup
First, let’s setup the master replication node. Because this process takes a consistent snapshot of the database on the server; there will be a global read lock on the database. This means the database will not accept any data or the database will be read-only and all writing to the database will be blocked for the period of the lock. If you are working on a live database server, you’ll have to plan for this downtime.

### Step 1: Update MySQL Configuration:
In the file **/etc/mysql/mysql.conf.d/mysqld.cnf** make sure the following are uncomment or set:
```
bind-address = 10.10.10.146
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
```
These three lines of command make the replication master listen on the private IP 10.10.10.146, sets its server-id to 1, which should be unique for each database servers on the replication and binary log is also enable

### Step 2: Restarting MySQL Service for Changes to take effect:
```
sudo systemctl restart mysql
```
### Step 3: Create a User for Replication:
Let’s set a dedicated account for the slave to connect to the master. The account will be given the **REPLICATION SLAVE** privilege. Here we are using the username **slave_repl**.
```
$ mysql -u root -p
Password:

mysql> CREATE USER 'slave_repl'@'10.10.10.147' IDENTIFIED BY 'slavereplicationpassword';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_repl'@'10.10.10.147';
Query OK, 0 rows affected (0.00 sec)
```
### Step 4: Lock the Master:
Next, we lock the master database to get a consistent snapshot to get started with the slave.
```
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
```
**Note**: this lock is removed either by exiting MySQL CLI client or by issuing ``UNLOCK TABLES``. Make the lock remains until we backup the database in step 6.
### Step 5: Note down the Master Replication Log File and Position:
```
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 |      813 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
```
However, keep the log information for the initialization of the slave replication steps.
### Step 6: Backup the Master DB:
```
$ mysqldump -u root -p --all-databases --master-data > dbname.sql
```
**Note**: mysqld can be stopped and then transfer the db files to the slave server. [This page has more details](http://dev.mysql.com/doc/refman/5.7/en/replication-snapshot-method.html).

### Step 7: Unlock the Master Databases:
You can unlock the master database by exiting MySQL CLI shell or by running:
```
mysql> UNLOCK TABLES;
```
### Step 8: Copy the File to the Slave:
Transfer or copy the dump file securely to the slave server, for e.g., using ``scp``:
```
$ scp dbname.sql 10.0.0.157:/tmp
```
## Replication Slave Setup
#### Step 1: Update Configuration:
In file **/etc/mysql/mysql.conf.d/mysqld.cnf**, make sure the following are uncomment or set:
```
bind-address = 10.10.10.147
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
```
Like for the master above, the slave listens on the private IP **10.10.10.147**, sets its **server_id** to 2, and enable binary logging.

### Step 2: Restart MySQL Service for Changes to Take Effect:
```
sudo systemctl restart mysql
```
### Step 3: Import the DB Dump:
Import the dump database file copied from the master to this slave:
```
$ mysql -u root -p < /tmp/dbname.sql
```
### Step 4: Setup the Slave to talk to the Master:
```
$ mysql -u root -p
Password:

mysql> STOP SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CHANGE MASTER TO
    -> MASTER_HOST='10.10.10.146',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='slavereplicationpassword',
    -> MASTER_LOG_FILE='mysql-bin.000006',
    -> MASTER_LOG_POS=813;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
```
Now, the slave is in sync with the master and ready for replication changes from the master. The **MASTER_LOG_FILE** and **MASTER_LOG_POS** value **mysql-bin.000006** and **813** respectively come from the output of the **SHOW MASTER STATUS** that we ran earlier.
## Conclusion
Finally, if there is no error after these steps. Test the replication by creating a new table in the master database. The change should reflect in the slave database instantly.