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.
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.
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).
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>START GROUP_REPLICATION;
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 | 172.16.0.1 | 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.
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.
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 -pEnter 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 -pEnter 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.
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.
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.
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.
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 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.
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 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.
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.
2,605 posts | 747 followers
FollowAlibaba Clouder - October 22, 2018
Alibaba Clouder - May 28, 2019
Alibaba Clouder - December 26, 2018
Alibaba Clouder - July 6, 2018
Alibaba Clouder - February 13, 2019
Alibaba Clouder - February 13, 2019
2,605 posts | 747 followers
Follow
ApsaraDB RDS for MySQL
An on-demand database hosting service for MySQL with automated monitoring, backup and disaster recovery capabilities
Learn MoreMore Posts by Alibaba Clouder