mysql 0q_基于docker快速构建MySQL主从复制环境

前言：最近一段时间在研究mysql主从复制，但觉得开虚拟机搭建太麻烦；刚好最近在学习Docker；于是顺便写了个自动搭建shell 脚本；

1、建议先下载好mysql镜像：docker pull mysql:5.6.34

------------------------------------------

2、执行脚本；

如下：

root@default:/mnt/sda1/dockers/tesh# more test2.sh

#!/bin/bash

#mysql数据文件路径;

MASTER_DATA_DIR=/docker/mysql/data/master

SLAVE_DATA_DIR=/docker/mysql/data/slave

#mysql配置文件路径;

MASTER_DIR=/docker/mysql/master

SLAVE_DIR=/docker/mysql/slave

##第一次执行，删除已存在的相关容器；

docker rm -f mysql-master

docker rm -f mysql-slave

##删除已存在的路径；

rm -rf $MASTER_DATA_DIR $SLAVE_DATA_DIR $MASTER_DIR $SLAVE_DIR

##创建路径相关文件夹；

mkdir -p $MASTER_DATA_DIR $SLAVE_DATA_DIR $MASTER_DIR $SLAVE_DIR

#master.cnf 配置文件

echo "

[mysqld]

#设置server-id

server_id = 1

#设置数据库字符集参数

character-set-server=utf8mb4

collation-server=utf8mb4_unicode_ci

#存储引擎配置，可选INNODB、MyISAM

default-storage-engine=INNODB

#Optimize omit

#ql_mode模式，默认配置；

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#开启二进制日志

log-bin = /var/lib/mysql/binlog

#log_bin_trust_function_creators 变量设置为1，MySQL不会对创建存储函数实施这些限制；

log_bin_trust_function_creators=1

#mysql复制主要有三种方式：基于SQL语句的复制，基于行的复制，混合模式复制。

#对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。

binlog_format = ROW

#日志自动清理配置

expire_logs_days = 99

sync_binlog = 0

slow-query-log=1

slow-query-log-file=/var/log/mysql/slow-queries.log

long_query_time = 3

log-queries-not-using-indexes

" > $MASTER_DIR/master.cnf

#slave.cnf 配置文件

echo "

[mysqld]

server_id = 2

character-set-server=utf8mb4

collation-server=utf8mb4_unicode_ci

default-storage-engine=INNODB

#Optimize omit

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

log-bin = /var/lib/mysql/binlog

log_bin_trust_function_creators=1

binlog_format = ROW

expire_logs_days = 99

sync_binlog = 0

relay_log=slave-relay-bin

log-slave-updates=1

slave-skip-errors=all

slow-query-log=1

slow-query-log-file=/var/log/mysql/slow-queries.log

long_query_time = 3

" > $SLAVE_DIR/slave.cnf

## Start instance

## Master容器创建

docker run --name mysql-master \

-p 13306:3306 \

-v $MASTER_DIR:/etc/mysql/conf.d \

-v $MASTER_DATA_DIR:/var/lib/mysql \

-e MYSQL_ROOT_PASSWORD=root \

-d mysql:5.6.34

## Slave容器创建

docker run --name mysql-slave \

-p 23306:3306 \

-v $SLAVE_DIR:/etc/mysql/conf.d \

-v $SLAVE_DATA_DIR:/var/lib/mysql \

-e MYSQL_ROOT_PASSWORD=root \

--link mysql-master:master \

-d mysql:5.6.34

## 需要等待容器起来，服务起来，等待20秒；

sleep 20

## 创建主从同步账号并授权

docker exec -it mysql-master mysql -proot -e "CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';"

docker exec -it mysql-master mysql -proot -e "GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';"

##二进制文件 和 位置

File_tag=`docker exec -it mysql-master mysql -proot -e "show master status\G" |grep File |awk '{print $2}'`

Position_tag=`docker exec -it mysql-master mysql -proot -e "show master status\G" |grep Position |awk '{print $2}'`

##停止slave 防止已存在进程；(可注释掉该行)

docker exec -it mysql-slave mysql -proot -e "stop slave;"

##执行同步SQL语句

docker exec -it mysql-slave mysql -proot -e "

CHANGE MASTER TO \

MASTER_HOST='master',\

MASTER_PORT=3306,\

MASTER_USER='repl',\

MASTER_PASSWORD='repl',\

MASTER_LOG_FILE='$File_tag',\

MASTER_LOG_POS=$Position_tag;

"

##启动slave

docker exec -it mysql-slave mysql -proot -e "start slave;"

## 查看主从状态，Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes 说明mysql 主从(一主一从)搭建成功；

docker exec -it mysql-slave mysql -proot -e "show slave status\G"

##完成

---------------------------------------------

3、执行输出：

root@default:/mnt/sda1/dockers/tesh# sh test2.sh

mysql-master

mysql-slave

52754e8a17681f3642934a1b6521775c7b3c8174e492c7cfeb0aae3f59370220

95d19e9dfc9693d768ae9edded3cb2220c77468cc73e63682d59ea786ac3e3bb

Warning: Using a password on the command line interface can be insecure.

Warning: Using a password on the command line interface can be insecure.

Warning: Using a password on the command line interface can be insecure.

Warning: Using a password on the command line interface can be insecure.

Warning: Using a password on the command line interface can be insecure.

Warning: Using a password on the command line interface can be insecure.

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: master

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000004

Read_Master_Log_Pos: 403

Relay_Log_File: slave-relay-bin.000002

Relay_Log_Pos: 280

Relay_Master_Log_File: binlog.000004

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: 403

Relay_Log_Space: 453

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: 1

Master_UUID: f41c4d43-7899-11e8-aff1-0242ac110002

Master_Info_File: /var/lib/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

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

参考1：https://www.cnblogs.com/ivictor/p/6440429.html

参考2：https://blog.csdn.net/lijiqidong/article/details/78482908