CentOS6.4系統MySQL主從複製基本配置實踐

shiyanjuncn發表於2016-04-13
CentOSMySql

對於MySQL資料庫一般用途的主從複製，可以實現資料的備份（如果希望在主節點失效後，能夠使從節點自動接管，就需要更加複雜的配置，這裡暫時先不考慮），如果主節點出現硬體故障，資料庫伺服器可以直接手動切換成備份節點（從節點），繼續提供服務。基本的主從複製配置起來非常容易，這裡我們做個簡單的記錄總結。
我們選擇兩臺伺服器來進行MySQL的主從複製實踐，一臺m1作為主節點，另一臺nn作為從節點。
兩臺機器上都需要安裝MySQL資料庫，如果想要卸掉預設安裝的，可以執行如下命令：

1 sudo rpm -e --nodeps mysql
2 yum list | grep mysql

現在可以在CentOS 6.4上直接執行如下命令進行安裝：

1 sudo yum install -y mysql-server mysql mysql-deve

為root使用者設定密碼：

1 mysqladmin -u root password `shiyanjun`

然後可以直接通過MySQL客戶端登入：

1 mysql -u root -p

主節點配置

首先，考慮到資料庫的安全，以及便於管理，我們需要在主節點m1上增加一個專用的複製使用者，使得任意想要從主節點進行復制從節點都必須使用這個賬號：

1 CREATE USER repli_user;
2 GRANT REPLICATION SLAVE ON *.* TO `repli_user`@`%` IDENTIFIED BY `shiyanjun`;

這裡還進行了操作授權，使用這個換用賬號來執行叢集複製。如果想要限制IP端段，也可以在這裡進行配置授權。
然後，在主節點m1上，修改MySQL配置檔案/etc/my.cnf，使其支援Master複製功能，修改後的內容如下所示：

01 [mysqld]
02 datadir=/var/lib/mysql
03 socket=/var/lib/mysql/mysql.sock
04 user=mysql
05 # Disabling symbolic-links is recommended to prevent assorted security risks
06 symbolic-links=0
07 server-id=1
08 log-bin=m-bin
09 log-bin-index=m-bin.index
10
11 [mysqld_safe]
12 log-error=/var/log/mysqld.log
13 pid-file=/var/run/mysqld/mysqld.pid

server-id指明主節點的身份，從節點通過這個server-id來識別該節點是Master節點（複製架構中的源資料庫伺服器節點）。
如果MySQL當前已經啟動，修改完叢集複製配置後需要重啟伺服器：

1 sudo service mysqld restart

從節點配置

接著，類似地進行從節點nn的配置，同樣修改MySQL配置檔案/etc/my.cnf，使其支援Slave端複製功能，修改後的內容如下所示：

01 [mysqld]
02 datadir=/var/lib/mysql
03 socket=/var/lib/mysql/mysql.sock
04 user=mysql
05 # Disabling symbolic-links is recommended to prevent assorted security risks
06 symbolic-links=0
07 server-id=2
08 relay-log=slave-relay-bin
09 relay-log-index=slave-relay-bin.index
10
11 [mysqld_safe]
12 log-error=/var/log/mysqld.log
13 pid-file=/var/run/mysqld/mysqld.pid

同樣，如果MySQL當前已經啟動，修改完叢集複製配置後需要重啟伺服器：

1 sudo service mysqld restart

然後，需要使從節點nn指向主節點，並啟動Slave複製，執行如下命令：

1 CHANGE MASTER TO MASTER_HOST=`m1`, MASTER_PORT=3306, MASTER_USER=`repli_user`, MASTER_PASSWORD=`shiyanjun`;
2 START SLAVE;

驗證叢集複製

這時，可以在主節點m1上執行相關操作，驗證從節點nn同步複製了主節點的資料庫中的內容變更。
如果此時，我們已經配置好了主從複製，那麼對於主節點m1上MysQL資料庫的任何變更都會複製到從節點nn上，包括建庫建表、插入更新等操作，下面我們從建庫開始：
在主節點m1上建庫建表：

01 CREATE DATABASE workflow;
02 CREATE TABLE `workflow`.`project` (
03 `id` int(11) NOT NULL AUTO_INCREMENT,
04 `name` varchar(100) NOT NULL,
05 `type` tinyint(4) NOT NULL DEFAULT `0`,
06 `description` varchar(500) DEFAULT NULL,
07 `create_at` date DEFAULT NULL,
08 `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP,
09 `status` tinyint(4) NOT NULL DEFAULT `0`,
10 PRIMARY KEY (`id`)
11 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

在m1上檢視binlog內容，執行命令：

1 SHOW BINLOG EVENTSG

binlog內容內容如下所示：

01 *************************** 1. row ***************************
02 Log_name: m-bin.000001
03 Pos: 4
04 Event_type: Format_desc
05 Server_id: 1
06 End_log_pos: 106
07 Info: Server ver: 5.1.73-log, Binlog ver: 4
08 *************************** 2. row ***************************
09 Log_name: m-bin.000001
10 Pos: 106
11 Event_type: Query
12 Server_id: 1
13 End_log_pos: 197
14 Info: CREATE DATABASE workflow
15 *************************** 3. row ***************************
16 Log_name: m-bin.000001
17 Pos: 197
18 Event_type: Query
19 Server_id: 1
20 End_log_pos: 671
21 Info: CREATE TABLE `workflow`.`project` (
22 `id` int(11) NOT NULL AUTO_INCREMENT,
23 `name` varchar(100) NOT NULL,
24 `type` tinyint(4) NOT NULL DEFAULT `0`,
25 `description` varchar(500) DEFAULT NULL,
26 `create_at` date DEFAULT NULL,
27 `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
28 `status` tinyint(4) NOT NULL DEFAULT `0`,
29 PRIMARY KEY (`id`)
30 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
31 3 rows in set (0.00 sec)

通過上述binlog內容，我們大概可以看到MySQL的binlog都記錄那些資訊，一個事件對應一行記錄。這些記錄資訊的組織結構如下所示：

  • Log_name：日誌名稱，指定的記錄操作的binlog日誌名稱，這裡是m-bin.000001，與我們前面在/etc/my.cnf中配置的相對應
  • Pos：記錄事件的起始位置
  • Event_type：事件型別
  • End_log_pos：記錄事件的結束位置
  • Server_id：伺服器標識
  • Info：事件描述資訊

然後，我們可以檢視在從節點nn上覆制的情況。通過如下命令檢視從節點nn上資料庫和表的資訊：

1 SHOW DATABASES;
2 USE workflow;
3 SHOW TABLES;
4 DESC project;

我們再看一下執行插入語句的情況。在主節點m1上執行如下SQL語句：

1 INSERT INTO `workflow`.`project` VALUES(1, `Avatar-II`, 1, `Avatar-II project`, `2014-02-16`, `2014-02-16 11:09:54`, 0);

可以在從節點上執行查詢，看到從節點nn上覆制了主節點m1上執行的INSERT語句的記錄：

1 SELECT * FROM workflow.project;

驗證複製成功。

複製常用命令

下面，我們總結了幾個在MySQL主從複製場景中常用到的幾個相關命令：

  • 終止主節點複製
1 STOP MASTER;
  • 清除主節點複製檔案
1 RESET MASTER;
  • 終止從節點複製
1 STOP SLAVE;
  • 清除從節點複製檔案
1 RESET SLAVE;
  • 檢視主節點複製狀態
1 SHOW MASTER STATUSG;

結果示例：

1 *************************** 1. row ***************************
2 File: m-bin.000001
3 Position: 956
4 Binlog_Do_DB:
5 Binlog_Ignore_DB:
6 1 row in set (0.00 sec)
  • 檢視從節點複製狀態
1 SHOW SLAVE STATUSG;

結果示例：

01 *************************** 1. row ***************************
02 Slave_IO_State: Waiting for master to send event
03 Master_Host: m1
04 Master_User: repli_user
05 Master_Port: 3306
06 Connect_Retry: 60
07 Master_Log_File: m-bin.000001
08 Read_Master_Log_Pos: 956
09 Relay_Log_File: slave-relay-bin.000002
10 Relay_Log_Pos: 1097
11 Relay_Master_Log_File: m-bin.000001
12 Slave_IO_Running: Yes
13 Slave_SQL_Running: Yes
14 Replicate_Do_DB:
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: 956
24 Relay_Log_Space: 1252
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 1 row in set (0.00 sec)
  • 檢視BINLOG列表
1 SHOW BINARY LOGSG


相關文章