你的位置:首页 > 操作系统

[操作系统]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 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.修改配置文件:

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

  5.创建虚拟机镜像(可选):

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

二、GTID复制

  1.将mysql1配置为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.将mysql2配置为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.将mysql3配置为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.在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    = 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和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