目录
一、docker中实现主从复制
1、启动主库，启动从库（必须同一个网桥）
# 主备配置文件
[root@kubernetes-node-05 ~]# cat mysql-master.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=1
binlog_format=row
log-bin=mysql-bin
skip-name-resolve
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
relay_log_purge=0
symbolic-links=0
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@kubernetes-node-05 ~]# cat mysql-node.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=2 #改成2
binlog_format=row
log-bin=mysql-bin
skip-name-resolve
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
relay_log_purge=0
symbolic-links=0
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
relay-log = mysql-relay-bin #从库的终极日志，等于主库的binlog日志
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
replicate-wild-ignore-table=sys.%
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# 创建网桥
[root@kubernetes-node-05 ~]# docker network create mysql
# 开启主从库
[root@kubernetes-node-05 ~]# docker run -d --name mysql-master-01 -p 3306:3306 --network=mysql -e MYSQL_ROOT_PASSWORD=1111 -v mysql-master.cnf:/etc/mysql/my.cnf mysql:5.7
[root@kubernetes-node-05 ~]# docker run -d --name mysql-node-01 --network=mysql -e MYSQL_ROOT_PASSWORD=1111 -v /root/mysql-node.cnf:/etc/mysql/my.cnf mysql:5.7
2、在主库中创建主从复制账号
# 主库上执行
[root@k8s-n-01 ~]# docker exec -it mysql-node-01 bash
root@f7e3a23ddcbf:/# mysql -uroot -p1111
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
root@f7e3a23ddcbf:/# mysql -uroot -p1111 -hmysql-master-01 #报错解决
mysql> grant replication slave on *.* to 'slave'@'%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
3、将从库加入集群
# 在从库中执行, 加入主节点
mysql> change master to master_host='mysql-master-01',master_port=3306,master_user='slave',master_password='123',master_log_file='mysql-bin.000001',master_log_pos=154;
Query OK, 0 rows affected, 1 warning (0.04 sec)
#开启同步
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
# 查看主从复制状态
mysql> show slave status G
如果还要加入从库，加一个my.cnf，改id，加入主机即可*
二、k8s中实现主从复制
一主一从
1.主节点yaml配置
[root@k8s-m-01 ~]# mkdir mysql-master
[root@k8s-m-01 ~]# cd mysql-master/
[root@k8s-m-01 mysql-master]# ll #创建5个yaml文件
-rw-r--r-- 1 root root 668 Aug 14 08:22 config.yaml
-rw-r--r-- 1 root root 828 Aug 14 08:23 deployment.yaml
-rw-r--r-- 1 root root 1292 Aug 14 10:15 job.yaml
-rw-r--r-- 1 root root 87 Aug 14 08:21 namespace.yaml
-rw-r--r-- 1 root root 225 Aug 14 08:24 service.yaml
root@k8s-m-01 mysql-master]# cat namespace.yaml
kind: Namespace #创建命名空间
apiVersion: v1
metadata:
name: mysql-master
[root@k8s-m-01 mysql-master]# cat config.yaml
kind: ConfigMap # 把配置文件写进去
apiVersion: v1
metadata:
name: mysql-master-config
namespace: mysql-master
data:
my.cnf: |
[mysqld]
datadir=/var/lib/mysql
server-id=1
binlog_format=row
log-bin=mysql-bin
skip-name-resolve
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
relay_log_purge=0
symbolic-links=0
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_safe]
log-error=/var/log/mysqld.log
---
kind: Secret #密码
apiVersion: v1
metadata:
namespace: mysql-master
name: mysql-password
data:
MYSQL_ROOT_PASSWORD: YWJjMTIzNDU2Cg== # echo abc123456 | base64
[root@k8s-m-01 mysql-master]# cat deployment.yaml
kind: Deployment
apiVersion: apps/v1
metadata:
name: mysql-master
namespace: mysql-master
spec:
selector:
matchLabels:
app: mysql-master
template:
metadata:
labels:
app: mysql-master
spec:
containers:
- name: mysql
image: mysql:5.7
imagePullPolicy: IfNotPresent #镜像拉取策略
env:
- name: MYSQL_ROOT_PASSWORD
value: "abc123456"
envFrom: #定义环境变量
- secretRef:
name: mysql-password
volumeMounts:
- mountPath: /etc/mysql/my.cnf #必须写文件名，
name: mysql-master-config
subPath: my.cnf
volumes:
- name: mysql-master-config
configMap:
name: mysql-master-config
items:
- key: my.cnf
path: my.cnf
[root@k8s-m-01 mysql-master]# vim service.yaml
kind: Service
apiVersion: v1
metadata:
name: mysql-master
namespace: mysql-master
spec:
ports:
- port: 3306
targetPort: 3306
protocol: TCP
name: mysql-master-port
selector:
app: mysql-master
[root@k8s-m-01 mysql-master]# kubectl apply -f namespace.yaml
namespace/mysql-master created
[root@k8s-m-01 mysql-master]# kubectl apply -f ./
configmap/mysql-master-config created
secret/mysql-password created
deployment.apps/mysql-master created
namespace/mysql-master unchanged
service/mysql-master created
[root@k8s-m-01 mysql-master]# vim job.yaml #批量处理短暂的一次性(每个任务仅运行一次就结束)任务
kind: Job #job控制器
apiVersion: batch/v1
metadata:
name: mysql-master-job
namespace: mysql-master
spec:
template:
metadata:
labels:
app: test-job
spec:
restartPolicy: OnFailure # 重启策略只能设置为Never或者OnFailure
containers:
- name: mysql
imagePullPolicy: IfNotPresent #镜像拉取策略
image: mysql:5.7
command: # 指定执行的命令
- "/bin/sh"
- "-c"
- |
MYSQL_MASTER_SVC_NAME=mysql-master.mysql-master.svc.cluster.local
MYSQL_MASTER_PASSWORD=abc123456
MYSQL_MASTER_USERNAME=root
while true
do
mysql -u${MYSQL_MASTER_USERNAME} -p${MYSQL_MASTER_PASSWORD} -h${MYSQL_MASTER_SVC_NAME} -e 'show databases;' >/dev/null 2>&1
if [ $? -eq 0 ];then
mysql -u${MYSQL_MASTER_USERNAME} -p${MYSQL_MASTER_PASSWORD} -h${MYSQL_MASTER_SVC_NAME} -e "grant replication slave on *.* to 'slave'@'%' identified by '123'; flush privileges;"
break;
fi
sleep 1;
done
[root@k8s-m-01 mysql-master]# kubectl apply -f job.yaml
job.batch/mysql-master-job created
[root@k8s-m-01 mysql-master]# kubectl get jobs -n mysql-master
NAME COMPLETIONS DURATION AGE
mysql-master-job 1/1 2s 3m10s
1、创建一个POD ---> 去执行指定的命令
2、如果运行成功，则状态变成：Completed
3、如果运行失败，则状态变成：Error
[root@k8s-m-01 mysql-master]# kubectl get pod -n mysql-master
NAME READY STATUS RESTARTS AGE
mysql-master-5b68694cd7-rhdk9 1/1 Running 0 111m
mysql-master-job-wzj6s 0/1 Completed 0 86s
[root@k8s-m-01 mysql-master]# kubectl run --rm -it tests --image=mysql:5.7 bash
If you don't see a command prompt, try pressing enter.
root@tests:/# mysql -uroot -pabc123456 -hmysql-master.mysql-master.svc.cluster.local
mysql: [Warning] Using a password on the command line interface can be insecure.
...
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select host,user from user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| % | root |
| % | slave | #slave用户部署完
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
5 rows in set (0.00 sec)
mysql> show master statusG
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 1050
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 1dcbd9ec-fc96-11eb-ba94-da7a452cf74a:1-9
1 row in set (0.00 sec)
2.从节点yaml配置
[root@k8s-m-01 ~]# mkdir mysql-node
[root@k8s-m-01 ~]# cd mysql-node/
[root@k8s-m-01 mysql-node]# ll
total 0
-rw-r--r-- 1 root root 0 Aug 14 11:05 config.yaml
-rw-r--r-- 1 root root 0 Aug 14 11:05 deployment.yaml
-rw-r--r-- 1 root root 0 Aug 14 11:06 job.yaml
-rw-r--r-- 1 root root 0 Aug 14 11:05 namespace.yaml
-rw-r--r-- 1 root root 0 Aug 14 11:05 service.yaml
[root@k8s-m-01 mysql-node]# vim namespace.yaml
kind: Namespace
apiVersion: v1
metadata:
name: mysql-node
[root@k8s-m-01 mysql-node]# vim config.yaml
kind: ConfigMap
apiVersion: v1
metadata:
name: mysql-node-config
namespace: mysql-node
data:
my.cnf: |
[mysqld]
datadir=/var/lib/mysql
server-id=2
binlog_format=row
log-bin=mysql-bin
skip-name-resolve
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
relay_log_purge=0
symbolic-links=0
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
relay-log=mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
replicate-wild-ignore-table=sys.%
[mysqld_safe]
log-error=/var/log/mysqld.log
---
kind: Secret
apiVersion: v1
metadata:
namespace: mysql-node
name: mysql-password
data:
MYSQL_ROOT_PASSWORD: YWJjMTIzNDU2Cg==
[root@k8s-m-01 mysql-node]# vim deployment.yaml
kind: Deployment
apiVersion: apps/v1
metadata:
name: mysql-node
namespace: mysql-node
spec:
selector:
matchLabels:
app: mysql-node
template:
metadata:
labels:
app: mysql-node
spec:
containers:
- name: mysql
image: alvinos/mysql-salve:5.7-v2
imagePullPolicy: IfNotPresent
env:
- name: MYSQL_ROOT_PASSWORD
value: "abc123456"
envFrom:
- secretRef:
name: mysql-password
volumeMounts:
- mountPath: /etc/mysql/my.cnf
name: mysql-node-config
subPath: my.cnf
volumes:
- name: mysql-node-config
configMap:
name: mysql-node-config
items:
- key: my.cnf
path: my.cnf
[root@k8s-m-01 mysql-node]# vim service.yaml
kind: Service
apiVersion: v1
metadata:
name: mysql-node
namespace: mysql-node
spec:
ports:
- port: 3306
targetPort: 3306
protocol: TCP
name: mysql-node-port
selector:
app: mysql-node
[root@k8s-m-01 mysql-node]# vim job.yaml
kind: Job
apiVersion: batch/v1
metadata:
namespace: mysql-node
name: mysql-node
spec:
template:
spec:
restartPolicy: OnFailure
containers:
- name: mysql
image: mysql:5.7
imagePullPolicy: IfNotPresent
command:
- "/bin/sh"
- "-c"
- |
MYSQL_NODE_SVC_NAME=mysql-node.mysql-node.svc.cluster.local
MYSQL_NODE_PASSWORD=abc123456
MYSQL_NODE_USERNAME=root
while true
do
mysql -u${MYSQL_NODE_USERNAME} -p${MYSQL_NODE_PASSWORD} -h${MYSQL_NODE_SVC_NAME} -e 'show databases;' >/dev/null 2>&1
if [ $? -eq 0 ];then
mysql -u${MYSQL_NODE_USERNAME} -p${MYSQL_NODE_PASSWORD} -h${MYSQL_NODE_SVC_NAME} -e "show master statusG" > /tmp/log
MASTER_LOG_FILE=`/bin/cat /tmp/log | /usr/bin/awk -F: 'NR==2{print $2}' | /usr/bin/tr -d " "`
MYSQL_LOG_POS=`/bin/cat /tmp/log | /usr/bin/awk -F: 'NR==3{print $2}' | /usr/bin/tr -d " "`
mysql -u${MYSQL_NODE_USERNAME} -p${MYSQL_NODE_PASSWORD} -h${MYSQL_NODE_SVC_NAME} -e "change master to master_host='mysql-master.mysql-master.svc.cluster.local',master_port=3306,master_user='slave',master_password='123',master_log_file='"${MASTER_LOG_FILE}"',master_log_pos="${MYSQL_LOG_POS}";"
mysql -u${MYSQL_NODE_USERNAME} -p${MYSQL_NODE_PASSWORD} -h${MYSQL_NODE_SVC_NAME} -e "start slave;"
break;
fi
sleep 1;
done
[root@k8s-m-01 mysql-node]# kubectl apply -f namespace.yaml
namespace/mysql-node unchanged
[root@k8s-m-01 mysql-node]# kubectl apply -f ./
configmap/mysql-node-config created
secret/mysql-password created
deployment.apps/mysql-node created
job.batch/mysql-node created
namespace/mysql-node unchanged
service/mysql-node unchanged
#进入容器测试
[root@k8s-m-01 mysql-node]# kubectl exec -it -n mysql-node mysql-node-794cd4f4bc-476rv -- bash
root@mysql-node-794cd4f4bc-476rv:~# mysql -uroot -pabc123456 -hmysql-node.mysql-node.svc.cluster.local
...
mysql>
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mysql-master.mysql-master.svc.cluster.local
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1050
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 1176
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes #YES
Slave_SQL_Running: Yes #yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
...
#此时进入容器，主从同步
[root@k8s-m-01 ~]# kubectl get pod -n mysql-master
NAME READY STATUS RESTARTS AGE
mysql-master-5b68694cd7-rhdk9 1/1 Running 0 3h33m
mysql-master-job-wzj6s 0/1 Completed 0 102m
[root@k8s-m-01 ~]# kubectl exec -it -n mysql-master mysql-master-5b68694cd7-rhdk9 -- bash
root@mysql-master-5b68694cd7-rhdk9:/# mysql -uroot -pabc123456 -hmysql-master.mysql-master.svc.cluster.local
mysql> create database longlong;
Query OK, 1 row affected (0.00 sec）
#从节点longlong库同步
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| discuz |
| longlong |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
一主多从
3.构建多从库镜像
[root@docker ~]# cat Dockerfile
FROM mysql:5.7
ADD my.cnf /etc/mysql/my.cnf
ADD docker-entrypoint.sh /usr/local/bin/docker-entrypoint.sh
WORKDIR /root
EXPOSE 3306 33060
CMD ["mysqld"]
[root@docker mysql]# cat my.cnf
[mysqld]
datadir=/var/lib/mysql
server-id=2
binlog_format=row
log-bin=mysql-bin
skip-name-resolve
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
relay_log_purge=0
symbolic-links=0
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
user=mysql
relay-log=mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
replicate-wild-ignore-table=sys.%
[mysqld_safe]
log-error=/var/log/mysqld.log
# 执行这个命令，在启动容器之前进行修改
/bin/echo "/bin/sed -i 's/server-id=2/server-id=`/bin/echo $RANDOM`/g' /etc/mysql/my.cnf" | /bin/sh -
# 将这条命令写入docker-entrypoint.sh添加到/usr/local/bin/docker-entrypoint.sh