MariaDB's GTID replication and multi-source replication, mariadbgtid Replication

MariaDB's GTID replication and multi-source replication, mariadbgtid Replication

What is GTID?

GTID is the global transaction ID (global transaction identifier). It was initially implemented by google and officially added to MySQL in MySQL 5.6. GTID is actually composed of UUID + TID. UUID is the unique identifier of a MySQL instance. TID indicates the number of transactions committed on the instance, and increases monotonically as the transaction is committed.

What is multi-source replication?

Multi-source replication means that one server can replicate from multiple slave servers. This is a new feature of MariaDB 10.0.

Experimental System: CentOS 6.6 _ x86_64

Prerequisites: Firewall and selinux are both disabled.

Tutorial Description: There are three hosts in this experiment, and IP addresses are allocated as topology.

Experimental software: mariadb-10.0.20

Tutorial topology:

　　　　

I. Preparations

1. Modify the names of the three hosts as follows:

　　　　

2. Configure the same hosts file for the three hosts as follows:

　　　　

3. Install mariadb:

tar xf mariadb-10.0.20-linux-x86_64.tar.gz  -C /usr/local/cd /usr/local/ln -sv mariadb-10.0.20-linux-x86_64 mysqlmkdir -pv /mydata/datauseradd -r mysqlchown -R mysql.mysql /mydata/data/cd mysql/chown -R root.mysql .scripts/mysql_install_db --user=mysql --datadir=/mydata/data/cp support-files/my-large.cnf /etc/my.cnfcp support-files/mysql.server /etc/init.d/mysqldchkconfig --add mysqldchkconfig mysqld on

4. modify the configuration file:

vim /etc/my.cnf--------------------------------->[mysqld]datadir = /mydata/data

5. Create a virtual machine image (optional ):

To avoid interference in the test, I will restore the three hosts to their initial state after completing the GTID test.

Ii. GTID Replication

1. Configure mysql1 as master:

[mysqld]server-id       = 1binlog-format=ROWlog-bin=/mydata/data/master-binlog-slave-updates=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1slave-parallel-threads=2binlog-checksum=CRC32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events=1report-host=mysql1

2. Configure mysql2 as slave:

[mysqld]server-id       = 2binlog-format=ROWlog-bin=/mydata/data/mysql-binlog-slave-updates=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1slave-parallel-threads=2binlog-checksum=CRC32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events=1report-host=mysql2

3. Configure mysql3 as slave:

[mysqld]server-id       = 3binlog-format=ROWlog-bin=/mydata/data/mysql-binlog-slave-updates=truemaster-info-repository=TABLErelay-log-info-repository=TABLEsync-master-info=1slave-parallel-threads=2binlog-checksum=CRC32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events=1report-host=mysql3

4. Create a copy user on the master node:

service mysqld start
/usr/local/mysql/bin/mysql-------------------------------------------->GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'jason'@'192.168.19.%' IDENTIFIED BY '123456';FLUSH PRIVILEGES;

5. operate on two slave nodes simultaneously:

service mysqld start/usr/local/mysql/bin/mysql------------------------------------------->CHANGE MASTER TO MASTER_HOST='mysql1',MASTER_USER='jason',MASTER_PASSWORD='123456',MASTER_USE_GTID=slave_pos;START SLAVE;

6. view the status on the slave node:

SHOW SLAVE STATUS\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: mysql1                  Master_User: jason                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: master-bin.000007          Read_Master_Log_Pos: 321               Relay_Log_File: mysql2-relay-bin.000002                Relay_Log_Pos: 613        Relay_Master_Log_File: master-bin.000007             Slave_IO_Running: Yes            Slave_SQL_Running: Yes
                          ...                   Using_Gtid: Slave_Pos

SHOW GLOBAL VARIABLES LIKE '%gtid%';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| gtid_binlog_pos        | 0-1-3 || gtid_binlog_state      | 0-1-3 || gtid_current_pos       | 0-1-3 || gtid_domain_id         | 0     || gtid_ignore_duplicates | OFF   || gtid_slave_pos         | 0-1-3 || gtid_strict_mode       | OFF   |+------------------------+-------+

7. Test: Create a database on the master node and view the database on the other two nodes:

Create database jjj; // CREATE the database show databases on the master node; // view the DATABASE from the slave Node

We can see that both slave servers can copy the data of the master server. The test was successful. In the following multi-source replication experiment, I will restore all hosts to the original state from a virtual snapshot.

　　　　　

Iii. Multi-source Replication

1. Configure mysql1 and mysql2 as the master host, configure mysql3 as the slave host, and edit the configuration file:

Mysql1:

[mysqld]server-id       = 1log-bin=/mydata/data/mysql-bin

Mysql2:

[mysqld]server-id       = 2log-bin=/mydata/data/mysql-bin

Mysql3:

[mysqld]
#log-bin=mysql-bin#binlog_format=mixedserver-id       = 3relay_log=/mydata/data/relay-log

2. Create and copy users for mysql1 and mysql2:

service mysqld start/usr/local/mysql/bin/mysql-------------------------------------------->GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'slave'@'192.168.19.66' IDENTIFIED BY '123456';FLUSH PRIVILEGES;

2. view the binary log location of the two hosts. The two locations are exactly the same:

SHOW MASTER LOGS;

3. Configure change master on mysql3:

service mysqld start/usr/local/mysql/bin/mysql----------------------------------------------->CHANGE MASTER 'm1' TO MASTER_HOST='mysql1',MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=647;CHANGE MASTER 'm2' TO MASTER_HOST='mysql2',MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=647;START ALL SLAVES;

SHOW ALL SLAVES STATUS\G*************************** 1. row ***************************              Connection_name: m1              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it               Slave_IO_State: Waiting for master to send event                  Master_Host: mysql1                  Master_User: slave                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000004          Read_Master_Log_Pos: 647               Relay_Log_File: relay-log-m1.000002                Relay_Log_Pos: 535        Relay_Master_Log_File: mysql-bin.000004             Slave_IO_Running: Yes            Slave_SQL_Running: Yes
　　　　　　　　　　　　　　　...*************************** 2. row ***************************              Connection_name: m2              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it               Slave_IO_State: Waiting for master to send event                  Master_Host: mysql2                  Master_User: slave                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000004          Read_Master_Log_Pos: 647               Relay_Log_File: relay-log-m2.000002                Relay_Log_Pos: 535        Relay_Master_Log_File: mysql-bin.000004             Slave_IO_Running: Yes            Slave_SQL_Running: Yes
　　　　　　　　　　　　　　　...

4. test:

Mysql1:

CREATE DATABASE mydb1;
CREATE TABLE mydb1.jjj (id int);

Mysql2:

CREATE DATABASE mydb2;
CREATE TABLE mydb2.jjj (id int);

Mysql3:

SHOW DATABASES;
SHOW TABLES FROM mydb1;
SHOW TABLES FROM mydb2;

Now, multi-source replication has been demonstrated. Thank you! If you have any questions, contact me at QQ: 82800452.