K8S常見的微服務(wù)中間件部署之MySQL主從

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　作者：尹正杰

版權(quán)聲明：原創(chuàng)作品，謝絕轉(zhuǎn)載！否則將追究法律責(zé)任。

一.編寫MySQL主從同步資源清單
- 1.資源清單準備
- 2.部署服務(wù)
二.配置主從同步
三.課后作業(yè)

一.編寫MySQL主從同步資源清單

1.資源清單準備

[root@master241 01-mysql-master-slave]# cat 01-cm-mysql.yaml 
apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-conf
data:
  master: |
    [mysqld]
    server_id=100
    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
    pid-file=/var/run/mysqld/mysqld.pid
    character-set-server=utf8mb4
    default_storage_engine=InnoDB

    [client]
    socket=/var/run/mysqld/mysqld.sock
    !includedir /etc/mysql/conf.d/

  slave: |
    [mysqld]
    server_id=200
    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
    pid-file=/var/run/mysqld/mysqld.pid
    character-set-server=utf8mb4
    default_storage_engine=InnoDB
    read_only=ON      　　　　
    relay_log=relay-log  　　　
    relay_log_index=relay-log.index 

    [client]
    socket=/var/run/mysqld/mysqld.sock
    !includedir /etc/mysql/conf.d/
[root@master241 01-mysql-master-slave]# 
[root@master241 01-mysql-master-slave]# 
[root@master241 01-mysql-master-slave]# cat 02-secret-mysql.yaml 
apiVersion: v1
kind: Secret
metadata:
  name: mysql-passwd
type: Opaque
stringData:
  MYSQL_ROOT_PASSWORD: yinzhengjie
[root@master241 01-mysql-master-slave]# 
[root@master241 01-mysql-master-slave]# 
[root@master241 01-mysql-master-slave]# cat 03-deploy-mysql.yaml 
apiVersion:  apps/v1
kind: Deployment
metadata:
  name: deploy-master
spec:
  replicas: 1
  selector:
    matchLabels:
      apps: master
  template:
    metadata:
      labels:
        apps: master
    spec:
      volumes:
      - name: data
        configMap:
          name: mysql-conf
          items:
          - key: master
            path: my.cnf
      containers:
      - image: docker.io/library/mysql:8.0.36-oracle
        volumeMounts:
        - name: data
          mountPath: /etc/my.cnf
          subPath: my.cnf
        name: c1
        env:
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-passwd
              key: MYSQL_ROOT_PASSWORD
        - name: MYSQL_DATABASE
          value: yinzhengjie
        - name: MYSQL_USER
          value: admin
        - name: MYSQL_PASSWORD
          value: jasonyin2020
        args:
        - --character-set-server=utf8 
        - --collation-server=utf8_bin 
        - --default-authentication-plugin=mysql_native_password

---

apiVersion:  apps/v1
kind: Deployment
metadata:
  name: deploy-slave
spec:
  replicas: 1
  selector:
    matchLabels:
      apps: slave
  template:
    metadata:
      labels:
        apps: slave
    spec:
      volumes:
      - name: data
        configMap:
          name: mysql-conf
          items:
          - key: slave
            path: my.cnf
      containers:
      - image: docker.io/library/mysql:8.0.36-oracle
        volumeMounts:
        - name: data
          mountPath: /etc/my.cnf
          subPath: my.cnf
        name: c1
        env:
        - name: MYSQL_ALLOW_EMPTY_PASSWORD
          value: "yes"
        args:
        - --character-set-server=utf8 
        - --collation-server=utf8_bin 
        - --default-authentication-plugin=mysql_native_password
[root@master241 01-mysql-master-slave]# 
[root@master241 01-mysql-master-slave]# 
[root@master241 01-mysql-master-slave]# cat 04-svc-mysql.yaml 
apiVersion: v1
kind: Service
metadata:
  name: svc-mysql
spec:
  ports:
  - port: 3306
  selector:
    apps: master
  type: ClusterIP
[root@master241 01-mysql-master-slave]#

2.部署服務(wù)

[root@master241 01-mysql-master-slave]# ll
total 24
drwxr-xr-x 2 root root 4096 Jul 10 14:56 ./
drwxr-xr-x 3 root root 4096 Jul 10 11:38 ../
-rw-r--r-- 1 root root  979 Jul 10 11:38 01-cm-mysql.yaml
-rw-r--r-- 1 root root  119 Jul 10 11:57 02-secret-mysql.yaml
-rw-r--r-- 1 root root 1822 Jul 10 14:56 03-deploy-mysql.yaml
-rw-r--r-- 1 root root  134 Jul 10 11:39 04-svc-mysql.yaml
[root@master241 01-mysql-master-slave]# 
[root@master241 01-mysql-master-slave]# 
[root@master241 01-mysql-master-slave]# kubectl apply -f .
configmap/mysql-conf created
secret/mysql-passwd created
deployment.apps/deploy-master created
deployment.apps/deploy-slave created
service/svc-mysql created
[root@master241 01-mysql-master-slave]# 
[root@master241 01-mysql-master-slave]# kubectl get pods -o wide -l "apps in (master,slave)"
NAME                             READY   STATUS    RESTARTS   AGE   IP               NODE        NOMINATED NODE   READINESS GATES
deploy-master-7cb4bd8f69-9v74l   2/2     Running   0          63s   10.100.165.152   worker242   <none>           <none>
deploy-slave-b56d49979-vcxst     2/2     Running   0          63s   10.100.207.16    worker243   <none>           <none>
[root@master241 01-mysql-master-slave]#

二.配置主從同步

1.主庫創(chuàng)建拷貝數(shù)據(jù)的賬號

[root@master241 01-mysql-master-slave]# kubectl exec -it deploy-master-7cb4bd8f69-9v74l -- mysql -pyinzhengjie
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 9
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> SELECT @@server_id;
+-------------+
| @@server_id |
+-------------+
|         100 |
+-------------+
1 row in set (0.00 sec)

mysql> CREATE USER jasonyin IDENTIFIED BY 'yinzhengjie';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'jasonyin';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR jasonyin;
+--------------------------------------------------+
| Grants for jasonyin@%                            |
+--------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `jasonyin`@`%` |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |      668 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

2.從庫和主庫建立鏈接

[root@master241 ~]# kubectl exec -it deploy-slave-b56d49979-vcxst -- mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
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> SELECT @@server_id;
+-------------+
| @@server_id |
+-------------+
|         200 |
+-------------+
1 row in set (0.00 sec)

mysql> CHANGE MASTER TO 
    ->   MASTER_HOST='svc-mysql', 
    ->   MASTER_USER='jasonyin', 
    ->   MASTER_PASSWORD='yinzhengjie', 
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='binlog.000002', 
    ->   MASTER_LOG_POS=668,
    ->   MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 10 warnings (0.01 sec)

mysql> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: svc-mysql
                  Master_User: jasonyin
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 668
               Relay_Log_File: relay-log  　　　.000002
                Relay_Log_Pos: 323
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 668
              Relay_Log_Space: 538
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 100
                  Master_UUID: 027807bd-5d5b-11f0-aa95-6aee3b288e24
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

mysql>

3.測試驗證主從同步

3.1 主庫寫入數(shù)據(jù)

[root@master241 01-mysql-master-slave]# kubectl exec -it deploy-master-7cb4bd8f69-9v74l -- mysql -pyinzhengjie
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> CREATE DATABASE wordpress;
Query OK, 1 row affected (0.01 sec)

mysql> USE wordpress
Database changed
mysql> CREATE TABLE blog(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(255) NOT NULL, hobby VARCHAR(255) NOT NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO blog(name,hobby) VALUES ('YinZhengJie','http://www.rzrgm.cn/yinzhengjie');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM blog;
+----+-------------+-------------------------------------+
| id | name        | hobby                               |
+----+-------------+-------------------------------------+
|  1 | YinZhengJie | http://www.rzrgm.cn/yinzhengjie |
+----+-------------+-------------------------------------+
1 row in set (0.00 sec)

mysql>

3.2 從庫查詢數(shù)據(jù)

[root@master241 ~]# kubectl exec -it deploy-slave-b56d49979-vcxst -- mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
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.00 sec)

mysql> SHOW TABLES FROM wordpress;
+---------------------+
| Tables_in_wordpress |
+---------------------+
| blog                |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM wordpress.blog;
+----+-------------+-------------------------------------+
| id | name        | hobby                               |
+----+-------------+-------------------------------------+
|  1 | YinZhengJie | http://www.rzrgm.cn/yinzhengjie |
+----+-------------+-------------------------------------+
1 row in set (0.00 sec)

mysql>

三.課后作業(yè)

完善MySQL主從復(fù)制案例，要求部署MySQL主從后，自動實現(xiàn)主從復(fù)制。


tips:
	- 需要先啟動主庫;
	- 可以使用Deployment或者StatefulSets控制器來實現(xiàn);

posted @ 2025-07-08 23:04 尹正杰閱讀(210) 評論(0) 收藏舉報

刷新頁面返回頂部

尹正杰

K8S常見的微服務(wù)中間件部署之MySQL主從

一.編寫MySQL主從同步資源清單

1.資源清單準備

2.部署服務(wù)

二.配置主從同步

1.主庫創(chuàng)建拷貝數(shù)據(jù)的賬號

2.從庫和主庫建立鏈接

3.測試驗證主從同步

3.1 主庫寫入數(shù)據(jù)

3.2 從庫查詢數(shù)據(jù)

三.課后作業(yè)

公告