docker 搭建mysql

docker搭建mysql主从

1.拉去最新的mysql

主:

docker run --restart always -p 3339:3306 --name mastermysql -v /Users/a/Documents/docker/mysql-master/conf:/etc/mysql/conf.d -v /Users/a/Documents/docker/mysql-master/logs:/logs -v /Users/a/Documents/docker/mysql-master/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=12345678 -d mysql

从:

docker run --restart always -p 3340:3306 --name slavemysql -v /Users/a/Documents/docker/mysql-slave/conf:/etc/mysql/conf.d -v /Users/a/Documents/docker/mysql-slave/logs:/logs -v /Users/a/Documents/docker/mysql-slave/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=12345678 -d mysql

查看内网ip

docker inspect --format='{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' mastermysql  

–>172.17.0.2

docker inspect --format='{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' slavemysql 

–>172.17.0.3

-p ：将容器的端口映射到主机端口。-v :挂载容器目录到本地 --restart :设置启动策略 -e MYSQL_ROOT_PASSWORD=12345678：初始化 root 用户的密码。

2.配置主mastermysql vim /docker/mysql-master/conf/master.cnf 进入容器重启mysql 重启mysql service mysql restart

[client]
port                        = 3306
socket                      = /var/lib/mysql/mysql.sock
[mysqld]
# Master node configuration 2 and slave node configuration 3， generally configured according to the Intranet IP
server-id                   = 2
port                        = 3306
datadir                     = /var/lib/mysql
socket                      = /var/lib/mysql/mysql.sock
default-storage-engine      = InnoDB
log-bin                     = mysql-bin
log-bin-index               = mysql-bin.index
relay-log                   = mysql-relay
relay-log-index             = mysql-relay.index
expire-logs-days            = 10
max-binlog-size             = 100M
max_binlog_cache_size       = 8M
log-slave-updates           = 1
binlog_cache_size           = 4M
# use MIXED binlog
binlog_format               = MIXED
#binlog_format              = ROW
replicate-do-db            = db%.%　　#需要同步的数据库
replicate-ignore-db        = mysql.%　　#不需要同步的数据库
# ignore tables
replicate-wild-ignore-table = mysql.%
sync_binlog                 = 1
relay_log_recovery          = 1
log_slave_updates           = 1
skip-name-resolve
sql_mode=STRICT_TRANS_TABLES
[mysqldump]
quick
max_allowed_packet          = 32M

3.配置从slavemysql vim /docker/mysql-slave/conf/slave.cnf 进入容器重启mysql 重启mysql service mysql restart

[client]
port                        = 3306
socket                      = /var/lib/mysql/mysql.sock
[mysqld]
# Master node configuration 2 and slave node configuration 3， generally configured according to the Intranet IP
server-id                   = 3
port                        = 3306
datadir                     = /var/lib/mysql
socket                      = /var/lib/mysql/mysql.sock
default-storage-engine      = InnoDB
log-bin                     = mysql-bin
log-bin-index               = mysql-bin.index
relay-log                   = mysql-relay
relay-log-index             = mysql-relay.index
expire-logs-days            = 10
max-binlog-size             = 100M
max_binlog_cache_size       = 8M
log-slave-updates           = 1
binlog_cache_size           = 4M
# use MIXED binlog
binlog_format               = MIXED
#binlog_format              = ROW
#replicate-do-db            = db%.%
#replicate-ignore-db        = mysql.%
# ignore tables
replicate-wild-ignore-table = mysql.%
sync_binlog                 = 1
relay_log_recovery          = 1
log_slave_updates           = 1
skip-name-resolve
sql_mode=STRICT_TRANS_TABLES
[mysqldump]
quick
max_allowed_packet          = 32M

4.分别进入容器 mastermysql 和 slavemysql 链接客户端

docker exec -it mastermysql /bin/bash

登录mysql客户端，创建用户，授权，开启binlog

mysql -uroot -h 127.0.0.1 -p
create user 'repl'@'%' identified by 'tkamc.00';
grant replication slave on *.* to 'repl'@'%' identified by 'tkamc.00';
flush privileges;
flush tables with read lock;   #锁定表状态
show master status;        #查看主状态，用于后面从的配置

#等待从配置以后执行
unlock tables;

docker exec -it slavemysql  /bin/bash
mysql -uroot -h 127.0.0.1 -p
show master status;
stop slave;
将slave指向master
change master to master_host='172.17.0.2',master_port=3306,master_user='repl',master_password='tkamc.00',master_log_file='mysql-bin.000002',master_log_pos=1342;

查看slave的运行状态

start slave;
show slave status\G;

Slave_IO_Running: YES 表示slave的日志读取线程开启
Slave_SQL_Running: YES 表示SQL执行线程开启
如果 ; Slave_SQL_Running: NO 再次执行

stop slave;
change master to Master_Log_File='mysql-bin.000003',Master_Log_Pos=773;
start slave;
show slave status\G;

建议：MySQL主从机制比较脆弱，谨慎操作，如果重启master，必须要先把slave停掉，即要在slave执行slave stop命令后，再去重启master的mysql服务，否则会导致中断，重启后，还需要把slave开启slave start。