Community Blog How to Replicate MySQL Database on Ubuntu 16.04

How to Replicate MySQL Database on Ubuntu 16.04

This article aims to describe how to use mysql database replication to allow you have a highly-available and scalable database.

MySQL Group Replication is a technology that allows you to create a highly-available database cluster with redundant data spread across multiple servers.

This fault-tolerant database architecture is provided as a plugin in MySQL Server starting from version 5.7.17. In MySQL Group Replication, the database state is replicated to multiple servers. So, if one server fails, the database service will still be available if the remaining servers can agree on a quorum.

Installing MySQL Community Edition

We will need to SSH to each Ubuntu 16.04 server to install MySQL server. The default MySQL server available on the Ubuntu software repository doesn't have the Group Replication plugin. So we will have to manually download the deb packages for MySQL Community Edition.

Generating Universally Unique Identifier (UUID) for the Group

Our MySQL Group replication service requires a unique group name. We are going to use the built in Linux uuidgen command to generate Universally Unique IDentifier (UUID).

Configuring MySQL Server 1

MySQL server should be up and running on the 3 Alibaba Cloud ECS instances. Next, we need to configure some settings on each server. We can do this by modifying the /etc/mysql/my.cnf file. Then replace the IP addresses with the correct values.

Setting Up Group Replication User for group_replication_recovery Channel

MySQL Group Replication works on a group_replication_recovery channel to transfer transactions between members. Hence, we must set up a replication user with REPLICATION-SLAVE permission on each server.

After you have successfully set up the replication user, you can install the group_replication plugin on the server using the command below:

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

Once the plugin is installed successfully, we are going to bootstrap the group using the commands below:

mysql>SET GLOBAL group_replication_bootstrap_group=ON;
mysql>SET GLOBAL group_replication_bootstrap_group=OFF;

To avoid starting up different groups, we have set the value of group_replication_bootstrap_group back to OFF after successfully starting the group.

You can check the status of the new group we have just created by running the command below:

mysql> SELECT * FROM performance_schema.replication_group_members;
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
| group_replication_applier | e3778cfd-b66e-11e8-aba5-42010a8e0002 |   |        3306 | ONLINE       |
1 row in set (0.00 sec)

This shows that indeed server 1 is a member of a group. We can now create a database, a table and insert some test data to make sure the database server is working as expected.

mysql> create database sample_db;

Then, switch to the database:

mysql> use sample_db;

Next, create a customers table. Please note, we are using the innoDB database engine:

mysql> create table customers (customer_id INT PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL) Engine = InnoDB;

Then, we can load sample data into the table by running the SQL commands below one by one:

mysql> insert into customers (customer_id, customer_name) values ('100', 'John James');
mysql> insert into customers (customer_id, customer_name) values ('200', 'Baby Doe');
mysql> insert into customers (customer_id, customer_name) values ('300', 'Jane Smith');

Then you can add server 2 and server 3 to the MySQL Group according to the step by step tutorial.

Related Blog Posts

How to Configure MySQL Master-Slave Replication on Ubuntu 16.04

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.

  1. Install MySQL Server On Server 1 (Master)
  2. Edit MySQL Configuration File on Server 1
  3. Create a New User for Replication Services on Server 1
  4. Install MySQL On Server 2 (Slave)
  5. Modify the Configuration File on Server 2
  6. Change Master-Slave Configuration on Server 2

Then you can 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.

Load Balancing MySQL Database with Read Replicas on Alibaba Cloud

Alibaba Cloud MySQL RDS Read Replicas provide enhanced performance and durability for database (DB) instances. This feature makes it easy to elastically scale out beyond the capacity constraints of a single DB Instance for read-heavy database workloads.

With the creation of multiple replicas for distributing the read traffic for a MySQL database, managing the traffic for these instances could be a complicated task to perform.

This article will introduce a powerful feature provided by Alibaba Cloud ApsaraDB for RDS to automatically split the Read and Write requests between the instances.

Related Documenation

Standard dual-replica edition

A standard dual-replica instance runs in a master-replica structure. The master node provides services for your business, and the replica node works to ensure high availability (HA). When the master node fails, the system switches services to the replica node within 30 seconds to ensure stability of services.

Dual-replica cluster edition

ApsaraDB for Redis provides dual-replica cluster instances to eliminate the bottleneck of Redis single-thread model and easily process large-capacity or high-performance services. A cluster instance of ApsaraDB for Redis supports built-in data sharding and reading algorithms. These transparent algorithms relieve you from efforts for research and development (R&D) and operations and maintenance (O&M) when you use the cluster instance of ApsaraDB for Redis.

Related Market Products

MySQL infrastructure powered by Websoft9(CentOS7.2)

Websoft9 MySQL is a pre-configured, ready to run image for running MySQL on Alibaba Cloud.MySQL is the world's most popular open source database. This is based on MySQL Community Edition 5.7.17 for Web development, Application deployment, and complex custom database stored procedures.

Mysql powered by Zhuyun

MySQL is a relational database management system, a relational database to store data in different tables, rather than all the data in a large warehouse, which increases the speed and flexibility. This is based on Mysql 5.7.

Related Products

ApsaraDB RDS for MySQL

MySQL is one of the most popular open-source databases in the world. As a key component of the open-source software bundle LAMP (Linux, Apache, MySQL, and Perl/PHP/Python), MySQL has been widely applied to different scenarios. ApsaraDB RDS for MySQL is an on-demand database hosting service for MySQL with automated monitoring, backup and disaster recovery capabilities.

Alibaba Cloud Databases

Alibaba Cloud offers fully managed database services. We monitor, backup, and recover your database automatically so that you can fully focus on your business development. To provide more stable and scalable database services, Alibaba Cloud optimized the source code based on the open-source database engines. Our database services, such as ApsaraDB RDS for MySQL and ApsaraDB RDS for PPAS have lower risk compare to the other services using the community edition.

Related Course

Mysql Database Concepts and Operations

After learning this MySQL series courses, the trainees will master the MySQL relevant basic concept, deployment procedure, HA solution, commonly used backup and recovery method and daily inspection skills, thus having a comprehensive understanding of MySQL database.

0 0 0
Share on

Alibaba Clouder

2,626 posts | 711 followers

You may also like