mysql資料庫的主從配置

mysql資料庫作為我們程式開發中，最容易遇到瓶頸的一個環節，其有很多可優化的地方，此處我們先從見效最快、技術要求較低的地方 -- 資料庫主從優化

主資料庫開啟二進制日志

1. 修改my.cnf并且将以下參數加入其中,重新開機mysql執行個體

2. [mysqld]

   # 其下還有很多其他部配置設定置，此處不再一一列舉，此處隻是說明該配置項需要放置到mysqld分組下

   # 由于bug,是以需要設定該參數.否則無法啟動mysql執行個體

   # server-id表示資料庫的唯一辨別符，主庫的server-id值必須比從庫大(程式員思路1為最大，常人了解時記住主庫一般配置為1~10即可，從庫從20往下進行遞增)

   server-id=11

   # 其中mysql-bin代表的是basename就是生成二進制日志檔案的字首部分,預設的位置在datadir目錄下,也可以設定為其他的路徑

   log-bin = mysql-bin

   # 以下配置項為可選配置項，根據需求進行特殊定制即可

   # 設定二進制的日志格式

   # 二進制日志的格式有三種：基于語句的格式（STATEMENNT）、基于行的格式（ROW）和混合模式（MIXED）

   # 在MySQL 5.7.7之前，預設格式是 STATEMENT。在MySQL 5.7.7及更高版本中，預設值是ROW

   binlog_format=row

   # 選擇部分資料庫進行記錄

   # 如果有多個資料庫需要配置，直接複制該條記錄重新指定資料庫名稱即可(預設記錄全部資料庫)

   binlog-do-db = test

   # 也可以選擇不記錄某些資料庫的二進制日志

   # 如果有多個資料庫需要配置，直接複制該條記錄重新指定資料庫名稱即可(預設忽略資料庫為空)

   # 此配置項優先級低于binlog-do-db，且為互斥關系，即一旦指定binlog-do-db，則此配置項将不再生效

   binlog-ignore-db = test

   binlog-ignore-db = mysql

   # 設定二進制日志分片大小，即二進制日志大小到達多少後自動建立并啟用新的日志進行記錄

   max_binlog_size = 500M

   # 設定日志的過期天數，即生成日志後多少天以後，自動删除該二進制日志

   # 此配置項機關為天，預設0表示永不過期

   expire_logs_days = 7

   # 除了二進制日志外，我們還可以記錄查詢日志，但因為查詢太過于吃性能且對原資料并無變更要求，是以一般情況下，我們并未啟用查詢日志

   # 此處僅為記錄查詢日志的記錄方式，請酌情使用

   # 啟用/關閉查詢日志，1-開啟 0-關閉

   general-log = 1

   # 存儲方式，可選值有table、file、none

   # file表示記錄到檔案中，table表示記錄到表中(此選項很少有人選，是以此處暫時忽略)

   log-output = file

   # 查詢日志路徑

   general-log-file = "/home/chenyubo/www/logs/mysql.general.log"

   # 慢查詢日志

   # 相較于查詢日志，慢查詢日志對于我們來說更加重要

   # 管理語句的慢查詢并不會記錄其中

   # 管理語句包括：alter table，analyze talbe，check table，create table，create index，drop index，optimize table和repaire table。

   # 慢查詢日志開關 0-關閉 1-開啟

   slow_query_log = 1

   # 慢查詢閥值，機關：秒，預設10秒

   long_query_time = 3

   # 設定慢查詢日志路徑

   # 如果慢查詢日志很多，可使用mysqldumpslow工具對慢查詢日志進行分類彙總

   slow_query_log_file = "/var/log/mysql/mysql.slow.log"

3. 檢視二進制日志的相關配置

4. -- 檢視資料庫二進制日志進度

   MariaDB [(none)]> show master status;

   +------------------+----------+--------------+------------------+

   | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

   +------------------+----------+--------------+------------------+

   | mysql-bin.000001 | 328 | | |

   +------------------+----------+--------------+------------------+

   1 row in set (0.000 sec)

   -- 檢視二進制日志清單

   MariaDB [(none)]> show binary logs;

   +------------------+-----------+

   | Log_name | File_size |

   +------------------+-----------+

   | mysql-bin.000001 | 328 |

   +------------------+-----------+

   1 row in set (0.000 sec)

   -- 檢視binlog相關系統參數值

   MariaDB [(none)]> show variables where variable_name in ('log_bin','log_bin_basename','log_bin_index');

   +------------------+--------------------------------+

   | Variable_name | Value |

   +------------------+--------------------------------+

   | log_bin | ON |

   | log_bin_basename | /var/lib/mysql/mysql-bin |

   | log_bin_index | /var/lib/mysql/mysql-bin.index |

   +------------------+--------------------------------+

   3 rows in set (0.001 sec)

   -- 可選SQL，用于管理二進制日志

   -- 這些SQL将不會在複制其執行結果，僅留存SQL用于選擇使用

   -- 删除所有二進制日志

   MariaDB [(none)]> reset master;

   -- 删除編号在xxxxxx之前的日志

   MariaDB [(none)]> purge master logs to 'mysql-bin.000006';

   -- 删除日期在'yyyy-mm-dd hh:mm:ss'之前的日志

   MariaDB [(none)]> purge master logs before '2020-07-10 12:12:12';

5. PS：在my.cnf中設定好log-bin之後，log_bin會自動設定為ON，basename設定為mysql-bin，index檔案也自動生成。index檔案記錄的就是所有的二進制檔案的名稱及位置資訊。
6. 在主資料庫裡建立一個同步賬号授權給從資料庫使用（grant建立，replication複制）

7. -- 建立同步賬号，用于從資料庫讀取二進制日志

   -- 賬戶：repl

   -- 登入域：任意

   -- 密碼：123456

   MariaDB [(none)]> create user 'repl'@'%' identified by '123456';

   -- 對該賬戶進行授權

   MariaDB [(none)]> grant replication slave on *.* to 'repl'@'%';

   -- 重新整理授權

   MariaDB [(none)]> flush privileges;

從庫配置

從庫配置和主庫稍微有些差別，差別為從庫需要配置主庫的相關資訊

[mysqld]
# 其下還有很多其他部配置設定置，此處不再一一列舉，此處隻是說明該配置項需要放置到mysqld分組下
# 由于bug,是以需要設定該參數.否則無法啟動mysql執行個體
# server-id表示資料庫的唯一辨別符，主庫的server-id值必須比從庫大(程式員思路1為最大，常人了解時記住主庫一般配置為1~10即可，從庫從20往下進行遞增)
server-id = 21
# 啟用中繼日志
# 一般情況下它在MySQL主從同步讀寫分離叢集的從節點才開啟。主節點一般不需要這個日志
# 此處存儲的為中繼日志的檔案名稱，生成格式和log-bin相同
relay-log = mysql-relay
# replicate-do-db 設定需要複制的資料庫(多資料庫逗号，隔開)
# replicate-do-table 設定需要複制的表
# replicate-wild-do-table 同replication-do-table功能一樣，但是可以通配符
# replicate-ignore-db 設定忽略的複制資料庫 (多資料庫逗号，隔開)
# replicate-ignore-table 設定需要忽略的複制表
# replicate-wild-ignore-table 同replication-ignore-table功能一樣，可以加通配符
比如，忽略mysql庫的所有表           

從庫配置完成後，進行重新開機服務 systemctl restart mariadb ，然後進入mysql指令行，進行配置主資料庫節點

-- 因配置項隻有傳回是否OK的結果，是以此處不再複制結果資訊
MariaDB [(none)]> change master to \
    master_host='192.168.86.100', -- 位址填主資料庫的位址
    master_port=3306, -- 指定主伺服器的端口資訊
    master_user='repl' , -- 填主資料庫的使用者
    master_password='123456' , -- 通路主資料庫密碼
    master_log_file='mysql_bin_0.000001' , -- 主資料庫的檔案名
    master_log_pos=134; -- 同步主資料庫從什麼位置開始，填數字就可以，無需引号，但是最後需要分号
-- 開啟主從同步
MariaDB [(none)]> start salve;
-- 檢視結果
-- 此結果中，需要看到【Slave_IO_Running:yes】和【Slave_SQL_Running:yes】才表示從庫配置完成
MariaDB [(none)]> show slave status;           

Docker 從庫配置

我們建議将資料庫直接以原生的方式搭建的主控端上，但是從庫并沒有那麼多要求，雖然也建議直接搭建到主控端上，但是考慮到有時我們隻有一台伺服器，如果想做主從的話，就要考慮到Docker和主控端之間的通訊了。

而且因為是主從，Docker伺服器需要聯通主控端，而主控端隻考慮到使用端口連結Docker即可

PS：考慮到有些Docker已經啟動過了，是以我們建議進入到Docker容器中，執行指令：ip addr進行檢視容器IP

PS：如果沒有設定專門的網關的話，可以直接在主控端執行ip addr show docker0檢視IP

# Docker 拉取資料庫鏡像
$ docker image pull mariadb
# 啟動容器
# 運作mariadb
# 本地配置：/home/mysql/data MySQL資料存儲目錄
# 綁定端口：3307                 開放本機端口
# 密碼配置：root123              配置MySQL連接配接密碼
# 20.10版本以上
$ docker run --name mysql01 --add-host="host.docker.internal:host-gateway" -v /home/xiaoyutab/Documents/mysqls:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root123 -p 3307:3306 -d mariadb
# 20.10版本以下
# 然後在主控端使用docker0的IP進行通路
$ docker run --name mysql01 -v /home/xiaoyutab/Documents/mysqls:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root123 -p 3307:3306 -d mariadb
# 進入Docker虛拟機
$ docker exec -it mysql01 /bin/bash
# 修改配置檔案，此處使用cp的形式進行複制-修改
[Docker]$ cd /etc/mysql/mariadb.conf.d
[Docker]$ cp 50-server.cnf /var/lib/mysql/
# 在主控端修改此檔案配置即可【是以檔案複制出來為root權限，是以需要使用sodu進行執行】
$ sudo vim /home/xiaoyutab/Documents/mysqls/50-server.cnf
# 複制回來
[Docker]$ rm 50-server.cnf ; cp /var/lib/mysql/50-server.cnf ./
# 重新開機容器内MySQL
$ docker stop mysql01
$ docker start mysql01
# 再次進入容器，進行資料複制
$ docker exec -it mysql01 /bin/bash
[Docker]$ mysql -uroot -proot123
[Docker]MariaDB [(none)]> change master to master_host='172.17.0.1',master_port=3306,master_user='admin',master_password='admin',master_log_file='mysql-bin.000001',master_log_pos=1;
Query OK, 0 rows affected (0.020 sec)

[Docker]MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.000 sec)

[Docker]MariaDB [(none)]> show slave status\G;
# 此結果中，需要看到【Slave_IO_Running:yes】和【Slave_SQL_Running:yes】才表示從庫配置完成           

錯誤處理

部分時候我們會遇到同步錯誤，這時候就需要使用以下方法進行解決處理了

Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

原因：主從同步遇到同步日志紊亂

解決辦法：

-- 主mysql
-- 檢視新的binlog日志
> show binary logs;
-- 從mysql
-- 停止同步
> stop slave;
-- 修改同步資訊
-- 如果是伺服器變更，也可以重新執行change語句進行配置，類似上文的配置方式
> change master to master_log_file='mysql-bin.000001',master_log_pos=1;
-- 開啟同步
> start slave;
-- 檢查狀态
> show slave status \G;           

Error 'Can't find any matching row in the user table' on query. Default database: ''. Query: 'grant replication slave on .to 'admin'@'localhost''

原因：主庫賬戶表的資料跟從庫不同步導緻，主庫要更新的記錄而從庫中不存在

解決方案：忽略掉不需要同步的表

PS：主要忽略的庫有：mysql、information_schema、performance_schema，其中後兩個為虛拟庫，預設忽略，是以主要配置忽略的庫為mysql