设置主从前先创建作为同步数据的用户,可直接在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: 33.134.243.92 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为正常
标签:Log,show,mysql8,192.168,节点,SSL,master,Master,主从 From: https://www.cnblogs.com/lovelp0327/p/17341424.html