資料庫系列——MySQL主從復制配置

1、背景

以前一個專案中資料庫最基礎同時也是最主流的是單機資料庫，讀寫都在一個庫中，當用戶逐漸增多，單機資料庫無法滿足性能要求時，就會進行讀寫分離改造（適用于讀多寫少），寫操作一個庫，讀操作多個庫，通常會做一個資料庫集群，開啟主從備份，一主多從，以提高讀取性能，當用戶更多讀寫分離也無法滿足時，就需要分布式資料庫，（借助MyCat、sharing-jdbc、Canal等等），螞蟻金服Oceanbase，

正常情況下讀寫分離的實作，首先要做一個一主多從的資料庫集群，同時還需要進行資料同步，這一篇記錄如何用mysql搭建一個一主多次的配置，

下一篇記錄代碼層面如何實作讀寫分離，

2、搭建一主多從資料庫集群

主從備份需要多臺虛擬機，如果用wmware完整克隆多個實體，注意直接克隆的虛擬機會導致每個資料庫的uuid相同，是需要修改為不同的uuid，

核心配置如下：

主庫配置

主資料庫（master）中新建一個用戶用于從資料庫（slave）讀取主資料庫二進制日志，sql陳述句如下：

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123456';#創建用戶
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';#分配權限
mysql>flush privileges; #重繪權限

同時修改mysql組態檔開啟二進制日志，新增部分如下：

[mysqld]
server-id=1
log-bin=master-bin #這邊寫的是對應bin的日志名稱
log-bin-index=master-bin.index

然后重啟資料庫，在主庫上使用show master status;陳述句查看主庫狀態，如下所示：

從庫配置

同樣先新增幾行配置：

[mysqld]
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin

然后重啟資料庫，使用如下陳述句連接主庫：

CHANGE MASTER TO
MASTER_HOST='192.168.226.5',
MASTER_USER='root',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=6953;

接著在從庫上運行start slave;開啟備份,正常情況如下圖所示：Slave_IO_Running和Slave_SQL_Running都為yes，

可以用這個步驟開啟多個從庫，

??默認情況下備份是主庫的全部操作都會備份到從庫，實際可能需要忽略某些庫，可以在主庫中增加如下配置：

# 不同步哪些資料庫
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema
# 只同步哪些資料庫，除此之外，其他不同步
binlog-do-db = game

實戰演示

為了方便演示我采用【win10安裝多個mysql實體】，原理是一樣的

1、下載mysql最新解壓版，這里是mysql-8.0.22

解壓，復制一份，分別新建my.ini檔案，

my.ini內容參考：注意埠的修改

[mysqld]
# 設定3306埠
port=3307
# 設定mysql的安裝目錄# 切記此處一定要用雙斜杠
basedir=D:\\DevPackTool\\InstallPack\\mysql1
# 設定mysql資料庫的資料的存放目錄
datadir=D:\\DevPackTool\\InstallPack\\mysql1\\data
# 允許最大連接數
max_connections=200
# 允許連接失敗的次數，這是為了防止有人從該主機試圖攻擊資料庫系統
max_connect_errors=10
# 服務端使用的字符集默認為UTF8
character-set-server=UTF8MB4
# 創建新表時將使用的默認存盤引擎
default-storage-engine=INNODB
# 默認使用“mysql_native_password”插件認證
default_authentication_plugin=mysql_native_password
#開啟binlog
log-bin=mysql-bin
replicate-do-db=test
server-id=3307  
[mysql]
# 設定mysql客戶端默認字符集
default-character-set=UTF8MB4

[client]
# 設定mysql客戶端連接服務端時默認使用的埠
port=3307
default-character-set=UTF8MB4

2、創建mysql

第一個mysql

cmd進入目錄：D:\DevPackTool\InstallPack\mysql1\bin，（選擇你自己的安裝路徑）需要管理員權限執行命令：

控制臺會列印出密碼，請記住初始化密碼，如果忘記把根目錄data檔案夾洗掉，重新執行上面陳述句，

繼續在D:\DevPackTool\InstallPack\mysql1\bin路徑里執行創建服務命令，將服務命名為MySQL1：

mysqld --install mysql1

啟用服務：

net start mysql1

登錄mysql：

mysql -uroot -p
輸入密碼

更改密碼：

alter user 'root'@'localhost' identified with mysql_native_password by '123456';

第二個mysql

和上面一樣的原理，之后登錄時要注意就是要加上埠號，因為默認是3306，3306已經被MySQL1占用了，MySQL2是3307，登錄時需要指定埠：

mysql -uroot -P3307 -p
輸入密碼
#注意大寫-P3307

配置參考上面的，需要在主庫和從庫上配置，

1、組態檔的配置

主庫：

從庫：

2、啟動配置

主庫配置

GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%'  ;

CREATE USER 'repl_user'@'%' IDENTIFIED BY '123456'


SHOW SLAVE STATUS

USE test

SHOW MASTER STATUS 

從庫配置：

CHANGE MASTER TO
  MASTER_HOST='127.0.0.1',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000002', 
  MASTER_LOG_POS=5905;
  
  START SLAVE;
  
  SHOW SLAVE STATUS

也可以同過命令列查看

3、測驗同步效果：