Tony 老師搭建 MySQL 主從伺服器

來杯可樂不加糖發表於2019-07-30
MySql伺服器

目的

  • 如果mysql主伺服器的訪部量較大，可以通過複製資料，在從伺服器 進行查詢操作，從而降低主伺服器的訪問壓力
  • 同時從伺服器作為主 伺服器的備份，可以避免主伺服器因為故障導致資料丟失的問題。

方式

  • 基於日誌點的複製
  • 基於GTID的複製

主從複製：（基於日誌點複製）

1、在主DB伺服器上建立複製賬號
* CREATE USER repl@'192.168.71.%' identified by 'password'; #建立，建議從伺服器IP段
* GRANT REPLICATION SLAVE ON *.* TO repl @'192.168.71.%';   #授權
2、配置主資料庫伺服器      # vim /etc/my.cnf
* bin_log = mysql-bin    #可指定二進位制日誌存放目錄，目錄需開啟寫入許可權
* server_id = 100        #叢集中唯一，如主機IP後幾段
* #配置完重啟
3、 配置從資料庫伺服器           #vim /etc/my.cnf
* bin_log = mysql-bin          #可指定二進位制日誌存放目錄，目錄需開啟寫入許可權
* server_id = 101              #叢集中唯一，如主機IP後幾段
* relay_log = mysql-relay-bin  #預設主機名，需指定名字
* log_slave_updates = on       #是否開啟relay_log寫入從伺服器bin_log中 叢集時做主伺服器時需要配置 [可選]
* read_only = on #安全配置引數，阻止沒有任何沒有sever許可權的使用者進行寫操作，並且可以保證從伺服器中由於程式錯誤導致的寫操作不會發生 [可選]
* #配置完重啟
4、同步資料
* flush tables with read lock; //鎖表
* mysqldump --single-transaction --master-data=2 --triggers --routines cron -uroot -p >>log.sql //匯出資料
* vim log.sql 在首行中加入：use cron;
* scp all.sql root@192.168.71.244:/root //將檔案備份到從伺服器root目錄下【若未安裝，則安裝】
* mysql -uroot -p < log.sql //匯入資料
* unlock tables // 備份完成後，解除全域性讀鎖定
**5、啟動複製鏈路**
* CHANGE MASTER TO MASTER_HOST = `master_host_ip`, MASTER_USER = `repl`,MASTER_PASSWORD=`123456`,MASTER_LOG_FILE=`mysql_log_file_name`,MASTER_LOG_POS= 4;
* #注：mysql_log_file_name,MASTER_LOG_POS 在log.sql中檢視【也可檢視master status:master_log_file，master_log_pos】
6、啟動slave
* start slave
7、檢視狀態
* show slave status\G
8、常用操作：
- stop salve   #停止slave連路
- reset slave  #重置slave連路
- start slave  #開啟slave連路
- stop master  #停止master連路
- reset master #重置master連路
- start master #開啟master連路
9、常見問題彙總：
* 若需change 某引數
* 首先，stop slave，其次，change master to '引數' = '值'
* 設定防火牆（主從均需要允許3306埠）
* window：新增防火牆規則
* linux：（centos7）
    systemctl status firewalld //系統中應先判斷firewalld 是否在執行
    firewall-cmd --list-all //檢視所有埠
    firewall-cmd --permanent --zone=public --add-port=3306/tcp //放行3306
    firewall-cmd --reload //使規則生效
    若出現unit is masked 則 systemctl unmask firewalld
* Q：Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active.
* A：change master to master_auto_position=0;

主從複製：（基於GTID複製）

1、在主DB伺服器上建立複製賬號
* CREATE USER repl@'192.168.71.%' identified by 'password';   #建立，建議從伺服器IP段
* GRANT REPLICATION SLAVE ON *.* TO repl @'192.168.71.%';     #授權
2、配置主資料庫伺服器    #vim /etc/my.cnf
* bin_log = mysql-bin  #可指定二進位制日誌存放目錄，目錄需開啟寫入許可權
* server_id = 100      #叢集中唯一，如主機IP後幾段
* gtid_mode = on  
* enforce_gtid_consistency = on
* log-slave-updates = on
* 配置完重啟
3、 配置從資料庫伺服器            #vim /etc/my.cnf
* bin_log = mysql-bin           #可指定二進位制日誌存放目錄，目錄需開啟寫入許可權
* server_id = 101               #叢集中唯一，如主機IP後幾段
* relay_log = mysql-relay-bin   #預設主機名，需指定名字
* log_slave_updates = on        #是否開啟relay_log寫入從伺服器bin_log中 叢集時做主伺服器時需要配置 [可選]
* read_only = on                #安全配置引數，阻止沒有任何沒有sever許可權的使用者進行寫操作，並且可以保證從伺服器中由於程式錯誤導致的寫操作不會發生 [可選]
* gtid_mode = on
* enforce_gtid_consistency = on
* master_info_repository = TABLE [建議] #預設存在檔案中，主伺服器與從伺服器連線資訊與中繼日誌資訊的儲存方式
* relay_log_info_repository = TABLE [建議]
* 配置完重啟
4、同步資料
* 同上
5、啟動複製鏈路
* CHANGE MASTER TO MASTER_HOST = `master_host_ip`, MASTER_USER = `repl`,MASTER_PASSWORD=`123456`,MASTER_AUTO_POSTITION = 1;
**6、啟動slave**
* start slave
**7、檢視狀態**
* show slave status\G

主主複製：

1、配置第一臺主DB
* server-id = 1
* log-bin=/var/lib/mysql/mysql-bin
* auto_increment_increment = 2
* auto_increment_offset = 1
* #配置完重啟
2、配置另一臺DB
* server-id = 3
* log-bin=/var/lib/mysql/mysql-bin
* auto_increment_increment = 2
* auto_increment_offset = 2
* 配置完重啟
3、建立使用者並授權
* 登入MySQL mysql -uroot -p
* 建立使用者：CREATE USER repl@'192.168.71.%' identified by 'password';
* 授權:GRANT REPLICATION SLAVE ON *.* TO repl @'192.168.71.%';
* Q:若遇到 your password does not satisfy the current policy requirements
* A: set global validate_password_policy=0;
*    set global validate_password_length=1;
4、資料同步
* flush tables with read lock;  #鎖表
* mysqldump --single-transaction --master-data = 2 --triggers --routines cron -uroot -p >> log.sql #匯出資料
* vim log.sql 在首行中加入：use cron;
* scp all.sql root@192.168.71.244:/root  #將檔案備份到從伺服器root目錄下【若未安裝，則安裝】
* mysql -uroot -p < log.sql  #匯入資料
* unlock tables  #備份完成後，解除全域性讀鎖定
5、建立關係（第二臺關聯第一臺）
* 登入第一臺MySQL
* show master status  #檢視狀態【可檢視master_log_file，master_log_pos】
* 登入第二臺MySQL
* change master to
* master_host='192.168.71.37', # 主節點
* master_port=3306, #主節點的埠號
* master_user='repl', # 賬號
* master_password='repl', # 密碼
* master_log_file='mysql-bin.000002', # show master status 對應的的日誌
* master_log_pos=2072; # show master status 對應的
* start slave #啟動庫
6、建立關係（第一臺關聯第二臺）
* 登入第二臺MySQL
* show master status #檢視狀態【可檢視master_log_file，master_log_pos】
* 登入第一臺MySQL
* change master to
* master_host='192.168.71.244', # 主節點
* master_port=3306, #主節點的埠號
* master_user='repl', # 賬號
* master_password='repl', # 密碼
* master_log_file='mysql-bin.000001', # show master status 對應的的日誌
* master_log_pos=702; # show master status 對應的
* start slave #啟動庫
* #日誌複製：若master 重啟，則日誌點發生改變，需要重新配置slave日誌點和位置,故筆者建議配置為GTID
本作品採用《CC 協議》，轉載必須註明作者和本文連結

來杯可樂不加糖。

相關文章