k8s基于sts实现MySQL主从方式
|
文章
|
36

486 字
|
44 分钟

1.方法一: 基于启动命令行操作

[root@k8s-cluster251 manifests]# cat 03-sts-mysql-master-slave-command.yaml 
apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-conf
data:
  mysql.conf: |
    [mysqld]
    skip-host-cache
    skip-name-resolve
    datadir=/var/lib/mysql
    socket=/var/run/mysqld/mysqld.sock
    secure-file-priv=/var/lib/mysql-files
    user=mysql
    log-bin=mysql-bin
    server-id=101
    pid-file=/var/run/mysqld/mysqld.pid
    
    
    [client]
    socket=/var/run/mysqld/mysqld.sock
    
    !includedir /etc/mysql/conf.d/

  sync-data.sh: |
    #!/bin/bash
    id=`echo $HOSTNAME | awk -F '-' '{print $NF}'`
    sleep 20
    if [[ $id -eq 0 ]]
       then
          mysql -pyinzhengjie -e "CREATE USER 'copy'@'%' IDENTIFIED BY 'yinzhengjie';"
          mysql -pyinzhengjie -e "GRANT REPLICATION SLAVE ON *.* TO 'copy'@'%';"
          mysql -pyinzhengjie -e "FLUSH PRIVILEGES;"
       else
          mysqldump  -h sts-mysql-0.master-slave  -pyinzhengjie -A > /tmp/all.sql 
          mysql -pyinzhengjie < /tmp/all.sql 
          filename=`mysql -h sts-mysql-0.master-slave  -pyinzhengjie -e "SHOW MASTER STATUS;" | awk 'NR==2{print$1}'`
          position=`mysql -h sts-mysql-0.master-slave  -pyinzhengjie -e "SHOW MASTER STATUS;" | awk 'NR==2{print$2}'`
          cat > /tmp/test.sql <<EOF
    CHANGE MASTER TO MASTER_HOST='sts-mysql-0.master-slave',MASTER_USER='copy',MASTER_PASSWORD='yinzhengjie',MASTER_LOG_FILE='$filename',MASTER_LOG_POS=$position;
    EOF
          mysql -pyinzhengjie < /tmp/test.sql
          mysql -pyinzhengjie -e "START SLAVE;"
    fi

---

apiVersion: v1
kind: Service
metadata:
  name: master-slave
spec:
  ports:
  - port: 3306
    name: db
  clusterIP: None
  selector:
    apps: db

---

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: sts-mysql
spec:
  selector:
    matchLabels:
      apps: db
  serviceName: master-slave
  replicas: 3
  template:
    metadata:
      labels:
        apps: db
    spec:
      volumes:
      - name: my-cnf
        configMap:
          defaultMode: 0777
          name: mysql-conf
          items:
          - key: mysql.conf
            path: mysql.conf
          - key: sync-data.sh
            path: sync-data.sh
      - name: data
        emptyDir: {}
      initContainers:
      - name: i1
        image: registry.cn-hangzhou.aliyuncs.com/yinzhengjie-k8s/apps:v1
        volumeMounts:
        - name: my-cnf
          mountPath: /data
        - name: data
          mountPath: /dest
        env:
        - name: pod-name
          valueFrom:
            fieldRef:
              fieldPath: metadata.name
        command:
        - /bin/sh
        - -c 
        - |
          #!/bin/sh
          id=`echo $HOSTNAME | awk -F '-' '{print $NF}'`
          cp /data/mysql.conf /dest/
          if [[ $id -eq 0 ]]
             then
                exit
             else
                sed -i "/server-id/s#101#$id#" /dest/mysql.conf 
          fi
      containers:
      - name: c1
        ports:
        - containerPort: 3306
          name: db
        image: harbor250.yinzhengjie.com/yinzhengjie-db/mysql:8.0.36-oracle
        imagePullPolicy: Always
        volumeMounts:
        - name: data
          mountPath: /data
        command:
        - /bin/bash
        - -c 
        - |
          docker-entrypoint.sh --defaults-file=/data/mysql.conf \
              --character-set-server=utf8mb4 \
              --collation-server=utf8mb4_unicode_ci \
              --default-authentication-plugin=mysql_native_password &
          sleep 5
          /sync-data.sh
          tail -f /etc/hosts
        env:
        - name: MYSQL_ROOT_PASSWORD
          value: yinzhengjie
        - name: MYSQL_DATABASE
          value: wordpress
        - name: MYSQL_USER
          value: linux101
        - name: MYSQL_PASSWORD
          value: "JasonYin"
        volumeMounts:
        - name: data
          mountPath: /data
        - name: my-cnf
          mountPath: /sync-data.sh
          subPath: sync-data.sh
[root@k8s-cluster251 manifests]#

2.方法二: 基于lifecycle的postStart机制

[root@k8s-cluster251 manifests]# cat 04-sts-mysql-master-slave-lifecycle.yaml 
apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-conf
data:
  mysql.conf: |
    [mysqld]
    skip-host-cache
    skip-name-resolve
    datadir=/var/lib/mysql
    socket=/var/run/mysqld/mysqld.sock
    secure-file-priv=/var/lib/mysql-files
    user=mysql
    log-bin=mysql-bin
    server-id=101
    pid-file=/var/run/mysqld/mysqld.pid
    
    
    [client]
    socket=/var/run/mysqld/mysqld.sock
    
    !includedir /etc/mysql/conf.d/

  sync-data.sh: |
    #!/bin/bash
    id=`echo $HOSTNAME | awk -F '-' '{print $NF}'`
    sleep 20
    if [[ $id -eq 0 ]]
       then
          mysql -pyinzhengjie -e "CREATE USER 'copy'@'%' IDENTIFIED BY 'yinzhengjie';"
          mysql -pyinzhengjie -e "GRANT REPLICATION SLAVE ON *.* TO 'copy'@'%';"
          mysql -pyinzhengjie -e "FLUSH PRIVILEGES;"
       else
          mysqldump  -h sts-mysql-0.master-slave  -pyinzhengjie -A > /tmp/all.sql 
          mysql -pyinzhengjie < /tmp/all.sql 
          filename=`mysql -h sts-mysql-0.master-slave  -pyinzhengjie -e "SHOW MASTER STATUS;" | awk 'NR==2{print$1}'`
          position=`mysql -h sts-mysql-0.master-slave  -pyinzhengjie -e "SHOW MASTER STATUS;" | awk 'NR==2{print$2}'`
          cat > /tmp/test.sql <<EOF
    CHANGE MASTER TO MASTER_HOST='sts-mysql-0.master-slave',MASTER_USER='copy',MASTER_PASSWORD='yinzhengjie',MASTER_LOG_FILE='$filename',MASTER_LOG_POS=$position;
    EOF
          mysql -pyinzhengjie < /tmp/test.sql
          mysql -pyinzhengjie -e "START SLAVE;"
    fi

---

apiVersion: v1
kind: Service
metadata:
  name: master-slave
spec:
  ports:
  - port: 3306
    name: db
  clusterIP: None
  selector:
    apps: db

---

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: sts-mysql
spec:
  selector:
    matchLabels:
      apps: db
  serviceName: master-slave
  replicas: 3
  template:
    metadata:
      labels:
        apps: db
    spec:
      volumes:
      - name: my-cnf
        configMap:
          defaultMode: 0777
          name: mysql-conf
          items:
          - key: mysql.conf
            path: mysql.conf
          - key: sync-data.sh
            path: sync-data.sh
      - name: data
        emptyDir: {}
      initContainers:
      - name: i1
        image: registry.cn-hangzhou.aliyuncs.com/yinzhengjie-k8s/apps:v1
        volumeMounts:
        - name: my-cnf
          mountPath: /data
        - name: data
          mountPath: /dest
        env:
        - name: pod-name
          valueFrom:
            fieldRef:
              fieldPath: metadata.name
        command:
        - /bin/sh
        - -c 
        - |
          #!/bin/sh
          id=`echo $HOSTNAME | awk -F '-' '{print $NF}'`
          cp /data/mysql.conf /dest/
          if [[ $id -eq 0 ]]
             then
                exit
             else
                sed -i "/server-id/s#101#$id#" /dest/mysql.conf 
          fi
      containers:
      - name: c1
        ports:
        - containerPort: 3306
          name: db
        image: harbor250.yinzhengjie.com/yinzhengjie-db/mysql:8.0.36-oracle
        imagePullPolicy: Always
        lifecycle:
          postStart:
            exec:
              command:
              - /bin/bash
              - -c
              - /sync-data.sh
        volumeMounts:
        - name: data
          mountPath: /data
        args:
        - --defaults-file=/data/mysql.conf
        - --character-set-server=utf8mb4
        - --collation-server=utf8mb4_unicode_ci
        - --default-authentication-plugin=mysql_native_password
        env:
        - name: MYSQL_ROOT_PASSWORD
          value: yinzhengjie
        - name: MYSQL_DATABASE
          value: wordpress
        - name: MYSQL_USER
          value: linux101
        - name: MYSQL_PASSWORD
          value: "JasonYin"
        volumeMounts:
        - name: data
          mountPath: /data
        - name: my-cnf
          mountPath: /sync-data.sh
          subPath: sync-data.sh
[root@k8s-cluster251 manifests]#

二.测试验证

1.主库执行命令操作

[root@k8s-cluster251 manifests]# kubectl apply -f  02-sts-mysql-master-slave-command.yaml 
configmap/mysql-conf created
service/master-slave created
statefulset.apps/sts-mysql created
[root@k8s-cluster251 manifests]# 
[root@k8s-cluster251 manifests]# kubectl get pods -o wide
NAME          READY   STATUS     RESTARTS   AGE   IP               NODE             NOMINATED NODE   READINESS GATES
sts-mysql-0   1/1     Running    0          5s    10.100.230.163   k8s-cluster251   <none>           <none>
sts-mysql-1   1/1     Running    0          3s    10.100.105.30    k8s-cluster253   <none>           <none>
sts-mysql-2   0/1     Init:0/1   0          0s    <none>           k8s-cluster252   <none>           <none>
[root@k8s-cluster251 manifests]# 
[root@k8s-cluster251 manifests]# kubectl get pods -o wide
NAME          READY   STATUS    RESTARTS   AGE   IP               NODE             NOMINATED NODE   READINESS GATES
sts-mysql-0   1/1     Running   0          8s    10.100.230.163   k8s-cluster251   <none>           <none>
sts-mysql-1   1/1     Running   0          6s    10.100.105.30    k8s-cluster253   <none>           <none>
sts-mysql-2   1/1     Running   0          3s    10.100.201.152   k8s-cluster252   <none>           <none>
[root@k8s-cluster251 manifests]# 
[root@k8s-cluster251 manifests]# kubectl exec -it sts-mysql-0 -- mysql -pyinzhengjie
Defaulted container "c1" out of: c1, i1 (init)
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.36 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wordpress          |
+--------------------+
5 rows in set (0.01 sec)

mysql> USE wordpress
Database changed
mysql> 
mysql> SHOW TABLES;
Empty set (0.00 sec)

mysql> 
mysql> CREATE TABLE t1(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL, hobby VARCHAR(255) NOT NULL);
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> INSERT INTO t1(name,hobby) VALUES ('SunWuKong','ZiXiaXianZi');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t1;
+----+-----------+-------------+
| id | name      | hobby       |
+----+-----------+-------------+
|  1 | SunWuKong | ZiXiaXianZi |
+----+-----------+-------------+
1 row in set (0.00 sec)

mysql>

2.从库执行命令操作

[root@k8s-cluster251 manifests]# kubectl exec -it sts-mysql-1 -c c1 -- mysql -pyinzhengjie -e "SELECT * FROM wordpress.t1"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-----------+-------------+
| id | name      | hobby       |
+----+-----------+-------------+
|  1 | SunWuKong | ZiXiaXianZi |
+----+-----------+-------------+
[root@k8s-cluster251 manifests]# 
[root@k8s-cluster251 manifests]# kubectl exec -it sts-mysql-2 -c c1 -- mysql -pyinzhengjie -e "SELECT * FROM wordpress.t1"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-----------+-------------+
| id | name      | hobby       |
+----+-----------+-------------+
|  1 | SunWuKong | ZiXiaXianZi |
+----+-----------+-------------+
[root@k8s-cluster251 manifests]# 


[root@k8s-cluster251 manifests]# kubectl exec -it sts-mysql-1 -c c1 -- mysql -pyinzhengjie -e "SHOW SLAVE STATUS\G" 
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: sts-mysql-0.master-slave
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1427
               Relay_Log_File: sts-mysql-1-relay-bin.000002
                Relay_Log_Pos: 926
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
			...
			
			
[root@k8s-cluster251 manifests]# kubectl exec -it sts-mysql-2 -c c1 -- mysql -pyinzhengjie -e "SHOW SLAVE STATUS\G" 
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: sts-mysql-0.master-slave
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1427
               Relay_Log_File: sts-mysql-2-relay-bin.000002
                Relay_Log_Pos: 926
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
			  ...
记录~
Linux
暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																

							
							
						

																

							
							
						

										
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!
	


									

			

			
			
		

	






上一篇

                    

                    
			        推荐文章
		            

		            

							
世界，您好！

							
							

							
云原生学习 – Prometheus 命令速查手册

							
							

							
云原生学习 – Grafana 命令速查手册

							
							

							
云原生学习 – Docker 命令速查手册

							
							

							
云原生学习 – Docker Compose 命令速查手册

							
							

							
K8S常见的微服务中间件部署之RabbitMQ

							
							

							
云原生学习 – Kibana 命令速查手册

							
							

							
云原生学习 – Elasticsearch 命令速查手册

							
							

							
云原生学习 – Ceph 命令速查手册

							
							

							
云原生学习 – Filebeat 命令速查手册