Mysql主从

本文作者：丁辉

部署 Mysql 主从

创建命名空间

kubectl create ns mysql

主节点配置

1. 编辑 mysql 配置文件

vi my.cnf

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# 服务端默认utf8编码
character-set-server=utf8mb4
# 默认存储引擎
default-storage-engine=INNODB

# 主从配置
log-bin=binlog
server-id=121
gtid-mode=on
enforce-gtid-consistency=on
log-slave-updates=on
expire_logs_days=14

# Compatible with versions before 8.0
default_authentication_plugin=mysql_native_password
skip-host-cache
skip-name-resolve

[client]
#设置客户端编码
default-character-set=utf8mb4
[mysql]
# 设置mysql客户端默认编码
default-character-set=utf8mb4

# Custom config should go here
!includedir /etc/mysql/conf.d/
# Custom config should go here
!includedir /etc/mysql/conf.d/

kubectl create configmap mysql-master-conf --from-file=./my.cnf -n mysql

2. 编辑 Yaml

vi mysql-master.yaml

apiVersion: apps/v1
kind: StatefulSet
metadata:
  namespace: mysql
  name: mysql-master
spec:
  replicas: 1
  serviceName: mysql-master-service
  selector:
    matchLabels:
      app: mysql-master
  template:
    metadata:
      labels:
        app: mysql-master
    spec:
      containers:
      - name: mysql-master
        image: mysql:8.0
        env:
        - name: MYSQL_ROOT_PASSWORD
          value: password
        volumeMounts:
        - mountPath: /var/lib/mysql
          name: data
        - name: file
          mountPath: /etc/mysql/my.cnf
          subPath: my.cnf
        ports:
        - containerPort: 3306
          protocol: TCP
        livenessProbe:
           exec:
             command:
               - mysql
               - --user=root
               - --password=password
               - --execute=SELECT 1
           initialDelaySeconds: 10 #启动后等待10秒开始检测
           periodSeconds: 10 #每隔10秒检测一次
      nodeName: #node1
      volumes:
      - name: data
        hostPath:
          path: /opt/mysql/data
      - name: file
        configMap:
          name: mysql-master-conf
---
apiVersion: v1
kind: Service
metadata:
  namespace: mysql
  name: mysql-master-service
spec:
  selector:
    app: mysql-master
  ports:
  - port: 3306
    targetPort: 3306
    protocol: TCP
  type: ClusterIP

kubectl apply -f mysql-master.yaml

从节点配置

1. 编辑 mysql 配置文件

vi my.cnf

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# 服务端默认utf8编码
character-set-server=utf8mb4
# 默认存储引擎
default-storage-engine=INNODB

# 主从配置
server-id=122
gtid-mode=on
enforce-gtid-consistency=on
log-slave-updates=on
expire_logs_days=14

# Compatible with versions before 8.0
default_authentication_plugin=mysql_native_password
skip-host-cache
skip-name-resolve

[client]
#设置客户端编码
default-character-set=utf8mb4
[mysql]
# 设置mysql客户端默认编码
default-character-set=utf8mb4

# Custom config should go here
!includedir /etc/mysql/conf.d/
# Custom config should go here
!includedir /etc/mysql/conf.d/

kubectl create configmap mysql-slave-conf --from-file=./my.cnf -n mysql

2. 编辑 Yaml

vi mysql-slave.yaml

apiVersion: apps/v1
kind: StatefulSet
metadata:
  namespace: mysql
  name: mysql-slave
spec:
  replicas: 1
  serviceName: mysql-slave-service
  selector:
    matchLabels:
      app: mysql-slave
  template:
    metadata:
      labels:
        app: mysql-slave
    spec:
      containers:
      - name: mysql-slave
        image: mysql:8.0
        env:
        - name: MYSQL_ROOT_PASSWORD
          value: password
        volumeMounts:
        - mountPath: /var/lib/mysql
          name: data
        - name: file
          mountPath: /etc/mysql/my.cnf
          subPath: my.cnf
        ports:
        - containerPort: 3306
          protocol: TCP
        livenessProbe:
           exec:
             command:
               - mysql
               - --user=root
               - --password=password
               - --execute=SELECT 1
           initialDelaySeconds: 10 #启动后等待10秒开始检测
           periodSeconds: 10 #每隔10秒检测一次
      nodeName: #node2
      volumes:
      - name: data
        hostPath:
          path: /opt/mysql/data
      - name: file
        configMap:
          name: mysql-slave-conf
---
apiVersion: v1
kind: Service
metadata:
  namespace: mysql
  name: mysql-slave-service
spec:
  selector:
    app: mysql-slave
  ports:
  - port: 3306
    targetPort: 3306
    protocol: TCP
  type: ClusterIP

vi mysql-slave.yaml

配置主从同步

1. 登录主节点

kubectl exec -it mysql-master-0 -n mysql bash

2. 登录数据库

mysql -u root -ppassword

3. 配置

CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'slave';
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
flush privileges;
SHOW MASTER STATUS;

4. 登录从节点

kubectl exec -it mysql-slave-0 -n mysql bash

5. 登录数据库

mysql -u root -ppassword

6. 配置

CHANGE MASTER TO
MASTER_HOST='IP',
MASTER_USER='slave',
MASTER_PASSWORD='slave',
MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.00000*',   #列：binlog.000001
MASTER_LOG_POS=***;   #列：868

7. 开启主从同步

start slave;

8. 查看同步状态

show slave status\G;

查看到这两个参数为 Yes 则代表配置成功

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes

9. 登录主节点，创建数据库

create database console;
create database region;

从节点查看仓库是否已同步