MySQL主從複製之GTID複製

原文： https://www.cnblogs.com/hmwh/p/9198705.html

GTID複製又叫全域性事物ID（global transaction ID），是一個已提交事物的編號，並且是一個全域性唯一的編號，MYSQL5.6版本之後在主從複製型別上新增了GTID複製。

GTID是由server_uuid和事物id組成的，即GTID=servier_uuid:transacton_id。Server_uuid是在資料庫啟動過程中自動生成的，每臺機器的server-uuid不一樣。UUID存放在資料目錄的auto.cnf檔案下。而trasaciton_id就是事物提交時由系統順序分配的一個不會重複的序列號。

 

3.1GTID存在的價值

1、GTID使用master_auto_position=1 代替了基於binlog和position號的主從複製搭建的方式，更便於主從複製的搭建。

2、GTID可以知道事務在最開始是在哪個例項上提交的。

3、GTID方便實現主從之間的failover，再也不用不斷的去找position和binlog。

3.2GTID搭建模式

GTID不需要傳統的binlog和position號了，而是在從庫”change master to”時使用”master_auto_position=1”的方式搭建，這就讓操作變得更加方便和可靠了。

 

3.3配置前期準備

在安裝好主從資料庫之後：

主庫需要以下配置：

gtid_mode=on

enforce_gtid_consistency=on

log_bin=on

 

從庫需要以下配置：

 

servier-id 主從庫不能一樣。

gtid_mode=on

enforce_gtid_consistency=on

log_slave_updates=1

 

主庫操作：

建立主從複製賬號

create user 'rep'@'192.16.20.%' identified by 'mysql';

 

grant replication slave on *.* to 'rep'@'172.16.20.%';

 

show grants for 'rep'@'172.16.20.%';

 

 

如果不同網段建議主從各建各的。

 

初始化：

/usr/local/mysql5.7/bin/mysqldump -S /tmp/mysql3307.sock --single-transaction -uroot -pmysql --master-data=2 -A > slave.sql（我全庫匯入的有問題,換成絕對路徑就行，只用的test庫）

 

注意：必須加引數 –master-data=2,讓備份出來的檔案中記錄備份這一刻binlog檔案與position號，為搭建主從環境做準備。檢視備份檔案中記錄的當前binlog檔案和position號。

 

 

scp salve.sql 172.16.20.21:/binlogbak

 

 

 

test庫操作：

mysqldump -S /tmp/mysql3307.sock --single-transaction -uroot -pmysql --master-data=2 --database test > slave_test.sql

 

注意，如果主從GTI不一樣，資料一致可以：

set global gtid_purged='*******';

 

 

如果資料不一樣，GTID也不一樣，建議按照下面先reset從庫:reset master;

mysql -S /tmp/mysql3307.sock -uroot -pmysql < slave_test.sql

 

 

注意：多次恢復會報錯，因為裡面已經有gtid的資訊了。

如果你還想繼續強制恢復，可以在從庫上 reset master ;reset後，gtid_executed，gtid_purged為空。

show global variables like '%gtid%';

 

3.4主從配置

 

1、如果是在已經跑的伺服器，你需要重啟一下mysql server。

2、啟動之前，一定要先關閉master的寫入，保證所有slave端都已經和master端資料保持同步。

3、所有slave需要加上skip_slave_start=1的配置引數，避免啟動後還是使用老的複製協議。

 

 

在資料庫命令列執行配置主從命令。

 

change master to master_host='172.16.20.32',master_port=3307,master_user='rep',master_password='mysql',master_auto_position=1;

 

提示：master_log_file='mysql-binlog.000010',MASTER_LOG_POS=797 這兩個引數替換成了 master_auto_position=1

 

 

show slave status\G;

 

 

start slave;

 

stop slave;

reset slave all; 清空從庫的所有配置資訊。

start slave;

 

 

一般複製建議半同步+GTID 複製：

 

 

 

附帶主從引數：

主：

[root@mysql5 ~]# cat /etc/my3307.cnf

[client]

port = 3307

socket = /tmp/mysql5.7.sock

[mysql]

prompt="\u@db \R:\m:\s [\d]> "

no-auto-rehash

[mysqld]

user = mysql

port = 3307

basedir = /usr/local/mysql5.7

datadir = /mydata/mysql/mysql3307/data

socket = /tmp/mysql3307.sock

rpl_semi_sync_master_enabled=1

rpl_semi_sync_master_timeout=1000

event_scheduler=1

sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

lower_case_table_names=1

secure_file_priv=/tmp

character-set-server = utf8mb4

skip_name_resolve = 1

open_files_limit = 65535

back_log = 1024

max_connections = 500

max_connect_errors = 1000000

table_open_cache = 1024

table_definition_cache = 1024

table_open_cache_instances = 64

thread_stack = 512K

external-locking = FALSE

max_allowed_packet = 32M

sort_buffer_size = 4M

join_buffer_size = 4M

thread_cache_size = 768

query_cache_size = 0

query_cache_type = 0

interactive_timeout = 600

wait_timeout = 600

tmp_table_size = 32M

max_heap_table_size = 32M

slow_query_log = 1

slow_query_log_file = /mydata/mysql/mysql3307/logs/slow.log

log-error = /mydata/mysql/mysql3307/logs/error.log

long_query_time = 0.5

server-id = 3307101

log-bin = /mydata/mysql/mysql3307/logs/mysql-binlog

sync_binlog = 1

binlog_cache_size = 4M

max_binlog_cache_size = 1G

max_binlog_size = 500M

expire_logs_days = 7

master_info_repository = TABLE

relay_log_info_repository = TABLE

gtid_mode = on

enforce_gtid_consistency = 1

log_slave_updates

binlog_format = row

relay_log_recovery = 1

relay-log-purge = 1

key_buffer_size = 32M

read_buffer_size = 8M

read_rnd_buffer_size = 4M

bulk_insert_buffer_size = 64M

lock_wait_timeout = 3600

explicit_defaults_for_timestamp = 1

innodb_thread_concurrency = 0

innodb_sync_spin_loops = 100

innodb_spin_wait_delay = 30

transaction_isolation = REPEATABLE-READ

innodb_buffer_pool_size = 1024M

innodb_buffer_pool_instances = 8

innodb_buffer_pool_load_at_startup = 1

innodb_buffer_pool_dump_at_shutdown = 1

innodb_data_file_path = ibdata1:1G:autoextend

innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size = 32M

innodb_log_file_size = 2G

innodb_log_files_in_group = 2

innodb_max_undo_log_size = 4G

innodb_io_capacity = 2000

innodb_io_capacity_max = 4000

innodb_flush_neighbors = 0

innodb_write_io_threads = 8

innodb_read_io_threads = 8

innodb_purge_threads = 4

innodb_page_cleaners = 4

innodb_open_files = 65535

innodb_max_dirty_pages_pct = 50

innodb_flush_method = O_DIRECT

innodb_lru_scan_depth = 4000

innodb_checksum_algorithm = crc32

innodb_lock_wait_timeout = 10

innodb_rollback_on_timeout = 1

innodb_print_all_deadlocks = 1

innodb_file_per_table = 1

innodb_online_alter_log_max_size = 500M

internal_tmp_disk_storage_engine = InnoDB

innodb_stats_on_metadata = 0

innodb_status_file = 1

innodb_status_output = 0

innodb_status_output_locks = 0

#performance_schema

performance_schema = 1

performance_schema_instrument = '%=on'

#innodb monitor

innodb_monitor_enable="module_innodb"

innodb_monitor_enable="module_server"

innodb_monitor_enable="module_dml"

innodb_monitor_enable="module_ddl"

innodb_monitor_enable="module_trx"

innodb_monitor_enable="module_os"

innodb_monitor_enable="module_purge"

innodb_monitor_enable="module_log"

innodb_monitor_enable="module_lock"

innodb_monitor_enable="module_buffer"

innodb_monitor_enable="module_index"

innodb_monitor_enable="module_ibuf_system"

innodb_monitor_enable="module_buffer_page"

innodb_monitor_enable="module_adaptive_hash"

[mysqldump]

quick

max_allowed_packet = 32M

 

 

從：

[root@rac1 ~]# cat /etc/my3307.cnf

[client]

port = 3307

socket = /tmp/mysql5.7.sock

[mysql]

prompt="\u@db \R:\m:\s [\d]> "

no-auto-rehash

[mysqld]

user = mysql

port = 3307

basedir = /usr/local/mysql5.7

datadir = /mydata/mysql/mysql3307/data

socket = /tmp/mysql3307.sock

character-set-server = utf8mb4

lower_case_table_names=1

sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

rpl_semi_sync_slave_enabled=1

skip_name_resolve = 1

open_files_limit = 65535

back_log = 1024

max_connections = 500

max_connect_errors = 1000000

table_open_cache = 1024

table_definition_cache = 1024

table_open_cache_instances = 64

thread_stack = 512K

external-locking = FALSE

max_allowed_packet = 32M

sort_buffer_size = 4M

join_buffer_size = 4M

thread_cache_size = 768

query_cache_size = 0

query_cache_type = 0

interactive_timeout = 600

wait_timeout = 600

tmp_table_size = 32M

max_heap_table_size = 32M

slow_query_log = 1

slow_query_log_file = /mydata/mysql/mysql3307/logs/slow.log

log-error = /mydata/mysql/mysql3307/logs/error.log

long_query_time = 0.1

server-id = 3307102

log-bin = /mydata/mysql/mysql3307/logs/mysql-binlog

sync_binlog = 1

binlog_cache_size = 4M

max_binlog_cache_size = 500m

max_binlog_size = 200m

expire_logs_days = 7

master_info_repository = TABLE

relay_log_info_repository = TABLE

gtid_mode = on

enforce_gtid_consistency = 1

log_slave_updates

binlog_format = row

relay_log_recovery = 1

relay-log-purge = 1

key_buffer_size = 32M

read_buffer_size = 8M

read_rnd_buffer_size = 4M

bulk_insert_buffer_size = 64M

lock_wait_timeout = 3600

explicit_defaults_for_timestamp = 1

innodb_thread_concurrency = 0

innodb_sync_spin_loops = 100

innodb_spin_wait_delay = 30

transaction_isolation = REPEATABLE-READ

innodb_buffer_pool_size = 1024M

innodb_buffer_pool_instances = 8

innodb_buffer_pool_load_at_startup = 1

innodb_buffer_pool_dump_at_shutdown = 1

innodb_data_file_path = ibdata1:1G:autoextend

innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size = 32M

innodb_log_file_size = 1g

innodb_log_files_in_group = 2

innodb_max_undo_log_size = 1G

innodb_io_capacity = 2000

innodb_io_capacity_max = 4000

innodb_flush_neighbors = 0

innodb_write_io_threads = 8

innodb_read_io_threads = 8

innodb_purge_threads = 4

innodb_page_cleaners = 4

innodb_open_files = 65535

innodb_max_dirty_pages_pct = 50

innodb_flush_method = O_DIRECT

innodb_lru_scan_depth = 4000

innodb_checksum_algorithm = crc32

innodb_lock_wait_timeout = 10

innodb_rollback_on_timeout = 1

innodb_print_all_deadlocks = 1

innodb_file_per_table = 1

innodb_online_alter_log_max_size = 4G

internal_tmp_disk_storage_engine = InnoDB

innodb_stats_on_metadata = 0

innodb_status_file = 1

innodb_status_output = 0

innodb_status_output_locks = 0

performance_schema = 1

performance_schema_instrument = '%=on'

#innodb monitor

innodb_monitor_enable="module_innodb"

innodb_monitor_enable="module_server"

innodb_monitor_enable="module_dml"

innodb_monitor_enable="module_ddl"

innodb_monitor_enable="module_trx"

innodb_monitor_enable="module_os"

innodb_monitor_enable="module_purge"

innodb_monitor_enable="module_log"

innodb_monitor_enable="module_lock"

innodb_monitor_enable="module_buffer"

innodb_monitor_enable="module_index"

innodb_monitor_enable="module_ibuf_system"

innodb_monitor_enable="module_buffer_page"

innodb_monitor_enable="module_adaptive_hash"

[mysqldump]

quick

max_allowed_packet = 32M

 

 

4、GTID複製與傳統複製的切換

 

主：172.16.20.32

從：172.16.10.21

 

a)    GTID切換成傳統複製

從庫資訊：

show slave status\G;

 

 

主庫：

 

 

從庫切換操作：

stop slave;

 

change master to master_auto_position=0,master_host='172.16.20.32', master_port=3307,master_user='rep',master_password='mysql',Master_Log_File='mysql-binlog.000007',Master_Log_Pos=194;

 

start slave;

 

主從 資料庫伺服器上 同時，依次 執行以下操作：

set global gtid_mode='on_permissive';

 

set global gtid_mode='off_permissive';

 

主從 關閉GTID功能：

set global enforce_gtid_consistency=off;

 

set global gtid_mode=off;

 

把gtid_mode=off和enforce_gtid_consistency=off寫入配置檔案my3307.cnf中。重啟後可以繼續生效，並進行測試。

 

b)    傳統複製切換成GTID過程

主從資料庫伺服器同時修改以下引數：

 

set global enforce_gtid_consistency=warn; error log 不會出現警告資訊，如果有，需要先修復，才能繼續後面操作。

 

set global enforce_gtid_consistency=on;

 

set global gtid_mode=off_permissive;

 

set global gtid_mode=on_permissive;

 

 

確認從庫沒等待的事務：

show global status like '%ongoing%';

 

 

0代表沒有等待的事務。

 

主從庫上同時設定gtid_mode=on;

 

set global gtid_mode=on;

 

 

show variables like '%gtid%';

 

 

把傳統複製模式改為（GTID）複製。

 

stop slave;

 

change master to master_auto_position=1;

 

 

檢視並進行測試：

 

 

主：

 

 

從：

 

 

例如主庫：

 

 

從庫：

 

5、GTID使用限制條件

GTID複製是針對事物，一個gtid對於一個事務。

1、 不能使用create table table_name select * from table_name。

2、 在一個事務中即包含事務表的操作，又包含非事物表。

3、 不支援create temporary table or drop temporary table語句操作。

4、 使用GTID複製從庫調過錯誤，不支援執行slave_skip_errors。

---

---

About Me

........................................................................................................................ ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在itpub、部落格園、CSDN和個人微 信公眾號（ xiaomaimiaolhr ）上有同步更新 ● 本文itpub地址： http://blog.itpub.net/26736162 ● 本文部落格園地址： http://www.cnblogs.com/lhrbest ● 本文CSDN地址： https://blog.csdn.net/lihuarongaini ● 本文pdf版、個人簡介及小麥苗雲盤地址： http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址： http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ........................................................................................................................ ● QQ群號： 230161599 （滿） 、618766405 ● 微 信群：可加我微 信，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友 （ 646634621 ） ，註明新增緣由 ● 於 2019-07-01 06:00 ~ 2019-07-31 24:00 在西安完成 ● 最新修改時間：2019-07-01 06:00 ~ 2019-07-31 24:00 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ........................................................................................................................ ● 小麥苗的微店 ： https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麥苗出版的資料庫類叢書 ： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麥苗OCP、OCM、高可用網路班 ： http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麥苗騰訊課堂主頁 ： https://lhr.ke.qq.com/ ........................................................................................................................ 使用 微 信客戶端 掃描下面的二維碼來關注小麥苗的微 信公眾號（ xiaomaimiaolhr ）及QQ群（DBA寶典）、新增小麥苗微 信， 學習最實用的資料庫技術。 ........................................................................................................................

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2651411/，如需轉載，請註明出處，否則將追究法律責任。