1.mysql5.6创建用户。
create user repl identified by 'Repl@123';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `repl`@`%`;2.mysql8.0.33 创建用户。
create user `repl`@`%` IDENTIFIED with mysql_native_password by 'Repl@123';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `repl`@`%`;
3.mysql5.6 查看主节点状态
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-binlog.000002
Position: 558
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: e8e078b8-e296-11ee-8313-000c29c378ab:1-2
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> SELECT @@GLOBAL.GTID_EXECUTED;
+------------------------------------------+
| @@GLOBAL.GTID_EXECUTED |
+------------------------------------------+
| e8e078b8-e296-11ee-8313-000c29c378ab:1-2 |
+------------------------------------------+
4.mysql8.0.33 作为从节点同步来自主节点的数据;
假如mysql5.6的数据导入到了8.0.33;
root@itpux 15:24: [(none)]> reset master;
Query OK, 0 rows affected (0.00 sec)
root@itpux 15:24: [(none)]> reset slave all;
Query OK, 0 rows affected, 1 warning (0.01 sec)
root@itpux 15:24: [(none)]> SET @@GLOBAL.GTID_PURGED='e8e078b8-e296-11ee-8313-000c29c378ab:1-2';
Query OK, 0 rows affected (0.00 sec)
root@itpux 15:24: [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.75.57',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='Repl@123',MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 8 warnings (0.01 sec)
root@itpux 15:25: [(none)]> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show slave status\G;
root@itpux 15:25: [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.75.57
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-binlog.000002
Read_Master_Log_Pos: 558
Relay_Log_File: relay.000002
Relay_Log_Pos: 423
Relay_Master_Log_File: mysql-binlog.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: 558
Relay_Log_Space: 623
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: 620
Master_UUID: e8e078b8-e296-11ee-8313-000c29c378ab
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica 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: e8e078b8-e296-11ee-8313-000c29c378ab:1-2
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
6.主从同步测试。
(1)主库
mysql> create database test1 charset utf8;
Query OK, 1 row affected (0.00 sec)
mysql> use test1;
Database changed
mysql> create table xsq1(id int,name varchar(30),age int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into xsq1 values(1,'xsq1',17),(2,'薛双奇',16),(3,'薛xxx',19);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> alter table xsq1 add primary key (id);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> delete from xsq1 where id=1;
Query OK, 1 row affected (0.00 sec)
mysql> update xsq1 set age=18 where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from xsq1;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 2 | 薛双奇 | 18 |
| 3 | 薛xxx | 19 |
+----+-----------+------+
2 rows in set (0.00 sec)
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-binlog.000002
Position: 2075
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: e8e078b8-e296-11ee-8313-000c29c378ab:1-8
1 row in set (0.00 sec)
(2)从库检查
root@itpux 15:25: [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test1 |
+--------------------+
5 rows in set (0.01 sec)
root@itpux 15:31: [(none)]> use test1;
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@itpux 15:31: [test1]> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| xsq1 |
+-----------------+
1 row in set (0.00 sec)
root@itpux 15:32: [test1]> select * from xsq1;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 2 | 薛双奇 | 18 |
| 3 | 薛xxx | 19 |
+----+-----------+------+
2 rows in set (0.00 sec)
root@itpux 15:32: [test1]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.75.57
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-binlog.000002
Read_Master_Log_Pos: 2075
Relay_Log_File: relay.000002
Relay_Log_Pos: 1940
Relay_Master_Log_File: mysql-binlog.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: 2075
Relay_Log_Space: 2140
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: 620
Master_UUID: e8e078b8-e296-11ee-8313-000c29c378ab
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica 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: e8e078b8-e296-11ee-8313-000c29c378ab:3-8
Executed_Gtid_Set: e8e078b8-e296-11ee-8313-000c29c378ab:1-8
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
可以发现主从同步，并且GTID都到了8这个位置。
7.目标端日志检查。
2024-03-15T15:24:53.798259+08:00 17 [System] [MY-010916] [Server] @@GLOBAL.GTID_PURGED was changed from '' to 'e8e078b8-e296-11ee-8313-000c29c378ab:1-2'.
2024-03-15T15:24:53.798328+08:00 17 [System] [MY-010917] [Server] @@GLOBAL.GTID_EXECUTED was changed from '' to 'e8e078b8-e296-11ee-8313-000c29c378ab:1-2'.
2024-03-15T15:25:13.292682+08:00 17 [System] [MY-010597] [Repl] 'CHANGE REPLICATION SOURCE TO FOR CHANNEL '' executed'. Previous state source_host='', source_port= 3306, source_log_file='', source_log_pos= 4, source_bind=''. New state source_host='192.168.75.57', source_port= 3306, source_log_file='', source_log_pos= 4, source_bind=''.
2024-03-15T15:25:32.402782+08:00 18 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
2024-03-15T15:25:32.422243+08:00 18 [System] [MY-014002] [Repl] Replica receiver thread for channel '': connected to source 'repl@192.168.75.57:3306' with server_uuid=e8e078b8-e296-11ee-8313-000c29c378ab, server_id=620. Starting GTID-based replication.
8.总结
虽然mysql5.6.20可以同步主从同步到8.0.33，但是如果在主库创建用户，则同步会失败。原因是5.6.20的版本的创建用户的语法与8.0.33的不相同。其他的基本兼容。迁移时需要特别注意
扫一扫
3970
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
