2020-11-02 使用K8s部署MySQL 双主集群

最近对线上集群做高可用升级，在对比了几个集群策略之后，综合现有资源、复杂度、可用度之后，选择了两个：

• 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上面。