docker中mysql(8.0.13)主从复制

mysql镜像获取

• docker pull mysql:8.0.13

创建mysql启动配置文件

• mkdir -p /home/docker/mysql/conf
• cd /home/docker/mysql/conf
• vim my.cnf my.cnf参数如下，注意下方备注

[mysqld]
user=mysql
character-set-server=utf8
default_authentication_plugin=mysql_native_password
pid-file    = /var/run/mysqld/mysqld.pid
socket    = /var/run/mysqld/mysqld.sock
datadir    = /var/lib/mysql

symbolic-links=0
character-set-server = utf8
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
ft_min_word_len = 4
# binlog日志位置及名称
log-bin = /var/lib/mysql/master
# 当前binlog日志的具体位置
log_bin_index=/var/lib/mysql/master
# 建议换成ip最后一段，只要每个mysql服务id不同即可
server-id = 97

binlog_format = mixed
expire_logs_days = 7
performance_schema = 0
explicit_defaults_for_timestamp
interactive_timeout = 28800
wait_timeout = 28800
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

启动mysql服务

• cd .. 进入mysql目录
• vim mysql_run.sh 参数如下

docker run -d -p 3306:3306 --privileged=true -v /home/docker/mysql/conf/my.cnf:/etc/mysql/my.cnf -v /home/docker/mysql/data:/var/lib/mysql -v /home/mysql/mysql-files:/var/lib/mysql-files/ -e MYSQL_ROOT_PASSWORD=admin --name mysql mysql:8.0.13

• chmod + 755 mysql_run.sh
• ./mysql_run.sh 启动mysql容器
• docker ps 查看mysql容器运行情况

需要几台mysql服务就重复上述操作

mysql主库设置

• docker exec -it mysql bin/bash //进入mysql服务容器
• mysql -uroot -padmin //登陆mysql数据库
• create user 'slave'@'%' identified by 'slave';//创建salve用户用于从库访问
• grant all privileges on *.* to 'slaver'@'%' with grant option;//授权 如果失败用
  GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
• 登陆从库mysql容器
• mysql -uroot -padmin -h ${master_ip}//master_id为主库ip，验证从库是否可以通过创建的用户访问主库
• 登陆主库mysql数据库
• show master status\G//查看当前主库的binlog日志文件名称及当前的position，结果如下：
• ==================================================
  mysql> show master status\G
  *************************** 1. row ***************************
  File: master.000011 //binlog日志文件名称
  Position: 142769423 //position
  Binlog_Do_DB:
  Binlog_Ignore_DB:
  Executed_Gtid_Set:
  1 row in set (0.00 sec)
  =============

mysql从库设置

• 登陆到从库mysql数据库
• change master to master_host='${master_ip}', master_user='slave', master_password='slave', master_port=3306, master_log_file='master.000011, master_log_pos=142769423;//master_ip是mysql主库数据库ip，master.000011是master当前binlog日志文件名称，142769423是position
• start slave; //启动从库
• show slave status\G//查看从库mysql状态，结果如下：

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 33.82.0.97
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master.000011
Read_Master_Log_Pos: 142769423
Relay_Log_File: 5a2f0ecf2c5d-relay-bin.000026
Relay_Log_Pos: 142769631
Relay_Master_Log_File: master.000011
Slave_IO_Running: Yes //yes就行了
Slave_SQL_Running: Yes//yes就行了
Replicate_Do_DB:
…//省略

• 多个从库重复操作

验证成功

• 登陆mysql主库创建表create database test;
• 登陆从库看看有没有创建成功即可。