Mysql数据库的单主以及双主

一、 准备docker环境

1. 卸载旧的docker版本

sudo yum remove docker \
                  docker-client \
                  docker-client-latest \
                  docker-common \
                  docker-latest \
                  docker-latest-logrotate \
                  docker-logrotate \
                  docker-engine

2. 安装需要的安装包

sudo yum install -y yum-utils

3. 设置镜像的仓库

sudo yum-config-manager \
    --add-repo \
    https://download.docker.com/linux/centos/docker-ce.repo #默认国外

sudo yum-config-manager \
    --add-repo \
    http://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repo #推荐使用阿里云

4.更新yum软件包索引

yum makecache fast

5. 安装docker相关 docker-ce社区版 ee企业版

sudo yum install docker-ce docker-ce-cli containerd.io

6. 启动docker

sudo systemctl start docker

7.使用docker version查看是否安装成功

二、 创建相应的目录

2.Master 节点 [10.242.135.51]

2.1 创建Master 的数据目录

mkdir -p <your MySQL master node data path>
eg: mkdir -p  /var/testMysql/master/data

cd <your MySQL master node data path>
eg: cd /var/testMysql/master/data

cd ../ && chmod -R 777 <data directory's name> 
eg: cd ../ && chmod -R 777 data

2.2 创建Master 的配置目录

mkdir -p <your MySQL master node configure path>
eg:  mkdir -p  /var/testMysql/master/conf

cd <your MySQL master node configure path>
eg: cd /var/testMysql/master/conf

cd ../ && chmod -R 777 <configure directory's name>
eg: cd ../ && chmod -R 777 conf

2.3 创建Master 配置文件

touch my.cnf && \
echo [mysqld] >> my.cnf && \
echo log-bin=mysql-bin >> my.cnf && \
echo server-id=1 >> my.cnf

2.4 运行容器

get current user id 或者 echo $UID
id <current username>

docker run -dit \
--name MySQL \
--restart always \
-e MYSQL_ROOT_PASSWORD=<your password> \
-v <your configure path>:/etc/mysql/conf.d  \
-v <your data path>/data:/var/lib/mysql \
-p 3306:3306 \
-u <current user id> \
mysql[:tag]

eg:
	docker run -dit \
	--name MySQLM \
	--restart always \
	-e MYSQL_ROOT_PASSWORD=123456 \
	-v /var/testMysql/master/conf:/etc/mysql/conf.d  \
	-v /var/testMysql/master/data:/var/lib/mysql \
	-p 3306:3306 \
	-u 0 \
	mysql:8.0.16 

2.5 配置

docker exec -it MySQL /bin/bash
mysql -u root –p

enter your password
# mysql 登录 
docker exec -it [容器id] bash
mysql -u root -p
 
# 给予远程用户所有表所有权限
GRANT ALL ON *.* TO 'root'@'%';
 
# 更改加密规则
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
 
# 远程访问
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; 

创建一个给从机链接用的账号及密码
CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
 
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';

# 刷新权限
flush privileges;

3.Slave 节点 [10.242.135.52]

3.1 创建Master 的数据目录

mkdir -p <your MySQL slave node's data path>
cd <your MySQL slave node's data path>
cd ../ && chmod -R 777 <data directory's name> 

3.2 创建Slave的配置目录

mkdir -p <your MySQL slave node's configure path>
cd <your MySQL slave node's configure path>
cd ../ && chmod -R 777 <configure directory's name>

3.3 创建Slave配置文件

touch my.cnf && \
echo [mysqld] >> my.cnf && \
echo log-bin=mysql-bin >> my.cnf && \
echo server-id=2 >> my.cnf

3.4 运行容器

get current user id 或者 echo $UID
id <current username>
docker run -dit \
--name MySQL \
--restart always \
-e MYSQL_ROOT_PASSWORD=<your password> \
-v <your configure path>:/etc/mysql/conf.d  \
-v <your data path>/data:/var/lib/mysql \
-p 3306:3306 \
-u <current user id> \
mysql[:tag]

3.5 配置

docker exec -it MySQL /bin/bash
mysql -u root –p

enter your password
# mysql 登录 
docker exec -it [容器id] bash
mysql -u root -p
 
# 给予远程用户所有表所有权限
GRANT ALL ON *.* TO 'root'@'%';
 
# 更改加密规则
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
 
# 远程访问
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; 

创建一个给从机链接用的账号及密码
CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
 
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';

# 刷新权限
flush privileges;

# 只读模式，可选
SET GLOBAL read_only=ON;
SET GLOBAL super_read_only=ON;

单向备份

在 10.242.135.51
show master status;

10.242.135.52

stop slave;


change master to 
master_host='10.242.135.51',
master_user='slaveac',
master_password='slaveac123456',
master_log_file='binlog.000005',
master_log_pos=6463;

start slave;

show slave status\G

见到 就可以了
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

双向只需俩边同时stop slave然后change master to ... 在分别start slave

主从的目的是未能备份 或 读写分离，当主从被破坏后，需要重新 对接；
先把 数据 导出，导入，保证 主服务 数据正确后，再重新构建
主从关系