mysql5.6基于GTID的主从复制

浏览数：39 / 时间：2015年06月12日

mysql5.6gtid模式的主从复制:

1.配置master的my.cnf文件:
   ##################master
   [mysqld]
   basedir = /usr/local/mysql
   datadir = /home/data/mysqldb
   socket = /home/data/mysqldb/mysql.sock
   port = 3306
   server_id = 1
   user=mysql

   binlog-format=ROW
   log-bin=master-bin.log-bin
   log-slave-updates=true
   gtid-mode=on
   enforce-gtid-consistency=true
   master-info_repository=TABLE
   relay-log-info-repository=TABLE
   sync-master-info=1
   slave-parallel-worker=2
   slave_allow_batching = 1
   master-verify-checksum=1
   slave-sql-verify-checksum=1
   binlog-rows-query-log-events=1
   server-id=1
   port=3306
   report-port=3306
   report-host=192.168.100.3
   lower_case_table_names=1
   #安装完半同步插件后即可在配置文件中打开下面的选项
   #rpl_semi_sync_slave_enabled=1
   #rpl_semi_sync_master_enabled=1
   #rpl_semi_sync_master_timeout=2000

2.配置master的my.cnf文件:
   ##################slave
   [mysqld]
   basedir = /usr/local/mysql
   datadir = /home/data/mysqldb
   socket = /home/data/mysqldb/mysql.sock
   port = 3306
   server_id = 1
   user=mysql
   binlog-format=ROW
   log-bin=master-bin.log-bin
   enforce-gtid-consistency=true
   master-info_repository=TABLE
   relay-log-info-repository=TABLE
   sync-master-info=1
   slave-parallel-worker=2
   slave_allow_batching = 1
   master-verify-checksum=1
   slave-sql-verify-checksum=1
   binlog-rows-query-log-events=1
   server-id=2
   report-port=3306
   port=3306
   report-host=192.168.100.5
   #安装完半同步插件后即可在配置文件中打开下面的选项
   #rpl_semi_sync_slave_enabled=1
   #rpl_semi_sync_master_enabled=1
   #rpl_semi_sync_master_timeout=1000
3.备份主数据库(须安装perconna-xtrabackup软件):
   innobackupex --user=backupuser --password=backupuser --no-timestamp /tmp/bak/fullbk
   将/tmp/bak/fullbk下的备份文件传至从库的/tmp/bak/fullbk目录下,并执行如下语句:
   innobackupex --apply-log /tmp/bak/fullbk
   将/tmp/bak/fullbk目录下的文件拷贝至datadir的目录中即可;

4.安装同步插件并授权:
   在mysql主从数据库中安装半同步插件:
   install plugin rpl_semi_sync_master soname ‘semisync_master.so‘;
   install plugin rpl_semi_sync_slave soname ‘semisync_slave.so‘;

   在mysql的主从数据库中授权,并开启半同步:
   GRANT REPLICATION SLAVE ON *.* TO ‘repl‘@‘192.168.100.3‘ IDENTIFIED BY ‘repl‘;
   GRANT REPLICATION SLAVE ON *.* TO ‘repl‘@‘192.168.100.5‘ IDENTIFIED BY ‘repl‘;
   set global rpl_semi_sync_slave_enabled=1;
   set global rpl_semi_sync_master_enabled=1;
   set global rpl_semi_sync_master_timeout=1000;

5.启动同步:
   change master to master host=‘‘,master_user=‘‘,master_password=‘‘,master_auto_position=1;
   start slave;
6.检查:
   在mysql的主数据库中查看从客户端:
   show slave hosts;

   在mysql从服务器中检查同步状态:
   mysql>show slave status\G
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 34810
        Seconds_Behind_Master: 4
             Master_Server_Id: 1
                Auto_Position: 1
7.至此,完成基于GTID的主从配置