Mysql 主从复制
环境准备
# 镜像搜索
docker search mysql
# 镜像拉取
docker pull docker.io/mysql:5.7
服务启动
#服务一
docker run --name mysqlm1 -p 3306:3306 --privileged=true -ti -e MYSQL_ROOT_PASSWORD=123456 -e MYSQL_USER=user -e MYSQL_PASSWORD=pass -v /home/mysql/docker-data/m1/conf:/etc/mysql/conf.d -v /home/mysql/docker-data/m1/data/:/var/lib/mysql -v /home/mysql/docker-data/m1/logs/:/var/log/mysql -d mysql:5.7
#服务二
docker run --name mysqls1 -p 13306:3306 --privileged=true -ti -e MYSQL_ROOT_PASSWORD=123456 -e MYSQL_USER=user -e MYSQL_PASSWORD=pass -v /home/mysql/docker-data/s1/conf:/etc/mysql/conf.d -v /home/mysql/docker-data/s1/data/:/var/lib/mysql -v /home/mysql/docker-data/s1/logs/:/var/log/mysql -d mysql:5.7
主从机配置
默认mysqlm1为主机,mysqls1为丛机
my.cnf配置
# 创建文件my.cnf
touch /home/mysql/docker-data/m1/conf/my.cnf
touch /home/mysql/docker-data/s1/conf/my.cnf
# 编辑my.cnf、拷贝配置填充
vim /home/mysql/docker-data/m1/conf/my.cnf
vim /home/mysql/docker-data/s1/conf/my.cnf
配置如下:
主机
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
character_set_server=utf8
init_connect='SET NAMES utf8'
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
lower_case_table_names=1
#指定主机号,不允许出现重复
server-id=129
#开启binlog
log-bin=mysql-bin
auto_increment_increment=2
auto_increment_offset=1
#rpl_semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=10000
从机
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
character_set_server=utf8
init_connect='SET NAMES utf8'
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
lower_case_table_names=1
#指定主机号,不允许出现重复
server-id=130
#开启binlog
log-bin=mysql-bin
auto_increment_increment=2
auto_increment_offset=1
#rpl_semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=10000
主机创建、授权主从权限
# 创建与用户
CREATE USER 'dog'@'%' IDENTIFIED BY '123456';
# 分配权限
# 分配库访问权限
GRANT ALL ON test.* TO 'dog'@'%';
# 核心权限
GRANT REPLICATION SLAVE,FILE,REPLICATION CLIENT ON *.* TO 'dog'@'%' IDENTIFIED BY '123456';
# 刷新权限
FLUSH PRIVILEGES;
# 重启容器
docker restart mysqlm1; # 主机上添加用户信息
# 查看主机的binlog信息
show master status;
画圈为重点、下面用得着
从机关联主机配置
#语法
change master to master_host='master的ip',master_port=master的端口号,master_user='repluser',master_password='123456',master_log_file='master中的binlob文件',master_log_pos=master中的position位置信息;
#命令
change master to master_host='192.168.200.132',master_port=3306,master_user='dog',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=154;
# 查看从机状态
show slave status \G;
# 启动从机
start slave;
# 查看主从信息
show global variables like "%log%";
# 查看mysql用户服务信息
show processlist;
启动前
启动后
主从信息
主机服务信息
从机服务信息
Mysql主主复制
基于上面主从复制之后,将从机变成主机就可以实现主主复制
主机创建、授权主从权限
# 创建与用户
CREATE USER 'dog'@'%' IDENTIFIED BY '123456';
# 分配权限
# 分配库访问权限
GRANT ALL ON test.* TO 'dog'@'%';
# 核心权限
GRANT REPLICATION SLAVE,FILE,REPLICATION CLIENT ON *.* TO 'dog'@'%' IDENTIFIED BY '123456';
# 刷新权限
FLUSH PRIVILEGES;
# 重启容器
docker restart mysqls1; # 从机变主机,从机上添加用户信息
# 查看主机的binlog信息
show master status;
从机关联主机配置
#命令
change master to master_host='192.168.200.132',master_port=13306,master_user='dog',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=1100;
# 查看从机状态
show slave status \G;
# 启动从机
start slave;
# 查看主从信息
show global variables like "%log%";
# 查看mysql用户服务信息
show processlist;
s1
m1