Mysql集群

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