之前参考过利用Kubernetes搭建mysql主从复制集群这篇帖子，在实际扩展方面与自己的实际需有有所差别，这里将自己的部署搭建过程描述一下。
配置文件
配置文件使用k8s的configMap。
Master
- 创建配置文件：
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]
####基本配置####
# 时区调整（所有节点统一）
default-time-zone = '+8:00'
# 服务器ID
server-id = 1
# 端口
port = 3306
# 开启二进制日志并配置日志名
log_bin = master.bin
# 忽略大小写区分
lower_case_table_names=1
# 关闭自动提交
autocommit = 0
# 修改默认编码
character_set_server=utf8
# timestamp列的默认值，null-null，其他-0000-00-00 00:00:00
explicit_defaults_for_timestamp = 1
# 临时文件路径
tmpdir = /tmp
# 定义支持的语法、数据校验等
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
# 事物的写入方式-哈希编码方式
transaction_write_set_extraction=MURMUR32
# 文件路径
datadir= /var/lib/mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
####安全相关####
# 跳过域名解析
skip_name_resolve = 1
# 最大错误连接数，阻止破解
max_connect_errors = 1000
# 事务隔离级别，只能读取到已经提交的数据
transaction_isolation = READ-COMMITTED
####性能相关####
# 最大IP连接数
max_connections = 300
# 临时表大小 64M
tmp_table_size = 67108864
# 限制server接受的数据包大小 16M
max_allowed_packet = 16777216
# 服务器关闭交互式连接前等待秒数，30分钟
interactive_timeout = 1800
# 服务器关闭非交互式连接前等待秒数，30分钟
wait_timeout = 1800
# 读入缓冲区大小
read_buffer_size = 1M
# 随机读缓冲区大小
read_rnd_buffer_size = 2M
# 每一次事物提交都将binlog_cache中的数据强制写到磁盘
sync_binlog = 1
####日志相关####
# 开启慢查询日志
slow_query_log = 1
# 慢查询日志名
slow_query_log_file = slow.log
# 慢查询阈值，查询时间超过阈值时写入到慢日志中
long_query_time = 2
# 未使用索引的查询也被记录到慢日志中
log_queries_not_using_indexes = 1
# 指定执行过慢的DDL语句写入慢日志
log_slow_admin_statements = 1
# 从库将超过查询阈值的查询记录到慢日志
log_slow_slave_statements = 1
# 设置每分钟记录记录的未使用索引的查询的数量10
log_throttle_queries_not_using_indexes = 10
# 少于100行的查询不会记录到慢日志中
min_examined_row_limit = 100
# 二进制日志自动删除的天数
expire_logs_days = 90
# 日志记录时间戳和系统时间一致
log_timestamps=system
# 错误日志路径
log-error=/var/log/mysql/mysqld_err.log
# 二进制文件模式
binlog_format = row
####复制方式相关-半同步复制####
# 插件路径
plugin_dir=/usr/lib/mysql/plugin
# 加载的插件列表
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
# 主库开启半同步
loose_rpl_semi_sync_master_enabled = 1
# 从库开启半同步
loose_rpl_semi_sync_slave_enabled = 1
# 等待超时时间
loose_rpl_semi_sync_master_timeout = 5000
# 设置主需要等待多少个slave应答，才能返回给客户端，默认为1
rpl_semi_sync_master_wait_for_slave_count=1
# 主库在返回给会话事务成功之前提交事务的方式
rpl_semi_sync_master_wait_point=AFTER_SYNC
####复制错误相关####
# 复制过程中从服务器跳过所有错误，也可以指定错误号
slave_skip_errors = all
####复制信息相关####
# 将主库状态和信息记录到表中
master_info_repository = TABLE
# 将relay日志中的从库日志位置记录到表中
relay_log_info_repository = TABLE
# 从库宕机后，自动放弃所有未执行的中继日志，重新从主库上获取日志
relay_log_recovery = 1
# 指定中继日志的位置和文件名
relay_log = relay.log
####GTID相关####
# 开启gtid工作模式
gtid_mode = on
# 只允许能保障事物安全，且能够被日志记录的SQL语句被执行
enforce_gtid_consistency = 1
# 从库从主库复制数据时的操作也写入binlog
log_slave_updates
# 重启和启动时，如何迭代使用binlog文件
binlog_gtid_simple_recovery = 1
####InnoDB相关####
# 缓冲池字节大小
innodb_buffer_pool_size = 800M
# 缓冲池实例数量
innodb_buffer_pool_instances = 8
# 启动时将热数据加载到内存
innodb_buffer_pool_load_at_startup = 1
# 关闭时将热数据dump到本地磁盘
innodb_buffer_pool_dump_at_shutdown = 1
# page cleaner线程每次刷新脏页的数量
innodb_lru_scan_depth = 2000
# 事务等待获取资源等待的最长时间
innodb_lock_wait_timeout = 5
# 调整刷新脏页的数量
innodb_io_capacity = 4000
# 刷新脏页的最大值
innodb_io_capacity_max = 8000
# 数据和日志写入磁盘的方式-直接写入磁盘
innodb_flush_method = O_DIRECT
# 文件格式，Barracuda支持压缩页，新格式
innodb_file_format = Barracuda
# 设置文件格式最高版本
innodb_file_format_max = Barracuda
# 刷新脏页临近页
innodb_flush_neighbors = 1
# 用来缓冲日志数据的缓冲区大小
innodb_log_buffer_size = 1M
# 单独的清除线程数量-0不适用单独线程
innodb_purge_threads = 4
# 为字段创建索引时，限制的字节长度，超过直接报错
innodb_large_prefix = 1
# 线程并发数
innodb_thread_concurrency = 64
# 将发生的所有死锁信息都记录到错误日志中
innodb_print_all_deadlocks = 1
# 严格检查模式，写法有错误直接报错，不警告
innodb_strict_mode = 1
# 建立索引时用于排序数据的排序缓冲区大小-10M
innodb_sort_buffer_size = 10485760
# 转储缓冲池中read out and dump 的最近使用的页的占比
innodb_buffer_pool_dump_pct = 40
# page cleaner线程数量
innodb_page_cleaners = 4
# 开启在线回收undo log日志文件
innodb_undo_log_truncate = 1
# 超过这个阈值时触发回收
innodb_max_undo_log_size = 2G
# 回收undo日志的频率
innodb_purge_rseg_truncate_frequency = 128
Slave
Slave的配置参考Master
Service
K8S的service文件配置对于主从数据库是一样的，这里以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
- 创建复制用户并配置权限
GRANT REPLICATION SLAVE ON *.* to 'repl'@'%' identified by 'Mysql2017SaasHisenseHics';
FLUSH PRIVILEGES;
- 查看主库当前状态：
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;
- 启动从库复制：
start slave；
- 查看复制状态：
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)
主从配置完成
