docker下配置mysql 主从

本机docker下配置mysql主从

首先安装docker mysql容器

$ docker pull mysql:5.6

拉取两个相同版本mysql

分别启动mysql 并设置root用户密码为admin：

//主mysql
docker run -d -e MYSQL_ROOT_PASSWORD=admin --name mysql-master -v /Volumes/docker/mysql/my-m.cnf:/etc/mysql/my.cnf -p 3307:3306 mysql:5.6

从mysql
docker run -d -e MYSQL_ROOT_PASSWORD=admin --name mysql-slave -v  /Volumes/docker/mysql/my-s.cnf:/etc/mysql/my.cnf -p 3308:3306 mysql:5.6

mysql master配置文件my-m.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
secure-file-priv = NULL
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp

log-bin = mysql-bin 
server-id = 100

# 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

#log-error  = /var/log/mysql/error.log

# 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/

 

mysql master配置文件my-s.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
secure-file-priv = NULL
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp

log-bin = mysql-bin 
server-id = 101

# 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

#log-error  = /var/log/mysql/error.log

# 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/

 

注意：主配置文件和从配置文件的server-id不同。

 

//进入主mysql：
bash -c "clear && docker exec -it mysql-master sh"

//链接mysql
mysql -uroot -padmin

//创建从用户

CREATE USER 'slave'@'%' IDENTIFIED BY '12345678';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

show master status;

mysql>
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 2386 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

记录好file 和position

//链接从数据库
bash -c "clear && docker exec -it mysql-slave sh"

//进入从数据库
mysql -uroot -p

//创建
change master to master_host='172.17.0.5', master_user='slave', master_password='12345678', master_port=3306, master_log_file='mysql-bin.000003', master_log_pos=2386, master_connect_retry=30;

ps:docker中查看主从mysql的ip

☁ lib docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
e5b0b0e6e33c mysql "docker-entrypoint..." 47 hours ago Up 2 minutes 33060/tcp, 0.0.0.0:3308->3306/tcp mysql-slave
402ea137d6da mysql "docker-entrypoint..." 47 hours ago Up 3 minutes 33060/tcp, 0.0.0.0:3307->3306/tcp mysql-master
d905b168284e php:7.1.0-fpm "docker-php-entryp..." 2 days ago Up 4 hours 0.0.0.0:9000->9000/tcp myphp
ae37295e5360 nginx "nginx -g 'daemon ..." 2 days ago Up 4 hours 0.0.0.0:8080->80/tcp mynginx
4725b5cd7515 redis:latest "docker-entrypoint..." 3 months ago Up 5 hours 0.0.0.0:6380->6379/tcp myredis
☁ lib
☁ lib docker inspect --format '{{ .NetworkSettings.IPAddress }}' 402
172.17.0.5

//启动同步
start slave；

//查看状态

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.5
Master_User: slave
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 2386
Relay_Log_File: e5b0b0e6e33c-relay-bin.000002
Relay_Log_Pos: 2014
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 2386
Relay_Log_Space: 2229
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_UUID: 7ce8f23c-a79f-11e8-a391-0242ac110005
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
1 row in set (0.00 sec)

ERROR:
No query specified

//都为yes表示成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes