由于gtid的优势,我们需要将传统基于file-pos的复制更改为基于gtid的复制,如何在线变更成为我们关心的一个点,如下为具体的方法:
目前我们有一个传统复制下的m-s结构:
port 3301 master
port 3302 slave
master上(3301): [zejin] 3301>select * from t_users; +----+------+ | id | name | +----+------+ | 1 | hao | | 2 | zhou | +----+------+ rows in set (0.00 sec) slave上(3302): [zejin] 3302>show slave status\g *************************** 1. row *************************** slave_io_state: waiting for master to send event master_host: 192.168.1.240 master_user: repl master_port: 3301 connect_retry: 60 master_log_file: binlog57.000002 read_master_log_pos: 417 relay_log_file: zejin240-relay-bin.000004 relay_log_pos: 628 relay_master_log_file: binlog57.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: 417 relay_log_space: 884 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: 3301 master_uuid: a97983fc-5a29-11e6-9d28-000c29d4dc3f master_info_file: /home/mysql/i3302/master.info sql_delay: 0 sql_remaining_delay: null slave_sql_running_state: slave has read all relay log; waiting for more updates 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: 0 replicate_rewrite_db: channel_name: master_tls_version: row in set (0.00 sec) [zejin] 3302>select * from t_users; +----+------+ | id | name | +----+------+ | 1 | hao | | 2 | zhou | +----+------+ rows in set (0.00 sec)
如下为在线变更的具体的操作步骤:
前提:
1.要求所有的mysql版本5.7.6或更高的版本。
2.目前拓扑结构中所有的mysql的gtid_mode的值为off状态。
3.如下的操作步骤都是有序的,不要跳跃着进行。
补充一下全局系统变量gtid_mode变量值说明:
off 新事务是非gtid, slave只接受不带gtid的事务,传送来gtid的事务会报错
off_permissive 新事务是非gtid, slave即接受不带gtid的事务也接受带gtid的事务
on_permissive 新事务是gtid, slave即接受不带gtid的事务也接受带gtid的事务
on 新事务是gtid, slave只接受带gtid的事务
需要注意的是,这几个值的改变是有顺序的,即
off<--->off_permissive<--->on_permissive<--->on
不能跳跃执行,会报错。
step1:在每个mysql实例上,将enforce_gtid_consistency设置为warning,哪台先执行不影响结果。
[zejin] 3302>set @@global.enforce_gtid_consistency=warn; query ok, 0 rows affected (0.00 sec) [zejin] 3301>set @@global.enforce_gtid_consistency=warn; query ok, 0 rows affected (0.00 sec)
注意:执行完这条语句后,如果出现gtid不兼容的语句用法,在错误日志会记录相关信息,那么需要调整应该程序避免不兼容的写法,直到完全没有产生不兼容的语句,可以通过应该程序去排查所有的sql,也可以设置后观察错误日志一段时间,这一步非常重要。
step2:在每个mysql实例上,设置enforce_gtid_consistency为on,哪台先执行不影响结果
在第一步完成后,就可以将值设置为on。
[zejin] 3301>set @@global.enforce_gtid_consistency=on; query ok, 0 rows affected (0.03 sec) [zejin] 3302>set @@global.enforce_gtid_consistency=on; query ok, 0 rows affected (0.00 sec)
step3:在每个mysql实例上,设置gtid_mode为off_permissiv;哪台先执行不影响结果
[zejin] 3301>set @@global.gtid_mode = off_permissive; query ok, 0 rows affected (0.00 sec) [zejin] 3302>set @@global.gtid_mode = off_permissive; query ok, 0 rows affected (0.00 sec)
step4:在每个mysql实例上,设置gtid_mode为on_permissiv;;哪台先执行不影响结果
[zejin] 3302>set @@global.gtid_mode = on_permissive; query ok, 0 rows affected (0.00 sec) [zejin] 3301>set @@global.gtid_mode = on_permissive; query ok, 0 rows affected (0.01 sec)
step5:在每个mysql实例上检查变量ongoing_anonymous_transaction_count
[zejin] 3301>show status like 'ongoing_anonymous_transaction_count'; +-------------------------------------+-------+ | variable_name | value | +-------------------------------------+-------+ | ongoing_anonymous_transaction_count | 0 | +-------------------------------------+-------+ row in set (0.02 sec) [zejin] 3302>show status like 'ongoing_anonymous_transaction_count'; +-------------------------------------+-------+ | variable_name | value | +-------------------------------------+-------+ | ongoing_anonymous_transaction_count | 0 | +-------------------------------------+-------+ row in set (0.02 sec)
需要等到此变量为0
step6: 确保所有的匿名事务(非gtid事务)已经被完全复制到所有的server上。
检查方法:
在master上: [zejin] 3301>show master status; +-----------------+----------+--------------+------------------+-------------------+ | file | position | binlog_do_db | binlog_ignore_db | executed_gtid_set | +-----------------+----------+--------------+------------------+-------------------+ | binlog57.000005 | 154 | | | | +-----------------+----------+--------------+------------------+-------------------+ row in set (0.00 sec) 在slave上, [zejin] 3302>show slave status\g *************************** 1. row *************************** …… relay_master_log_file: binlog57.000005 exec_master_log_pos: 154 ……
检查这两项relay_master_log_file的值大于binlog57.000005,
或者等于relay_master_log_file等于binlog57.000005并且exec_master_log_pos的值大于等于154即可
或者slave直接用函数:
[zejin] 3302>select master_pos_wait('binlog57.000005', 154); +-----------------------------------------+ | master_pos_wait('binlog57.000005', 154) | +-----------------------------------------+ | 0 | +-----------------------------------------+ row in set (0.00 sec)
返回结果大于等于0就说明匿名事务已经全部复制完成
step7: 确认整个拓扑结构中已经没有匿名事务的存在,如之前产生的所有匿名事务已经全部被执行完毕,甚至二进制日志中也不要有匿名事务,可以通过flush logs,并让mysql来自动清理旧的二进制日志文件。
step8: 在每个mysql实例上,设置gtid_mode为on,
[zejin] 3301>set @@global.gtid_mode = on; query ok, 0 rows affected (0.04 sec) [zejin] 3302>set @@global.gtid_mode = on; query ok, 0 rows affected (0.04 sec)
step9: 在每个mysql实例的配置文件my.cnf上,增加gtid-mode=on
验证:
[zejin] 3301>insert into t_users values(3,'chen'); query ok, 1 row affected (0.02 sec) [zejin] 3301>update t_users set name='li' where id=1; query ok, 1 row affected (0.03 sec) rows matched: 1 changed: 1 warnings: 0 [zejin] 3301>select * from t_users; +----+------+ | id | name | +----+------+ | 1 | li | | 2 | zhou | | 3 | chen | +----+------+ rows in set (0.00 sec) [zejin] 3302>show slave status\g *************************** 1. row *************************** slave_io_state: waiting for master to send event master_host: 192.168.1.240 master_user: repl master_port: 3301 connect_retry: 60 master_log_file: binlog57.000006 read_master_log_pos: 462 relay_log_file: zejin240-relay-bin.000012 relay_log_pos: 673 relay_master_log_file: binlog57.000006 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: 462 relay_log_space: 969 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: 3301 master_uuid: a97983fc-5a29-11e6-9d28-000c29d4dc3f master_info_file: /home/mysql/i3302/master.info sql_delay: 0 sql_remaining_delay: null slave_sql_running_state: slave has read all relay log; waiting for more updates master_retry_count: 86400 master_bind: last_io_error_timestamp: last_sql_error_timestamp: master_ssl_crl: master_ssl_crlpath: retrieved_gtid_set: a97983fc-5a29-11e6-9d28-000c29d4dc3f:1-2 executed_gtid_set: a97983fc-5a29-11e6-9d28-000c29d4dc3f:1-2 auto_position: 0 replicate_rewrite_db: channel_name: master_tls_version: row in set (0.00 sec)
至此完成从传统复制到gtid复制的在线转换。
以上这篇mysql5.7不停业务将传统复制变更为gtid复制的实例就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持移动技术网。
如对本文有疑问, 点击进行留言回复!!
mysql·update语句报错:Data truncation: Truncated incorrect DOUBLE value: ‘系统开小差啦~请稍后再试‘
MySQL Fix Product Name Format(trim去空格+upper/lower大小写)
mysql pxc LIMITATIONS 8.0.19-10
网友评论