mysql主从复制--mysql-5.6基于GTID及多线程复制

    A为master，B、C为slave，当A宕机时，B将成为New Master。C需将自己有的事务而B没有的事务复制给B，然后B才能成为Master。    B和C双方事务的协商过程，由于GTID可自行完成。提高了mysql宕机后，服务的恢复速度，在一定程度上提高了mysql的高可用能力。
Slave Multi-thread    slave多线程复制，在涉及多个数据库时，为每一个数据库启动一个线程；当只有一个数据库时，多线程复制无意义。slave-parallel-works=0禁用多线程复制


mysql 5.6提供了众多的复制管理工具，依赖python2.7及以上版本，rhel 6上python2.7，rhel 5 上的python为2.4https://launchpad.net/mysql-utilities遵循GPL协定，但不属于mysql官方

mysqlreplicate    通过追踪GTID，跳过已经复制过的事务，快速启动复制功能。
mysqlcheck    检查复制环境是否满足需求的工具
    简单验证部署，快速解决故障；    检查binlog是否开启，显示配置错误信息；    检查slave的连接状态和复制权限
mysqlrplshow    检查并显示mysql replication的拓扑图
mysqlfailover    故障转移工具，快速将slave提升为master
mysqlrpladmin    rpl管理工具

---

1 准备工作1.1 时间同步# ntpdate cn.pool.ntp.org

1.2 主机名解析# vim /etc/hosts
192.168.8.30 node1.test.com192.168.8.31 node2.test.com
1.3 创建mysql数据目录# mkdir /data/mysql -p# useradd -r mysql# chown -R mysql.mysql /data/mysql

2 初始化mysql# tar -xf mysql-5.6.31-linux-glibc2.5-x86_64.tar.gz -C /usr/local/# cd /usr/local/# ln -sv mysql-5.6.31-linux-glibc2.5-x86_64/ mysql# cd mysql# chown -R root.mysql ./*
# scripts/mysql_install_db --user=mysql --datadir=/data/mysql/

3 复制mysql服务脚本到init.d，并加入服务列表# cp support-files/mysql.server /etc/init.d/mysqld# chkconfig --add mysqld
# vim /etc/profile.d/mysql.shexport PATH=$PATH:/usr/local/mysql/bin# source /etc/profile.d/mysql.sh
4 初步配置，测试是否可以正常启动，此处的使用/usr/local/mysql/my.cnf# vim my.cnf
datadir=/data/mysqlinnodb-file-per-table=1server-id=1 #此处slave用11log-bin=master-binsocket=/tmp/mysql.sock
正常启动
说明：    ib_logfile0是innodb的事务日志，默认大小为48M

以上配置主从相同。
5 master5.1 增加配置# vim my.cnf
binlog-format=ROWlog-slave-updates=truegtid-mode=onenforce-gtid-consistency=truemaster-info-repository=TABLErelay-log-info-repository=TABLEsync-master-info=1slave-parallel-workers=2binlog-checksum=CRC32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events=1report-port=3306port=3306report-host=192.168.8.30
启动正常
5.2 查看gtid是否正常mysql> show global variables like ‘%gtid%‘;+---------------------------------+-------+| Variable_name                   | Value |+---------------------------------+-------+| enforce_gtid_consistency        | ON    || gtid_mode                       | ON    |+---------------------------------+-------+

5.2 查看master状态
mysql> show master status;+---------------+----------+--------------+------------------+-------------------+| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+---------------+----------+--------------+------------------+-------------------+| master.000002 |      151 |              |                  |                   |+---------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)
5.3 查看master的server_uuid
mysql> show global variables like ‘%uuid%‘;+---------------+--------------------------------------+| Variable_name | Value                                |+---------------+--------------------------------------+| server_uuid   | 5bf1de82-40fd-11e6-baa4-000c2989f319 |+---------------+--------------------------------------+
5.4 授权slave复制mysql> grant replication slave on *.* to ‘rpl‘@‘192.168.8.31‘ identified by ‘rpl‘;mysql> flush privileges;
6 slave6.1 增加配置# vim my.cnfbinlog-format=ROWlog-slave-updates=truegtid-mode=onenforce-gtid-consistency=truemaster-info-repository=TABLErelay-log-info-repository=TABLEsync-master-info=1slave-parallel-workers=2binlog-checksum=CRC32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events=1report-port=3306port=3306report-host=192.168.8.31
6.2 查看gtid是否正常
mysql> show global variables like ‘%gtid%‘;+---------------------------------+-------+| Variable_name                   | Value |+---------------------------------+-------+| enforce_gtid_consistency        | ON    || gtid_mode                       | ON    |+---------------------------------+-------+
6.3 查看slave的server_uuid
mysql> show global variables like "%uuid%";+---------------+--------------------------------------+| Variable_name | Value                                |+---------------+--------------------------------------+| server_uuid   | 81d29785-40fd-11e6-baa5-000c29635658 |+---------------+--------------------------------------+
mysql> change master to master_host=‘192.168.8.30‘,master_user=‘rpl‘,master_password=‘rpl‘,master_auto_position=1;

说明：当指定master_log_file，master_log_pos时，需使用master_auto_position=0mysql> change master to master_host=‘192.168.8.30‘,master_user=‘rpl‘,master_password=‘rpl‘,master_log_file=‘master-bin.000006‘,master_log_pos=191,master_auto_position=0;
6.4 启动slave复制mysql> start slave;mysql> show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.8.30                  Master_User: rpl                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: master-bin.000007          Read_Master_Log_Pos: 191               Relay_Log_File: node2-relay-bin.000004                Relay_Log_Pos: 403        Relay_Master_Log_File: master-bin.000007             Slave_IO_Running: Yes            Slave_SQL_Running: Yes

7 master查看slave信息mysql> show slave hosts;+-----------+--------------+------+-----------+--------------------------------------+| Server_id | Host         | Port | Master_id | Slave_UUID                           |+-----------+--------------+------+-----------+--------------------------------------+|        11 | 192.168.8.31 | 3306 |         1 | 81d29785-40fd-11e6-baa5-000c29635658 |+-----------+--------------+------+-----------+--------------------------------------+


















来自为知笔记(Wiz)

mysql主从复制--mysql-5.6基于GTID及多线程复制

标签：服务器   server   多线程   mysql   style