MySQL Clustering

After installing MariaDB or MySQL, follow these instructions on 2 or more servers to set up clustering:

Generate the Group Replication Name

SSH to Server 1 and log in to MySQL server as root.

sudo mysql -u root -p

Enter your MySQL server root password and press ENTER to continue.

Generate the UUID.

SELECT UUID();

Make a note of your new UUID. It will be formatted similarly to the example shown below. Each server uses this UUID as the Replication Group Name.

+--------------------------------------+
| UUID()                               |
+--------------------------------------+
| 00000000-1111-2222-3333-444444444444 |
+--------------------------------------+

Exit MySQL.

QUIT;

Configure Server 1

Edit /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf depending on which one has a configuration already.

sudo nano /etc/mysql/my.cnf

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

Modify or add the information below:.

Replace 192.0.2.1 with the address of Server 1.
Replace 192.0.2.2 and 192.0.2.3 with the addresses of Server 2 and 3 respectively (or add/remove servers as necessary).

Replace the value of the loose-group_replication_group_name with the UUID retrieved from Server 1 previously.

[mysqld]

server_id=1 
bind-address=0.0.0.0
gtid_mode=ON 
enforce_gtid_consistency=ON
binlog_checksum=NONE

plugin_load_add='group_replication.so'
group_replication_single_primary_mode=OFF
loose-group_replication_group_name="00000000-1111-2222-3333-444444444444"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "192.0.2.1:33061"
loose-group_replication_group_seeds="192.0.2.1:33061, 192.0.2.2:33061, 192.0.2.3:33061"
loose-group_replication_bootstrap_group=OFF
report_host=192.0.2.1
super_read_only=OFF

Note: The loose- prefix for the group_replication variables instructs the server to start even if the Group Replication plugin is not loaded when the server starts.

Save and close the file.

The MySQL configuration settings explained:

server_id=1
- This is server 1 in the replication group.
bind-address=0.0.0.0
- Listen on all IP addresses.
gtid_mode=ON
- Run replication with global transaction identifiers.
enforce_gtid_consistency=ON
- MySQL will only execute statements that can be logged safely with GTID.
binlog_checksum=NONE
- Disable writing checksums to the binary log.
plugin_load_add='group_replication.so'
- Load the group replication plugin.
group_replication_single_primary_mode=OFF
- Multi-master replication model, all members have read-write access.
loose-group_replication_group_name="00000000-1111-2222-3333-444444444444"
- The unique name of the replication group.
loose-group_replication_start_on_boot=OFF
- Start replication at boot time. You will not need to manually start replication. Read more about the implications and precautions of auto-starting replication.
loose-group_replication_local_address= "192.0.2.1:33061"
- The address and port used by this server for replication.
loose-group_replication_group_seeds="192.0.2.1:33061, 192.0.2.2:33061, 192.0.2.3:33061"
- The host and port combinations of the other group members.
loose-group_replication_bootstrap_group=OFF
- Set OFF to avoid bootstrapping the group from multiple servers and creating conflicts.
report_host=192.0.2.1
- The IP address this server uses to report to the other members.
super_read_only=OFF
- Starts the server in read-only mode (when ON) until replication is started successfully at which point it turns back off and allows writing as well.

Restart the MySQL server to apply the changes.

sudo service mysql restart

Create a Replication User

sudo mysql -u root -p

Create the replication user. Replace EXAMPLE_PASSWORD with a strong password.

SET SQL_LOG_BIN=0;
CREATE USER 'replication_user'@'%' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_PASSWORD';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='replication_user', MASTER_PASSWORD='EXAMPLE_PASSWORD' FOR CHANNEL 'group_replication_recovery';

Bootstrap the Group Replication Plugin on the First Server

Only bootstrap one member of the group to avoid creating multiple groups with the same name. To do this, run the commands below on the first server.

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;

Turn group_replication_bootstrap_group off to avoid creating multiple groups when you restart the MySQL server.

SET GLOBAL group_replication_bootstrap_group=OFF;

Verify the status of the group by querying the replication_group_members.

SELECT MEMBER_ID, MEMBER_HOST, MEMBER_STATE FROM performance_schema.replication_group_members;

Verify your output is similar to this. Your MEMBER_ID and MEMBER_HOST will be different.

+--------------------------------------+-------------+--------------+
| MEMBER_ID                            | MEMBER_HOST | MEMBER_STATE |
+--------------------------------------+-------------+--------------+
| 11111111-40c4-11e9-92b4-7a4c400acda6 | 192.0.2.1   | ONLINE       |
+--------------------------------------+-------------+--------------+

Create a test database, test_db.

CREATE DATABASE test_db;

Switch to the database.

USE test_db;

Create a test table, test_tbl.

CREATE TABLE test_tbl (employee_id INT PRIMARY KEY, employee_name VARCHAR(30) NOT NULL) Engine = InnoDB;

Confirm the table exists.

SHOW TABLES;

+-------------------+
| Tables_in_test_db |
+-------------------+
| test_tbl          |
+-------------------+

Configure Start on Boot

Edit /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf depending on which one has a configuration already.

sudo nano /etc/mysql/my.cnf

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

Modify the start on boot and super read only settings to match below:

loose-group_replication_start_on_boot=ON
super_read_only=ON

Save and close the file.

Restart the MySQL server to apply the changes.

sudo service mysql restart

Configure Additional Servers

Edit /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf depending on which one has a configuration already.

sudo nano /etc/mysql/my.cnf

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

Modify or add the information below:.

Replace 192.0.2.2 with the address of Server 2.
Replace 192.0.2.1 and 192.0.2.3 with the addresses of Server 1 and 3 respectively (or add/remove servers as necessary).

Replace the value of the loose-group_replication_group_name with the UUID retrieved from Server 1 previously.

[mysqld]

server_id=2
bind-address=0.0.0.0
gtid_mode=ON 
enforce_gtid_consistency=ON
binlog_checksum=NONE

plugin_load_add='group_replication.so'
group_replication_single_primary_mode=OFF
loose-group_replication_group_name="00000000-1111-2222-3333-444444444444"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "192.0.2.2:33061"
loose-group_replication_group_seeds="192.0.2.1:33061, 192.0.2.2:33061, 192.0.2.3:33061"
loose-group_replication_bootstrap_group=OFF
report_host=192.0.2.2
super_read_only=OFF

Note: The loose- prefix for the group_replication variables instructs the server to start even if the Group Replication plugin is not loaded when the server starts.

Save and close the file.

Restart the MySQL server to apply the changes.

sudo service mysql restart

Create a Replication User

sudo mysql -u root -p

Create the replication user. Replace EXAMPLE_PASSWORD with a strong password.

SET SQL_LOG_BIN=0;
CREATE USER 'replication_user'@'%' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_PASSWORD';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='replication_user', MASTER_PASSWORD='EXAMPLE_PASSWORD' FOR CHANNEL 'group_replication_recovery';

Start the Group Replication plugin.

START GROUP_REPLICATION;

Verify Server 2 is now a member of the group.

SELECT MEMBER_ID, MEMBER_HOST, MEMBER_STATE FROM performance_schema.replication_group_members;

Verify your output is similar to this. Your MEMBER_IDs and MEMBER_HOSTs will be different.

+--------------------------------------+-------------+--------------+
| MEMBER_ID                            | MEMBER_HOST | MEMBER_STATE |
+--------------------------------------+-------------+--------------+
| 11111111-40c4-11e9-92b4-7a4c400acda6 | 192.0.2.1  | ONLINE        |
| 22222222-40c4-11e9-92b4-7a4c400acda6 | 192.0.2.2  | ONLINE        |
+--------------------------------------+-------------+--------------+

Verify Server 2 has replicated the test database (system databases will be shown also, but we want to make sure our new database is listed).

SHOW databases;

+--------------------+
| Database           |
+--------------------+
| test_db            |
+--------------------+

Confirm the test table exists on Server 2.

SHOW TABLES;

+-------------------+
| Tables_in_test_db |
+-------------------+
| test_tbl          |
+-------------------+

Configure Start on Boot

Edit /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf depending on which one has a configuration already.

sudo nano /etc/mysql/my.cnf

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

Modify the start on boot and super read only settings to match below:

loose-group_replication_start_on_boot=ON
super_read_only=ON

Save and close the file.

Restart the MySQL server to apply the changes.

sudo service mysql restart

MySQL Clustering

Generate the Group Replication Name

Configure Server 1

Create a Replication User

Bootstrap the Group Replication Plugin on the First Server

Configure Start on Boot

Configure Additional Servers

Create a Replication User

Configure Start on Boot

No Comments