mysql8主从节点搭建

设置主从前先创建作为同步数据的用户，可直接在Navicat中创建并对需同步的库授权。

注意创建用户的密码插件plugin要保持一致，MySQL8.0 设为mysql_native_password ，此项可在Navicat直接设置。

以192.168.1.1 从

和192.168.1.2 主

1、在主节点修改配置文件 /etc/my.cnf 添加 

server_id=1
log-bin=mysql-bin

2、在从节点 修改配置文件 /etc/my.cnf 添加

server_id=2 
replicate-do-db=traffic -- 指定同步的库

　　

3、设置主从前，在192.168.1.1和192.168.1.2中，进入数据库分别执行

stop slave；

reset master；

show master status;

4、192.168.1.2 主节点执行 show master status; 如下

+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |      155 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

5、192.168.1.1从节点执行 show master status;如下

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      155 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

6、在从节点执行：

change master to 
master_host='192.168.1.1',              
master_user='admin',
master_password='Longshine#1',
master_log_file='binlog.000001',        --取192.168.1.2的show master status\G  file值
master_log_pos=155;	       　　　　  --取93的show master status\G  position值

7、在主节点执行：

change master to 
master_host='192.168.1.2',              
master_user='admin',
master_password='Longshine#1',
master_log_file='mysql-bin.000001',       --取192.168.1.1的show master status\G  file值
master_log_pos=155;	        　　　　　　--取192.168.1.1的show master status\G  position值

8、mysql> 在主节点执行 show slave status\G

 1 *************************** 1. row ***************************
 2                Slave_IO_State: Waiting for master to send event
 3                   Master_Host: 192.168.1.1
 4                   Master_User: admin
 5                   Master_Port: 3306
 6                 Connect_Retry: 60
 7               Master_Log_File: mysql-bin.000001
 8           Read_Master_Log_Pos: 155
 9                Relay_Log_File: localhost-relay-bin.000002
10                 Relay_Log_Pos: 322
11         Relay_Master_Log_File: mysql-bin.000001
12              Slave_IO_Running: Yes
13             Slave_SQL_Running: Yes
14               Replicate_Do_DB: traffic
15           Replicate_Ignore_DB:
16            Replicate_Do_Table:
17        Replicate_Ignore_Table:
18       Replicate_Wild_Do_Table:
19   Replicate_Wild_Ignore_Table:
20                    Last_Errno: 0
21                    Last_Error:
22                  Skip_Counter: 0
23           Exec_Master_Log_Pos: 155
24               Relay_Log_Space: 534
25               Until_Condition: None
26                Until_Log_File:
27                 Until_Log_Pos: 0
28            Master_SSL_Allowed: No
29            Master_SSL_CA_File:
30            Master_SSL_CA_Path:
31               Master_SSL_Cert:
32             Master_SSL_Cipher:
33                Master_SSL_Key:
34         Seconds_Behind_Master: 0
35 Master_SSL_Verify_Server_Cert: No
36                 Last_IO_Errno: 0
37                 Last_IO_Error:
38                Last_SQL_Errno: 0
39                Last_SQL_Error:
40   Replicate_Ignore_Server_Ids:
41              Master_Server_Id: 92
42                   Master_UUID: 355ec184-a124-11ec-86af-84656961a397
43              Master_Info_File: mysql.slave_master_info
44                     SQL_Delay: 0
45           SQL_Remaining_Delay: NULL
46       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
47            Master_Retry_Count: 86400
48                   Master_Bind:
49       Last_IO_Error_Timestamp:
50      Last_SQL_Error_Timestamp:
51                Master_SSL_Crl:
52            Master_SSL_Crlpath:
53            Retrieved_Gtid_Set:
54             Executed_Gtid_Set:
55                 Auto_Position: 0
56          Replicate_Rewrite_DB:
57                  Channel_Name:
58            Master_TLS_Version:
59        Master_public_key_path:
60         Get_master_public_key: 0
61             Network_Namespace:

--- Slave_IO_Running: Yes

--- Slave_SQL_Running: Yes 都为yes为正常

posted @ 2023-04-21 18:43  The-End-Ronin  阅读(131)  评论(0)  编辑  收藏  举报