Configure GTID for MySQL and MariaDB master-slave Clusters

Configure GTID for MySQL and MariaDB master-slave Clusters

I wrote MySQL and MariaDB traditional master-slave cluster configuration, which is very mature. Since Mysql5.6 and MariaDB10.0, a new master-slave GTID solution has been available. However, the two systems have branches in this version. The specific implementation and configuration methods are different.

MariaDB:

The version I used is version 10.1. Currently, this version is not stable, but does not affect the test. Deploy two database instances first. For more information, see

Until the copied user is created.

Because the database is newly created, the status of the master database is reset first. This operation is not required. If synchronization SQL Execution fails, you can skip this line of failed SQL statements.

MariaDB [mysql]> reset master;

At this time, we can find that the old bin-log has been deleted, and only one newly created log file is left.

Then execute the following statement. Note that the master_use_gtid = current_pos line is different from the traditional mode.

MariaDB [mysql]> change master

Master_host = 'localhost ',

Master_port = 10001,

Master_user = 'rep ',

Master_password = '000000 ',

Master_use_gtid = current_pos;

Query OK, 0 rows affected (0.36 sec)

 

MariaDB [mysql]> start slave;

Query OK, 0 rows affected (0.18 sec)

 

MariaDB [mysql]> show slave status \ G;

* *************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: localhost

Master_User: rep

Master_Port: 10001

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 313

Relay_Log_File: lyw-hp-relay-bin.000002

Relay_Log_Pos: 601

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

......

Using_Gtid: Current_Pos

Using_Gtid: Current_Pos is added later than the traditional master-slave mode, which indicates that the new master-slave mode is used.

After the settings, you also need to test whether the modification to the master database affects the content of the slave database.

Mysql:

Mysql5.6 supports GTID replication. The configuration method is different from that of MariaDB. You need to modify the configuration in the configuration file first. The new content is as follows:

[Mysqld]

Gtid-mode = on

Log-slave-updates = true

Enforce-gtid-consistency = true

Then start the two databases. You also need to configure the corresponding synchronization user.

Then execute the following command from the slave database. Note that the last line of master_auto_position = 1 is different from that of MariaDB.

Mysql> change master

Master_host = 'localhost ',

Master_port = 20001,

Master_user = 'rep ',

Master_password = '000000 ',

Master_auto_position = 1;

Mysql> start slave;

Mysql> show slave status \ G;

* *************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: localhost

Master_User: rep

Master_Port: 20001

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 850

Relay_Log_File: lyw-hp-relay-bin.000002

Relay_Log_Pos: 1060

Relay_Master_Log_File: mysql-bin.000004

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

......

Retrieved_Gtid_Set: a34819a9-700f-11e5-a841-34238703623c: 1-3

Executed_Gtid_Set: a34819a9-700f-11e5-a841-34238703623c: 1-3

It can be seen that two rows are added with GTID-related statuses, Retrieved_Gtid_Set and Executed_Gtid_Set, which indicate that GTID is adopted.

In the same way, a primary database can be followed by multiple slave databases to increase data reliability and read throughput.

The above master-slave configuration is the most basic configuration, and it is not enough to be used online. After the master fails, it will not automatically switch. Please note.

Cobar deployment and use of MySQL sharded high-availability clusters

Deployment and use of Fabric for MySQL sharded high-availability clusters

-------------------------------------- Split line --------------------------------------

Linux Tutorial: How to check the MariaDB server version

Implementation of MariaDB Proxy read/write splitting

How to compile and install the MariaDB database in Linux

Install MariaDB database using yum in CentOS

Install MariaDB and MySQL

How to migrate MySQL 5.5 database to MariaDB 10 on Ubuntu

Install MariaDB on the Ubuntu 14.04 (Trusty) Server