MySQL主從復(fù)制實(shí)戰(zhàn)-基于GTID的復(fù)制代碼分享

mysql>?create?user?'repl'@'172.%'?identified?by?'123456';

mysql>?grant?replication?slave?on?*.*?to?'repl'@'172.%';

mysql>?select?user,?host?from?mysql.user;  +-----------+-----------+  |?user??|?host??|  +-----------+-----------+  |?prontera?|?%???|  |?root??|?%???|  |?mysql.sys?|?localhost?|  |?root??|?localhost?|  +-----------+-----------+  4?rows?in?set?(0.00?sec)

mysql>?show?grants?for?repl@'172.%';  +--------------------------------------------------+  |?Grants?for?repl@172.%???????|  +--------------------------------------------------+  |?GRANT?REPLICATION?SLAVE?ON?*.*?TO?'repl'@'172.%'?|  +--------------------------------------------------+  1?row?in?set?(0.00?sec)

[mysqld]  log_bin?=?/var/log/mysql/mysql-bin  log_bin_index?=?/var/log/mysql/mysql-bin.index  binlog_format?=?row  server_id?=?101  gtid_mode?=?ON  enforce_gtid_consistency?=?ON  #log_slave_updates?=?ON

mysql>?create?table?dept?select?*?from?departments;  ERROR?1786?(HY000):?Statement?violates?GTID?consistency:?CREATE?TABLE?...?SELECT.

create?temporary?table

mysql>?begin;  Query?OK,?0?rows?affected?(0.00?sec)    mysql>?create?temporary?table?dept(id?int);  ERROR?1787?(HY000):?Statement?violates?GTID?consistency:?  CREATE?TEMPORARY?TABLE?and?DROP?TEMPORARY?TABLE?can?only?be?executed?outside?transactional?context.?  These?statements?are?also?not?allowed?in?a?function?or?trigger?because?functions?and?triggers?are?also?  considered?to?be?multi-statement?transactions.

mysql>?CREATE?TABLE?`dept_innodb`?(id?INT(11)?UNSIGNED?NOT?NULL?PRIMARY?KEY?AUTO_INCREMENT);  Query?OK,?0?rows?affected?(0.04?sec)    mysql>?CREATE?TABLE?`dept_myisam`?(id?INT(11)?UNSIGNED?NOT?NULL?PRIMARY?KEY?AUTO_INCREMENT)?ENGINE?=?`MyISAM`;  Query?OK,?0?rows?affected?(0.03?sec)    mysql>?begin;  Query?OK,?0?rows?affected?(0.00?sec)    mysql>?insert?into?dept_innodb(id)?value(1);  Query?OK,?1?row?affected?(0.00?sec)    mysql>?insert?into?dept_myisam(id)?value(1);  ERROR?1785?(HY000):?Statement?violates?GTID?consistency:?  Updates?to?non-transactional?tables?can?only?be?done?in?either?autocommitted?statements?or?  single-statement?transactions,?and?never?in?the?same?statement?as?updates?to?transactional?tables.

[mysqld]  log_bin?=?/var/log/mysql/mysql-bin  log_bin_index?=?/var/log/mysql/mysql-bin.index  server_id?=?102  #?slaves  relay_log??=?/var/log/mysql/relay-bin  relay_log_index?=?/var/log/mysql/relay-bin.index  relay_log_info_file?=?/var/log/mysql/relay-bin.info  enforce_gtid_consistency?=?ON  log_slave_updates?=?ON  read_only?=?ON  master_info_repository?=?TABLE  relay_log_info_repository?=?TABLE

mysqldump?--single-transaction?--master-data=2?--triggers?--routines?--all-databases?--events?-u?root?-p?>?backup.sql

ERROR?1840?(HY000)?at?line?24:?@@GLOBAL.GTID_PURGED?can?only?be?set?when?@@GLOBAL.GTID_EXECUTED?is?empty.

mysql>?change?master?to?master_host='master',?master_user='repl',?master_password='123456',?master_auto_position=1;  Query?OK,?0?rows?affected,?2?warnings?(0.06?sec)

mysql>?start?slave;

mysql>?show?slave?statusG  ***************************?1.?row?***************************  ????Slave_IO_State:?Queueing?master?event?to?the?relay?log  ?????Master_Host:?master  ?????Master_User:?repl  ?????Master_Port:?3306  ????Connect_Retry:?60  ????Master_Log_File:?mysql-bin.000002  ???Read_Master_Log_Pos:?12793692  ????Relay_Log_File:?relay-bin.000002  ????Relay_Log_Pos:?1027  ??Relay_Master_Log_File:?mysql-bin.000002  ????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:  ?????Last_Errno:?0  ?????Last_Error:  ?????Skip_Counter:?0  ???Exec_Master_Log_Pos:?814  ????Relay_Log_Space:?12794106  ????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:?5096  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:?101  ?????Master_UUID:?a9fd4765-ec70-11e6-b543-0242ac140002  ????Master_Info_File:?mysql.slave_master_info  ?????SQL_Delay:?0  ???SQL_Remaining_Delay:?NULL  ??Slave_SQL_Running_State:?Reading?event?from?the?relay?log  ???Master_Retry_Count:?86400  ?????Master_Bind:  ??Last_IO_Error_timestamp:  ??Last_SQL_Error_Timestamp:  ????Master_SSL_Crl:  ???Master_SSL_Crlpath:  ???Retrieved_Gtid_Set:?a9fd4765-ec70-11e6-b543-0242ac140002:1-39  ???Executed_Gtid_Set:?a9fd4765-ec70-11e6-b543-0242ac140002:1-4  ????Auto_Position:?1  ???Replicate_Rewrite_DB:  ?????Channel_Name:  ???Master_TLS_Version:  1?row?in?set?(0.00?sec)