Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

A practical case of multi-source replication in mysql5.6-& gt;mysql5.7 GTID mode

2025-01-22 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

Background description: the company has multiple mysql instances, single instance and multiple databases, and version 5.6, which adds a lot of tedious things to data query and analysis. Therefore, it is recommended to copy mysql5.6 instances from multiple sources to mysql5.7 instances to facilitate data query, analysis and permission management.

Environment description: 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

Operation steps

1. Install mysql5.6 mysql5.7

The detailed process will not be repeated. If you have any questions, please refer to my blog.

Http://blog.itpub.net/29987453/viewspace-2149405/

2. Configure mysql master01 configuration

1) configure 10.89.3.224 mysql5.6.39 master1

# # serverid must be different in a replication group

Server-id = 123

# # enabling gtid mysql5.6 support for gtid

Gtid_mode=on

# # enforce transaction consistency to ensure transaction security

Enforce-gtid-consistency=on

# # record the log data transferred from the master database on the slave database

Log-slave-updates=1

# # required to enable binlog

Log_bin = master01-binlog # # it is strongly recommended to set the row mode

Binlog_format = row

# # date when binlog is saved

Expire_logs_days = 30

# relay log records log data sent from the master server from the slave server

Skip_slave_start=1

# configure master-info and relay-log to be stored in table

Master-info-repository = TABLE

Relay-log-info-repository = TABLE

# specify the replicated database

Replicate-do-db=roket1,roket2

# # specify ignored databases (libraries that do not need to be copied)

Replicate-ignore- db=mysql,information_schema,performance_schema,test

2) restart mysql server after configuration

Service mysqld restart

3) Log in to the database and create a master-slave synchronization account

Grant replication slave on *. * to 'repl'@'10.89.3.222' identified by' 123456'

Flush privileges

4) reset binlog log

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. Configure mysql master03 configuration

1) configure 10.89.3.225 mysql5.6.39 master2

# # serverid must be different in a replication group

Server-id = 234

# # enabling gtid mysql5.6 support for gtid

Gtid_mode=on

# # enforce transaction consistency to ensure transaction security

Enforce-gtid-consistency=on

# # record the log data transferred from the master database on the slave database

Log-slave-updates=1

# # required to enable binlog

Log_bin = master01-binlog # # it is strongly recommended to set the row mode

Binlog_format = row

# # date when binlog is saved

Expire_logs_days = 30

# relay log records log data sent from the master server from the slave server

Skip_slave_start=1

# configure master-info and relay-log to be stored in table

Master-info-repository = TABLE

Relay-log-info-repository = TABLE

# specify the replicated database

Replicate-do-db=maya1,maya2

# # specify ignored databases (libraries that do not need to be copied)

Replicate-ignore- db=mysql,information_schema,performance_schema,test

2) restart mysql server after configuration

Service mysqld restart

3) Log in to the database and create a master-slave synchronization account

Grant replication slave on *. * to 'repl'@'10.89.3.222' identified by' 123456'

Flush privileges

4) reset binlog log

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. Configure slave database

1) configure 10.89.3.222 mysql5.7.21 slave

# # serverid must be different in a replication group

Server-id = 234

Gtid_mode=on

# # enforce transaction consistency to ensure transaction security

Enforce-gtid-consistency=on

# # record the log data transferred from the master database on the slave database

Log-slave-updates=1

# # required to enable binlog

Log_bin = master01-binlog # # it is strongly recommended to set the row mode

Binlog_format = row

# # date when binlog is saved

Expire_logs_days = 30

# relay log records log data sent from the master server from the slave server

Skip_slave_start=1

# configure master-info and relay-log to be stored in table

Master-info-repository = TABLE

Relay-log-info-repository = TABLE

# configure a read-only database

Read_only=on

4) reset binlog log

(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. Enable master-slave replication

1) turn off the firewall of master1 and master2

2) enable master-slave replication

(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) View the status of master-slave replication

(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)

Show that master-slave replication is normal

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

6. Test and check data

1) Log in to master1 database

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.01sec)

(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) Log in to master2 database

(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) Log in to the main library

(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)

After verification, the data of master1 and master2 instances have been synchronized to slave normally, and multi-source replication has been completed.

Summary: 1. First of all, give likes for mysql5.7 multi-source copy.

2. Solve many problems, such as unified analysis of data, query access control and so on, when the company's business needs multiple instances.

3. Solve the troublesome problem of multi-instance backup.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report