文章目录
概述
mysql的原理不多说了,可以看我上一篇文章MySQL系列——集群复制方式及原理
本次进行的是docker-compose的一键部署主从,如果一键部署不好用的话,欢迎留言,文件目录结构如下
主从配置步骤
配置Master
使用Dockerfile构建Image
FROM mysql:latest
COPY my.cnf /etc/mysql/conf.d
COPY master.sh /docker-entrypoint-initdb.d/
CMD ["mysqld"]
Dockerfile步骤说明:
- pull
mysql:latest
- 复制本地文件
my.cnf
到容器内/etc/mysql/conf.d
- 复制本地文件
master.sh
到容器内/docker-entrypoint-initdb.d/
- 运行
创建my.cnf
[mysqld]
## 设置server_id,同一局域网中需要唯一
server-id=100
## 端口
port=3306
## 加密方法
default-authentication-plugin=mysql_native_password
## 最大连接数
max_connections=800
## 对于同一主机,如果有超出该参数值个数的中断错误连接,则该主机将被禁止连接
max_connect_errors=1000
## 开启binlog
log_bin=ON
## 慢查询
slow_query_log=ON
## 慢查询日志
slow_query_log_file=/var/lib/mysql/slow.log
## 错误日志路径
log_error=/var/lib/mysql/mysql.err
## slave复制的数据会写入log-bin日志文件里
log-slave-updates=ON
## 保证事务安全完整性的
enforce-gtid-consistency=ON
## slave进行同步复制的时候,无须找到binlog日志和POS点
gtid_mode=ON
## 不记录information_schema的二进制日志
binlog-ignore-db=information_schema
## 不记录mysql
binlog-ignore-db=mysql
## 不记录sys
binlog-ignore-db=sys
## 不记录performance_schema
binlog-ignore-db=performance_schema
## STRICT_TRANS_TABLES:如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制
## NO_ZERO_IN_DATE:不允许日期和月份为零
## NO_ZERO_DATE:不允许插入零日期,插入零日期会抛出错误而不是警告
## ERROR_FOR_DIVISION_BY_ZERO:在INSERT或UPDATE过程中,如果数据被零除,则产生错误而非警告
## NO_ENGINE_SUBSTITUTION:如果需要的存储引擎被禁用或未编译,那么抛出错误
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
创建master.sh
用来创建用户及配置权限,MASTER_SYNC_USER等变量,来自于docker-compose
#!/bin/bash
MASTER_SYNC_USER=${MASTER_SYNC_USER:-sync_admin}
MASTER_SYNC_PASSWORD=${MASTER_SYNC_PASSWORD:-sync_admin123456}
ADMIN_USER=${ADMIN_USER:-root}
ADMIN_PASSWORD=${ADMIN_PASSWORD:-admin123456}
ALLOW_HOST=${ALLOW_HOST:-%}
CREATE_USER_SQL="CREATE USER '$MASTER_SYNC_USER'@'$ALLOW_HOST' IDENTIFIED BY '$MASTER_SYNC_PASSWORD';"
GRANT_PRIVILEGES_SQL="GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO '$MASTER_SYNC_USER'@'$ALLOW_HOST';"
FLUSH_PRIVILEGES_SQL="FLUSH PRIVILEGES;"
mysql -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e "$CREATE_USER_SQL"
mysql -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e "$GRANT_PRIVILEGES_SQL"
mysql -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e "$FLUSH_PRIVILEGES_SQL"
配置Slave
使用Dockerfile构建Image
FROM mysql:latest
COPY my.cnf /etc/mysql/conf.d
COPY slave.sh /docker-entrypoint-initdb.d/
CMD ["mysqld"]
Dockerfile步骤说明:
- pull
mysql:latest
- 复制本地文件
my.cnf
到容器内/etc/mysql/conf.d
- 复制本地文件
slave.sh
到容器内/docker-entrypoint-initdb.d/
- 运行
创建my.cnf
[mysqld]
## 设置server_id,同一局域网中需要唯一
server_id=101
## 端口
port=3306
## 加密方法
default-authentication-plugin=mysql_native_password
## 最大连接数
max_connections=800
## 对于同一主机,如果有超出该参数值个数的中断错误连接,则该主机将被禁止连接
max_connect_errors=1000
## 开启binlog
log_bin=ON
## 慢查询
slow_query_log=ON
## 慢查询日志
slow_query_log_file=/var/lib/mysql/slow.log
## 错误日志路径
log_error=/var/lib/mysql/mysql.err
## slave从m复制的数据会写入log-bin日志文件里
log-slave-updates=ON
## 保证事务安全完整性的
enforce-gtid-consistency=ON
## slave进行同步复制的时候,无须找到binlog日志和POS点
gtid_mode=ON
## 不记录information_schema的二进制日志
binlog-ignore-db=information_schema
## 不记录mysql
binlog-ignore-db=mysql
## 不记录sys
binlog-ignore-db=sys
## 不记录performance_schema
binlog-ignore-db=performance_schema
## STRICT_TRANS_TABLES:如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制
## NO_ZERO_IN_DATE:不允许日期和月份为零
## NO_ZERO_DATE:不允许插入零日期,插入零日期会抛出错误而不是警告
## ERROR_FOR_DIVISION_BY_ZERO:在INSERT或UPDATE过程中,如果数据被零除,则产生错误而非警告
## NO_ENGINE_SUBSTITUTION:如果需要的存储引擎被禁用或未编译,那么抛出错误
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
创建slave.sh
主要是用来配置主从,MASTER_SYNC_USER等变量,来自于docker-compose
#!/bin/bash
SLAVE_SYNC_USER="${MASTER_SYNC_USER:-sync_admin}"
SLAVE_SYNC_PASSWORD="${MASTER_SYNC_PASSWORD:-sync_admin123456}"
ADMIN_USER="${ADMIN_USER:-root}"
ADMIN_PASSWORD="${ADMIN_PASSWORD:-admin123456}"
MASTER_HOST="${MASTER_HOST:-%}"
MASTER_PORT="${MASTER_PORT:-%}"
sleep 10
RESULT=`mysql -h$MASTER_HOST -p$MASTER_PORT -u"$SLAVE_SYNC_USER" -p"$SLAVE_SYNC_PASSWORD" -e "SHOW MASTER STATUS;" | grep -v grep |tail -n +2| awk '{print $1,$2}'`
LOG_FILE_NAME=`echo $RESULT | grep -v grep | awk '{print $1}'`
LOG_FILE_POS=`echo $RESULT | grep -v grep | awk '{print $2}'`
SYNC_SQL="change master to master_host='$MASTER_HOST', master_port=$MASTER_PORT, master_user='$SLAVE_SYNC_USER',master_password='$SLAVE_SYNC_PASSWORD',master_log_file='$LOG_FILE_NAME',master_log_pos=$LOG_FILE_POS;"
START_SYNC_SQL="start slave;"
STATUS_SQL="show slave status\G;"
mysql -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e "$SYNC_SQL"
mysql -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e "$START_SYNC_SQL"
mysql -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e "$STATUS_SQL"
配置docker-compose
version: '3'
services:
mysql_master:
container_name: mysql_master
environment:
- MYSQL_ROOT_PASSWORD=admin123456
- MYSQL_DATABASE=test_db
- MYSQL_USER=test
- MYSQL_PASSWORD=123456
- MASTER_SYNC_USER=sync_admin
- MASTER_SYNC_PASSWORD=sync_admin123456
- ADMIN_USER=root
- ADMIN_PASSWORD=admin123456
- TZ=Asia/Shanghai
image: mysql8_master:latest
build:
context: ./mysql_master
ports:
- 4306:3306
volumes:
- ./mysql_master/data:/var/lib/mysql
networks:
shardingSphere:
ipv4_address: 192.168.150.10
restart: always
mysql_slave1:
container_name: mysql_slave1
image: mysql8_slave1:latest
build:
context: ./mysql_slave/slave1
environment:
- MYSQL_ROOT_PASSWORD=admin123456
- MYSQL_DATABASE=test_db
- MYSQL_USER=test
- MYSQL_PASSWORD=123456
- MASTER_SYNC_USER=sync_admin
- MASTER_SYNC_PASSWORD=sync_admin123456
- ADMIN_USER=root
- ADMIN_PASSWORD=admin123456
- MASTER_HOST=192.168.150.10
- MASTER_PORT=3306
- TZ=Asia/Shanghai
ports:
- 5306:3306
volumes:
- "./mysql_slave/slave1/data:/var/lib/mysql"
restart: always
networks:
shardingSphere:
ipv4_address: 192.168.150.11
depends_on:
- mysql_master
mysql_slave2:
container_name: mysql_slave2
image: mysql8_slave2:latest
build:
context: ./mysql_slave/slave2
environment:
- MYSQL_ROOT_PASSWORD=admin123456
- MYSQL_DATABASE=test_db
- MYSQL_USER=test
- MYSQL_PASSWORD=123456
- MASTER_SYNC_USER=sync_admin
- MASTER_SYNC_PASSWORD=sync_admin123456
- ADMIN_USER=root
- ADMIN_PASSWORD=admin123456
- MASTER_HOST=192.168.150.10
- MASTER_PORT=3306
- TZ=Asia/Shanghai
ports:
- 5307:3306
volumes:
- "./mysql_slave/slave2/data:/var/lib/mysql"
restart: always
networks:
shardingSphere:
ipv4_address: 192.168.150.12
depends_on:
- mysql_master
mysql_slave3:
container_name: mysql_slave3
image: mysql8_slave3:latest
build:
context: ./mysql_slave/slave3
environment:
- MYSQL_ROOT_PASSWORD=admin123456
- MYSQL_DATABASE=test_db
- MYSQL_USER=test
- MYSQL_PASSWORD=123456
- MASTER_SYNC_USER=sync_admin
- MASTER_SYNC_PASSWORD=sync_admin123456
- ADMIN_USER=root
- ADMIN_PASSWORD=admin123456
- MASTER_HOST=192.168.150.10
- MASTER_PORT=3306
- TZ=Asia/Shanghai
ports:
- 5308:3306
volumes:
- "./mysql_slave/slave3/data:/var/lib/mysql"
restart: always
networks:
shardingSphere:
ipv4_address: 192.168.150.13
depends_on:
- mysql_master
networks:
shardingSphere:
ipam:
driver: default
config:
- subnet: "192.168.150.0/24"
在Windows上运行docker-compose需要注意的点
1.网络配置
由于每个slave都有脚本,且通过environment
的MASTER_HOST
和>MASTER_PORT
指定从库,所以需要指定IP
所以使用docker-compose
创建networks
和subnet
,再只当主库和从库的ipv4_address
在生产环境中,需根据自己的业务需求去配置networks
2.shell脚本的编码
shell脚本的编码默认是LF,如果你在windows上运行,并且保存了,很有可能变为了CRLF
CRLF是 \r\n LF是\n
如果构建dockerfiles后运行时,发现执行master.sh出错(No File Or No Directory),就需要转换编码,转换编码可以使用vscode
运行及验证
运行
PS C:\Users\ray\Documents\GitHub\docker_deployment_sample\mysql_master_slave> docker-compose -f docker-compose.yml up -d
Creating network "mysql_master_slave_shardingSphere" with the default driver
Creating mysql_master ... done
Creating mysql_slave2 ... done
Creating mysql_slave1 ... done
Creating mysql_slave3 ... done
验证Master
PS C:\Users\ray\Documents\GitHub\docker_deployment_sample\mysql_master_slave> docker exec -it mysql_master mysql -uroot -padmin123456 -e 'show master status \G'
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
File: ON.000003
Position: 197
Binlog_Do_DB:
Binlog_Ignore_DB: information_schema,mysql,sys,performance_schema
Executed_Gtid_Set: cbd30c58-4b88-11ed-ae00-0242c0a8960a:1-4
可以看到,File: ON.000003
和Position: 197
验证Slave
PS C:\Users\ray\Documents\GitHub\docker_deployment_sample\mysql_master_slave> docker exec -it mysql_slave1 mysql -uroot -padmin123456 -e 'show slave status \G'
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.150.10
Master_User: sync_admin
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_binlog_master.000003
Read_Master_Log_Pos: 197
Relay_Log_File: e85f617647bb-relay-bin.000004
Relay_Log_Pos: 319
Relay_Master_Log_File: mysql_binlog_master.000003
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: 197
Relay_Log_Space: 756
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: fc5b1978-4c87-11ed-aec5-0242c0a8960a
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: fdb533b6-4c87-11ed-af85-0242c0a8960b:1
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
Slave_IO_Running: Yes
、Slave_SQL_Running: Yes
表示正常运行,Exec_Master_Log_Pos: 197
代表binlog的位置,和主库197是一致的
验证数据同步
- 在主库中插入一条数据
[root@ray~]$ docker exec -it mysql_master mysql -uroot -padmin123456 -e "INSERT INTO test_db.test_user ( NAME, age ) VALUES ( 'zhangsan', 18 );"
- 查看主库数据
[root@ray~]$ docker exec -it mysql_master mysql -uroot -padmin123456 -e "SELECT * FROM test_db.test_user;"
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 18 |
+----+----------+------+
- 查看从库数据
[root@ray~]$ docker exec -it mysql_slave1 mysql -uroot -padmin123456 -e "SELECT * FROM test_db.test_user;"
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 18 |
+----+----------+------+
单主多从的缺陷
- 从库数据不能同步到主库和其他从库
- 主库挂了,如果在从库插入数据,等待主库启动后,数据不同步
从库数据不能同步到主库和其他从库
- 在从库
mysql_slave1
插入一条lisi
的数据
[root@ray~]$ docker exec -it mysql_slave1 mysql -uroot -padmin123456 -e "INSERT INTO test_db.test_user ( NAME, age ) VALUES ( 'lisi', 20 );"
- 查看从库
mysql_slave1
的数据
[root@ray~]$ docker exec -it mysql_slave1 mysql -uroot -padmin123456 -e "SELECT * FROM test_db.test_user;"
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 18 |
| 2 | lisi | 20 |
+----+----------+------+
- 查看主库
mysql_master
的数据
[root@ray~]$ docker exec -it mysql_master mysql -uroot -padmin123456 -e "SELECT * FROM test_db.test_user;"
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 18 |
+----+----------+------+
- 查看从库
mysql_slave3
的数据
[root@ray~]$ docker exec -it mysql_slave3 mysql -uroot -padmin123456 -e "SELECT * FROM test_db.test_user;"
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 18 |
+----+----------+------+
很明显从库mysql_slave1
有两条数据,而主库mysql_master
和从库mysql_slave3
只有一条数据
主库挂了,不能同步
- 由于某种原因主库
mysql_master
挂了,这里使用docker
命令让主库停止运行
[root@ray~]$ docker stop mysql_master
mysql_master
- 在从库
mysql_slave2
中插入一条wangwu
的数据
[root@ray~]$ docker exec -it mysql_slave2 mysql -uroot -padmin123456 -e "INSERT INTO test_db.test_user ( NAME, age ) VALUES ( 'wangwu', 25 );"
- 使用dockers命令启动主库
[root@ray~]$ docker start mysql_master
mysql_master
- 查询主库的数据
[root@ray~]$ docker exec -it mysql_master mysql -uroot -padmin123456 -e "SELECT * FROM test_db.test_user;"
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 18 |
+----+----------+------+
- 查看从库
mysql_slave2
的数据
[root@ray~]$ docker exec -it mysql_slave2 mysql -uroot -padmin123456 -e "SELECT * FROM test_db.test_user;"
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 18 |
| 2 | wangwu | 25 |
+----+----------+------+
很明显从库mysql_slave2
有两条数据,而主库mysql_master
只有一条数据