基于docker环境安装mysql并实现主从

前提：环境已经部署安装docker，如没有可以参考上一篇docker安装方法

1、使用Docker拉取MySQL镜像

$ docker pull mysql:5.7

2、安装主库master

2.1、创建主库挂载文件地址

mkdir -pv /usr/local/mysql/master/conf
mkdir -pv /usr/local/mysql/master/data
mkdir -pv /usr/local/mysql/master/log

2.2、在conf下创建my.cnf文件

[mysqld]
server_id=1
log-bin= mysql-bin
expire_logs_days=10
#数据库名称忽略大小写
lower_case_table_names=1
log-bin= mysql-bin
read-only=0
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
 ###引用默认配置
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

2.3、安装并运行一组命令

docker run --name mysqlmaster -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root -v /usr/local/mysql/master/conf/my.cnf:/etc/mysql/my.cnf -v /usr/local/mysql/master/data:/var/lib/mysql -v /usr/local/mysql/master/log:/var/log/mysql --privileged=true -d mysql:5.7

#进入容器
docker exec -it mysqlmaster bash

#进入mysql
mysql -u root -p

#创建slaver的账号
--create user  'backup'@'%'  identified by 'backup';

#授权
GRANT REPLICATION SLAVE ON *.* to 'backup'@'%' identified by 'backup';

#刷新权限
flush privileges;

#查看信息
show master status \G;
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 439
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

ERROR:
No query specified

3、安装从库slave

3.1、创建从库挂载文件地址

mkdir -pv /usr/local/mysql/slave/conf
mkdir -pv /usr/local/mysql/slave/data
mkdir -pv /usr/local/mysql/slave/log

3.2、配置文件my.cnf

[mysqld]
server_id=2
lower_case_table_names=1
log-bin= mysql-bin
read-only=0
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
explicit_defaults_for_timestamp=true
bind-address = 0.0.0.0
###引用默认配置
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

3.3、安装并运行一组命令

docker run --name mysqlslave -p 3307:3306 -e MYSQL_ROOT_PASSWORD=root -v /usr/local/mysql/slave/conf/my.cnf:/etc/mysql/my.cnf -v /usr/local/mysql/slave/data:/var/lib/mysql -v /usr/local/mysql/slave/log:/var/log/mysql --privileged=true -d mysql:5.7

#进入容器
docker exec -it mysqlslave bash
#进入mysql
mysql -u root -p
#执行主从
change master to master_host='192.168.100.16',master_port=3306,master_user='backup',master_password='backup',master_log_file='mysql-bin.000003',master_log_pos=439;

#开启复制
start slave;

#查看主从是否成功
show slave status \G;

正常情况下，SlaveIORunning 和 SlaveSQLRunning 都是Yes 及成功，操作主库验证从库即可。

备注：执行从库的master_log_file='mysql-bin.000003',master_log_pos=439;这些一定是主库的对应的值。