How To Setup Mysql 5.7 Replication On Ubuntu 16.04

0
347
(Last Updated On: 1 June 2018)

Here’s how to setup replication between two MySQL 5.7 servers running on Ubuntu 16.04 LTS. Replication is commonly used for improved availability or to have an extra node to run analytic queries and reports. We also show how you can easily use OpsDash to monitor the replication status.

The Ubuntu 16.04 LTS repositories come with version 5.7 of MySQL, so installing it is only a matter of:

The rest of the post assumes you have setup two identical nodes running MySQL, which can talk to each other over a private network, and that the nodes have the IPs:

 

01. MASTER SETUP

First let’s setup the master. This process needs to take a consistent snapshot of the database, which involves placing a global read lock. This means the database will be read-only and all writes will be blocked for the duration of the lock. You’ll need to plan for this downtime.

I. UPDATE THE CONFIGURATION:

In the file /etc/mysql/mysql.conf.d/mysqld.cnf uncomment or set the following:

This makes the master listen on the private IP, sets it server-id (which must be unique for each node among a set of connected master and slave nodes) to 1, and enables binary logging.

 

II. RESTART THE MYSQL SERVICE FOR CHANGES TO TAKE EFFECT:

 

III. CREATE A USER FOR REPLICATION:

We’ll set the slave up to connect to the master via a dedicated account. The account needs REPLICATION SLAVE privilege. Here we’re using the username repl.

 

IV. LOCK THE MASTER:

Next we need to lock the master to get a consistent snapshot to bootstrap the slave with.

Note that this lock is released either when you exit the mysql CLI client, or when you issue UNLOCK TABLES. The lock needs to remain in place until the mysqldump in step 6 below is complete.

 

V. NOTE DOWN MASTER REPLICATION LOG POSITION:

This information will be needed to initialize the slave in a later step.

 

VI. DUMP THE MASTER DB:

Open new terminal, then type this command

Note: you can also stop mysqld and transfer the db files. This page has more details.

After mysqldump process, type this command

 

VII. COPY THE FILE TO THE SLAVE:

Transfer the dump file securely to the slave, for e.g., using scp:

or using rsync command

 

VIII. UNLOCK THE MASTER DATABASES:

Back to first terminal. You can do this either by exiting your mysql CLI shell, or by running:

 

02. SLAVE SETUP

I. UPDATE THE CONFIGURATION:

In file /etc/mysql/mysql.conf.d/mysqld.cnf, uncomment or set the following:

Like for the master above, this makes the slave listen on the private IP, sets it server-id (this time to 2), and enables binary logging. While binary logging is not strictly necessary, it makes sense to keep the slave configuration as close to the master’s as possible, in case the slave is promoted to a master. It also allows this slave to act as the master for another slave.

 

II. RESTART THE MYSQL SERVICE FOR CHANGES TO TAKE EFFECT:

 

III. IMPORT THE DB DUMP:

Import the dump file you created at the master and copied over to this slave:

 

IV. SETUP THE SLAVE TO TALK TO THE MASTER:

NOTE: ‘mysql-bin.000001‘ and 634 must be the same as step 1-6

At this point, the slave is in sync with the master and ready to accept and replay changes made to the master. The values mysql-bin.000001 and 634 come from the output of step 1-6 that you ran earlier.

 

V. CHECK THE SLAVE REPLICATION STATUS.

You be able to see the details of the slave replication by typing in this command. The \G rearranges the text to make it more readable.

Output

NOTE: both Slave_IO_Running and Slave_SQL_Running must be set to Yes. If No, read  troubleshooting sections.

 

VI. VERIFYING MYSQL REPLICATION ON MASTER AND SLAVE SERVER

it’s really very important to know that the replication is working perfectly. On Master server create table and insert some values in it.

On Master Server

 

On Slave Server

Verifying the SLAVE, by running the same command, it will return the same values in the slave too.

That’s it, finally you’ve configured MySQL Replication in a few simple steps. More information can be found at MySQL Replication Guide.

 

Troubleshooting

This troubleshooting guide lists common errors faced with MySQL Replication Process.

  1. How To Repair MySQL Replication
  2. mysql replication on windows server
  3. MySQL: Slave SQL Thread not running

 

 

Source:

  1. mysql.com
  2. howtoforge.com
  3. rackspace.com

 

.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.