19--k8s之docker实现主从复制、 k8s实现主从复制

目录

• 一、docker中实现主从复制
  • 1、启动主库，启动从库（必须同一个网桥）
  • 2、在主库中创建主从复制账号
  • 3、将从库加入集群
• 二、k8s中实现主从复制
  • 1.主节点yaml配置
  • 2.从节点yaml配置
  • 3.构建多从库镜像

一、docker中实现主从复制

1、启动主库，启动从库（必须同一个网桥）

# 主备配置文件
[root@kubernetes-node-05 ~]# cat mysql-master.cnf 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=1
binlog_format=row
log-bin=mysql-bin
skip-name-resolve
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
relay_log_purge=0
symbolic-links=0
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[root@kubernetes-node-05 ~]# cat mysql-node.cnf 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=2     #改成2
binlog_format=row
log-bin=mysql-bin
skip-name-resolve
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
relay_log_purge=0
symbolic-links=0
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

relay-log = mysql-relay-bin    #从库的终极日志，等于主库的binlog日志
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
replicate-wild-ignore-table=sys.%
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# 创建网桥
[root@kubernetes-node-05 ~]# docker network create mysql

# 开启主从库
[root@kubernetes-node-05 ~]# docker run -d --name mysql-master-01 -p 3306:3306 --network=mysql -e MYSQL_ROOT_PASSWORD=1111 -v mysql-master.cnf:/etc/mysql/my.cnf mysql:5.7

[root@kubernetes-node-05 ~]# docker run -d --name mysql-node-01  --network=mysql -e MYSQL_ROOT_PASSWORD=1111 -v /root/mysql-node.cnf:/etc/mysql/my.cnf mysql:5.7

2、在主库中创建主从复制账号

# 主库上执行
[root@k8s-n-01 ~]# docker exec -it mysql-node-01 bash
root@f7e3a23ddcbf:/# mysql -uroot -p1111
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
root@f7e3a23ddcbf:/# mysql -uroot -p1111 -hmysql-master-01   #报错解决

mysql> grant replication slave on *.* to 'slave'@'%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

3、将从库加入集群

# 在从库中执行, 加入主节点
mysql> change master to master_host='mysql-master-01',master_port=3306,master_user='slave',master_password='123',master_log_file='mysql-bin.000001',master_log_pos=154;
Query OK, 0 rows affected, 1 warning (0.04 sec)

#开启同步
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

# 查看主从复制状态
mysql> show slave status G

如果还要加入从库，加一个my.cnf，改id，加入主机即可*

二、k8s中实现主从复制

一主一从

1.主节点yaml配置

[root@k8s-m-01 ~]# mkdir mysql-master
[root@k8s-m-01 ~]# cd mysql-master/
 
[root@k8s-m-01 mysql-master]# ll    #创建5个yaml文件
-rw-r--r-- 1 root root  668 Aug 14 08:22 config.yaml
-rw-r--r-- 1 root root  828 Aug 14 08:23 deployment.yaml
-rw-r--r-- 1 root root 1292 Aug 14 10:15 job.yaml
-rw-r--r-- 1 root root   87 Aug 14 08:21 namespace.yaml
-rw-r--r-- 1 root root  225 Aug 14 08:24 service.yaml

root@k8s-m-01 mysql-master]# cat namespace.yaml 
kind: Namespace      #创建命名空间
apiVersion: v1
metadata:
  name: mysql-master

[root@k8s-m-01 mysql-master]# cat config.yaml 
kind: ConfigMap        # 把配置文件写进去
apiVersion: v1
metadata:
  name: mysql-master-config
  namespace: mysql-master
data:
  my.cnf: |
    [mysqld]
    datadir=/var/lib/mysql
    server-id=1
    binlog_format=row
    log-bin=mysql-bin
    skip-name-resolve
    gtid-mode=on
    enforce-gtid-consistency=true
    log-slave-updates=1
    relay_log_purge=0
    symbolic-links=0
    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

    [mysqld_safe]
    log-error=/var/log/mysqld.log
---
kind: Secret    #密码
apiVersion: v1
metadata:
  namespace: mysql-master
  name: mysql-password
data:
  MYSQL_ROOT_PASSWORD: YWJjMTIzNDU2Cg==      # echo abc123456 | base64


[root@k8s-m-01 mysql-master]# cat deployment.yaml 
kind: Deployment
apiVersion: apps/v1
metadata:
  name: mysql-master
  namespace: mysql-master
spec:
  selector:
    matchLabels:
      app: mysql-master
  template:
    metadata:
      labels:
        app: mysql-master
    spec:
      containers:
        - name: mysql
          image: mysql:5.7
          imagePullPolicy: IfNotPresent #镜像拉取策略
          env:
            - name: MYSQL_ROOT_PASSWORD
              value: "abc123456"
          envFrom:  #定义环境变量
            - secretRef:
                name: mysql-password
          volumeMounts:
            - mountPath: /etc/mysql/my.cnf   #必须写文件名，
              name: mysql-master-config
              subPath: my.cnf
      volumes:
        - name: mysql-master-config
          configMap:
            name: mysql-master-config
            items:
              - key: my.cnf
                path: my.cnf
                
[root@k8s-m-01 mysql-master]# vim service.yaml 
kind: Service
apiVersion: v1
metadata:
  name: mysql-master
  namespace: mysql-master
spec:
  ports:
    - port: 3306
      targetPort: 3306
      protocol: TCP
      name: mysql-master-port
  selector:
    app: mysql-master
    
[root@k8s-m-01 mysql-master]# kubectl apply -f namespace.yaml 
namespace/mysql-master created

[root@k8s-m-01 mysql-master]# kubectl apply -f ./
configmap/mysql-master-config created
secret/mysql-password created
deployment.apps/mysql-master created
namespace/mysql-master unchanged
service/mysql-master created

[root@k8s-m-01 mysql-master]# vim job.yaml #批量处理短暂的一次性(每个任务仅运行一次就结束)任务
kind: Job   #job控制器
apiVersion: batch/v1
metadata:
  name: mysql-master-job
  namespace: mysql-master
spec:
  template:
    metadata:
      labels:
        app: test-job
    spec:
      restartPolicy: OnFailure # 重启策略只能设置为Never或者OnFailure
      containers:
        - name: mysql
          imagePullPolicy: IfNotPresent #镜像拉取策略
          image: mysql:5.7

          command:   # 指定执行的命令
            - "/bin/sh"
            - "-c"
            - |
              MYSQL_MASTER_SVC_NAME=mysql-master.mysql-master.svc.cluster.local
                                   
              MYSQL_MASTER_PASSWORD=abc123456
              MYSQL_MASTER_USERNAME=root

              while true
              do
                mysql -u${MYSQL_MASTER_USERNAME} -p${MYSQL_MASTER_PASSWORD} -h${MYSQL_MASTER_SVC_NAME} -e 'show databases;' >/dev/null 2>&1
                if [ $? -eq 0 ];then

                    mysql -u${MYSQL_MASTER_USERNAME} -p${MYSQL_MASTER_PASSWORD} -h${MYSQL_MASTER_SVC_NAME} -e "grant replication slave on *.* to 'slave'@'%' identified by '123'; flush privileges;"

                    break;
                fi
                sleep 1;
              done
              
 [root@k8s-m-01 mysql-master]# kubectl apply -f job.yaml 
job.batch/mysql-master-job created
[root@k8s-m-01 mysql-master]# kubectl get jobs -n mysql-master
NAME               COMPLETIONS   DURATION   AGE
mysql-master-job   1/1           2s         3m10s

1、创建一个POD ---> 去执行指定的命令
2、如果运行成功，则状态变成：Completed
3、如果运行失败，则状态变成：Error
[root@k8s-m-01 mysql-master]# kubectl get pod -n mysql-master
NAME                            READY   STATUS      RESTARTS   AGE
mysql-master-5b68694cd7-rhdk9   1/1     Running     0          111m
mysql-master-job-wzj6s          0/1     Completed   0          86s

[root@k8s-m-01 mysql-master]# kubectl run --rm -it tests --image=mysql:5.7 bash
If you don't see a command prompt, try pressing enter.

root@tests:/# mysql -uroot -pabc123456 -hmysql-master.mysql-master.svc.cluster.local 
mysql: [Warning] Using a password on the command line interface can be insecure.
...
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select host,user from user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | root          |
| %         | slave         |  #slave用户部署完
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
5 rows in set (0.00 sec)

mysql> show master statusG
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 1050
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 1dcbd9ec-fc96-11eb-ba94-da7a452cf74a:1-9
1 row in set (0.00 sec)

2.从节点yaml配置

[root@k8s-m-01 ~]# mkdir mysql-node
[root@k8s-m-01 ~]# cd mysql-node/
[root@k8s-m-01 mysql-node]# ll
total 0
-rw-r--r-- 1 root root 0 Aug 14 11:05 config.yaml
-rw-r--r-- 1 root root 0 Aug 14 11:05 deployment.yaml
-rw-r--r-- 1 root root 0 Aug 14 11:06 job.yaml
-rw-r--r-- 1 root root 0 Aug 14 11:05 namespace.yaml
-rw-r--r-- 1 root root 0 Aug 14 11:05 service.yaml

[root@k8s-m-01 mysql-node]# vim namespace.yaml
kind: Namespace
apiVersion: v1
metadata:
  name: mysql-node

[root@k8s-m-01 mysql-node]# vim config.yaml
kind: ConfigMap
apiVersion: v1
metadata:
  name: mysql-node-config
  namespace: mysql-node
data:
  my.cnf: |
    [mysqld]
    datadir=/var/lib/mysql
    server-id=2
    binlog_format=row
    log-bin=mysql-bin
    skip-name-resolve
    gtid-mode=on
    enforce-gtid-consistency=true
    log-slave-updates=1
    relay_log_purge=0
    symbolic-links=0
    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

    relay-log=mysql-relay-bin
    replicate-wild-ignore-table=mysql.%
    replicate-wild-ignore-table=test.%
    replicate-wild-ignore-table=information_schema.%
    replicate-wild-ignore-table=sys.%

    [mysqld_safe]
    log-error=/var/log/mysqld.log
---
kind: Secret
apiVersion: v1
metadata:
  namespace: mysql-node
  name: mysql-password
data:
  MYSQL_ROOT_PASSWORD: YWJjMTIzNDU2Cg==
  

[root@k8s-m-01 mysql-node]# vim deployment.yaml  
  kind: Deployment
apiVersion: apps/v1
metadata:
  name: mysql-node
  namespace: mysql-node
spec:
  selector:
    matchLabels:
      app: mysql-node
  template:
    metadata:
      labels:
        app: mysql-node
    spec:
      containers:
        - name: mysql
          image: alvinos/mysql-salve:5.7-v2
          imagePullPolicy: IfNotPresent
          env:
            - name: MYSQL_ROOT_PASSWORD
              value: "abc123456"
          envFrom:
            - secretRef:
                name: mysql-password
          volumeMounts:
            - mountPath: /etc/mysql/my.cnf
              name: mysql-node-config
              subPath: my.cnf
      volumes:
        - name: mysql-node-config
          configMap:
            name: mysql-node-config
            items:
              - key: my.cnf
                path: my.cnf
                
[root@k8s-m-01 mysql-node]# vim service.yaml
kind: Service
apiVersion: v1
metadata:
  name: mysql-node
  namespace: mysql-node
spec:
  ports:
    - port: 3306
      targetPort: 3306
      protocol: TCP
      name: mysql-node-port
  selector:
    app: mysql-node
    
[root@k8s-m-01 mysql-node]# vim job.yaml
kind: Job
apiVersion: batch/v1
metadata:
  namespace: mysql-node
  name: mysql-node
spec:
  template:
    spec:
      restartPolicy: OnFailure
      containers:
        - name: mysql
          image: mysql:5.7
          imagePullPolicy: IfNotPresent
          command:
            - "/bin/sh"
            - "-c"
            - |
              MYSQL_NODE_SVC_NAME=mysql-node.mysql-node.svc.cluster.local
              MYSQL_NODE_PASSWORD=abc123456
              MYSQL_NODE_USERNAME=root

              while true
              do
                mysql -u${MYSQL_NODE_USERNAME} -p${MYSQL_NODE_PASSWORD} -h${MYSQL_NODE_SVC_NAME} -e 'show databases;' >/dev/null 2>&1
                if [ $? -eq 0 ];then

                    mysql -u${MYSQL_NODE_USERNAME} -p${MYSQL_NODE_PASSWORD} -h${MYSQL_NODE_SVC_NAME}  -e "show master statusG" > /tmp/log

                    MASTER_LOG_FILE=`/bin/cat /tmp/log | /usr/bin/awk -F: 'NR==2{print $2}' | /usr/bin/tr -d " "`

                    MYSQL_LOG_POS=`/bin/cat /tmp/log | /usr/bin/awk -F: 'NR==3{print $2}' | /usr/bin/tr -d " "`

                    mysql -u${MYSQL_NODE_USERNAME} -p${MYSQL_NODE_PASSWORD} -h${MYSQL_NODE_SVC_NAME} -e "change master to master_host='mysql-master.mysql-master.svc.cluster.local',master_port=3306,master_user='slave',master_password='123',master_log_file='"${MASTER_LOG_FILE}"',master_log_pos="${MYSQL_LOG_POS}";"

                    mysql -u${MYSQL_NODE_USERNAME} -p${MYSQL_NODE_PASSWORD} -h${MYSQL_NODE_SVC_NAME} -e "start slave;"
                    break;
                fi
                sleep 1;
              done

[root@k8s-m-01 mysql-node]# kubectl apply -f namespace.yaml 
namespace/mysql-node unchanged
[root@k8s-m-01 mysql-node]# kubectl apply -f ./
configmap/mysql-node-config created
secret/mysql-password created
deployment.apps/mysql-node created
job.batch/mysql-node created
namespace/mysql-node unchanged
service/mysql-node unchanged

#进入容器测试
[root@k8s-m-01 mysql-node]# kubectl exec -it -n mysql-node mysql-node-794cd4f4bc-476rv -- bash
root@mysql-node-794cd4f4bc-476rv:~# mysql -uroot -pabc123456 -hmysql-node.mysql-node.svc.cluster.local
...
mysql> 
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql-master.mysql-master.svc.cluster.local
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1050
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 1176
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes   #YES
            Slave_SQL_Running: Yes    #yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       ...

#此时进入容器，主从同步
[root@k8s-m-01 ~]# kubectl get pod -n mysql-master 
NAME                            READY   STATUS      RESTARTS   AGE
mysql-master-5b68694cd7-rhdk9   1/1     Running     0          3h33m
mysql-master-job-wzj6s          0/1     Completed   0          102m
[root@k8s-m-01 ~]# kubectl exec -it -n mysql-master mysql-master-5b68694cd7-rhdk9 -- bash
root@mysql-master-5b68694cd7-rhdk9:/# mysql -uroot -pabc123456 -hmysql-master.mysql-master.svc.cluster.local
mysql> create database longlong;
Query OK, 1 row affected (0.00 sec）

#从节点longlong库同步
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| discuz             |
| longlong           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

一主多从

3.构建多从库镜像

[root@docker ~]# cat Dockerfile 
FROM mysql:5.7 

ADD my.cnf /etc/mysql/my.cnf
ADD docker-entrypoint.sh /usr/local/bin/docker-entrypoint.sh
WORKDIR /root

EXPOSE 3306 33060

CMD ["mysqld"]

[root@docker mysql]# cat my.cnf 
[mysqld]
datadir=/var/lib/mysql
server-id=2
binlog_format=row
log-bin=mysql-bin
skip-name-resolve
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
relay_log_purge=0
symbolic-links=0
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
user=mysql

relay-log=mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
replicate-wild-ignore-table=sys.%

[mysqld_safe]
log-error=/var/log/mysqld.log

# 执行这个命令，在启动容器之前进行修改
/bin/echo "/bin/sed -i 's/server-id=2/server-id=`/bin/echo $RANDOM`/g' /etc/mysql/my.cnf" | /bin/sh -

# 将这条命令写入docker-entrypoint.sh添加到/usr/local/bin/docker-entrypoint.sh