Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog How to Configure MySQL Master-Slave Replication on Ubuntu 16.04

How to Configure MySQL Master-Slave Replication on Ubuntu 16.04

In this guide, we will show you how to setup and configure MySQL Master-Slave replication on your Ubuntu 16.04 server running on Alibaba Cloud.

By Francis Ndungu, Alibaba Cloud Tech Share Author. Tech Share is Alibaba Cloud's incentive program to encourage the sharing of technical knowledge and best practices within the cloud community.

Alibaba Cloud offers a hassle-free on-demand computing service, Elastic Compute Service (ECS), which can be deployed with just a few clicks and install a Linux distribution of your choice.

With each instance configured, you can run highly scalable database servers such as MySQL. On top of these, you can take advantage of their fast memory and latest CPUs to create a redundant environment for your MySQL server.

A MySQL Master-Slave replication uses separate Alibaba Cloud ECS instances to offer a clustered environment for your database. Data is live-copied to multiple computers for backup service, analysis or for scaling out and creating different nodes for multiple applications.

In this guide, we will show you how to setup and configure MySQL Master-Slave replication on your Ubuntu 16.04 server running on Alibaba Cloud.

Prerequisites

  1. A valid Alibaba Cloud account
  2. Two separate Alibaba Cloud ECS instances running Ubuntu 16.04 Operating System
  3. A non-root user with sudo-privileges for both instances.

For the sake of simplicity, we will use be using two servers; server 1 and server 2 with the IP addresses 11.11.11.11 and 22.22.22.22.

Step 1: Install MySQL Server On Server 1 (Master)

The first step is installing MySQL server on server 1 with IP address 11.11.11.11. So SSH to your server and run the below commands:

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

Press Y and hit Enter when prompted to confirm the installation.

Step 2: Edit MySQL Configuration File on Server 1

Next, edit the MySQL configuration file in order for MySQL server to allow remote access:

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

Look for the line bind-address and change its value to '0.0.0.0'.

 bind-address = 127.0.0.1

Change to :

bind-address =0.0.0.0

This will cause MySQL to allow inbound connections.

Next, add the below lines near the end of that configuration file:

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index

Press CTRL+X, Y and hit Enter to save the file.

Restart MySQL server for the changes to take effect:

$ sudo service mysql restart

Step 3: Create a New User for Replication Services on Server 1

Next you need to create a new user that will handle replication services on server 1. So, log in to your MySQL on server 1.

$ sudo mysql -u root -p

Enter your password when prompted to do so and hit Enter to continue.

On the MySQL prompt that appears, enter the below SQL queries to create the user:

mysql > create user 'replication_user'@'%' identified by 'PASSWORD';
mysql > GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
mysql > FLUSH PRIVILEGES;

Remember to replace 'PASSWORD' with a strong value for security purposes.

Before you exit the MySQL prompt, enter the command below to view information about the position and file of the Master Server

mysql> show master status; 
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Here, you need to note the file name, 'mysql-bin.000001' and Position '154'. You will need these details when configuring server 2. Please note, these values might be different on your server.

Step 4: Install MySQL On Server 2 (Slave)

Just like you did in the first server, you need to install MySQL server on the second server. Run the commands below:

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

Press Y and hit Enter when prompted to confirm the installation.

Also, you will need to supply a root password for your MySQL server. Then, hit Enter on the screen that pops-up. Repeat the password one more time on the next screen for confirmation.

Step 5: Modify the Configuration File on Server 2

Next, edit the MySQL configuration file on server 2 to allow remote access:

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

Look for the line bind-address and change its value to '0.0.0.0'.

bind-address = 127.0.0.1

Change to:

bind-address =0.0.0.0

This will cause MySQL to allow inbound connections.

Next, add the below lines near the end of the file:

server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index

Please note the server-id is '2'.

Press CTRL+X, Y and hit Enter to save the file.

Restart MySQL on server 2 for the changes to take effect:

$ sudo service mysql restart

Step 6: Change Master-Slave Configuration on Server 2

Next, login to MySQL on server 2:

$ sudo mysql -u root -p

Enter your password and hit Enter to continue.

On the MySQL prompt that appears, enter the below details and replace IP address (11.11.11.11) with the public IP address associated with your Alibaba ECS instance running server 1.

Also, replace the 'MASTER_LOG_FILE' and 'MASTER_LOG_POS' with the information you obtained from server 1.

The MySQL username and password should also match the values you created on server 1.

mysql> stop slave; 
mysql> CHANGE MASTER TO MASTER_HOST = '11.11.11.11', MASTER_USER = 'replication_user', MASTER_PASSWORD = 'PASSWORD', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 753; 
mysql > start slave;
mysql > Quit;

Step 7: Test the Configuration

You should now test the configuration and see whether MySQL replication is working as expected. To confirm this, log in to MySQL on server 1

$ sudo mysql -u root -p

Enter your root password and hit Enter to continue.

On the MySQL prompt that appears, create a database (e.g. replica_demo ):

mysql> create database replica_demo;
Query OK, 1 row affected (0.01 sec)

Then, log in to MySQL on server 2:

$ sudo mysql -u root -p

Enter your root password and hit Enter to continue.

On the MySQL prompt that appears, type the below SQL command to list available databases:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| replica_demo       |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

The output above is for server 2 and it clearly confirms that MySQL Master-Slave replication is working as expected because the database 'replica-demo' was replicated on server 2.

Step 8: MySQL Master-Slave Proof of Concept

We can take the proof of concept further and add a table 'students' on the 'replica_demo' database on server 1:

On server 1 run the commands below on the MySQL prompt:

mysql> use replica_demo

Output

Database changed

Then create the students table with the command below:

mysql> CREATE TABLE students ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, student_name VARCHAR(30) NOT NULL );

Output

mysql> Query OK, 0 rows affected (0.06 sec)

You need to insert some values using the command below:

mysql> insert into students(id, student_name) values ('1001', 'John Doe');

Output

Query OK, 1 row affected (0.01 sec)

Then add another record,

mysql> insert into students(id, student_name) values ('1002', 'Mary Doe');

Output

Query OK, 1 row affected (0.03 sec)

To confirm if the changes were effected on server 2, run the commands below:

mysql> use replica_demo

Output

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

Then, run the command below to list records on the students table:

mysql> select * from students;
+------+--------------+
| id   | student_name |
+------+--------------+
| 1001 | John Doe     |
| 1002 | Mary Doe     |
+------+--------------+
2 rows in set (0.00 sec)

That's it. The students table we created on server 1 and the list of students were successfully replicated on server 2.

Conclusion

In this guide, we have showed you how to setup MySQL server on two separate Alibaba Cloud ECS instances. We have also taken you through the steps of setting up a Master-Slave configuration for your MySQL databases.

We also have proven the concept by adding a sample database and some records. If you followed the guide, you were able to add another layer of security to your database by creating a MySQL replication node that live-copies your production database.

MySQL Database Database Backup Tech_Share
1 1 0
Share on

Read previous post:

How to Manage Alibaba MySQL Database from CLI

Read next post:

Alibaba Group Tackling Poverty through Technology

Alibaba Clouder

2,599 posts | 762 followers

You may also like

Comments

Raja_KT February 8, 2019 at 7:45 am

Good one and it is quite handy. I am also of the opinion of using Apsara as many automations. It takes a lot of doing to build master-slave replication and RAID as documented in Apsara. Most of the stuffs now require HA, reliability etc. To install and take care of security, it is a pain. Backup again , can be costly, even if we bring in cheap tools. We need to manage the Licensing. Again the server and expensive DBA tasks can be daunting. Again , the autoscaling part is a question.

Alibaba Clouder

2,599 posts | 762 followers

Related Products

More Posts by Alibaba Clouder

See All