MariaDB的GTID复制和多源复制

什么是GTID？

GTID就是全局事务ID(global transaction identifier )，最初由google实现，官方MySQL在5.6才加入该功能。GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。

什么是多源复制？

多源复制意味着一个服务器能从多个从服务器上复制。这是MariaDB 10.0的一个新特性。

实验系统：CentOS 6.6_x86_64

实验前提：防火墙和selinux都关闭

实验说明：本实验共有3台主机，IP分配如拓扑

实验软件：mariadb-10.0.20

实验拓扑：

一、准备工作

1.修改三台主机的名字，对应如下：

2.三台主机配置相同的hosts文件为如下内容：

3.安装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 mysql mkdir -pv /mydata/data useradd -r mysql chown -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.cnf cp support-files/mysql.server /etc/init.d/mysqld chkconfig --add mysqld chkconfig mysqld on

4.修改配置文件：

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

5.创建虚拟机镜像（可选）：

为了试验不受干扰，做完GTID试验我会将三台主机恢复至初始状态。

二、GTID复制

1.将mysql1配置为master：

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

2.将mysql2配置为slave：

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

3.将mysql3配置为slave：

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

4.在master节点创建复制用户：

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.在两台slave节点同时操作：

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.在slave节点查看状态：

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.测试，在master节点创建数据库，并在其他两个节点进行查看：

CREATE DATABASE jjj;           //主节点创建数据库 SHOW DATABASES;                //从节点查看

可以看到两台从服务器都可以正常复制主服务器的数据了，试验成功。下面进行多源复制的实验，我将所有主机恢复虚拟快照至原始状态。

三、多源复制

1.我将mysql1和mysql2配置为master主机，mysql3配置为slave主机，编辑配置文件：

mysql1：

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

mysql2：

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

mysql3：

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

2.mysql1和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.查看两台主机的二进制日志位置，这里两台位置恰好一致：

SHOW MASTER LOGS;

3.mysql3上配置CHANGE MASTER：

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.测试：

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;

至此，多源复制也演示完毕，谢谢！如有问题，请联系我，QQ：82800452