docker实现mysql主从配置
环境准备
mysql镜像获取和主从容器生成
docker pull mysql:5.7
docker run -d -p 3306:3306 --name mysqlMaster -v /mysqlConfig/master/cnf:/etc/mysql/conf.d -v /mysqlConfig/master/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7
docker run -d -p 3307:3306 --name mysqlSlave -v /mysqlConfig/slave/cnf:/etc/mysql/conf.d -v /mysqlConfig/slave/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 --link mysqlMaster:master mysql:5.7
命令解读
-
-v /mysqlConfig/slave/cnf:/etc/mysql/conf.d 表示将mysql容器内部路径/etc/mysql/conf.d目录挂载到主机路径/mysqlConfig/slave/cnf目录下。目的是为了将配置文件保留在主机，防止配置文件随容器删除而丢失。
-
-v /mysqlConfig/master/data:/var/lib/mysql 表示将容器内/var/lib/mysql路径下所有文件挂载到主机目录下，该路径是mysql数据库的数据文件。
-
生成slave容器命令中，--link mysqlMaster:master 表示slave容器网络连接到master网络中，其中:master表示别名，在配置连接master时会用到。
配置文件
- 在主机路径 /mysqlConfig/master/cnf 下创建文件mysql.cnf，以下是配置内容
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
symbolic-links=0
character-set-server = utf8
#skip-networking
innodb_print_all_deadlocks = 1
max_connections = 2000
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 128
max_allowed_packet = 4M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 28M
key_buffer_size = 4M
thread_cache_size = 8
query_cache_type = 1
query_cache_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4
log-bin = mysql-bin
server-id = 1
binlog_format = mixed
performance_schema = 0
explicit_defaults_for_timestamp
#lower_case_table_names = 1
interactive_timeout = 28800
wait_timeout = 28800
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
- 在主机路径 /mysqlConfig/slave/cnf 下创建文件mysql.cnf，以下是配置内容
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
symbolic-links=0
character-set-server = utf8
#skip-networking
innodb_print_all_deadlocks = 1
max_connections = 2000
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 128
max_allowed_packet = 4M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 28M
key_buffer_size = 4M
thread_cache_size = 8
query_cache_type = 1
query_cache_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4
log-bin = mysql-bin
server-id = 2
binlog_format = mixed
performance_schema = 0
explicit_defaults_for_timestamp
#lower_case_table_names = 1
interactive_timeout = 28800
wait_timeout = 28800
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
配置主从数据库
master数据库配置
# 进入master容器
docker exec -it mysqlMaster /bin/bash
# 进入数据库
mysql -uroot -p123456;
# 配置连接master数据库账号
grant replication slave on *.* to 'slave'@'%' identified by '123456';
# 刷新
flush privileges;
# 查看binlog状态，记录File和Position值
show master status;
slave数据库配置
# 进入master容器
docker exec -it mysqlMaster /bin/bash
# 进入数据库
mysql -uroot -p123456;
# master_host对应的是link的容器映射名master
# master_log_file和master_log_pos分别对应master数据库中File和Position值
change master to master_host='master',master_user='slave',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=889;
# 启动slave模式
start slave;
# 查看状态，若Slave_IO_Running: Yes和Slave_SQL_Running: Yes则成功
show slave status\G;
测试
进入master容器
# 创建数据库
create database master;
# 进入数据库
use master;
# 创建表
create table user(id bigint, name varchar(255));
# 插入数据
insert into user(id, name) value (1, 'master');
进入slave容器
# 进入数据库
use master;
# 查看数据
select * from user
浙公网安备 33010602011771号