| 机器 | 角色 |
|---|---|
| mysql-0 | master\slave |
| mysql-1 | slave\master |
最近对线上集群做高可用升级，在对比了几个集群策略之后，综合现有资源、复杂度、可用度之后，选择了两个：
- MySQL双主的互为备份的2节点集群
- MySQL基于wsrep协议的Galera Cluster的mysql多主集群
初步处于复杂度考虑，选择了方案一。
方案一又有两个版本：
- 利用keepalived做活跃监督
- 利用k8s service做负载均衡，达到和活跃监督一样的效果
我们选择最简单的k8s service，先做测试。无论哪一种，首先都要实现双主的互为备份的2节点部署。
一、部署
选择 dockerhub上的MySQL：5.7.32作为基础镜像。依赖于镜像本身的启动命令，做修改，然后部署。
1.Dockerfile
以dockerhub上的mysql为基础镜像
From mysql:5.7.32
COPY my.cnf /etc/mysql/
COPY replication.sh /usr/local/bin/
COPY docker-entrypoint.sh /usr/local/bin/
ENV MYSQL_ROOT_PASSWORD=123456
RUN chmod 755 /usr/local/bin/replication.sh && \
chmod a+x /usr/local/bin/replication.sh && \
chmod 755 /usr/local/bin/docker-entrypoint.sh && \
chmod a+x /usr/local/bin/docker-entrypoint.sh && \
chmod 755 /etc/mysql/my.cnf && \
chmod a+x /etc/mysql/my.cnf && \
chown -R mysql:mysql /usr/local/bin/replication.sh && \
chown -R mysql:mysql /usr/sbin/mysqld && \
chown -R mysql:mysql /etc/mysql/
ENTRYPOINT ["docker-entrypoint.sh"]
EXPOSE 3306 33060
CMD ["mysqld"]
2.docker-entrypoint.sh
新增一个方法：mysql_replication
mysql_replication() {
echo 'start to do the mysql replication'
master_host=''
if [[ $MY_POD_NAME = 'mysql-0' ]];then
master_host='mysql-1.mysql.'${MY_POD_NAMESPACE}'.svc.cluster.local'
else
master_host='mysql-0.mysql.'${MY_POD_NAMESPACE}'.svc.cluster.local'
fi
echo 'add the master and slave roles, the master host is:' $master_host
replication_user=${MYSQL_REPLICATION_USER:-replication}
replication_password=${MYSQL_REPLICATION_PASSWORD:-replication_password}
echo 'the replication user is: '$replication_user ', pass is: '$replication_password
docker_process_sql --database=mysql <<-EOSQL
CREATE USER '$replication_user'@'%' IDENTIFIED BY '$replication_password' ;
GRANT REPLICATION SLAVE ON *.* TO '$replication_user'@'%' IDENTIFIED BY '$replication_password' ;
FLUSH PRIVILEGES ;
EOSQL
echo 'created the replication user.'
echo 'start to register to master: ' $master_host
master_bin_fetch=`
docker_process_sql --database=mysql <<-EOSQL
show master status;
EOSQL
`
echo 'fetch the master status.'
title='File Position Binlog_Do_DB Binlog_Ignore_DB Executed_Gtid_Set'
title_length=${#title}
master_bin_info=${master_bin_fetch: $title_length+1}
echo 'master bin info is: '$master_bin_info
bin_name='mysql-bin.000002'
bin_length=${#bin_name}
bin_file==${master_bin_info: 0 :$bin_length+1}
position_number=${master_bin_info: $bin_length+1}
echo 'the master bin file is: '$bin_file ', the position number is: '$position_number
#从本地文件读取最新的BIN_FILE,不过现在没有用了，作废了
bin_file=`ls /var/log/mysql | grep mysql-bin.0 | sort -n | tail -1`
echo 'the master bin file is: '$bin_file ', the position number is set to 0'
#不设置MASTER_BIN_FILE和MASTER_LOG_POSITION,去除了master pod的区别设置，依赖MySQL自己去发现
docker_process_sql --database=mysql <<-EOSQL
STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO master_host='$master_host', master_user='$replication_user', master_password='$replication_password';
CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = ('mysql.%');
START SLAVE;
EOSQL
3.my.cnf
这里有一个报错：
2020-11-02T07:35:58.108646Z 2 [ERROR] Slave SQL for channel '': Error 'Operation CREATE USER failed for 'replication'@'%'' on query. Default database: 'mysql'. Query: 'CREATE USER 'replication'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*1A629BF5797BF6AE523D06B26B94561098D18F4C'', Error_code: 1396
2020-11-02T07:35:58.108692Z 2 [Warning] Slave: Operation CREATE USER failed for 'replication'@'%' Error_code: 1396
2020-11-02T07:35:58.108701Z 2 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000002' position 3071516.
是因为主从复制还带有master、slave中的User表，我们新增了用户，在log bin中写有日志，两个MySQL已启动就去同步log bin，造成原有用户已存在，同步失败的错误。
解决这个错误，就要忽略User表的变化，修改my.cnf中的replicate-ignore-db
或者修改REPLICATE_WILD_IGNORE_TABLE
这个还需要调研
# Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# The MySQL Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld]
#这里的ID只能是纯数子，多么痛的领悟
server-id=<%id%>
log-bin=/var/log/mysql/mysql-bin.log
replicate-ignore-db = mysql
log_bin_index = /var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
#log-error=/usr/local/mysql/log/error.log
#log=/usr/local/mysql/log/mysql.log
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
#log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
sql_mode="IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
4.mysql.yaml
apiVersion: apps/v1
kind: StatefulSet
metadata:
name: mysql
labels:
app: mysql
spec:
replicas: 2
updateStrategy:
type: RollingUpdate
serviceName: "mysql"
selector:
matchLabels:
app: mysql
template:
metadata:
labels:
app: mysql
spec:
nodeSelector:
mysql: "true"
securityContext:
runAsUser: 0
containers:
- name: mysql
image: test/mysql:mysql1
env:
- name: MY_POD_NAMESPACE
valueFrom:
fieldRef:
fieldPath: metadata.namespace
- name: MY_POD_NAME
valueFrom:
fieldRef:
fieldPath: metadata.name
imagePullPolicy: Never
ports:
- containerPort: 3306
---
kind: Service
apiVersion: v1
metadata:
name: mysql
spec:
ports:
- port: 3306
targetPort: 3306
selector:
app: mysql
二、测试
分别登陆到两个MySQL，使用
show slave status\G;
创建新的数据库
create database test;
观察新数据库有没有同步到另一个MySQL上面。
