docker安装mysql配置主从复制

docker安装mysql配置主从复制

1. 使用docker安装mysql

1. 创建脚本docker_mysql.sh文件

docker rm -f $(docker ps -a |  grep "mysql"  | awk '{print $1}')

docker run -p 3306:3306 --name mysql --restart=always -v /opt/mydata/mysql/log:/var/log/mysql -v /opt/mydata/mysql/data:/var/lib/mysql -v /opt/mydata/mysql/conf:/etc/mysql  -e MYSQL_ROOT_PASSWORD=root123456  -d mysql:5.7
cd /mydata/mysql/conf
if [ ! -f "$my.cnf" ]; then
  touch "$my.cnf"
else
  rm -rf my.cnf
  touch "$my.cnf"
fi
tee /opt/mydata/mysql/conf/my.cnf <<-'EOF'
[client]
default-character-set=utf8 

[mysql]
default-character-set=utf8 

[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci' 
init_connect='SET NAMES utf8'
character-set-server=utf8 
collation-server=utf8_unicode_ci 
skip-character-set-client-handshake 
skip-name-resolve
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
EOF

2. 在两台服务器上执行docker_mysql.sh

bash docker_mysql.sh

2. 配置主从复制

Master：

vim /opt/mydata/mysql/conf/my.cnf

# 添加如下内容
## 同一局域网内注意要唯一
server-id=100  
## 开启二进制日志功能，可以随便取（关键）
log-bin=mysql-bin

# 重启容器
docker restart 容器id

# 进入容器,授予用户 slave REPLICATION SLAVE权限和REPLICATION CLIENT权限，用于在主从库之间同步数据
docker exec -it mysql /bin/bash
mysql -uroot -p
CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

# 查看File和Position,配置Slave需要用到.
# File对应下面的master_log_file,Position对应下面的master_log_pos
show master status;
exit;

# 退出容器
ctrl+p+q

Slave:

vim /opt/mydata/mysql/conf/my.cnf

# 添加如下内容
## 设置server_id,注意要唯一
server-id=101  
## 开启二进制日志功能，以备Slave作为其它Slave的Master时使用
log-bin=mysql-slave-bin   
## relay_log配置中继日志
relay_log=edu-mysql-relay-bin


# 重启容器
docker restart 容器id

# 进入容器执行下面命令
docker exec -it mysql /bin/bash
mysql -uroot -p
# master_host是主机IP地址, master_log_file master_log_pos 从主机Master获取
change master to master_host='192.168.200.243', master_user='slave', master_password='123456', master_port=3306, master_log_file='mysql-bin.000006', master_log_pos=371, master_connect_retry=30;
# 查看主从同步状态,此时SlaveIORunning 和 SlaveSQLRunning 都是No，因为我们还没有开启主从复制过程
show slave status \G;
# 开启主从复制
start slave;
# 再次查看主从同步状态,此时SlaveIORunning 和 SlaveSQLRunning 都是Yes
show slave status \G;
exit;

# 退出容器
ctrl+p+q