AD同步Mysql_Docker制作mysql主从同步

1 安装docker

# 清理环境

[root@docker-server ~]# yum remove docker \

docker-client \

docker-client-latest \

docker-common \

docker-latest \

docker-latest-logrotate \

docker-logrotate \

docker-selinux \

docker-engine-selinux \

docker-engine

# 安装docker环境

[root@docker-server ~]# yum install -y yum-utils device-mapper-persistent-data lvm2 git

# 更换下载地址

[root@docker-server ~]# yum-config-manager --add-repo http://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repo

[root@docker-server ~]# yum makecache fast # 生成缓存 如果要清理缓存 yum clean all

[root@docker-server ~]# yum -y install docker-ce # 下载docker

[root@docker-server ~]# systemctl start docker # 启动Docker服务

[root@docker-server ~]# systemctl enable docker # 设置开机自动启动

2 准备工作

# 1.创建目录层级

[root@docker-server ~]# mkdir /application/mysql/{master/{data,conf,logs},slave/{data,conf,logs}} -p

[root@docker-server ~]# cd /application/mysql/

[root@docker-server mysql]# yum -y install tree

[root@docker-server mysql]# tree -L 2

.

├── master

│ ├── conf

│ ├── data

│ └── logs

└── slave

├── conf

├── data

└── logs

8 directories, 0 files

# 在两个mysql的conf目录中准备my.cnf配置文件方便映射到mysql容器里面

# 这里将mysql5.7的配置文件拷贝到两个目录中

[root@docker-server ~]# ls

anaconda-ks.cfg my.cnf

[root@docker-server ~]# cp my.cnf /application/mysql/master/conf/

[root@docker-server ~]# cp my.cnf /application/mysql/slave/conf/

# 对两个配置文件做主从的修改

[root@docker-server ~]# cd /application/mysql/master/conf/

[root@docker-server conf]# vim my.cnf

# 在 [mysqld] 标签下添加如下内容

server-id=1 # 定义server id master必写

log-bin = mylog # 开启binlog日志，master比写

gtid_mode = ON # 开启gtid

enforce_gtid_consistency=1 # 强制gtid

[client] # 后三行不知道什么意思，后面会报错

port=3306

socket=/var/lib/mysql/mysql.sock

[root@docker-server conf]# cd ../../slave/conf/

[root@docker-server conf]# vim my.cnf

# 在 [mysqld] 标签下添加如下内容

server-id=2

gtid_mode = ON

enforce_gtid_consistency=1

master-info-repository=TABLE

relay-log-info-repository=TABLE

[client]

port=3306

socket=/var/lib/mysql/mysql.sock

3 制作容器

# 1.下载镜像

[root@docker-server ~]# docker pull daocloud.io/library/mysql:5.7

[root@docker-server ~]# docker images

REPOSITORY TAG IMAGE ID CREATED SIZE

daocloud.io/library/mysql 5.7 718a6da099d8 2 months ago 448MB

# 2.启动master

[root@docker-server ~]# docker run -itd --privileged --restart=always -p 3301:3306 -v /application/mysql/master/data/:/var/lib/mysql -v /application/mysql/master/conf:/etc/mysql/mysql.conf.d -v /application/mysql/master/logs:/var/log/ --name mysql-master -e MYSQL_ROOT_PASSWORD=P@ssw0rd 718a6da0 /bin/bash

[root@docker-server ~]# docker exec -it mysql-master /bin/bash

root@567a03945e70:/# chmod 777 /var/log/ -R # 设置权限，不然mysql用户没有权限写入日志

root@567a03945e70:/# chmod 777 /var/lib/mysql/ -R # 设置权限不然报错

root@567a03945e70:/# service mysql start # 启动数据库

...

[info] MySQL Community Server 5.7.31 is started.

参数解释：

--privileged # 提权，让容器里面的root用户拥有正真的root权限，否则是root用户但是是普通用户的权限

--restart=always # 随着docker服务的启动在启动容器

-p # 端口映射，将宿主机的端口:容器内部的端口

-v, --volume # 给容器挂载存储卷，将本机的某个目录挂载到容器的某个目录

# 4.启动slave

[root@docker-server ~]# docker run -itd --privileged --restart=always -p 3302:3306 -v /application/mysql/slave/data/:/var/lib/mysql -v /application/mysql/slave/conf:/etc/mysql/mysql.conf.d -v /application/mysql/slave/logs:/var/log/ --name mysql-slave -e MYSQL_ROOT_PASSWORD=P@ssw0rd 718a6da0 /bin/bash

[root@docker-server ~]# docker exec -it mysql-slave /bin/bash

root@6b9adcad0206:/# chmod 777 /var/log/ -R && chmod 777 /var/lib/mysql/ -R

root@6b9adcad0206:~# service mysql start

..

[info] MySQL Community Server 5.7.31 is started.

4 制作主从同步

[root@docker-server ~]# docker ps

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

6b9adcad0206 718a6da0 "docker-entrypoint.s…" 28 minutes ago Up 28 minutes 33060/tcp, 0.0.0.0:3302->3306/tcp mysql-slave

567a03945e70 718a6da0 "docker-entrypoint.s…" 40 minutes ago Up 40 minutes 33060/tcp, 0.0.0.0:3301->3306/tcp mysql-master

# 1.登录master创建主从同步账号，并授权

[root@docker-server ~]# docker exec -it mysql-master /bin/bash

root@567a03945e70:/# mysql -uroot -pP@ssw0rd

...

mysql> grant replication slave,reload,super on *.* to 'slave'@'%' identified by 'P@ssw0rd'; # 创建主从同步账号，并授权

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges; # 刷新授权表

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

# 2.获取mysql-master容器的ip地址

[root@docker-server ~]# docker inspect mysql-master | grep IPAddress

"SecondaryIPAddresses": null,

"IPAddress": "172.17.0.2",

"IPAddress": "172.17.0.2",

# 3.登录slave容器操作

[root@docker-server ~]# docker exec -it mysql-slave /bin/bash

root@6b9adcad0206:/# mysql -uroot -pP@ssw0rd

...

mysql> change master to

-> master_host='172.17.0.2', # 指定master的容器ip地址

-> master_user='slave', # 指定同步用户

-> master_password='P@ssw0rd', # 同步用户的密码

-> master_auto_position=1;

Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave; # 启动slave角色

mysql> show slave status\G # 查看状态，验证sql和IO是不是yes。

主从同步完成

5 测试

[root@docker-server ~]# docker exec -it mysql-master /bin/bash

root@567a03945e70:/# mysql -uroot -pP@ssw0rd

mysql> create database test1; # 创建一个库

Query OK, 1 row affected (0.00 sec)

mysql> exit

Bye

[root@docker-server ~]# docker exec -it mysql-slave /bin/bash

root@6b9adcad0206:/# mysql -uroot -pP@ssw0rd

....

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| test1 |

+--------------------+

5 rows in set (0.01 sec)

mysql>

6 错误整理

mysqld: Can't create/write to file '/var/lib/mysql/is_writable' (Errcode: 13 - Permission denied)

解决方法：

没有权限给个权限就好 chmod 777 /var/lib/mysql/ -R

2020-10-16T08:43:05.431459Z 0 [ERROR] --initialize specified but the data directory exists and is not writable. Aborting.

2020-10-16T08:43:05.431491Z 0 [ERROR] Aborting

解决方法：

这个错误就是说mysql在进行初始化的时后,会检测数据目录是否存在,如过不存在,mysql会创建它,如果存在,而且这个目录里有数据,mysql会报错,并且终止初始化。那么这里将这个目录下所有的内容全部删除了，重新执行启动命令即可

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

mysqld: unrecognized service

解决方法：

把my.cnf配置文件里的这些删了

[client]

port=3306

socket=/var/lib/mysql/mysql.sock