mysql 架構篇系列 2 複製架構一主一從搭建(非同步複製)

一. 環境準備

　　1.1 主庫環境(172.168.18.201)

　　1.2 從庫環境(172.168.18.203)

　　1.3 主庫是用二進位安裝，從庫是用RPM安裝，下麵是查看的不同命令，腳本如下所示：

-- 主庫啟動mysql服務
service mysql start
-- 主庫連接到mysql命令
mysql -uroot -p123456 
-- 主庫查看mysql是否啟動
service mysql  status

-- 從庫啟動 mysql服務
 systemctl  start mysqld
-- 從庫連接到mysql命令
mysql -uroot -p js*2015%
-- 從庫查看mysql是否啟動
systemctl  status  mysqld

　　1.4 其它
　　　　(1) 主從伺服器相互接通對方ip。 例如在主庫上拼: ping 172.168.18.203。
　　　　(2) 使用telnet 拼通對方mysql埠。例如在從庫上拼: telnet 172.168.18.201 3306

二. 非同步複製搭建（主庫）

　　主從複製配置步驟比較簡單，下麵詳細介紹，確保主從庫上安裝了相同版本的資料庫，因為複製的功能在持續的改進中。這裡一個是5.7.23，一個是5.7.20。二個相近的版本(最好是一個版本)。

　　2.1 在主庫上，設置一個複製使用的mysql賬戶，並授予replication slave 許可權，創建主庫複製用戶rep1。(註意：需要把 'rep1'@'172.168.18.201'  改成 'rep1'@'%'   在文章最後，測試複製是否成功會講到)

-- 172.168.18.201 主庫 創建rep1 用戶
GRANT REPLICATION SLAVE ON *.* TO 'rep1'@'172.168.18.201'  IDENTIFIED BY '123456'

　　2.2 在主庫上修改伺服器配置文件my.cnf, 開啟binlog, 並設置server-id的值，之後重啟資料庫服務生效。開啟binlog日誌，詳細說明在：mysql 開發進階篇系列 33 工具篇(mysqlbinlog日誌管理工具) 中講到，在my.cnf配置如下：

[mysqld]
server-id=1
log-bin=/var/lib/mysql/mysql-bin

　　2.3 在主庫上，設置讀鎖定，這個操作為了確保沒有資料庫操作，以便獲得一個一致性的快照。

 -- 鎖住當前主庫, 其它會話增刪改等修改操作將會阻塞
FLUSH TABLES WITH READ LOCK;

　　2.4 得到主庫當前的二進位日誌名和偏移量值，這個操作是為了在從庫上啟動以後，從這個點開始進行數據的恢復

 SHOW MASTER STATUS

　　2.5 現在主資料庫已經停止更新操作了，需要生成主資料庫的備份，備份可以直接在操作系統下複製全部的數據到從庫伺服器上，也可以通過mysqldump或xtrabackup 工具進行資料庫備份。下麵使用xtrabackup來克隆到slave。下麵在主庫上進行備份。

-- 先刪除以前歷史備份
[root@hsr base]# rm -rf /data/backups/base
 -- 完整備份

[root@hsr base]# xtrabackup --host=127.0.0.1 --user bkpuser --password=123456 --backup --target-dir=/data/backups/base

　　2.6 備份結束後，恢復主庫寫操作

UNLOCK TABLES;

　　2.7 將備份文件複製到從庫上

　　　　可以使用rsync命令來實現文件拷貝，我這裡簡單起見使用xtfp來傳送文件。同樣在從庫上也建相同的目錄，主庫數據文件拷貝到從庫目錄後，如下圖所示

三. 非同步複製搭建(從庫)

　　3.1  數據還原，使主庫和從庫的資料庫和表保持一致，從庫是一個相同的副本

　　　　在從庫上安裝好xtrabackup工具，配置好mysql備份用戶，腳本如下，可參照：mysql 開發進階篇系列 45 物理備份與恢復(xtrabackup 安裝，用戶許可權，配置)

(1) -- 從庫 203配置mysql備份用戶腳本如下
CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY '12345678';
GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
GRANT PROCESS,SUPER ON *.* TO 'bkpuser'@'localhost';

(2) -- 數據還原到從庫,從庫data目錄是 /var/lib/mysql，按下麵腳本順序操作
-- 停掉從庫服務
[root@xuegod64 mysql]# systemctl  stop mysqld
-- 將mysql文件重命名為mysqlbak文件(備份)
[root@xuegod64 mysql]# mv /var/lib/mysql /var/lib/mysqlbak
-- 數據還原到從庫
[root@xuegod64 lib]# xtrabackup  --defaults-file=/etc/my.cnf  --host=127.0.0.1  --user bkpuser --password=12345678 --copy-back 
--target-dir=/data/backups/base

　　　　此時數據已還原，如下圖所示 ：

（3）-- 下麵將mysql文件的屬主從root改為mysql
[root@xuegod64 lib]# chown -R mysql.mysql /var/lib/mysql
[root@xuegod64 lib]# chmod 770 /var/lib/mysql

　　3.2 配置從庫 my.cnf

　　　　修改從庫配置文件，增加server-id 參數，註意server-id的值必須唯一，不能和主資料庫的配置相同，如果有多個從伺服器，每個從伺服器必須有唯一的server-id值。

 [mysqld]
server-id=2

　　3.3 在從庫上，使用skip-slave-start選項啟動從資料庫，這樣不會立即啟動從庫的複製進程，目的方便下一步配置

[mysqld]
server-id=2
skip-slave-start=true

-- 啟動服務
[root@xuegod64 lib]# systemctl  start  mysqld

　　3.4 對從庫做相應的配置，指定複製使用的用戶，主資料庫伺服器ip, 埠，以及開始執行複製的日誌文件和位置等，腳本如下：

CHANGE MASTER TO 
MASTER_HOST='172.168.18.201',
MASTER_PORT=3306,
MASTER_USER='rep1',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000070',
MASTER_LOG_POS=785

　　3.5 從庫上啟動slave線程

START SLAVE

　　3.6 查看是否配置正確(從庫上)

SHOW PROCESSLIST

　　　　如下圖所示：表明slave已經連接上master, 並開始接受並執行日誌。

四. 驗證測試複製　

　　4.1  測試複製服務的正確性。 下麵在主資料庫上執行一個更新操作，觀察是否在從庫上同步。

-- 在主庫上更新數據
SELECT * FROM testbackup WHERE id=12
UPDATE testbackup SET `name`='小李子' WHERE id=12

　　4.2 查看從庫testbackup表

-- 數據沒有更新過來，檢查從庫複製線程狀態
SHOW SLAVE STATUS

　　　　錯誤提示：在last_io_error中出現: error connecting to master '[email protected]:3306' - retry-time: 60  retries: 10

　　4.3 測試從庫203上連接主庫201，結果連接失敗

[root@xuegod64 lib]# mysql -urep1 -h 172.168.18.201 -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'rep1'@'aca812cb.ipt.aol.com' (using password: YES)

--解決方法：在主庫將rep1的host 值從172.168.18.201改為 %
   UPDATE mysql.user SET `Host`='%' WHERE `User`='rep1'
    FLUSH PRIVILEGES
-- 再測試從庫203上連接主庫201, 結果連接成功
[root@xuegod64 lib]# mysql -urep1 -h 172.168.18.201 -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 180

-- 從庫203上停止，再重啟slave線程。
STOP SLAVE
START SLAVE

　　　　最終在從庫203查看數據是否已經同步， 結果已同步