Centos8.3、mysql8.0主從複製實戰記錄

時光城主發表於2021-06-21
CentOSMySql

引言

     最近又上線了一個專案，感覺自己這段時間收穫不少就想把自己做這個專案用的技術總結梳理一下。這個專案是我自己發起，領導們不是特別重視所以得到資源有限，資源有限的情況我只能選擇手動搭建資料庫環境，資源充足的情況下還是推薦使用類似PolarDB這種雲資料庫。原因很多，穩定性好、使用維護簡單、擴容方便、還提供一系列的sql審計工具。 

環境準備

號

環境和軟體

版本

數量

1

centos

8.3

3

2

mysql

8.0.21

3

 

安裝命令

yum -y install mysql-server                     # 安裝mysql -y 的意思就是安裝過程如果需要輸入y 可以不用在輸入。
systemctl enable mysqld.service                 # 設定為開啟自啟
systemctl start mysqld.service                  # 開啟mysql服務

   vim  /etc/my.cnf                                        # 編輯mysql配置檔案 預設安裝是在/etc目錄下 

   skip-grant-tables --shared-memory           # 設定跳過密碼 新增到[service-clinet]標籤下如下圖：

 

  mysql -u root -p                                     # 輸入命令 然後Enter鍵跳過密碼登入

  ALTER USER 'root'@'localhost' IDENTIFIED BY 'jishuzhai';  #新增root使用者和密碼 注意localhost意味這不能遠端登入可以換成% 這裡不推薦 建議建立新的賬號遠端訪問

  再次編輯 /etc/my.cnf檔案註釋掉剛才跳過密碼那句話

  systemctl restart mysqld.service # 重啟mysql服務

mysql安裝注意事項

   安裝命令這裡只供參考，不過個人推薦使用yum命令安裝，這樣簡單快捷而且方便擴容，不過有一點在生產環境要注意，那就是磁碟初始容量一定要大，最好是冗餘的，即使初始化資料容量很小。主從會產生大量的日誌檔案，磁碟容量很小很容易導致磁碟被寫滿，那個時候再去擴容需要停機維護。另外資料庫備份推薦備份目錄為資料盤並且主從資料庫都備份。

建立遠端訪問的賬號

CREATE USER 'proxysql'@'%' IDENTIFIED BY 'jishuzhai'; #建立賬號
GRANT ALL PRIVILEGES ON *.* TO 'proxysql'@'%' WITH GRANT OPTION; # 賦予許可權
flush privileges; # 重新整理許可權

注意 讀取賬號只需要主資料庫一個就可以了，但是這裡為了下一步使用proxysql做讀寫分離 所以每一個資料庫例項都建立了一個賬號。

建立測試資料庫

CREATE DATABASE brief_test;# 建立資料庫

CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `service_name` varchar(50) NOT NULL COMMENT '自增',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; # 建立測試表

INSERT INTO `test` VALUES (1, 'master');
INSERT INTO `test` VALUES (2, 'slave1');
INSERT INTO `test` VALUES (3, 'slave2'); # 新增測試資料

 

注意在主庫上建立 然後同步到所有從資料庫 主從開始前要保證資料一致 不然會繫結失敗。

編輯主從資料庫配置

登入到主資料的伺服器編輯資料庫配置檔案

將以下配置新增到/etc/my.conf 檔案中 如下圖

[mysqld]
#配置主庫
server_id=1              #伺服器id 不能重複 1,2,3,4這種以此類推
log_bin=master-bin
log_bin-index=master-bin.index
binlog-do-db=brief_test  #需要同步的資料庫名稱 配置多個資料庫需要配置多個binlog-do-db 例如 binlog-do-db=brief_test1 binlog-do-db=brief_test2 binlog-do-db=brief_test3 這種方式配置

 

systemctl restart mysqld.service#重啟mysql服務

登入到從資料庫將以下配置新增到/etc/my.conf檔案中如下圖

[mysqld]
server_id=2    #服務編號不能重複
read_only=on   #設定為只讀

systemctl restart mysqld.service #重啟mysql服務

最後一步配置主從 

（1）登入主資料庫 執行 show master status 如下圖

記錄file 和 position的值從資料庫繫結要用的到

 

（2）登入從資料庫執行：

CHANGE MASTER TO MASTER_HOST='172.16.102.7', #主資料庫ip
MASTER_PORT=3306,                            #主資料庫埠
MASTER_USER='proxysql',                      #主資料庫複製賬號
MASTER_PASSWORD='jishuzhai',                 #主資料庫密碼
MASTER_LOG_FILE='master-bin.000001',         #剛才從master資料庫查詢file值
MASTER_LOG_POS=156;                          #剛才從master資料庫查詢postition值

然後執行

start slave; #開啟slave

然後執行 

show slave status;#檢視slave狀態

正確結果如下圖：

常見問題：

    show slave status結果slave_io_running 和 slave_sql_running值均為No或者一個為no，我遇到四種情況。

    第一種情況是server_id 重複。Mysql 8.0 的 server_id 必須在[mysql]標籤下 而且必須在[clinet-servser]配置之前才生效，結果就導致我明明配置server_id 卻還是連線失敗。針對這種情況可以使用

    select @@server_id #查詢資料庫的server_id 可以看到當前的id

   第二種情況是uuid重複 使用虛擬機器安裝好資料庫 然後克隆出slave1、slave2 這種情況uuid也是重複的 導致連線失敗。針對這種情況可以手動修改

   vim  /var/lib/mysql/auto.cnf 如下圖：

   

   隨便更改一個字元保持uuid 不重複即可。

   第三種情況 主從資料庫不一致 主要表現某個應用連線到從資料庫並且寫入了資料 從資料庫雖然設定了只讀 但是root許可權的賬號可以寫入。這種情況最麻煩但是好處理。停止所有應用或者設定主資料庫鎖住 然後把主資料同步到從資料庫然後重新連線。

第四種情況 事物回滾導致的 這種情況比少但是好處理

使用以下命令:

STOP SLAVE #先執行關閉語句
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;#重置值
START SLAVE #開啟語句

 

關於清理binary日誌

 主從會產生大量的日誌檔案 可以使用一下方式自動清理 

show binary logs;#查詢日誌
set GLOBAL binlog_expire_logs_seconds = 1296000 #設定日誌過期時間為15天單位秒 超過15的會被自動清除
SELECT @@binlog_expire_logs_seconds #查詢是否生效
flush logs;# 立即生效

 

相關文章