標籤：伺服器 server 多線程 mysql style
GTID，Global Transaction Identifiers，全域事務標識符 由伺服器的UUID和事務ID號組成一個唯一的標識。mysql 5.6後，事務首部會記錄server UUID，追蹤十分簡單。
UUID，Universally Unique Identifier，通用唯一識別碼。
A為master，B、C為slave，當A宕機時，B將成為New Master。C需將自己有的事務而B沒有的事務複製給B，然後B才能成為Master。 B和C雙方事務的協商過程，由於GTID可自行完成。提高了mysql宕機後，服務的恢複速度，在一定程度上提高了mysql的高可用能力。650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/83/A2/wKioL1d5GpygsPMIAABJeFsZe4w786.png" border="0" name="" "261" height="256">
Slave Multi-thread slave多線程複製，在涉及多個資料庫時，為每一個資料庫啟動一個線程；當只有一個資料庫時，多線程複製無意義。slave-parallel-works=0禁用多線程複製
650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/83/A3/wKiom1d5Gpzg4ysfAABkHknb_G8353.jpg" border="0" name="" "278" height="276">
mysql 5.6提供了眾多的複製管理工具，依賴python2.7及以上版本，rhel 6上python2.7，rhel 5 上的python為2.4https://launchpad.net/mysql-utilities遵循GPL協定，但不屬於mysql官方650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/83/A2/wKioL1d5GpzyYL8WAAA8sl555FA282.jpg" border="0" name="" "461" height="72">
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及多線程複製
