基于docker实现mysql主从，数据持久化存储

一：环境配置

mysql-master

[root@localhost ~]# mkdir /mysql-master-data
[root@localhost ~]# mkdir /mysql-master
[root@localhost mysql-master]# vim my.cnf

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
#log-error      = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address   = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=1
log-bin=mysql-bin #开启二进制文件
#binlog-do-db=demo    #需要同步的二进制数据库名；
binlog-ignore-db=information_schema    #不同步的二进制数据库名,如果不设置可以将其注释掉;
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
                     

mysql-slave

[root@localhost ~]# mkdir /mysql-slave
[root@localhost ~]# mkdir /mysql-slave-data
[root@localhost ~]# touch /mysql-slave-data/relay.log
[root@localhost mysql-slave]# vim my.cnf

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
#log-error      = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address   = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
server-id=2
relay-log=/var/lib/mysql/relay.log

二：编写Dockerfile

master

from mysql5.7
add mysql-master /etc/mysql # 文件必须和Dockerfile在同一个文件下

生成镜像

docker build -t mysql-master .  # .表示Dockerfile所在的当前文件

slave

from mysql5.7
add mysql-slave /etc/mysql

生成镜像

docker build -t mysql-slave .

三：启动容器

mysql-master

docker run -p4444:3306 --name mysql-master -e MYSQL_ROOT_PASSWORD=123456 -v /mysql-master-data:/var/lib/mysql -v /mysql-master:/etc/mysql -d mysql:5.7

docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql-master

mysql-slave

docker run -p3333:3306 --name mysql-slave -e MYSQL_ROOT_PASSWORD=123456 -v /mysql-slave-data:/var/lib/mysql -v mysql-slave:/etc/mysql -d mysql:5.7

docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql-slave

四:配置主从

master

1>锁表，先加锁，防止两边数据不一致;如果业务还未上线，这个就没有必要了
mysql> flush tables with read lock;

2>创建授权用户
mysql> grant replication slave on *.* to 'slave'@'172.17.0.3' identified by '123456';

3>查看binglog信息，只有打开二进制日志，这句命令才有结果，表示当前数据库的二进制日志写到什么位置
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: information_schema,mysql,performance_schema,sys
Executed_Gtid_Set: 
1 row in set (0.00 sec)


# 注意：此时千万不要往里面写东西

slave

mysql> change master to master_host='172.17.0.2', master_user='slave', master_password='123456', master_port=3306, master_log_file='mysql-bin.000002', master_log_pos=154;
参数说明：
    master_host：        master ip
    master_user：        同步用户
    master_password：    密码
    master_port：　　　　 端口
    master_log_file：    master上面查看到的二进制日志名
    master_log_pos：     master上面查看到的POS值

mysql>slave start;

mysql> show slave status \G;
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes