1.k8s部署mysql主从
一.编写MySQL主从同步资源清单
1.编写资源清单
01-cm-mysql.yaml
root@master231:/opt/pods/mysql# cat 01-cm-mysql.yaml
apiVersion: v1
kind: ConfigMap
metadata:
name: mysql-conf
data:
master: |
[mysqld]
server_id=100
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-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/
02-secret-mysql.yaml
root@master231:/opt/pods/mysql# cat 02-secret-mysql.yaml
apiVersion: v1
kind: Secret
metadata:
name: mysql-passwd
type: Opaque
stringData:
MYSQL_ROOT_PASSWORD: Lxygwqf
03-deploy-mysql.yaml
root@master231:/opt/pods/mysql# 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: harbor.wuflstu.com/middleware/mysql:8.3.0-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: wufl
- name: MYSQL_USER
value: admin
- name: MYSQL_PASSWORD
value: wufl2026
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: harbor.wuflstu.com/middleware/mysql:8.3.0-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
args:
- --character-set-server=utf8
- --collation-server=utf8_bin
- --default-authentication-plugin=mysql_native_password
04-svc-mysql.yaml
root@master231:/opt/pods/mysql# cat 04-svc-mysql.yaml
apiVersion: v1
kind: Service
metadata:
name: svc-mysql
spec:
ports:
- port: 3306
#固定外部端口
nodePort: 30306
selector:
apps: master
#type: ClusterIP
type: NodePort
2.部署服务
root@master231:/opt/pods/mysql# ll
total 24
drwxr-xr-x 2 root root 4096 Apr 10 13:29 ./
drwxr-xr-x 5 root root 4096 Apr 10 10:26 ../
-rw-r--r-- 1 root root 909 Apr 10 11:08 01-cm-mysql.yaml
-rw-r--r-- 1 root root 115 Apr 10 11:15 02-secret-mysql.yaml
-rw-r--r-- 1 root root 1920 Apr 10 11:15 03-deploy-mysql.yaml
-rw-r--r-- 1 root root 200 Apr 10 13:29 04-svc-mysql.yaml
root@master231:/opt/pods/mysql#
启动服务
root@master231:/opt/pods/mysql# 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@master231:/opt/pods/mysql# kubectl get pods -o wide
NAME READY STATUS RESTARTS AGE IP NODE NOMINATED NODE READINESS GATES
deploy-master-cfd8dfbf4-2wtnv 1/1 Running 0 46s 10.100.1.33 worker232 <none> <none>
deploy-slave-7979f64b4d-rwbck 1/1 Running 0 46s 10.100.2.38 worker233 <none> <none>
二.配置服务
1.主库创建拷贝数据的账号
1)登录主库
root@master231:/opt/pods/mysql# kubectl exec -it deploy-master-cfd8dfbf4-2wtnv -- mysql -pLxygwqf
2)查看当前mysql的server_id
mysql> SELECT @@server_id;
+-------------+
| @@server_id |
+-------------+
| 100 |
+-------------+
1 row in set (0.00 sec)
3)创建用于主从复制的专用账号
mysql> CREATE USER wufl IDENTIFIED BY 'Lxygwqf';
Query OK, 0 rows affected (0.09 sec)
4)给复制账号授予复制权限
mysql> GRANT REPLICATION SLAVE ON *.* TO 'wufl';
Query OK, 0 rows affected (0.01 sec)
REPLICATION SLAVE：MySQL 专门用于主从复制的权限
ON *.*：对所有库、所有表生效
只有拥有这个权限的账号，从库才能拉取主库的 binlog
5)查看wufl拥有的权限
mysql> SHOW GRANTS FOR wufl;
+----------------------------------------------+
| Grants for wufl@% |
+----------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `wufl`@`%` |
+----------------------------------------------+
1 row in set (0.01 sec)
@% 表示允许从任意 IP 连接（K8s 环境必须这样）
6)查看主库当前 binlog 位置
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 | 661 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set, 1 warning (0.00 sec)
File :binlog.000002 主库当前正在写入的二进制日志文件名
Position;661 日志写到了661这个字节的位置;
配置从库时，必须严格填写这两个值，从库才知道从哪里开始同步
![image-20260411125146158]()
2.从库和主库建立链接
-
1)登录从库
root@master231:~# kubectl exec -it deploy-slave-7979f64b4d-rwbck -- mysql -pLxygwqf
2)查看从库 server_id
mysql> SELECT @@server_id;
+-------------+
| @@server_id |
+-------------+
| 200 |
+-------------+
1 row in set (0.00 sec)
查看从库的唯一编号
主库是100,从库是200
两个id不一样,主从复制的必要条件
3)配置从库连接主库(核心命令)
mysql> CHANGE MASTER TO
-> MASTER_HOST='svc-mysql',
-> MASTER_USER='wufl',
-> MASTER_PASSWORD='Lxygwqf',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='binlog.000002',
-> MASTER_LOG_POS=661,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 10 warnings (0.15 sec)
具体含义
CHANGE MASTER TO
MASTER_HOST='svc-mysql', #主库的地址,是服务名（Service)
MASTER_USER='wufl', #连接主库用的 复制用户名
MASTER_PASSWORD='Lxygwqf', #复制用户的密码,必须一致
MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.00002', #从主库的那个 binlog 日志文件开始同步
MASTER_LOG_POS=661, #从 binlog 的哪个位置开始同步
MASTER_CONNECT_RETRY=10; #连接主库失败后，等待多少秒再重试
4)启动从库的主从复制功能
mysql> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.14 sec)
含义:
让从库开始去连接主库
开始拉取 binlog 日志
开始同步主库的数据
5)查看从库当前的主从复制状态
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: svc-mysql
Master_User: wufl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 661
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 325
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes # IO 线程：去主库拉日志 正常
Slave_SQL_Running: Yes # SQL 线程：执行日志同步数据 正常
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: 661
Relay_Log_Space: 530
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: 8719d98e-3559-11f1-8771-f698534de11b
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: 10
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.01 sec)
查看核心状态
Slave_IO_Running: Yes # IO 线程：去主库拉日志 正常
Slave_SQL_Running: Yes # SQL 线程：执行日志同步数据 正常
3.验证主从同步
1)登录登录主库
root@master231:/opt/pods/mysql# kubectl exec -it deploy-master-cfd8dfbf4-2wtnv -- mysql -pLxygwqf
2)创建数据库 testwufl
mysql> CREATE DATABASE testwufl;
Query OK, 1 row affected (0.03 sec)
3)进入数据库
mysql> use testwufl;
Database changed
4)创建表 blog;
CREATE TABLE blog(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
hobby VARCHAR(255) NOT NULL
);
5)插入数据
mysql> INSERT INTO blog(name,hobby) VALUES ('wufl_shuaige','https://home.cnblogs.com/u/wflstu');
Query OK, 1 row affected (0.10 sec)
6)查看数据
mysql> SELECT * FROM blog;
+----+--------------+-----------------------------------+
| id | name | hobby |
+----+--------------+-----------------------------------+
| 1 | wufl_shuaige | https://home.cnblogs.com/u/wflstu |
+----+--------------+-----------------------------------+
1 row in set (0.00 sec)
4从库登录查询
登录从库
kubectl exec -it deploy-slave-7979f64b4d-rwbck -- mysql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testwufl |
+--------------------+
5 rows in set (0.03 sec)
mysql>
查看testwufl
mysql> show tables from testwufl;
+--------------------+
| Tables_in_testwufl |
+--------------------+
| blog |
+--------------------+
1 row in set (0.00 sec)
查看表格
mysql> select * from blog;
+----+--------------+-----------------------------------+
| id | name | hobby |
+----+--------------+-----------------------------------+
| 1 | wufl_shuaige | https://home.cnblogs.com/u/wflstu |
+----+--------------+-----------------------------------+
1 row in set (0.00 sec)
mysql>
浙公网安备 33010602011771号