MARIADB Gtid replication and multi-source replication

What is Gtid?

Gtid is the global transaction ID (transaction identifier), originally implemented by Google, and official MySQL joined the feature in 5.6. Gtid is actually made up of Uuid+tid. Where the UUID is the unique identity of a MySQL instance. The TID represents the number of transactions that have been committed on the instance and is monotonically incremented as the transaction commits.

What is multi-source replication?

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

Experimental system: CentOS 6.6_x86_64

Lab Prerequisites: Firewall and SELinux are off

The experiment shows that there are 3 hosts in this experiment, such as the topology of IP assignment

Lab Software: mariadb-10.0.20

Experimental topology:

　　　　

First, the preparatory work

1. Modify the names of the three hosts, corresponding to the following:

　　　　

2. The Hosts file with the same host configuration as the following:

　　　　

3. Install MARIADB:

TarXF mariadb-10.0. --linux-x86_64.Tar. gz-c/usr/local/CD/usr/local/LN-SV mariadb-10.0. --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/CPsupport-files/my-large.cnf/etc/my.cnfCPsupport-files/mysql.server/etc/init.d/Mysqldchkconfig--add Mysqldchkconfig mysqld on

4. Modify the configuration file:

vim/etc/my.cnf--------------------------------->=/mydata/data

5. Create a virtual machine image (optional):

In order to test undisturbed, finish Gtid test I will restore three hosts to their original state.

Second, Gtid copy

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=1 Report-host=mysql1

2. Configure the 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=1 Report-host=mysql2

3. Configure the 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=1 Report-host=mysql3

4. Create the replication user on the master node:

Service mysqld Start
/usr/local/mysql/bin/mysql-------------------------------------------->REPLICATION SLAVE, REPLICATION CLIENT'Jason'@'192.168. 19.%'  ' 123456 ' ; FLUSH privileges;

5. Operate simultaneously on two slave nodes:

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 forMaster to send event MASTER_HOST:MYSQL1 Master_user:jason Master _port:3306Connect_retry: -Master_log_file:master-bin.000007Read_master_log_pos:321RELAY_LOG_FILE:MYSQL2-relay-bin.000002Relay_log_pos:613Relay_master_log_file:master-bin.000007Slave_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 it on the other two nodes:

CREATE DATABASE jjj;           // Master node creation database SHOW DATABASES;                // View from Node

You can see that both of the data from the server can replicate the master server correctly, and the test is successful. Under the experiment of multi-source replication, I restored the virtual snapshot to the original state of all the hosts.

　　　　　

Third, multi-source replication

1. I configured MYSQL1 and MYSQL2 as Master host, Mysql3 configured as 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.MYSQL1 and MYSQL2 Create replication users:

service mysqld start/usr/local/mysql/bin/mysql--------------------------------------------> REPLICATION slave,replication CLIENT ' slave ' @'192.168.19.66'123456'; FLUSH privileges;

2. Look at the binary log location for both hosts, where the two locations are exactly the same:

SHOW MASTER LOGS;

Configure Change MASTER on 3.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 have read all relay log; waiting forThe slave i/O thread to update it slave_io_state:waiting forMaster to send event MASTER_HOST:MYSQL1 Master_user:slave Master _port:3306Connect_retry: -Master_log_file:mysql-bin.000004Read_master_log_pos:647Relay_log_file:relay-log-m1.000002Relay_log_pos:535Relay_master_log_file:mysql-bin.000004Slave_io_running:yes Slave_sql_running:yes
...***************************2. Row ***************************connection_name:m2 Slave_sql_state:slave have read all relay log; waiting forThe slave i/O thread to update it slave_io_state:waiting forMaster to send event Master_host:mysql2 Master_user:slave Master _port:3306Connect_retry: -Master_log_file:mysql-bin.000004Read_master_log_pos:647Relay_log_file:relay-log-m2.000002Relay_log_pos:535Relay_master_log_file:mysql-bin.000004Slave_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;

At this point, multi-source replication has also been demonstrated, thank you! If you have any questions, please contact me, qq:82800452

MARIADB Gtid replication and multi-source replication