mysql5.6.xGTID主從複製配置
本文環境:
主庫：CentOS6.7 x64 192.168.0.65 mysql-5.6.29
備庫：CentOS6.7 x64 192.168.0.66 mysql-5.6.29
一、配置Mysql5.6 GTID主從（本節配置適合主從都是空庫的情況）
1. mysql主伺服器配置
說明: 基於GTID的主從複製需要在配置檔案中新增如下內容。
# vi /etc/my.cnf
[mysqld]
binlog-format = ROW
log-bin = master-bin
log-bin-index = master-bin.index
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-workers = 2
binlog-checksum = CRC32
master-verify-checksum = 1
slave-sql-verify-checksum = 1
binlog-rows-query-log_events = 1
report-host = 192.168.1.120
server-id = 1
重啟資料庫:
# service mysqld restart
檢視gtid資訊:
mysql> show global variables like `%GTID%`;
+———————————+—————————————-+
| Variable_name | Value |
+———————————+—————————————-+
| binlog_gtid_simple_recovery | OFF |
| enforce_gtid_consistency | ON |
| gtid_executed | |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| simplified_binlog_gtid_recovery | OFF |
+———————————+—————————————-+
2. 主伺服器配置同步複製帳號
grant replication slave on *.* to `repl`@`%` identified by `123456`;
flush privileges;
3. mysql從伺服器配置
說明: 預設只要server-id不相同即可。
# vi /etc/my.cnf
[mysqld]
binlog-format = ROW
log-bin = mysql-bin
relay-log = slave-relay-bin
relay-log-index = slave-relay-bin.index
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-workers = 2
binlog-checksum = CRC32
master-verify-checksum = 1
slave-sql-verify-checksum = 1
binlog-rows-query-log_events = 1
report-host = 192.168.1.121
server-id = 11
重啟資料庫:
# service mysqld restart
檢視gtid狀態:
mysql> show global variables like `%GTID%`;
+———————————+—————————————-+
| Variable_name | Value |
+———————————+—————————————-+
| binlog_gtid_simple_recovery | OFF |
| enforce_gtid_consistency | ON |
| gtid_executed | |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| simplified_binlog_gtid_recovery | OFF |
+———————————+—————————————-+
7 rows in set (0.00 sec)
4. 連線主Mysql,配置主從
(1) 連線主資料庫
mysql>
CHANGE MASTER TO
MASTER_HOST=`192.168.0.65`,
MASTER_PORT=3306,
MASTER_USER=`repl`,
MASTER_PASSWORD=`123456`,
MASTER_AUTO_POSITION=1;
(2) 啟動從同步程式
mysql> start slave;
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.65
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000007
Read_Master_Log_Pos: 290
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 502
Relay_Master_Log_File: master-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: mysql.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 290
Relay_Log_Space: 706
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: c8bb22a1-024e-11e6-a1e8-000c29225fa0
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 1
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
#檢視如下兩個引數為YES，說明從庫執行正常。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
5. 驗證同步情況
(1) 主資料庫建立一個資料庫
mysql> create database abc;
Query OK, 1 row affected (0.02 sec)
mysql> show master statusG;
*************************** 1. row ***************************
File: master-bin.000007
Position: 290
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: c8bb22a1-024e-11e6-a1e8-000c29225fa0:1
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
(2) 從資料庫檢視同步情況
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| abc |
| mydb |
| mysql |
| performance_schema |
+——————–+
5 rows in set (0.01 sec)
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.65
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000007
Read_Master_Log_Pos: 290
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 502
Relay_Master_Log_File: master-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: mysql.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 290
Relay_Log_Space: 706
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: c8bb22a1-024e-11e6-a1e8-000c29225fa0
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: c8bb22a1-024e-11e6-a1e8-000c29225fa0:1
Executed_Gtid_Set: c8bb22a1-024e-11e6-a1e8-000c29225fa0:1
Auto_Position: 1
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
二、 配置Mysql5.6 GTID主從 （主從非同步模式轉成GTID模式）方式二
mysql-5.6主從同步配置示例 http://koumm.blog.51cto.com/703525/1764093
原環境本身已經是非同步主從同步模式。
1，從主庫新增如上GTID相關配置檔案，重啟資料庫後，主庫鎖表，備份資料庫。
mysql> flush tables with read lock;
2，從庫操重新配置從庫，清除掉原來的主庫配置資訊, 或者採用匯入資料庫。
mysql> stop slave;
mysql> reset slave;
3，在保證資料同步的情況下從庫重新連線主庫同步。
mysql>
CHANGE MASTER TO
MASTER_HOST=`192.168.0.65`,
MASTER_PORT=3306,
MASTER_USER=`repl`,
MASTER_PASSWORD=`123456`,
MASTER_AUTO_POSITION=1;
4，主庫
mysql> unlock tables;
5，從庫啟動並測試同步情況
三、小結
可以在GTID的基礎上配置半自動同步複製,可以檢視相關文件，實際環境中需要對GTID相當瞭解後再用於生產環境。
本文出自 “koumm的linux技術部落格” 部落格，請務必保留此出處http://koumm.blog.51cto.com/703525/1767786
相關文章
- mysql複製--主從複製配置2018-04-12MySql
- 配置mysql5.5主從複製、半同步複製、主主複製2016-01-18MySql
- MySql 主從複製配置2014-01-08MySql
- MySQL主從複製配置2015-05-06MySql
- MYSQL主從複製配置（整理）2018-09-29MySql
- Redis 4.0主從複製配置2017-11-04Redis
- MYSQL主從複製製作配置方案2018-06-11MySql
- mysql for linux 配置主從複製2014-10-25MySqlLinux
- mysql主從複製原理及配置2015-06-13MySql
- MySQL主從複製與主主複製2017-03-01MySql
- MySQL主從複製、半同步複製和主主複製2016-02-22MySql
- mysql5.7主從複製，主主複製2018-08-21MySql
- MySQL 主從複製安裝部署配置2019-03-27MySql
- Redis replication主從複製原理及配置2019-05-15Redis
- Docker 教程十五配置MySQL 主從複製2018-08-16DockerMySql
- 簡單配置mysql的主從複製2013-08-15MySql
- Linux下配置MySQL主從複製2015-04-24LinuxMySql
- MySQL 主從複製的原理和配置2015-11-17MySql
- MySQL主從複製、半同步複製和主主複製概述2016-03-14MySql
- 主從複製2024-06-30
- mysql主主複製（雙主複製）配置步驟2016-03-24MySql
- MySQL的主從複製、半同步複製、主主複製詳解2016-03-11MySql
- MySQL主從複製配置心跳功能介紹2021-09-09MySql
- Dcoker教程之九配置Redis主從複製2018-07-19Redis
- MySQL基於binlog主從複製配置2015-09-11MySql
- MySQL的主從複製與MySQL的主主複製2017-11-16MySql
- Redis - 主從複製2020-06-21Redis
- Redis：主從複製2018-11-22Redis
- MongoDB主從複製2015-01-14MongoDB
- MySQL 主從複製2008-04-01MySql
- 【MySql】主從複製2011-12-17MySql
- Redis主從複製2024-06-16Redis
- MySQL主從複製2024-11-11MySql
- MySQL主從複製_複製過濾2017-12-26MySql
- MySQL主主複製（雙主複製）配置過程介紹2017-04-25MySql
- mysql主從複製配置與問題解決2018-06-18MySql
- 主從複製、雙主複製及半同步複製、以及基於SSL的複製2017-09-16
- MySQL主從複製之GTID複製2019-07-23MySql