mysql权限主机_docker部署mysql主主备份（keepalived）跨主机自动切换

部署mysql主备()跨主机自动切换

部署mysql主备

部署mysql主备()跨主机自动切换

部署mysql主备代理（swarm）

1. 环境准备 2. 主主同步原理

​ 主主复制是指两台MySQL主机中都可以更改数据，另一台主机也会做出相应的更改。如果你聪明的话，你可能已经知道如何实现它了。是的，将两个主从复制有机合并即可。不过配置时需要注意一些问题，比如主键重复、-id不能重复等。

​ 两台MySQL服务器同时互为对方的从库和主库。这一方案不仅分流了流量压力，还解决了“单点故障”问题。如果任何单元出现故障，则可以使用另一组服务。

主主复制----->相互主从

3. 预期目标

搭建双主Mysql环境

实现数据库故障时自动切换数据库

依赖项容器名称服务器端口 vip

主持人

mysql-

192.168.56.100

3306

192.168.56.120

主持人

mysql-

192.168.56.101

3306

4. 设置配置 4.1.在两台主机之间打开防火墙

firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="192.168.56.101" accept"
firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="192.168.56.100" accept"
firewall-cmd --reload
firewall-cmd --list-all

4.2.主要 1 服务器 (56.100) 4.2.1 -.yml

mkdir -p /home/scsdm/services/mysql && cd /home/scsdm/services/mysql
cat > docker-compose.yml << EOF
version: '3'
services:
  node1:
    image: mysql:5.7.23
    hostname: mysql-master1
    container_name: mysql-master1
    restart: always
    ports:
      - "3306:3306"
    network_mode: host
    environment:
      - MYSQL_ROOT_PASSWORD=hancloud1234!
      - TZ=Asia/Shanghai
    volumes:
      - $PWD/mysql_master1/slowSql:/data/mysql
      - $PWD/mysql_master1/data:/var/lib/mysql
      - $PWD/mysql_master1/logs:/var/log/mysql
      - $PWD/mysql_master1/conf/my.cnf:/etc/mysql/my.cnf
    privileged: true
    command: ['mysqld','--character-set-server=utf8mb4', '--collation-server=utf8mb4_unicode_ci','--default-time-zone=+08:00']
    entrypoint: bash -c "chown -R mysql:mysql /var/log/mysql && exec /entrypoint.sh mysqld"
EOF

4.2.2 配置

mkdir -p /home/scsdm/services/mysql/mysql_master1/conf
cd /home/scsdm/services/mysql/mysql_master1/conf
cat > my.cnf << EOF
!includedir /etc/mysql/conf.d/ 
!includedir /etc/mysql/mysql.conf.d/
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
default-time-zone=+08:00
max_connections=1000
# 开启binlog
log-bin=mysql-bin
log-bin-index=mysql-bin.index
# 服务器唯一id，默认值1
server-id=1
max_binlog_size=1G
max_binlog_cache_size=1G
# 设置日志格式，默认值ROW
binlog_format=mixed
expire_logs_days=7
# 开启慢查询
slow_query_log=1
long_query_time=2
slow_query_log_file=/data/mysql/slow.log
# 错误日志
log_error=/var/log/mysql/error.log
# 设置需要复制的数据库，默认复制全部数据库
#binlog-do-db=mcp_manager
# 设置不需要复制的数据库
binlog-ignore-db=mysql
binlog-ignore-db=infomation_schema
EOF

4.2.3 启动主1服务器

cd /home/scsdm/services/mysql
# my.cnf 权限不能是777，会被忽略
chmod 644 /home/scsdm/services/mysql/mysql_master1/conf/my.cnf
docker-compose up -d

4.2.4 进入容器

#进入容器：env LANG=C.UTF-8 避免容器中显示中文乱码
docker exec -it mysql-master1 env LANG=C.UTF-8 /bin/bash
#进入容器内的mysql命令行
mysql -uroot -p

4.2.5 创建备份用户

-- 创建slave用户
CREATE USER 'slave'@'%';
-- 设置密码
ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'hancloud@1234';
-- 授予复制权限
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
-- 刷新权限
FLUSH PRIVILEGES;

查看创建的用户信息

select host,user from mysql.user;

4.2.6 查询主机状态

执行此步骤后，请勿再次操作主服务器MYSQL，以免主服务器状态值发生变化。

SHOW MASTER STATUS;

注意 File 和 的值。

执行此步骤后，请勿再次操作主服务器MYSQL，以免主服务器状态值发生变化。

4.3.主 2 服务器 (56.101) 4.3.1 -.yml

mkdir -p /home/scsdm/services/mysql && cd /home/scsdm/services/mysql
cat > docker-compose.yml << EOF
version: '3'
services:
  node1:
    image: mysql:5.7.23
    hostname: mysql-master2
    container_name: mysql-master2
    restart: always
    ports:
      - "3306:3306"
    network_mode: host
    environment:
      - MYSQL_ROOT_PASSWORD=hancloud1234!
      - TZ=Asia/Shanghai
    volumes:
      - $PWD/mysql_master2/slowSql:/data/mysql
      - $PWD/mysql_master2/data:/var/lib/mysql
      - $PWD/mysql_master2/logs:/var/log/mysql
      - $PWD/mysql_master2/conf/my.cnf:/etc/mysql/my.cnf
    privileged: true
    command: ['mysqld','--character-set-server=utf8mb4', '--collation-server=utf8mb4_unicode_ci','--default-time-zone=+08:00']
    entrypoint: bash -c "chown -R mysql:mysql /var/log/mysql && exec /entrypoint.sh mysqld"
EOF

4.3.2 配置

mkdir -p /home/scsdm/services/mysql/mysql_master2/conf
cd /home/scsdm/services/mysql/mysql_master2/conf
cat > my.cnf << EOF
!includedir /etc/mysql/conf.d/ 
!includedir /etc/mysql/mysql.conf.d/
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
default-time-zone=+08:00
max_connections=1000
# 开启binlog
log-bin=mysql-bin
log-bin-index=mysql-bin.index
# 服务器唯一id，默认值1
server-id=2
max_binlog_size=1G
max_binlog_cache_size=1G
# 设置日志格式，默认值ROW
binlog_format=mixed
expire_logs_days=7
# 开启慢查询
slow_query_log=1
long_query_time=2
slow_query_log_file=/data/mysql/slow.log
# 错误日志
log_error=/var/log/mysql/error.log
# 设置需要复制的数据库，默认复制全部数据库
#binlog-do-db=mcp_manager
# 设置不需要复制的数据库
binlog-ignore-db=mysql
binlog-ignore-db=infomation_schema
EOF

4.3.3 启动主2服务器

cd /home/scsdm/services/mysql
# my.cnf 权限不能是777，会被忽略
chmod 644 /home/scsdm/services/mysql/mysql_master2/conf/my.cnf
docker-compose up -d

4.3.4 进入容器

#进入容器：env LANG=C.UTF-8 避免容器中显示中文乱码
docker exec -it mysql-master2 env LANG=C.UTF-8 /bin/bash
#进入容器内的mysql命令行
mysql -uroot -p

4.3.5 创建备份用户

-- 创建slave用户
CREATE USER 'slave'@'%';
-- 设置密码
ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'hancloud@1234';
-- 授予复制权限
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
-- 刷新权限
FLUSH PRIVILEGES;

4.3.6 查询主机状态

执行此步骤后，请勿再次操作主服务器MYSQL，以免主服务器状态值发生变化。

SHOW MASTER STATUS;

注意 File 和 的值。

4.3.7 配置 2

# 配置主Mysql地址
CHANGE MASTER TO MASTER_HOST='192.168.56.100', 
# 配置主Mssql创建的用于备份的用户名和密码和端口
MASTER_USER='slave',MASTER_PASSWORD='hancloud@1234', MASTER_PORT=3306,
# 配置主Mssql的binlog日志名称和当前所在位置（上面记录的位置）
MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=959; 

4.3.8 启动 2复制功能

START SLAVE;
-- 查看状态（不需要分号）
SHOW SLAVE STATUS\G

4.3.9 配置 1

# 配置主Mysql地址
CHANGE MASTER TO MASTER_HOST='192.168.56.101', 
# 配置主Mssql创建的用于备份的用户名和密码和端口
MASTER_USER='slave',MASTER_PASSWORD='hancloud@1234', MASTER_PORT=3306,
# 配置主Mssql的binlog日志名称和当前所在位置（上面记录的位置）
MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=959; 

4.3.10 启动主1复制功能

START SLAVE;
-- 查看状态（不需要分号）
SHOW SLAVE STATUS\G

4.3.11 关闭主-主复制

-- 在从机上执行。功能说明：停止I/O 线程和SQL线程的操作。
stop slave; 
-- 在从机上执行。功能说明：用于删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件。
reset slave;
-- 在主机上执行。功能说明：删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件。
-- 用于第一次进行搭建主从库时，进行主库binlog初始化工作；
reset master;

4.4 部署 4.4.1 以下内容分别在两台机器上执行。

mkdir -p /home/scsdm/services/keepalived/checksh && cd /home/scsdm/services/keepalived
cat > /home/scsdm/services/keepalived/checksh/check-mysql.sh << EOF
#!/bin/bash
count=`netstat -apn | grep 3306 | wc -l`
if [ $count -gt 0 ]; then
    exit 0
else
   sleep 1 
   count=`netstat -apn | grep 3306 | wc -l`
   if [ $count -eq 0 ]; then
   pkill keepalived
   fi 
   exit 1
fi
EOF
chmod -R +775 /home/scsdm/services/keepalived/checksh
# 虚拟ip
IP="192.168.56.120"
# 需要的网卡
ETH="enp0s3"
cat > /home/scsdm/services/keepalived/keepalived.conf << EOF
global_defs {
   router_id LVS_2
   script_user root                    # 脚本执行用户
   #enable_script_security              # 不要开启脚本安全权限
}
vrrp_script checkMsql
{
    script "/checksh/check-mysql.sh"  # 这个地址写的事容器内脚本路径
    interval 2
    weight -30
}
vrrp_instance VI_1 {
    state BACKUP
    # 注意网卡
    interface ETH
    virtual_router_id 51
    priority 100
    advert_int 1
    nopreempt
    
    virtual_ipaddress {
      # 需要修改成对应的虚拟ip
        IP
    }
    authentication {
        auth_type PASS
        auth_pass password
    }
    track_script {
        checkMsql
    }
}
EOF
cat > /home/scsdm/services/keepalived/docker-compose.yml << EOF
version: '3'
services:
  keepalived:
    image: osixia/keepalived:stable
    hostname: keepalived
    volumes:
      - "${PWD}/checksh:/checksh"
      - "${PWD}/keepalived.conf:/container/service/keepalived/assets/keepalived.conf"
      - "/etc/localtime:/etc/localtime:ro"
      - "/proc:/host_proc"
    #restart: always
    privileged: true
    network_mode: host
    command: --loglevel debug --copy-service
EOF
docker-compose up -d

您可以使用该虚拟IP来访问

5. 测试同步 5.1.在中创建数据

CREATE DATABASE mcp_manager;
USE mcp_manager;
CREATE TABLE t_user (
 id BIGINT AUTO_INCREMENT,
 uname VARCHAR(30),
 PRIMARY KEY (id)
);
INSERT INTO t_user(uname) VALUES('xiaohong');
INSERT INTO t_user(uname) VALUES('xiaoming');

5.2.查看数据库

5.3.将新数据添加到主页

INSERT INTO t_user(uname) VALUES('xiaohong2');
INSERT INTO t_user(uname) VALUES('xiaoming2');

5.4.查看数据库

6.mysql双主架构的优缺点 6.1 双主架构的实现原理

当从库IO线程接收到主库传过来的二进制日志（）并保存为从库的中继日志（relay log）时，则从库SQL线程将中继日志（relay log）的事件重做为On从库，实现主从数据同步。

如果SQL线程发现该事件与当前从库相同，则会丢弃该事件。因此，如果两台MySQL服务器互为主从，同一个事件就不会重复执行。

6.2 双主架构的设计目标

MySQL复制集群中，采用双主或多主架构来解决主从架构的单点故障，减少主从切换故障处理时间，增加MySQL集群的高可用性。

6.3 双主架构实施方案

1、主备模式。两台MySQL服务器互为主从。其中一个作为主节点向外界提供服务，另一个作为备份节点（）。当提供服务的主节点发生故障时，服务请求会快速切换到备用节点，故障恢复后原主节点转换为备用节点（）。

2、主主模式，两台MySQL互为主从，两台MySQL均作为主节点对外提供服务。当其中一台MySQL发生故障时，针对故障节点的请求将快速切换到另一台MySQL。最初定向到非故障节点的请求不受影响。

在-模式下，两个库都提供读写服务。如果应用程序通过两个主库操作相同的数据，就会发生冲突，导致数据覆盖（使用语句模式复制）或复制异常（使用行模式复制）。因此，需要对读写服务进行控制：

1.基于自主主键控制，通过设置自增属性t和ment来控制每个主节点产生不同的自增值，并根据不同的自增值访问不同的主节点。

2、基于库级或表级控制，例如应用程序APP1访问节点node1上的DB1库，应用程序APP2访问节点node2上的DB2库，则两个之间不会操作同一张表的数据节点，所以不会有交易。冲突。

为了确保应用程序使用相同的数据库连接配置而不受故障转移的影响，常见的解决方案包括：

1、VIP，通过vrrpd将VIP动态绑定到新的主节点或者动态绑定

2.域名，通过切换域名将域名指向新的主节点

3、代理通过更新代理中存储的路由信息​​来指向新的主节点。

6.4 双主架构的优点

1、-模式可以将读写请求分配到两个主节点，有效提高服务器利用率。

2、主节点故障后，可以快速进行主从切换。

3、当故障节点恢复后，故障节点可以通过复制进行数据恢复（应用其他节点数据）和数据同步（将不同步的数据传输到其他节点）。

6.5 双主架构的缺点

1、当主节点上的MySQL实例发生故障时，部分数据（）可能无法同步到其他主节点，导致数据丢失（直到故障节点恢复）。

2、主主模式下，很容易因为数据访问控制不当而导致数据冲突。

3、为了提高系统的高可用性，将双主架构扩展为双主多从结构。还有主节点故障后多个从库选主并恢复复制的问题。

6.6 MMM架构

在双主架构中，MySQL本身不具备自动故障转移功能。开源项目MySQL-MMM（-for MySQL）可用于监控MySQL主主复制并实现自动故障转移。

在半同步复制的双主架构下，可以有效保证两个主数据库的同步，然后配合MMM工具实现自动故障转移，保证集群的高可用性。