In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
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.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
337.You have lost all your RMAN backup set pieces due to a disk failure. Unfortunately, you have ana
© 2024 shulou.com SLNews company. All rights reserved.