mysql5.6 — mysql5.7 GTID模式下多源复制之实践案例
发布时间：2022-03-28 14:48:19 所属栏目：MySql教程 来源：互联网
导读：背景说明：公司有多个mysql实例，单实例多个数据库，而且版本还是5.6,这给数据查询分析增加了不少繁琐的事情。所以推荐使用mysql5.6的实例多源复制到mysql5.7实例下方便数据的查询、分析以及权限的管理 环境说明：10.89.3.224 mysql5.6.39 master1 10.89.3.2
|
背景说明：公司有多个mysql实例，单实例多个数据库，而且版本还是5.6,这给数据查询分析增加了不少繁琐的事情。所以推荐使用mysql5.6的实例多源复制到mysql5.7实例下方便数据的查询、分析以及权限的管理 环境说明：10.89.3.224 mysql5.6.39 master1 10.89.3.225 mysql5.6.39 master2 10.89.3.222 mysql5.7.21 slave OS:centos 7.4 mysql_port:3309 操作步骤 1、安装mysql5.6 mysql5.7 详细过程不再重复、如有疑问欢迎参考我的博客 http://blog.itpub.net/29987453/viewspace-2149405/ 2、配置mysql master01配置 1)配置10.89.3.224 mysql5.6.39 master1 ##在一个复制组内的serverid必须不一样 server-id = 123 ##gtid 开启gtid mysql5.6支持 gtid_mode=on ##强制事务一致性，保证事务的安全 enforce-gtid-consistency=on ##在从库上记录从主库传过来的日志数据 log-slave-updates=1 ##开启binlog 必须的 log_bin = master01-binlog ##强烈建议设置row模式 binlog_format = row ##binlog保存的日期 expire_logs_days = 30 #relay log在从服务器中记录从主服务器传过来的日志数据 skip_slave_start=1 ###设置master-info和relay-log 存放在table中 master-info-repository = TABLE relay-log-info-repository = TABLE #指定复制的数据库 replicate-do-db=roket1,roket2 ##指定忽略的数据库(不需要复制的库) replicate-ignore- db=mysql,information_schema,performance_schema,test 2)配置完之后重启mysql server service mysqld restart 3)登录数据库，创建主从同步账号 grant replication slave on *.* to 'repl'@'10.89.3.222' identified by '123456'; flush privileges; 4)重置binlog日志 reset master; Query OK, 0 rows affected (0.01 sec) (root@localhost:mysql.sock) [(none)]>show binary logs; +------------------------+-----------+ | Log_name | File_size | +------------------------+-----------+ | master01-binlog.000001 | 151 | +------------------------+-----------+ 3、配置mysql master03配置 1)配置10.89.3.225 mysql5.6.39 master2 ##在一个复制组内的serverid必须不一样 server-id = 234 ##gtid 开启gtid mysql5.6支持 gtid_mode=on ##强制事务一致性，保证事务的安全 enforce-gtid-consistency=on ##在从库上记录从主库传过来的日志数据 log-slave-updates=1 ##开启binlog 必须的 log_bin = master01-binlog ##强烈建议设置row模式 binlog_format = row ##binlog保存的日期 expire_logs_days = 30 #relay log在从服务器中记录从主服务器传过来的日志数据 skip_slave_start=1 ###设置master-info和relay-log 存放在table中 master-info-repository = TABLE relay-log-info-repository = TABLE #指定复制的数据库 replicate-do-db=maya1,maya2 ##指定忽略的数据库(不需要复制的库) replicate-ignore- db=mysql,information_schema,performance_schema,test 2)配置完之后重启mysql server service mysqld restart 3)登录数据库，创建主从同步账号 grant replication slave on *.* to 'repl'@'10.89.3.222' identified by '123456'; flush privileges; 4)重置binlog日志 reset master; Query OK, 0 rows affected (0.01 sec) (root@localhost:mysql.sock) [(none)]>show binary logs; +------------------------+-----------+ | Log_name | File_size | +------------------------+-----------+ | master01-binlog.000001 | 151 | +————————————+-----------+ 4、配置slave数据库 1）配置配置10.89.3.222 mysql5.7.21 slave ##在一个复制组内的serverid必须不一样 server-id = 234 gtid_mode=on ##强制事务一致性，保证事务的安全 enforce-gtid-consistency=on ##在从库上记录从主库传过来的日志数据 log-slave-updates=1 ##开启binlog 必须的 log_bin = master01-binlog ##强烈建议设置row模式 binlog_format = row ##binlog保存的日期 expire_logs_days = 30 #relay log在从服务器中记录从主服务器传过来的日志数据 skip_slave_start=1 ###设置master-info和relay-log 存放在table中 master-info-repository = TABLE relay-log-info-repository = TABLE #配置只读数据库 read_only=on 4)重置binlog日志 (root@localhost:mysql.sock) [(none)]>reset master; Query OK, 0 rows affected (0.02 sec) (root@localhost:mysql.sock) [(none)]>show binary logs; +---------------------+-----------+ | Log_name | File_size | +---------------------+-----------+ | slave-binlog.000001 | 154 | +---------------------+-----------+ 1 row in set (0.00 sec) (root@localhost:mysql.sock) [(none)]>reset slave; Query OK, 0 rows affected (0.00 sec) 5、开启主从复制 1)关闭master1 和master2的防火墙 2)开启主从复制 (root@localhost:mysql.sock) [(none)]> CHANGE MASTER TO MASTER_HOST = '10.89.3.225', MASTER_PORT = 3309, MASTER_USER = 'repl', MASTER_PASSWORD = '123456', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'master2'; Query OK, 0 rows affected, 2 warnings (0.02 sec) (root@localhost:mysql.sock) [(none)]> CHANGE MASTER TO MASTER_HOST = '10.89.3.224', MASTER_PORT = 3309, MASTER_USER = 'repl', MASTER_PASSWORD = '123456', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'master1'; Query OK, 0 rows affected, 2 warnings (0.02 sec) (root@localhost:mysql.sock) [(none)]>start slave; 3)查看主从复制状态 (root@localhost:mysql.sock) [(none)]>show slave status for channel 'master1'G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.89.3.224 Master_User: repl Master_Port: 3309 Connect_Retry: 60 Master_Log_File: master01-binlog.000001 Read_Master_Log_Pos: 151 Relay_Log_File: mysql-relay-bin-master1.000002 Relay_Log_Pos: 376 Relay_Master_Log_File: master01-binlog.000001 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: 151 Relay_Log_Space: 591 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: 123 Master_UUID: 3809e1da-25c0-11e8-93b1-080027857522 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 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: 1 Replicate_Rewrite_DB: Channel_Name: master1 Master_TLS_Version: 1 row in set (0.00 sec) (root@localhost:mysql.sock) [(none)]>show slave status for channel 'master2'G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.89.3.225 Master_User: repl Master_Port: 3309 Connect_Retry: 60 Master_Log_File: master02-binlog.000001 Read_Master_Log_Pos: 151 Relay_Log_File: mysql-relay-bin-master2.000002 Relay_Log_Pos: 376 Relay_Master_Log_File: master02-binlog.000001 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: 151 Relay_Log_Space: 591 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: 234 Master_UUID: ab8c56e0-25c0-11e8-93b4-0800278c8292 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 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: 1 Replicate_Rewrite_DB: Channel_Name: master2 Master_TLS_Version: 1 row in set (0.00 sec) 显示 主从复制正常 Slave_IO_Running: Yes Slave_SQL_Running: Yes 6、测试核对数据 1)登录master1数据库 create database roket1; Query OK, 1 row affected (0.00 sec) (root@localhost:mysql.sock) [(none)]>create database roket2; Query OK, 1 row affected (0.01 sec) (root@localhost:mysql.sock) [(none)]>use roket1; Database changed (root@localhost:mysql.sock) [roket1]>create table t1( -> id bigint not null auto_increment, -> name varchar(20), -> primary key(id) -> )engine=innodb; Query OK, 0 rows affected (0.02 sec) (root@localhost:mysql.sock) [roket1]>insert into t1 (name) values (‘master1'),('master1'),('master1')； Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 (root@localhost:mysql.sock) [roket1]>select * from t1; +----+---------+ | id | name | +----+---------+ | 1 | master1 | | 2 | master1 | | 3 | master1 | +----+---------+ 2）登录master2数据库 (root@localhost:mysql.sock) [(none)]>create database maya1; Query OK, 1 row affected (0.00 sec) (root@localhost:mysql.sock) [(none)]>create database maya2; Query OK, 1 row affected (0.00 sec) (root@localhost:mysql.sock) [(none)]>use maya1; Database changed (root@localhost:mysql.sock) [maya1]>create table t2( -> id bigint not null auto_increment, -> name varchar(20), -> primary key(id) -> )engine=innodb; Query OK, 0 rows affected (0.02 sec) (root@localhost:mysql.sock) [maya1]>insert into t2 (name) values ('master2'),('master2'),('master2') ; Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 (root@localhost:mysql.sock) [maya1]>select * from t2; +----+---------+ | id | name | +----+---------+ | 1 | master2 | | 2 | master2 | | 3 | master2 | +----+---------+ 3 rows in set (0.00 sec) 3)登录主库 (root@localhost:mysql.sock) [(none)]>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | maya1 | | maya2 | | mysql | | performance_schema | | roket1 | | roket2 | | sys | (root@localhost:mysql.sock) [(none)]> (root@localhost:mysql.sock) [(none)]>use roket1 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed (root@localhost:mysql.sock) [roket1]>show tables; +------------------+ | Tables_in_roket1 | +------------------+ | t1 | +------------------+ 1 row in set (0.00 sec) (root@localhost:mysql.sock) [roket1]>select * from t1; +----+---------+ | id | name | +----+---------+ | 1 | master1 | | 2 | master1 | | 3 | master1 | +----+---------+ 3 rows in set (0.00 sec) (root@localhost:mysql.sock) [maya1]>use maya1 Database changed (root@localhost:mysql.sock) [maya1]>select * from t2; +----+---------+ | id | name | +----+---------+ | 1 | master2 | | 2 | master2 | | 3 | master2 | +----+---------+ 3 rows in set (0.00 sec) 经核对、master1、master2的实例的数据已经正常同步到slave，多源复制搞定 总结：1、首先为mysql5.7多源复制点歌赞 2、解决了公司业务的需要多实例的情况下，统一分析数据、查询权限控制等众多问题 3、解决了多实例备份麻烦的问题。 （编辑：威海站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
