之前參考過(guò)利用Kubernetes搭建mysql主從復(fù)制集群這篇帖子，在實(shí)際擴(kuò)展方面與自己的實(shí)際需有有所差別，這里將自己的部署搭建過(guò)程描述一下。
配置文件
配置文件使用k8s的configMap。
Master
- 創(chuàng)建配置文件：
kubectl --namespace=saas-common create configmap mysql-master --from-file=master.cnf
- 查看配置文件信息：
kubectl describe configmap --namespace=saas-common mysql-master
Name: mysql-master
Namespace: saas-common
Labels: <none>
Annotations: <none>
Data
====
master.cnf:
----
[mysqld]
####基本配置####
# 時(shí)區(qū)調(diào)整（所有節(jié)點(diǎn)統(tǒng)一）
default-time-zone = '+8:00'
# 服務(wù)器ID
server-id = 1
# 端口
port = 3306
# 開(kāi)啟二進(jìn)制日志并配置日志名
log_bin = master.bin
# 忽略大小寫(xiě)區(qū)分
lower_case_table_names=1
# 關(guān)閉自動(dòng)提交
autocommit = 0
# 修改默認(rèn)編碼
character_set_server=utf8
# timestamp列的默認(rèn)值，null-null，其他-0000-00-00 00:00:00
explicit_defaults_for_timestamp = 1
# 臨時(shí)文件路徑
tmpdir = /tmp
# 定義支持的語(yǔ)法、數(shù)據(jù)校驗(yàn)等
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
# 兼容5.7版本之前的用法
show_compatibility_56=on
# 事物的寫(xiě)入方式-哈希編碼方式
transaction_write_set_extraction=MURMUR32
# 文件路徑
datadir= /var/lib/mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
####安全相關(guān)####
# 跳過(guò)域名解析
skip_name_resolve = 1
# 最大錯(cuò)誤連接數(shù)，阻止破解
max_connect_errors = 1000
# 事務(wù)隔離級(jí)別，只能讀取到已經(jīng)提交的數(shù)據(jù)
transaction_isolation = READ-COMMITTED
####性能相關(guān)####
# 最大IP連接數(shù)
max_connections = 300
# 臨時(shí)表大小 64M
tmp_table_size = 67108864
# 限制server接受的數(shù)據(jù)包大小 16M
max_allowed_packet = 16777216
# 服務(wù)器關(guān)閉交互式連接前等待秒數(shù)，30分鐘
interactive_timeout = 1800
# 服務(wù)器關(guān)閉非交互式連接前等待秒數(shù)，30分鐘
wait_timeout = 1800
# 讀入緩沖區(qū)大小
read_buffer_size = 1M
# 隨機(jī)讀緩沖區(qū)大小
read_rnd_buffer_size = 2M
# 每一次事物提交都將binlog_cache中的數(shù)據(jù)強(qiáng)制寫(xiě)到磁盤(pán)
sync_binlog = 1
####日志相關(guān)####
# 開(kāi)啟慢查詢?nèi)罩?slow_query_log = 1
# 慢查詢?nèi)罩久?slow_query_log_file = slow.log
# 慢查詢閾值，查詢時(shí)間超過(guò)閾值時(shí)寫(xiě)入到慢日志中
long_query_time = 2
# 未使用索引的查詢也被記錄到慢日志中
log_queries_not_using_indexes = 1
# 指定執(zhí)行過(guò)慢的DDL語(yǔ)句寫(xiě)入慢日志
log_slow_admin_statements = 1
# 從庫(kù)將超過(guò)查詢閾值的查詢記錄到慢日志
log_slow_slave_statements = 1
# 設(shè)置每分鐘記錄記錄的未使用索引的查詢的數(shù)量10
log_throttle_queries_not_using_indexes = 10
# 少于100行的查詢不會(huì)記錄到慢日志中
min_examined_row_limit = 100
# 二進(jìn)制日志自動(dòng)刪除的天數(shù)
expire_logs_days = 90
# 日志記錄時(shí)間戳和系統(tǒng)時(shí)間一致
log_timestamps=system
# 錯(cuò)誤日志路徑
log-error=/var/log/mysql/mysqld_err.log
# 二進(jìn)制文件模式
binlog_format = row
####復(fù)制方式相關(guān)-半同步復(fù)制####
# 插件路徑
plugin_dir=/usr/lib/mysql/plugin
# 加載的插件列表
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
# 主庫(kù)開(kāi)啟半同步
loose_rpl_semi_sync_master_enabled = 1
# 從庫(kù)開(kāi)啟半同步
loose_rpl_semi_sync_slave_enabled = 1
# 等待超時(shí)時(shí)間
loose_rpl_semi_sync_master_timeout = 5000
# 設(shè)置主需要等待多少個(gè)slave應(yīng)答，才能返回給客戶端，默認(rèn)為1
rpl_semi_sync_master_wait_for_slave_count=1
# 主庫(kù)在返回給會(huì)話事務(wù)成功之前提交事務(wù)的方式
rpl_semi_sync_master_wait_point=AFTER_SYNC
####復(fù)制錯(cuò)誤相關(guān)####
# 復(fù)制過(guò)程中從服務(wù)器跳過(guò)所有錯(cuò)誤，也可以指定錯(cuò)誤號(hào)
slave_skip_errors = all
####復(fù)制信息相關(guān)####
# 將主庫(kù)狀態(tài)和信息記錄到表中
master_info_repository = TABLE
# 將relay日志中的從庫(kù)日志位置記錄到表中
relay_log_info_repository = TABLE
# 從庫(kù)宕機(jī)后，自動(dòng)放棄所有未執(zhí)行的中繼日志，重新從主庫(kù)上獲取日志
relay_log_recovery = 1
# 指定中繼日志的位置和文件名
relay_log = relay.log
####GTID相關(guān)####
# 開(kāi)啟gtid工作模式
gtid_mode = on
# 只允許能保障事物安全，且能夠被日志記錄的SQL語(yǔ)句被執(zhí)行
enforce_gtid_consistency = 1
# 從庫(kù)從主庫(kù)復(fù)制數(shù)據(jù)時(shí)的操作也寫(xiě)入binlog
log_slave_updates
# 重啟和啟動(dòng)時(shí)，如何迭代使用binlog文件
binlog_gtid_simple_recovery = 1
####InnoDB相關(guān)####
# 緩沖池字節(jié)大小
innodb_buffer_pool_size = 800M
# 緩沖池實(shí)例數(shù)量
innodb_buffer_pool_instances = 8
# 啟動(dòng)時(shí)將熱數(shù)據(jù)加載到內(nèi)存
innodb_buffer_pool_load_at_startup = 1
# 關(guān)閉時(shí)將熱數(shù)據(jù)dump到本地磁盤(pán)
innodb_buffer_pool_dump_at_shutdown = 1
# page cleaner線程每次刷新臟頁(yè)的數(shù)量
innodb_lru_scan_depth = 2000
# 事務(wù)等待獲取資源等待的最長(zhǎng)時(shí)間
innodb_lock_wait_timeout = 5
# 調(diào)整刷新臟頁(yè)的數(shù)量
innodb_io_capacity = 4000
# 刷新臟頁(yè)的最大值
innodb_io_capacity_max = 8000
# 數(shù)據(jù)和日志寫(xiě)入磁盤(pán)的方式-直接寫(xiě)入磁盤(pán)
innodb_flush_method = O_DIRECT
# 文件格式，Barracuda支持壓縮頁(yè)，新格式
innodb_file_format = Barracuda
# 設(shè)置文件格式最高版本
innodb_file_format_max = Barracuda
# 刷新臟頁(yè)臨近頁(yè)
innodb_flush_neighbors = 1
# 用來(lái)緩沖日志數(shù)據(jù)的緩沖區(qū)大小
innodb_log_buffer_size = 1M
# 單獨(dú)的清除線程數(shù)量-0不適用單獨(dú)線程
innodb_purge_threads = 4
# 為字段創(chuàng)建索引時(shí)，限制的字節(jié)長(zhǎng)度，超過(guò)直接報(bào)錯(cuò)
innodb_large_prefix = 1
# 線程并發(fā)數(shù)
innodb_thread_concurrency = 64
# 將發(fā)生的所有死鎖信息都記錄到錯(cuò)誤日志中
innodb_print_all_deadlocks = 1
# 嚴(yán)格檢查模式，寫(xiě)法有錯(cuò)誤直接報(bào)錯(cuò)，不警告
innodb_strict_mode = 1
# 建立索引時(shí)用于排序數(shù)據(jù)的排序緩沖區(qū)大小-10M
innodb_sort_buffer_size = 10485760
# 轉(zhuǎn)儲(chǔ)緩沖池中read out and dump 的最近使用的頁(yè)的占比
innodb_buffer_pool_dump_pct = 40
# page cleaner線程數(shù)量
innodb_page_cleaners = 4
# 開(kāi)啟在線回收undo log日志文件
innodb_undo_log_truncate = 1
# 超過(guò)這個(gè)閾值時(shí)觸發(fā)回收
innodb_max_undo_log_size = 2G
# 回收undo日志的頻率
innodb_purge_rseg_truncate_frequency = 128
Slave
Slave的配置參考Master
Service
K8S的service文件配置對(duì)于主從數(shù)據(jù)庫(kù)是一樣的，這里以Master為例：
apiVersion: v1
kind: Service
metadata:
name: mysql-master
namespace: saas-common
labels:
name: mysql-master
role: saas-common
spec:
ports:
- port: 3306
nodePort: 32306
targetPort: 3306
type: NodePort
selector:
name: mysql-master
$kubectl apply -f service.yaml
$kubectl get svc --namespace=saas-common
NAME CLUSTER-IP EXTERNAL-IP PORT(S) AGE
mysql-master 172.19.6.209 <nodes> 3306:32306/TCP 1m
mysql-slave 172.19.10.204 <nodes> 3306:32307/TCP 6s
Deployment
以Master為例：
apiVersion: extensions/v1beta1
kind: Deployment
metadata:
name: mysql-master
namespace: saas-common
spec:
replicas: 1
template:
metadata:
labels:
name: mysql-master
role: saas-common
spec:
nodeSelector:
mysql: master
imagePullSecrets:
- name: regsecret.common
containers:
- name: mysql-master
image: registry.hisensehics.com:443/mysql:5.7.18
env:
- name: MYSQL_ROOT_PASSWORD
value: 'Mysql2017SaasHisenseHics'
- name: MYSQL_REPLICATION_USER
value: 'repl'
- name: MYSQL_REPLICATION_PASSWORD
value: 'Mysql2017SaasHisenseHics'
ports:
- containerPort: 3306
name: mysql-master
volumeMounts:
- name: conf
mountPath: /etc/mysql/mysql.conf.d
- name: data
mountPath: /var/lib/mysql
- name: log
mountPath: /var/log/mysql
- name: timezone
mountPath: /etc/localtime:ro
volumes:
- name: conf
mountPath: /etc/mysql/mysql.conf.d
- name: data
mountPath: /var/lib/mysql
- name: log
mountPath: /var/log/mysql
- name: timezone
mountPath: /etc/localtime:ro
volumes:
- name: conf
configMap:
name: mysql-master
- name: data
hostPath:
path: /opt/mysql/data
- name: log
hostPath:
path: /opt/mysql/logs
- name: timezone
hostPath:
path: /etc/localtime
$kubectl apply -f 3-deployment.yaml
$kubectl get deployment --namespace=saas-common
NAME DESIRED CURRENT UP-TO-DATE AVAILABLE AGE
mysql-master 1 1 1 1 7m
主從配置
Master
- 創(chuàng)建復(fù)制用戶并配置權(quán)限
GRANT REPLICATION SLAVE ON *.* to 'repl'@'%' identified by 'Mysql2017SaasHisenseHics';
FLUSH PRIVILEGES;
- 查看主庫(kù)當(dāng)前狀態(tài)：
MySQL [(none)]> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| master.000003 | 621 | | | f5bf2375-4a7d-11e7-a732-0a58ac100144:1-7 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
Slave
- 配置主從信息：
change master to master_host ='172.19.6.209', master_port = 3306, master_user = 'repl', master_password = 'Mysql2017SaasHisenseHics', master_auto_position =621;
- 啟動(dòng)從庫(kù)復(fù)制：
start slave；
- 查看復(fù)制狀態(tài)：
MySQL [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.19.6.209
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master.000003
Read_Master_Log_Pos: 621
Relay_Log_File: relay.000003
Relay_Log_Pos: 828
Relay_Master_Log_File: master.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 621
Relay_Log_Space: 3001638
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: f5bf2375-4a7d-11e7-a732-0a58ac100144
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: f5bf2375-4a7d-11e7-a732-0a58ac100144:1-7
Executed_Gtid_Set: d34748bd-4a7f-11e7-b80b-0a58ac10060e:1-5,
f5bf2375-4a7d-11e7-a732-0a58ac100144:1-7
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
主從配置完成
