mysql 8.4 主從複製

小吉猫發表於2024-11-01
MySql

master 節點

my.cnf

server-id=1
log-bin=mysql-bin
binlog-do-db=repl  #需要同步的資料庫,如果沒有本行表示同步所有的資料庫
binlog-ignore-db=mysql

建立複製使用者

CREATE USER 'repluser'@'192.168.4.222' IDENTIFIED WITH caching_sha2_password BY RANDOM PASSWORD;
+----—----+---------------+----------------------+-------------+
|   user   |     host      | generated password   | auth_factor |
+----------+---------------+----------------------+-------------+
| repluser | 192.168.4.222 | E_FngC]q9b<Q2IggW>}6 |           1 |
+----------+---------------+----------------------+-------------+
1 row in set (0.01 sec)

授權

grant replication slave on *.* to 'repluser'@'192.168.4.222';

檢視複製點

SHOW BINARY LOG STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 |      158 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

replication 節點

my.cnf

server-id=2
log-bin= mysql-bin
relay-log= mysql-relay-bin
read-only=1
log_replica_updates=1

設定 replication 源

 CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.1.61',SOURCE_USER='repluser',SOURCE_PASSWORD='E_FngC]q9b<Q2IggW>}6',SOURCE_PORT=3306,SOURCE_LOG_FILE='mysql-bin.000007',SOURCE_LOG_POS=158,GET_SOURCE_PUBLIC_KEY=1;

啟用 replica 複製

start replica;

檢視 replica 狀態

show replica status\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 192.168.1.61
                  Source_User: repluser
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql-bin.000007
          Read_Source_Log_Pos: 158
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 328
        Relay_Source_Log_File: mysql-bin.000007
           Replica_IO_Running: Yes
          Replica_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_Source_Log_Pos: 158
              Relay_Log_Space: 539
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File: 
           Source_SSL_CA_Path: 
              Source_SSL_Cert: 
            Source_SSL_Cipher: 
               Source_SSL_Key: 
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Source_Server_Id: 100
                  Source_UUID: 1f596efb-9821-11ef-b99f-525400bb665d
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 10
                  Source_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Source_SSL_Crl: 
           Source_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Source_TLS_Version: 
       Source_public_key_path: 
        Get_Source_public_key: 1
            Network_Namespace: 
1 row in set (0.00 sec)

相關文章