MySQL(Maria) Master Slave Replication

Posted on

主機A (Master)

Mater my.cnf 設定

在 [mysqld] 底下設定參數

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[mysqld]
# 主機 1
server-id=1
# 要同步的Database (可多個)
binlog-do-db=[DB Name]
# Binary log 只保留三天
expire_logs_days=3
# Log
relay-log=/var/lib/mysql/mysql-relay-bin
relay-log-index=/var/lib/mysql/mysql-relay-bin.index
log-error=/var/lib/mysql/mysql.err
master-info-file=/var/lib/mysql/mysql-master.info
relay-log-info-file=/var/lib/mysql/mysql-relay-log.info
log-bin=/var/lib/mysql/mysql-bin

進入 DB 設定

1
mysql -u root -p

建立要讓 Slave 連線的帳號

1
2
3
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;

查詢 Master 狀態

1
show master status;
1
2
3
4
5
6
+------------------+----------+----------------+------------------+
| File             | Position | Binlog_Do_DB   | Binlog_Ignore_DB |
+------------------+----------+----------------+------------------+
| mysql-bin.000001 |      832 | [DB Name]      |                  |
+------------------+----------+----------------+------------------+
1 row in set (0.00 sec)

記下 File、Position，要設定到 Slave 主機

主機B (Slave)

Mater my.cnf 設定

在 [mysqld] 底下設定參數

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[mysqld]
# 主機 2
server-id=2
# 要同步的Database (可多個)
replicate-do-db=[DB Name]
# Binary log 只保留三天
expire_logs_days=3
# Log
relay-log=/var/lib/mysql/mysql-relay-bin
relay-log-index=/var/lib/mysql/mysql-relay-bin.index
log-error=/var/lib/mysql/mysql.err
master-info-file=/var/lib/mysql/mysql-master.info
relay-log-info-file=/var/lib/mysql/mysql-relay-log.info
log-bin=/var/lib/mysql/mysql-bin

進入 DB 設定

1
mysql -u root -p

停止 Slave

1
stop slave;

設定 Slave

1
CHANGE MASTER TO MASTER_HOST='[MasterIP]', MASTER_USER='[MasterUser]', MASTER_PASSWORD='[MasterPassword]', MASTER_LOG_FILE='[MasterLogFullFile]', MASTER_LOG_POS=[MasterPosition];

開啟 Slave

1
start slave;

查詢 Slave 狀態

1
show slave status \G;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: [Master Host IP]
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 545
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 769
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: [DB Name]
          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: 545
              Relay_Log_Space: 1067
              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: NULL
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: 1
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: No
                  Gtid_IO_Pos:
      Replicate_Do_Domain_Ids:
  Replicate_Ignore_Domain_Ids:
                Parallel_Mode: conservative
1 row in set (0.00 sec)

Slave_IO_Running、Slave_SQL_Running 需為 Yes 才是正常

注意

當 Master or Slave 有做重啟動作時，需回到 Slave 查看 Slave 狀態

1
show slave status \G;