本文最后更新于153 天前，其中的信息可能已经过时，如有错误请发送邮件到big_fw@foxmail.com

一.编写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.部署服务

[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.主库创建拷贝数据的账号

[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 主库写入数据

[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','https://www.cnblogs.com/yinzhengjie');
Query OK, 1 row affected (0.01 sec)

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

mysql>

3.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 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 | https://www.cnblogs.com/yinzhengjie |
+----+-------------+-------------------------------------+
1 row in set (0.00 sec)

mysql>

三.课后作业

完善MySQL主从复制案例，要求部署MySQL主从后，自动实现主从复制。


tips:
	- 需要先启动主库;
	- 可以使用Deployment或者StatefulSets控制器来实现;