#开始前需要先把linux的防火墙关掉
#查看防火墙状态
systemctl status firewalld
#关闭防火墙
systemctl stop firewalld
启动两台MySQL
#拉取mysql8.0.18镜像
docker pull mysql:8.0.18
#启动第一个mysql(主)(暴露33060端口)
docker run -p 33060:3306 --name mysql8_33060 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0.18
#启动第二个mysql(从)(暴露33061端口)
docker run -p 33061:3306 --name mysql8_33061_s1 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0.18
# 查看运行的mysql容器
docker ps
对MySQL的配置文件进行修改
#进入主mysql
docker exec -it mysql8_33060 /bin/bash
#进入容器后,默认是没vim的,所以需要先安装vim才可以查看修改配置文件
#先更新下apt-get的源,下载速度快一些
mv /etc/apt/sources.list /etc/apt/sources.list.bak && \
echo "deb http://mirrors.163.com/debian/ jessie main non-free contrib" >/etc/apt/sources.list && \
echo "deb http://mirrors.163.com/debian/ jessie-proposed-updates main non-free contrib" >>/etc/apt/sources.list && \
echo "deb-src http://mirrors.163.com/debian/ jessie main non-free contrib" >>/etc/apt/sources.list && \
echo "deb-src http://mirrors.163.com/debian/ jessie-proposed-updates main non-free contrib" >>/etc/apt/sources.list
#然后需要更新apt-get
apt-get update
#安装vim
apt-get install vim -y
#打开配置文件
vim /etc/mysql/my.cnf
#然后复制下面的配置,黏贴到[mysqld]下
#设置主mysql的id
server-id = 1
#启用二进制日志
log-bin=mysql-bin
#设置logbin格式
binlog_format = STATEMENT
#然后按esc键,输入 :wq 退出
#退出容器
exit
#然后进入从mysql
docker exec -it mysql8_33061_s1 /bin/bash
#安装vim
mv /etc/apt/sources.list /etc/apt/sources.list.bak && \
echo "deb http://mirrors.163.com/debian/ jessie main non-free contrib" >/etc/apt/sources.list && \
echo "deb http://mirrors.163.com/debian/ jessie-proposed-updates main non-free contrib" >>/etc/apt/sources.list && \
echo "deb-src http://mirrors.163.com/debian/ jessie main non-free contrib" >>/etc/apt/sources.list && \
echo "deb-src http://mirrors.163.com/debian/ jessie-proposed-updates main non-free contrib" >>/etc/apt/sources.list
apt-get update
apt-get install vim -y
vim /etc/mysql/my.cnf
#设置从mysql的id
server-id = 2
#启用中继日志
relay-log = mysql-relay
# :wq 退出
#退出容器
exit
#重启两个mysql容器
docker restart mysql8_33060
docker restart mysql8_33061_s1
配置主从复制
#进入主mysql容器
docker exec -it mysql8_33060 /bin/bash
#登录主mysql
mysql -uroot -p123456 -h10.211.55.26 -P33060
#创建用于主从复制的账号db_sync,密码db_sync
create user 'db_sync'@'%' identified with mysql_native_password by 'db_sync';
#授权
grant replication slave on *.* to 'db_sync'@'%';
#刷新权限
FLUSH PRIVILEGES;
#确认位点 记录下文件名以及位点(重启或者刷新都会改变)
show master status;
# mysql-bin.000001
# 823
#退出主mysql
exit
#登录从mysql
mysql -uroot -p123456 -h10.211.55.26 -P33061
#先停止同步
STOP SLAVE;
#修改从库指向到主库,使用上一步记录的文件名以及位点
# master_host docker容器linux的ip地址
# master_port 主mysql暴露的端口
# master_user 主mysql的用户名
# master_password 主mysql的密码
#(最后两项修改成刚刚从主mysql查到的,主mysql每次刷新权限或者重启时,这两个值都会改变,所以每次都需要查看是否相同)
CHANGE MASTER TO
master_host = '10.211.55.26',
master_port = 33060,
master_user = 'db_sync',
master_password = 'db_sync',
master_log_file = 'mysql-bin.000001',
master_log_pos = 823;
#启动同步
START SLAVE;
#查看Slave_IO_Runing和Slave_SQL_Runing字段值都为Yes,表示同步配置成功。
show slave status \G;
#退出mysql
exit
#退出容器
exit
测试
#在主mysql创建数据库
create database test;
#在从mysql上查看
show databases;
#在主mysql创建数据表并插入数据
use test;
create table t_user(
`t_id` int primary key,
`t_name` varchar(20) not null
);
insert into t_user values(1, 'angenin');
#在从mysql上查看
use test;
select * from t_user;
主mysql:
从mysql:
实现mysql的读写分离可以用Sharding-Sphere中间件,此中间件可以进行分库分表,读写分离。
这是看尚硅谷的Sharding-Sphere视频记的笔记:Sharding-Sphere 学习笔记