如果格式錯亂，可以查看附件內容

1. 背景在傢裡的主機部署瞭一套 seafile，使用的是 docker 數據庫，數據完全在本地，平時訪問數據非常快，感受不到任何延遲。但考慮到容災，即偶發的傢裡停電、停網，需要做到異地備份 &異地訪問。所以非常需要將數據庫遷移出本地。而我之前在騰訊雲購買瞭一個 mysql 數據庫實例，可以考慮使用公有雲 mysql，本地的 seafile 僅是一個邏輯層，可以做到快速遷移。上述方案實施後，我發現本地訪問 seafile 有肉眼可見的延遲，切換頁面後有 1-2 秒的延遲。需要再次升級架構。
2. 目標為 mysql 數據庫搭建主從分離架構，做到本地訪問的加速。
3. 技術方案選擇

簡單搜索瞭一下數據庫主從方案的實現，有在業務層區分 mysql 實例，有 mysql 官方的 mysql router，以及 java spring 的插件，還有 proxysql。

3.1 業務層區分實例

顧名思義，就是寫業務代碼的時候，連接兩個數據庫實例。在 orm 層如果識別是讀請求，就用從庫；否則用主庫。這種適合從頭開發一個業務的時候使用，沒有中間的數據庫轉發，效率會高一些（具體高多少沒有實測過）。

對於 seafile 這種開源軟件來說，不可能再修改代碼區分讀寫實例瞭。

3.2 mysql router

這個是 mysql 官方的方案，但之前看某篇文章說是要區分端口來訪問的（文章後來找不到瞭），就沒繼續深入研究。

3.3 java 數據庫中間件

顯然在 spring boot 項目中有用，在 seafile 這種開源軟件中無法使用。

3.4 proxysql

看到這個 c++ 寫的中間件比較符合要求，能夠提供一個端口供業務訪問，自己能區分出讀寫，並轉發到不同的實例上。

缺點：

1. 在 ubuntu 下無法直接用 apt 安裝
2. 需要在 proxysql 中新建用戶，而且這些用戶需要和主從庫中的用戶名和密碼完全相同

整體的方案如下：

65a72ebe6970c4e67a848c1df7889db8

整個方案分為三個部分：

1. 主從同步的設置
2. 讀寫分離的配置
3. 透明訪問第三步的透明訪問是依靠配置來實現的。比如主庫的端口是 3306，那麼 proxysql 的端口隻能設置為 3306，需要各方面配置完全和主庫一致才能做到透明訪問。所以從庫的端口隻能設置為 3307。另外使用 openwrt 的 dns 服務器將本地的數據庫域名請求解析到 proxysql 的 ip。這樣在本地 seafile 故障時，可以快速在公網部署一套 seafile 邏輯層，由於公網域名會解析到 mysql 主庫上，無需修改任何 seafile 配置。
4. 實施過程

4.1 主從同步的設置

4.1.1 備份主庫

SQLmysqldump -u root -h your_ip -P your_port -p --all-databases > backdb.sql

4.1.2 恢復數據到從庫

新建一個從庫實例，docker-compose.yml 如下：

YAMLversion: '2.0'services: mysql: container_name: mysql5.7 image: mysql:5.7 ports: - 3307:3307 privileged: true volumes: - /data/mysql5.7/log:/var/log/mysql - /home/ubuntu/conf/5600g/database_pack/conf:/etc/mysql - /data/mysql5.7/data:/var/lib/mysql environment: MYSQL_ROOT_PASSWORD: "your_passwd" command: [ '--character-set-server=utf8mb4', '--collation-server=utf8mb4_general_ci', '--max_connections=3000' ]

從庫使用的配置文件 my.cnf 如下

Bash[client]port=3306default-character-set=utf8mb4[mysql]default-character-set=utf8mb4[mysqld]port = 3307# server 的編碼，自定義，與主庫不一樣即可server-id = 5600# 從庫需要設置為隻讀read_only=ON# gtid 配置，如果 master 設置瞭 gtid，則從庫需要開啟gtid_mode=ONenforce_gtid_consistency = ONcharacter_set_server=utf8mb4init_connect='SET NAMES utf8mb4'# 主機的該目錄要做成 777 權限，否則 docker 啟動時 mysql 會顯示無權限打日志log-error=/var/log/mysql/mysqld.log# 不區分大小寫lower_case_table_names = 1# 不開啟sql嚴格模式sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"pid-file = /var/run/mysqld/mysqld.pidsocket = /var/run/mysqld/mysqld.sockdatadir = /var/lib/mysqlsymbolic-links=0replicate-ignore-db=information_schemareplicate-ignore-db=performance_schemareplicate-ignore-db=sys# mysql 表存瞭用戶名，還是需要同步的，不然讀寫分離後，從庫無法同步新增的用戶，會連接不上#replicate-ignore-db=mysqllog-slave-updatesslave-skip-errors=allslave-net-timeout=60log_bin_trust_function_creators = 1

文件結構如下：

Bash➜ database_pack git:(master) ✗ tree.├── conf│ └── my.cnf└── docker-compose.yml

運行 docker-compose up -d 啟動從庫，然後在從庫執行下面的 sql 恢復數據庫即可。

SQLmysql -u root -p -h your_ip -P your_port < backdb.sql

4.1.3 新建備份用戶

先新建用戶 slave，再給 slave 用戶授予權限

SQLgrant replication slave on *.* to 'slave'@'%'；

4.1.4 開始同步

在主庫中執行如下 sql 查詢信息

SQLshow master status;

會返回 master 的 log file 和 log pos，填充到下面的 sql 中。

接下來在從庫執行如下 sql

SQLCHANGE MASTER TO MASTER_HOST='your_master_ip',MASTER_PORT=your_master_port,MASTER_USER='slave',MASTER_PASSWORD='your_slave_passwd',MASTER_LOG_FILE='your_slave_bin_name',MASTER_LOG_POS=your_pos;

然後在從庫執行如下 sql 啟動主從同步

SQLstart slave;

在從庫執行如下 sql 查看同步狀態

SQLshow slave statusG

如果配置出現問題，執行如下 sql 重新來一遍

SQLreset slave;change master to ...start slave;

比如我出現瞭如下問題，應該是 gtid_mode 配置不同造成的

SQL2023-05-27T16:10:17.564506Z 3 [ERROR] Slave I/O for channel '': The replication receiver thread cannot start because the master has GTID_MODE = ON and this server has GTID_MODE = OFF. Error_code: 1593

在主庫中執行如下命令查看是否開啟瞭 gtid_mode，如果是，在從庫的 my.cnf 就要開啟 gtid_mode 瞭

SQLshow variables like "gtid_mode";

參考 http://cloud.tencent.com/developer/article/2121789 解決 gtid 的問題

同步成功後，兩個 Slave 會顯示 Yes。而且主庫中新增的庫表會自動同步過來。

4.2 讀寫分離的配置

4.2.1 安裝 proxysql

參考 http://github.com/sysown/proxysql 完成 proxysql 的安裝，下面的命令要替換為自己需要的二進制版本。

Bashwget http://github.com/sysown/proxysql/releases/download/v2.5.2/proxysql_2.5.2-ubuntu22_amd64.debsudo dpkg -i proxysql_2.5.2-ubuntu22_amd64.deb

4.2.2 修改默認 mysql 連接端口為 3306

建議提前修改這個端口。如果最後再修改，會因為操作不當導致所有的 proxysql 配置丟失。

登錄 proxysql 的管理接口

Bashmysql -uadmin -padmin -P6032 -h127.0.0.1

查看當前客戶端連接端口：（默認為：0.0.0.0:6033）

SQLshow variables like '%mysql-interfaces%';

臨時修改

SQLset mysql-interfaces="0.0.0.0:3306";

固化修改（根據官方文檔，執行LOAD MYSQL VARIABLES TO RUNTIME會重置該配置，因此不能執行這一命令）

SQLSAVE MYSQL VARIABLES TO DISK;

重啟 proxysql

Shell# load admin variables to runtime;# save admin variables to disk;# SAVE MYSQL VARIABLES TO DISK;# 上次執行完 PROXYSQL RESTART 後所有的 server 配置都沒瞭，用戶與路由規則還存在；下次可以試試執行上面的命令，看能否保存PROXYSQL RESTART;

驗證

Shellnetstat -tnlp | grep proxysql

4.2.3 錄入 mysql 用戶

SQLSET mysql-monitor_username='slave';SET mysql-monitor_password='your_passwd';

4.2.4 在 proxysql 中插入用戶

SQLINSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('your_usename','your_passwd',10);

這裡的用戶還隻是在內存中，運行下面的命令使其生效

SQLLOAD MYSQL USERS TO RUNTIME;SAVE MYSQL USERS TO DISK;LOAD MYSQL VARIABLES TO RUNTIME;SAVE MYSQL VARIABLES TO DISK;

查看用戶

SQLSELECT * FROM mysql_users;

4.2.5 錄入 mysql 服務器

我們在插入 mysql 服務器時就指定好組，10 是寫入，20 是讀

SQLINSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'your_master_ip', 3306);INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (20,'your_slave_ip', 3307);SELECT * FROM mysql_servers;

查看狀態和延遲可能沒有數據，沒有關系，等一會會有，可以繼續操作。

查看連接狀態

SQLselect * from mysql_server_connect_log;

測試連接延遲

SQLselect * from mysql_server_ping_log;

4.2.6 設置讀寫分離組

SQLINSERT INTO mysql_replication_hostgroups VALUES(10,20,"read_only","common");

查看

SQLSELECT * FROM mysql_replication_hostgroups;LOAD MYSQL SERVERS TO RUNTIME;SAVE MYSQL SERVERS TO DISK;

4.2.7 插入調度規則

Select for update 要鎖表，需要調度到主庫上。

SQL INSERT INTO mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1);

查看規則

SQLSELECT * FROM mysql_query_rulesGLOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK;

4.2.8 驗證

驗證點 1：連接到 proxysql 6033 端口後，測試連接到的 mysql server_id 是不同的

SQLmysql> SELECT @@server_id;+-------------+| @@server_id |+-------------+| 5600 |+-------------+1 row in set (0.01 sec)mysql> BEGIN;SELECT @@server_id;commit;Query OK, 0 rows affected (0.03 sec)+-------------+| @@server_id |+-------------+| 1031927 |+-------------+1 row in set (0.03 sec)Query OK, 0 rows affected (0.01 sec)

驗證點 2：在 proxysql 管理端查看讀寫分離的 log

SQL select hostgroup,username,digest_text,count_star from stats_mysql_query_digest; select * from stats_mysql_query_digest;

4.3 透明訪問

在 3.4 proxysql 的整體方案中有闡述，有如下兩種透明：

1. 業務直連 3306 接口，無需感知讀寫實例的區別；
2. 業務的配置文件中寫 db 的域名，在本地 dns 中設置域名指向本地 ip，在遷移過程中無需修改配置。

5. 總結

• Mysql 讀寫分離不僅可用來提升系統容量，還可以做到主庫網絡訪問緩慢時，用從庫加速（正規的公司業務肯定不會這麼用，但對個人項目是有用的）；
• 整體流程還是比較復雜的，整個項目耗時約 1d。

6. 參考

MySQL主從復制讀寫分離，看這篇就夠瞭！

MySQL 讀寫分離初衷，方案有哪些 - 墨天輪，一些讀寫分離庫的推薦

ProxySQL 配置詳解及讀寫分離(+GTID)等功能說明 (完整篇) - 散盡浮華 - 博客園 ProxySQL 的詳細使用說明，太繁瑣瞭，自己隻想要一個讀寫分離而已

http://cloud.tencent.com/developer/article/1429052 一個簡單點的讀寫分離配置

7. 其他

7.1 數據庫用戶權限錯誤導致 seafile 502

切換到遠程 db 後，發現訪問網頁總是 502 報錯，在日志目錄中未觀測到異常，本地的 8000 端口沒開，使用 docker logs 看到如下報錯，應該是權限設置有誤。

Bash2023-05-27 22:27:38 ../common/seaf-db.c(788): Failed to prepare sql SELECT COUNT(id) FROM EmailUser WHERE is_active = 1 AND email NOT LIKE '%@seafile_group': SELECT command denied to user 'seafile'@'113.90.37.46' for table 'EmailUser'2023-05-27 22:27:38 ../common/user-mgr.c(97): Failed to get user number from DB.2023-05-27 22:27:38 seafile-session.c(424): Failed to init user manager.failed to run "seaf-server -t" [65280][2023-05-27 22:27:38] Skip running setup-seafile-mysql.py because there is existing seafile-data folder.Traceback (most recent call last): File "/scripts/start.py", line 95, in <module> main() File "/scripts/start.py", line 80, in main call('{} start'.format(get_script('seafile.sh'))) File "/scripts/utils.py", line 70, in call return subprocess.check_call(*a, **kw) File "/usr/lib/python3.8/subprocess.py", line 364, in check_call raise CalledProcessError(retcode, cmd)subprocess.CalledProcessError: Command '/opt/seafile/seafile-pro-server-9.0.16/seafile.sh start' returned non-zero exit status 1.

執行下面的 sql 給 seafile 用戶授權即可，註意需要 seahub_db 的刪除權限，在重新登錄的時候會刪除 session 表的數據。

SQLgrant CREATE,INSERT,UPDATE,SELECT on `ccnet_db`.* to 'seafile'@'%'grant CREATE,INSERT,UPDATE,SELECT,DELETE on `seahub_db`.* to 'seafile'@'%'grant INSERT,SELECT,UPDATE,CREATE on `seafile_db`.* to 'seafile'@'%'

7.2 開始同步時 root 用戶無權限

之前從庫有這個配置 replicate-ignore-db=mysql，導致雖然同步瞭 mysql 主庫的 mysql.user 表，依然無法登錄。去掉該配置後，root 用戶可以登錄，但復用瞭主庫的權限，導致 root 遠程用戶沒有權限。而嘗試從 localhost 登錄時，又顯示無法登錄（在 docker 中用 localhost，可能被識別為遠程用戶瞭？）。無奈，先去掉從庫的隻讀配置，再在從庫執行下面的 sql 授予權限。

SQL update mysql.user set Super_priv='Y' where User='root' and Host='%' limit 1; update mysql.user set Shutdown_priv='Y' where User='root' and Host='%' limit 1; update mysql.user set File_priv='Y' where User='root' and Host='%' limit 1;

但發現還沒有權限，使用 flush privileges 也沒用。隨後重啟服務器，此時就有權限瞭。

啟示：一定要先把 root 賬戶的遠程權限都開啟後，再做主從復制的操作。

7.3 查看 db 是否隻讀

SQLshow global variables like "%read_only%";

7.4 主庫新增用戶後無法同步到從庫

新增一個用戶 stock 後發現總是連接不上，查從庫發現是沒有同步。從庫沒有忽略 mysql 庫，可能是主庫忽略瞭。所以嘗試連接到從庫上，手工添加一個同名賬戶，成功瞭。啟示：用戶的添加要同時在主庫、從庫、proxysql 進行

Pythoncreate user stock identified by 'KyR+um0%OJ!r7Ln8';grant SELECT,UPDATE on stock.* to stock @'%';flush privileges;

發現總是會丟失一些配置，記錄下各種配置的存儲與加載

SQL//加載到內存load mysql users to runtime;load mysql servers to runtime;load mysql query rules to runtime;load mysql variables to runtime;load admin variables to runtime;//永久生效save mysql users to disk;save mysql servers to disk;save mysql query rules to disk;save mysql variables to disk;save admin variables to disk;