docker 实现mysql主从复制

主从复制:

本机是阿里云服务器，Centos7 内核3.10.先下载mysql镜像（上图）

1、创建文件夹:data、conf、logs用于mysql数据库文件和日志文件以及配置文件挂载。数据库文件等不可能放在容器里面

我的master和slave分开的
mkdir -p /mysql/master/data
mkdir -p /mysql/master/conf
mkdir -p /mysql/master/logs
mkdir -p /mysql/slave/data
mkdir -p /mysql/slave/conf
mkdir -p /mysql/slave/logs

在conf创建master.cnf文件。文件内容:

# Copyright (c) 2014, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL Community Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp

log-bin = mysql-bin 
server-id = 1 

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address   = 127.0.0.1


# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

再创建slave.cnf文件（注意放入的文件夹是slave的conf）。注意:server-id不能一样，是数字就可以 比如1和2 101和102

这2个文件名随意，路径挂载正确即可

2、挂载配置文件
docker run --name mastermysql -v /mysql/master/data:/var/lib/mysql -v /mysql/master/conf:/etc/mysql/conf.d -v /mysql/master/logs:/var/log/mysql -e MYSQL_ROOT_PASSWORD=admin -p 5306:3306 -d mysql:5.7.19

docker run --name slavemysql -v /mysql/slave/data:/var/lib/mysql -v /mysql/slave/conf:/etc/mysql/conf.d -v /mysql/slave/logs:/var/log/mysql -e MYSQL_ROOT_PASSWORD=admin -p 5307:3306 -d mysql:5.7.19

解释下:-d 是后台守护进程运行mysql   -p 是外部访问映射的端口  3306是docker容器默认的端口。和服务器端口无关

3、进入主库master（记住是进入主库）   命令:docker exec -it 40e6437e0a81  /bin/bash          

40e6437e0a81  是容器ID

进入到mysql 命令show master status；查看部分信息。主要是2个信息 File 和Position  后面要用

创建用户，可建可不建。不建议直接使用root这种用户

 

创建用户:

格式:CREATE USER 'username'@'host' IDENTIFIED BY 'password';
username：你将创建的用户名
host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

CREATE USER 'ypp'@'%' IDENTIFIED BY 'xxxxx';  创建一个用户

授权:

格式:GRANT privileges ON databasename.tablename TO 'username'@'host'

privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
databasename：数据库名
tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*

GRANT ALL ON *.* TO 'ypp'@'%';  目前测试所有权限都给

登录到从库进入mysql，配置主库信息：

change master to master_host='xxx.xxx.xx.xx', master_port=5306,master_user='ypp',master_password='xxxxx', master_log_file='mysql-bin.000001',master_log_pos=154;

master_host:主库的IP地址/服务器地址

master_port:主库的端口

master_user:主库开放出来的用户

master_password：用户的密码

master_log_file：主库的日志文件，二进制日志文件，进行同步的。就是上面show master status 里面的File

master_log_pos：上面show master status 里面的Position 

 

mysql>start slave;

mysql>show slave status\G

出现以下截图表示主从复制配置成功

如果Slave_IO_Running显示connecting，查看主密码是否正确，网络是否能联通，pos是否正确等

mysql>stop slave; #停止I/O 线程和SQL线程的操作。

确认无误再输入主从配置信息，然后开启复制

mysql>start slave;

mysql>show slave status\G

 

测试:

成功

时间有限，主从复制就到这，后面记录读写分离