Kubernetes搭建MySQL主从集群
- 前言
- MySQL master镜像制作
- MySQL slave镜像制作
- yaml文件准备
- 集群验证
前言
文章以mysql8.0版本为基础搭建mysql一主一从集群
开始前需要先从dockerhub拉取mysql:8.0镜像
docker pull mysql:8.0
MySQL master镜像制作
首先在虚机上准备两个目录
[root@k8s-node1 mysql]# ll
total 0
drwxr-xr-x 2 root root 48 Jul 26 21:54 master
drwxr-xr-x 2 root root 6 Jul 26 21:47 slave
使用docker run 命令进入到mysql:8.0镜像中
[root@k8s-node1 master]# docker run --rm -it 192.168.0.101:5000/mysql:8.0 bash
root@80b5b6798bc1:/# ls -l
total 8
drwxr-xr-x 2 root root 4096 Jun 21 00:00 bin
drwxr-xr-x 2 root root 6 Jun 13 10:30 boot
drwxr-xr-x 5 root root 360 Jul 26 13:49 dev
drwxr-xr-x 2 root root 6 Jun 23 07:11 docker-entrypoint-initdb.d
lrwxrwxrwx 1 root root 34 Jun 23 07:11 entrypoint.sh -> usr/local/bin/docker-entrypoint.sh
drwxr-xr-x 1 root root 66 Jul 26 13:49 etc
drwxr-xr-x 2 root root 6 Jun 13 10:30 home
drwxr-xr-x 1 root root 30 Jun 23 07:10 lib
drwxr-xr-x 2 root root 34 Jun 21 00:00 lib64
drwxr-xr-x 2 root root 6 Jun 21 00:00 media
drwxr-xr-x 2 root root 6 Jun 21 00:00 mnt
drwxr-xr-x 2 root root 6 Jun 21 00:00 opt
dr-xr-xr-x 188 root root 0 Jul 26 13:49 proc
drwx------ 1 root root 24 Jun 23 07:11 root
drwxr-xr-x 1 root root 20 Jun 23 07:11 run
drwxr-xr-x 2 root root 4096 Jun 21 00:00 sbin
drwxr-xr-x 2 root root 6 Jun 21 00:00 srv
dr-xr-xr-x 13 root root 0 Jul 26 13:48 sys
drwxrwxrwt 1 root root 6 Jun 23 07:11 tmp
drwxr-xr-x 1 root root 19 Jun 21 00:00 usr
drwxr-xr-x 1 root root 41 Jun 21 00:00 var
我们将usr/local/bin/docker-entrypoint.sh拷贝到本地
另开一个tab执行命令
docker cp 80b5b6798bc1:usr/local/bin/docker-entrypoint.sh /master/
再将mysql镜像中/etc/mysql/my.cnf拷贝到本地
docker cp 80b5b6798bc1:/etc/mysql/my.cnf /master/
退出镜像,编辑docker-entrypoint.sh文件,找到docker_setup_db函数,在函数内的最下方加入以下内容
if [ -n "$MYSQL_REPLICATION_USER" ] && [ -n "$MYSQL_REPLICATION_PASSWORD" ]; then
mysql_note "Creating user $MYSQL_REPLICATION_USER"
docker_process_sql --database=mysql <<<"CREATE USER '$MYSQL_REPLICATION_USER'@'%' IDENTIFIED WITH 'mysql_native_password' BY '$MYSQL_REPLICATION_PASSWORD';"
docker_process_sql --database=mysql <<<"GRANT REPLICATION SLAVE ON *.* TO '$MYSQL_REPLICATION_USER'@'%';"
docker_process_sql --database=mysql <<<"FLUSH PRIVILEGES ;"
fi
主要是创建一个用于复制的用户,并且赋予复制权限,代码中的变量我们后面会通过yaml文件注入进去
接下来编辑mysql的配置文件my.cnf
在[mysqld]下加入三行内容
[mysqld]
server-id=1
#log-bin=master-bin
default_authentication_plugin=mysql_native_password
第一行是定义master服务的id,在主从集群中必须保证不重复
第二行是开启binlog日志,binlog日志主要用于记录mysql服务器上执行的ddl、dml语句;slave主要复制master的binlog日志到slave的relaylog,之后replay,实现主从同步;mysql8.0之后默认开启binlog,所以这里将其注释了
第三行定义mysql默认使用的认证插件,对集群搭建没有影响,如果不设置为mysql_native_password,会导致大部分连接工具无法连接到mysql
接着准备Dockerfile,同样在master目录下建立Dockerfile文件
FROM 192.168.0.101:5000/mysql:8.0
ADD my.cnf /etc/mysql/
ADD docker-entrypoint.sh /usr/local/bin/
RUN ln -sf /usr/local/bin/docker-entrypoint.sh /entrypoint.sh
最后执行docker build制作master镜像
docker build -t 192.168.0.101:5000/mysql_master:8.0 -f Dockerfile .
MySQL slave镜像制作
首先进入到slave目录下,将docker-entrypoint.sh和my.cnf拷贝出来
编辑docker-entrypoint.sh
同样找到docker_setup_db函数,在函数内的最下方加入以下内容
if [ -n "$MYSQL_MASTER_SERVICE_HOST" ] && [ -n "$MYSQL_REPLICATION_USER" ]; then
mysql_note "Connecting master_host: $MYSQL_MASTER_SERVICE_HOST, which user: $MYSQL_REPLICATION_USER, password: $MYSQL_REPLICATION_PASSWORD"
docker_process_sql --database=mysql <<<"STOP SLAVE;"
docker_process_sql --database=mysql <<<"CHANGE MASTER TO master_host='$MYSQL_MASTER_SERVICE_HOST', master_user='$MYSQL_REPLICATION_USER', master_password='$MYSQL_REPLICATION_PASSWORD' ;"
docker_process_sql --database=mysql <<<"START SLAVE;"
fi
主要是使用在master中创建的用户连接master,用于复制binlog日志内容
接下来编辑my.cnf文件
server-id=2
#log-bin=master-bin
default_authentication_plugin=mysql_native_password
同master一样,只需保证server-id不同即可
准备Dockerfile文件,将master的Dockerfile文件拷贝到slave目录即可
最后执行命令制作镜像
docker build -t 192.168.0.101:5000/mysql_slave:8.0 -f Dockerfile .
yaml文件准备
镜像已经制作完毕,接下来准备yaml文件,首先创建一个目录用于存放yaml文件
进入yaml目录中,先创建master的yaml文件
apiVersion: v1
kind: Service
metadata:
name: mysql-master
namespace: db
labels:
app: mysql-master
spec:
ports:
- port: 3306
nodePort: 30306
targetPort: 3306
type: NodePort
selector:
app: mysql-master
---
apiVersion: apps/v1
kind: Deployment
metadata:
namespace: db
name: mysql-master
spec:
replicas: 1
selector:
matchLabels:
app: mysql-master
template:
metadata:
labels:
app: mysql-master
spec:
containers:
- name: mysql
image: 192.168.0.101:5000/mysql_master:8.0
ports:
- containerPort: 3306
env:
- name: MYSQL_ROOT_PASSWORD
value: "123456"
- name: MYSQL_REPLICATION_USER
value: "rep"
- name: MYSQL_REPLICATION_PASSWORD
value: "123456"
在svc暴露了master的端口为30306
在env中定义了root用户的密码以及用于复制的用户名和密码
namespace为db
接下来准备slave的yaml文件
apiVersion: v1
kind: Service
metadata:
name: mysql-slave
namespace: db
labels:
app: mysql-slave
spec:
ports:
- port: 3306
nodePort: 30307
targetPort: 3306
type: NodePort
selector:
app: mysql-slave
---
apiVersion: apps/v1
kind: Deployment
metadata:
namespace: db
name: mysql-slave
spec:
replicas: 1
selector:
matchLabels:
app: mysql-slave
template:
metadata:
labels:
app: mysql-slave
spec:
containers:
- name: mysql
image: 192.168.0.101:5000/mysql_slave:8.0
ports:
- containerPort: 3306
env:
- name: MYSQL_ROOT_PASSWORD
value: "123456"
- name: MYSQL_REPLICATION_USER
value: "rep"
- name: MYSQL_REPLICATION_PASSWORD
value: "123456"
- name: MYSQL_MASTER_SERVICE_HOST
value: "mysql-master"
在svc暴露了slave的端口为30307
在env中除了定义与master相同的root用户的密码以及用于复制的用户名和密码之外,还定义了slave连接master的主机名,值使用的是master的svc name
namespace为db
最后由于以上yaml中使用的ns为db,所以需要先创建db
kubectl create namespace db
然后创建master和slave的pod
kubectl create -f master.yaml
kubectl create -f slave.yaml
查看pod状态
[root@k8s-node1 yaml]# kubectl get pod -n db
NAME READY STATUS RESTARTS AGE
mysql-master-ffd4d498d-k8p49 1/1 Running 0 83s
mysql-slave-bf7464c6b-whg57 1/1 Running 0 20s
集群验证
进入slave的pod,连接mysql数据库,验证集群
[root@k8s-node1 yaml]# kubectl exec -it -n db mysql-slave-bf7464c6b-whg57 bash
root@mysql-slave-bf7464c6b-whg57:/# mysql -uroot -p
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mysql-master
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 156
Relay_Log_File: mysql-slave-bf7464c6b-whg57-relay-bin.000004
Relay_Log_Pos: 365
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: 156
Relay_Log_Space: 3120015
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: 1
Master_UUID: 2d9fc314-ee21-11eb-b43a-4ec05dee4d94
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave 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)
Slave_IO_Running、Slave_SQL_Running都是YES说明集群搭建成功
接下来使用工具连接master和slave,创建数据库验证主从复制是否成功
连接master
连接slave
连接成功
在master中创建数据库test,刷新slave,发现slave成功复制test库
主从复制搭建完成,但是主从复制集群只能slave复制master,master不会复制slave,所以需要遵循主写从读的原则;
比如我们在slave的test库中创建user表,master不会同步数据
如果需要实现master复制slave,只需要进入到master的pod中,连接上mysql,执行以下语句即可
[root@k8s-node1 yaml]# kubectl exec -it -n db mysql-master-ffd4d498d-k8p49 bash
root@mysql-master-ffd4d498d-k8p49:/# mysql -uroot -p
Enter password:
mysql> stop slave;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> CHANGE MASTER TO master_host='mysql-slave',master_user='rep',master_password='123456';
Query OK, 0 rows affected, 6 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
修改master的主机为slave,即可实现master与slave互为主从,互相复制
打开工具,查看master,发现user表已同步