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.