Sharding-Jdbc學習筆記一之讀寫分離mysql安裝篇

1. mysql相關配置

1.1 安裝

安裝一主兩從， 使用docker desktop版本安裝

先隨便安裝一個自己想要版本的myql映象，主要目的是將該版本映象相關的配置檔案拷貝出來，因為我們要採用目錄掛載的方式，但是手裡又沒有配置檔案和資料目錄，那就要用人家已經寫好的，然後進去將我們想要的檔案拷貝到宿主機上

# 預設拉取最新版本的mysql
docker pull mysql
 
# 啟動容器載入映象
docker run -itd --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql
 
# 進入容器
docker exec -it mysql /bin/bash
 
# 容器內部連線測試mysql，輸入密碼成功後則說明安裝成功
mysql -u root -p

在本機建立存放一主兩從mysql服務的資料目錄和配置檔案目錄, mysql目錄為master, mysqls1和mysqls2為兩個slave

# 建立本地掛載目錄
mkdir E:\docker\mysql\data && mkdir E:\docker\mysql\conf &&
mkdir E:\docker\mysqls1\data && mkdir E:\docker\mysqls1\conf &&
mkdir E:\docker\mysqls2\data && mkdir E:\docker\mysqls2\conf

將上面安裝好的mysql容器裡的配置檔案拷貝出來, 首先要確定配置檔案所在容器中的目錄，這一點可以從dockerfile中檢視， 到dockerhub上查詢指定版本的映象

確定好目錄之後執行拷貝動作，拷貝完成後，原容器就沒用了，然後停掉並且刪除它即可

# 將mysql這個容器的配置檔案和資料目錄拷貝出來,可以看下映象對應的docker file，查出原始檔案所在目錄
docker cp mysql:/var/lib/mysql E:\docker\mysql\data &&
docker cp mysql:/etc/mysql/ E:\docker\mysql\conf &&
docker cp mysql:/var/lib/mysql E:\docker\mysqls1\data &&
docker cp mysql:/etc/mysql/ E:\docker\mysqls1\conf &&
docker cp mysql:/var/lib/mysql E:\docker\mysqls2\data &&
docker cp mysql:/etc/mysql/ E:\docker\mysqls2\conf
 
# 停止並刪除容器
docker stop mysql
docker rm mysql

掛載本地目錄，以預設配置建立三個mysql容器，分別進入容器驗證是否執行成功

#######################################################################################
# 修改my.conf，更改每個mysql服務的埠號，雖然我們已經做了埠對映，可以保證在容器內每個服務依然用3306，
# 但是不知道為啥，後面對映好之後命令列可以進入，navicat會報錯Lost connection to MySQL server at ‘reading initial communication packet。然後還是修改了容器內服務自己的埠號
mysqls1 my.conf port=3307
mysqls2 my.conf port=3308
#######################################################################################
 
# 由於是在本地演示，只有一個主機，而且需要slave容器內連線master, 所以需要給每個容器分配固定ip，方便容器互聯
# 建立橋接網路
docker network create --driver=bridge --subnet=172.20.12.0/16 mynet
 
# 掛載本地目錄重新構建容器
docker run -itd --name mysql --net mynet --ip 172.20.12.1 -v E:\docker\mysql\data\mysql:/var/lib/mysql -v E:\docker\mysql\conf\mysql:/etc/mysql/ -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql
 
#  啟動第二個服務
docker run -itd --name mysqls1 --net mynet --ip 172.20.12.2 -v E:\docker\mysqls1\data\mysql:/var/lib/mysql -v E:\docker\mysqls1\conf\mysql:/etc/mysql/ -p 3307:3307 -e MYSQL_ROOT_PASSWORD=123456 mysql
#  啟動第三個服務
docker run -itd --name mysqls2 --net mynet --ip 172.20.12.3 -v E:\docker\mysqls2\data\mysql:/var/lib/mysql -v E:\docker\mysqls2\conf\mysql:/etc/mysql/ -p 3308:3308 -e MYSQL_ROOT_PASSWORD=123456 mysql
 
# 進入容器並登陸驗證是否安裝成功
docker exec -it mysql /bin/bash
mysql -uroot -p
 
docker exec -it mysqls1 /bin/bash
mysql -uroot -p
 
docker exec -it mysqls1 /bin/bash
mysql -uroot -p

如果想要更改mysql的配置，則只要修改宿主機即本機目錄下的my.conf目錄即可

• mysql ======> 配置目錄: E:\docker\mysql\conf 資料目錄: E:\docker\mysql\data
• mysqls1 ====> 配置目錄: E:\docker\mysqls1\conf 資料目錄: E:\docker\mysqls1\data
• mysqls2 ====> 配置目錄: E:\docker\mysqls2\conf 資料目錄: E:\docker\mysqls2\data

到這裡基本的安裝就完成了，關於主從同步的配置以及需要更改的配置還有坑，則在下面的響應步驟中再細講

使用Navicat客戶端去連線服務的時候報錯authentication plugin 'caching_sha2_password'

## 進入mysql容器
docker exec -it mysql /bin/bash
 
## 連線到Mysql
mysql -u root -p
 
## 檢視當前密碼規則, 可以看到為caching_sha2_password
use mysql;
select user,plugin from user where user='root';
 
## 更改密碼外掛規則，並重新指定密碼, root@後面連的是主機，主機是%則所有主機都會被更改規則
alter user 'root'@'%' identified with mysql_native_password by '123456';
# 還是更改密碼規則，但是%是不包含localhost的，所以要再授權一下localhost
alter user 'root'@'localhost' identified with mysql_native_password by '123456';
 
## 可以再次檢視密碼規則，已經看到被更改過來了
select user,plugin from user where user='root';
 
## 重新整理許可權
FLUSH PRIVILEGES;
 
# 修改另外兩個的步驟和上面一樣，只是要進入各自不同的容器

1.2 主從配置檔案設定

1.2.1. master伺服器配置

在mysql的配置檔案，my.cnf或my.ini下加入如下內容，在本例中即E:\docker\mysql\conf\my.conf

# 設定服務id，主從不能一致
server-id=1
# 開啟binlog日誌
log-bin=mysql-bin
# 設定binlog最多儲存7天
expire_logs_days=7
# STATEMENT模式 預設值。每一條會修改資料的sql語句會記錄到binlog中,優點是並不需要記錄每一條sql語句和每一行的數#                 據變化，減少了binlog日誌量，節約IO，提高效能
# ROW模式 不記錄每條sql語句的上下文資訊，僅需記錄哪條資料被修改了，修改成什麼樣了,缺點是會產生大量的日誌
# MIXED模式(以上兩種模式混合使用),一般的複製使用STATEMENT模式儲存binlog，對於STATEMENT模式無法複製的操作使用#        ROW模式儲存binlog，MySQL會根據執行的SQL語句選擇日誌儲存方式。
binlog_format=MIXED
# 設定需要同步的資料庫
binlog-do-db=boot-quick
# 遮蔽系統庫同步
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema

1.2.2. 修改從伺服器配置檔案

修改兩個mysql從伺服器的配置檔案，my.cnf或my.ini下加入如下內容,在本例中即E:\docker\mysqls1\conf\my.conf, E:\docker\mysqls2\conf\my.conf

#########################E:\docker\mysqls1\conf\my.conf#######################
# 開啟日誌
log-bin=mysql-bin
# 設定服務id，主從不能一致
server-id=2
# 設定需要同步的資料庫
replicate_wild_do_table=boot-quick.%
# 遮蔽系統庫同步
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
 
#########################E:\docker\mysqls2\conf\my.conf#######################
# 開啟日誌
log-bin=mysql-bin
# 設定服務id，主從不能一致
server‐id=3
# 設定要忽略的表
replicate-ignore-db=boot-quick.user_article
replicate-ignore-db=boot-quick.user_article_1
# 設定需要同步的資料庫
replicate_wild_do_table=boot-quick.%
# 遮蔽系統庫同步
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%

1.23. 重啟所有伺服器

1.3. 主從同步配置

1.3.1 在主庫建立用於同步的賬號

非常重要的一個要注意的點

在實際專案中，如果要讀寫分離，必然是多臺伺服器，但是我們自己本機搭建環境演示完的話，要麼搭建多個虛擬機器也可以，否則就是隻有一臺伺服器，然後使用容器進行隔離。

如我們目前搭建的伺服器，都是我們自己的主機localhost， 但是localhost在容器內部和外部確實兩個不同的概念。

下面我們會在mysql master建立一個用於同步的賬號，授權的時候千萬不要授權localhost，因為這個帳號是在從庫裡使用的，在從庫的容器內localhost指的是自己了，那麼這個使用者肯定是不存在的。。。這是在本機搭建環境最最要注意的一個點，一不注意就會被同步時說access deny搞亂好幾天~~

那麼現在我們要先獲取mysql master這臺容器的ip， 用於建立使用者授權時指定ip為這臺容器的ip, 當然容器的ip最好先建立網路，然後分配固定ip,我們前面已經給每個容器分配了固定ip，這裡也可以來檢查一下，看下用作master的mysql

docker inspect mysql

# 進入mysql主伺服器容器
docker exec -it mysql /bin/bash
# 連線到主機
mysql -uroot -p
# 建立使用者， 注意mysql的%是不包含localhost的, 'master_slave_sync'@'%'和'master_slave_sync'@'localhost'會# 被認為是兩個使用者，如果我們在本機演示用localhost就可，如果不是本機，最好直接使用ip,不必要用%，否則就要建立兩
# 個帳號，下面演示是說明確實可以建立兩個帳號，而且授權也是一樣的道理，%和localhost確實是分開的
CREATE USER 'master_slave_sync'@'%' IDENTIFIED with mysql_native_password BY '123456';
# 還是建立使用者， mysql的主機%是不包含localhost的，我們如果只有一個主機演示的話，需要再建立一下localhost
CREATE USER 'master_slave_sync'@'172.20.12.1' IDENTIFIED with mysql_native_password BY '123456';
# 授權
GRANT REPLICATION SLAVE ON *.* TO 'master_slave_sync'@'%';
# mysql授權的%並不包含localhost,如果我們演示使用本機的話，localhost主機也必須給
GRANT REPLICATION SLAVE ON *.* TO 'master_slave_sync'@'172.17.0.4';
# 重新整理許可權
FLUSH PRIVILEGES;
# 檢視節點資訊, 會返回當前binlog的日誌位置記錄點，這個點決定了從庫想要從哪裡開始同步資料
show master status;
 

1.3.2 配置從庫向主庫的同步連線資訊

進入從庫資料庫

-- 暫停同步
stop slave;
 
-- 重置slave,正常先不要執行
-- 啟動slave報錯Slave failed to initialize relay log info structure from the repository時使用
reset slave;
 
-- 指向主庫連線資訊
-- 修改從庫指向到主庫，使用上一步master status記錄的檔名以及位點
-- 這裡有一點需要注意的是，這個檔案位置點表示同步的時候從這個點開始同步，那麼之前的資料是不會同步的。
-- 所以如果是想要全量同步，請先自行將主庫和從庫的資料進行一次同步，然後再進行這個操作
CHANGE MASTER TO
master_host = '172.17.0.1',
master_user = 'master_slave_sync',
master_password = '123456',
-- 對應show master status的file
master_log_file = 'mysql-bin.000007',
-- 對應show master status的Position
master_log_pos = 1427;
 
-- 重啟slave
start slave;
-- 檢視slave狀態
show slave status;

show slave status請注意檢視以下欄位是否正常，或者錯誤原因是什麼

注意,下面這個報錯，如果排除發現主機和從機的server_id確實不一致，那麼再檢查每個服務的有一個配置檔案叫auto.conf，裡面有一個server-uuid可能是一樣的，刪除這個檔案或者更改對應的值即可，這個檔案所在目錄為mysql的data目錄下

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

1.4 sql語句

前面已經配置好了主從同步配置，所以只要在master庫執行語句即可，slave會自動同步

 
CREATE DATABASE IF NOT EXISTS `boot-quick` default charset utf8mb4 COLLATE utf8mb4_general_ci;
 
use `boot-quick`;
 
DROP TABLE IF EXISTS auth_user;
 
CREATE TABLE auth_user
(
    id BIGINT(20) NOT NULL COMMENT '主鍵ID' AUTO_INCREMENT,
    user_name VARCHAR(30) NOT NULL COMMENT '姓名',
    user_token varchar(64) NOT NULL COMMENT '使用者隨機碼，生成金鑰的鹽，註冊時生成且不可變！',
    password VARCHAR(32) NOT NULL COMMENT '密碼',
    email VARCHAR(50) NULL DEFAULT NULL COMMENT '郵箱',
    birthday DATE NULL DEFAULT NULL COMMENT '生日',
    last_modify_password bigint  COMMENT '最後一次修改密碼的時間',
    last_login_time bigint COMMENT '最後一次使用密碼登入的時間',
 
    create_by VARCHAR(32) NULL,
    create_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    modify_by VARCHAR(32) NULL,
    modify_time TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    removed INT NOT NULL DEFAULT 0,
    version INT NOT NULL DEFAULT 1,
 
    PRIMARY KEY (id)
 
);