- 拉取镜像,我这里拉取5.6镜像
docker pull mysql:5.6.43
2.找一个目录编写主从库配置文件,这里我直接在上博客找的:
(1)主库master.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
(2)从库slave.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
3.启动两个mysql 端口号分别为 13306 和 23306,注意 运行一下命令需要进入到有master.cnf 和slave.cnf配置的目录下,可以自己创建一个目录。另外运行时系统会提示你是否需要共享文件给docker,点击共享即可。
docker run -p 13306:3306 --name master -v $PWD/master.cnf:/etc/mysql/conf.d/master.cnf -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.6.43
docker run -p 23306:3306 --name slave -v $PWD/slave.cnf:/etc/mysql/conf.d/slave.cnf -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.6.43
4.主库创建一个账,并进行权限配置。(为了方便我这里直接用navicat进行连接配置,当然为了提高逼格,你也可以进入容器进行配置)
运行sql:
-- 创建用户
CREATE user jskj;
GRANT ALL PRIVILEGES ON *.* TO jskj@'%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;
5.查询主库mysql-bin信息: show master status
6.从库配置连接主库,并开启从库同步功能
change master to master_host='宿主机ip', master_user='jskj',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=1689;
7.测试
主库创建jskj数据库并创建biz_user表同时插入数据。从库同步成功:
8.以上配置已经完成,接下来进行要点说明:
(1)数据库配置说明:
lower_case_table_names=1 : 打开时,是忽略表名、列名等数据结构的大小写
server-id=1 :此mysq服务器的唯一标识,一般第一台master配置为1 第二台mysql服务器配置为2 第三n台配置为n ...。
log-bin:开启二进制记录。这是为了主从复制而做的设置。本文使用RBR(Row-Based Replication)模式。
slow_query_log=1:开启慢查询日志。如果某一条SQL执行的时间超过long_query_time设置的秒数,那么就记录下来。记录文件路径可以使用show variables;命令,在变量名是slow_query_log_file下查找到具体的日志文件路径。
long_query_time=1:单位是秒。指如果某一条SQL语句执行时间超过1秒,就记录下来。必须开启慢查询日志了以后,此变量才能使用。
log_error:开启错误日志。show variables like 'log_error'; 就可以查询到日志文件的路径。mysql的docker官方镜像如果设置别的取值会导致容器无法正常启动。
(2)从库配置说明:
change master to
master_host='ip', //Master 服务器Ip地址,
master_port=3306,//Master 端口号
master_user='zijixie',//Master 用户名
master_password='123456',//用户密码
master_log_file='mysql-bin.000004',//Master服务器产生的日志要和Master服务器所配置的相互对应如下如所示的file对应
master_log_pos=1689; //申明从库从主库的日志文件中哪个位置开始读取,一般跟从库的position对应。