2023面试高手

这个屌丝很懒，什么也没留下！

热门标签

云上 docker 安装 MySQL8+keepalived双主热备高可用_云服务器mysql主从热备

作者：2023面试高手 | 2024-05-18 07:50:38

踩

云服务器mysql主从热备

环境准备

两台云服务器：IP1：10.0.1.143，IP2: 10.0.1.237

申请虚拟IP：10.0.1.204

流程说明

VIP地址为10.0.1.204，如果主机10.0.1.143挂了，会自动切换到备机10.0.1.237上，由于是双主，数据可以进行同步恢复

部署流程大致为

在10.0.1.143服务器上部署docker、mysql一主一丛
在10.0.1.237服务器上部署docker、mysql一主一丛
10.0.1.143的Master1数据库和10.0.1.237的数据库互为主从
在两台服务器上安装keeplived形成高可用

配置第一台一主一丛

创建主配置文件和目录


mkdir -p /data/mysql/master/config
mkdir -p /data/mysql/master/data
mkdir -p /data/mysql/master/logs
touch /data/mysql/master/config/my.cnf
chmod +x /data/mysql/master/config/my.cnf

创建从配置文件和目录


mkdir -p /data/mysql/slave/config
mkdir -p /data/mysql/slave/data
mkdir -p /data/mysql/slave/logs
touch /data/mysql/slave/config/my.cnf
chmod +x /data/mysql/slave/config/my.cnf

主my.cnf配置文件


[mysqld]
server-id=101
 
max_connections=500
max_allowed_packet=32M
wait_timeout=600
 
slow_query_log=ON
slow_query_log_file=/var/lib/mysql/slow.log
long_query_time=1
log_queries_not_using_indexes=ON
log_throttle_queries_not_using_indexes=10
 
max_execution_time=600
log-bin=mysql-bin
binlog_format=ROW
max_binlog_size=500M
max_binlog_cache_size=1G
max_binlog_stmt_cache_size=1G
 
innodb_flush_method=O_DIRECT
innodb_log_buffer_size=1024M
innodb_buffer_pool_size=512M
innodb_print_all_deadlocks=1
innodb_rollback_on_timeout=ON
innodb_deadlock_detect=ON
 
max_relay_log_size=500M
relay_log_purge=ON
relay_log_recovery=ON
 
log_replica_updates=ON
 
host_cache_size=0
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql
 
pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/run/mysqld/mysqld.sock

从 my.cnf配置文件


[mysqld]
server-id=102
 
max_connections=500
max_allowed_packet=32M
wait_timeout=600
 
slow_query_log=ON
slow_query_log_file=/var/lib/mysql/slow.log
long_query_time=1
log_queries_not_using_indexes=ON
log_throttle_queries_not_using_indexes=10
 
max_execution_time=600
log-bin=mysql-bin
binlog_format=ROW
max_binlog_size=500M
max_binlog_cache_size=1G
max_binlog_stmt_cache_size=1G
 
innodb_flush_method=O_DIRECT
innodb_log_buffer_size=1024M
innodb_buffer_pool_size=512M
innodb_print_all_deadlocks=1
innodb_rollback_on_timeout=ON
innodb_deadlock_detect=ON
 
max_relay_log_size=500M
relay_log_purge=ON
relay_log_recovery=ON
 
log_replica_updates=ON
 
host_cache_size=0
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql
 
pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/run/mysqld/mysqld.sock

使用docker-compose.yml安装Mysql8，执行如下命令


version: '3'
services: 
  mysql_master:
    image: mysql:8.0.30
    restart: always
    container_name: mysql_master
    volumes:
      - /data/mysql/master/data:/var/lib/mysql
      - /data/mysql/master/config/my.cnf:/etc/mysql/conf.d/my.cnf
      - /data/mysql/master/logs:/var/log/mysql
      #      数据库还原目录 可将需要还原的sql文件放在这里
      - /data/mysql/init/mysql:/docker-entrypoint-initdb.d
    environment:
      - "MYSQL_ROOT_PASSWORD=yx@123456"
      - "MYSQL_DATABASE=test"
      - "TZ=Asia/Shanghai"
    ports:
      - 3306:3306
    privileged: true
  mysql_slave:
    image: mysql:8.0.30
    restart: always
    container_name: mysql_slave
    volumes:
      - /data/mysql/slave/data:/var/lib/mysql
      - /data/mysql/slave/config/my.cnf:/etc/mysql/conf.d/my.cnf
      - /data/mysql/slave/logs:/var/log/mysql
      #      数据库还原目录 可将需要还原的sql文件放在这里
      - /data/mysql/init/mysql:/docker-entrypoint-initdb.d
    environment:
      - "MYSQL_ROOT_PASSWORD=yx@123456"
      - "MYSQL_DATABASE=test"
      - "TZ=Asia/Shanghai"
    ports:
      - 4306:3306
    privileged: true

在主机上安装mysql客户端软件


wget https://cdn.mysql.com/archives/mysql-8.0/mysql-community-client-8.0.32-1.el7.x86_64.rpm
 
wget https://cdn.mysql.com/archives/mysql-8.0/mysql-community-client-plugins-8.0.32-1.el7.x86_64.rpm
 
wget https://cdn.mysql.com/archives/mysql-8.0/mysql-community-libs-8.0.32-1.el7.x86_64.rpm
 
wget https://cdn.mysql.com/archives/mysql-8.0/mysql-community-common-8.0.32-1.el7.x86_64.rpm

检查 rpm -qa | grep mariadb 并卸载


[root@wy9jygsgl0t9rcw5-0002 client]#  rpm -qa | grep mariadb
mariadb-libs-5.5.68-1.el7.x86_64
[root@wy9jygsgl0t9rcw5-0002 client]# rpm -e  --nodeps mariadb-libs-5.5.68-1.el7.x86_64


rpm -ivh mysql-community-common-8.0.32-1.el7.x86_64.rpm
 
rpm -ivh mysql-community-client-plugins-8.0.32-1.el7.x86_64.rpm
 
rpm -ivh mysql-community-libs-8.0.32-1.el7.x86_64.rpm
 
rpm -ivh mysql-community-client-8.0.32-1.el7.x86_64.rpm

登陆主数据库

mysql -h10.0.1.143 -P3306 -uroot -p

创建账号


CREATE USER r_yxwg identified with mysql_native_password by 'r_yx@123456';
 
GRANT REPLICATION SLAVE ON *.* to 'r_yxwg'@'%';
 
FLUSH PRIVILEGES;

查询主库文件和标识

SHOW MASTER STATUS;

登陆从数据库

mysql -h10.0.1.143 -P4306 -uroot -p

文件和标识为主库的


change master to master_host='10.0.1.143',master_port=3306,master_user='r_yxwg',master_password='r_yx@123456',master_log_file='mysql-bin.000003',master_log_pos=823;
start slave;
 
 
show slave status\G;

配置第二台一主一丛

创建流程第一台一样（同上）

主主配置

登陆A服务器mysql -h10.0.1.143 -P3306 -uroot -p


CREATE USER repl identified with mysql_native_password by 'repl@123456';
GRANT REPLICATION SLAVE ON *.* to 'repl'@'%';
flush privileges;
 
-- 添加写锁，防止设置过程中，写入数据，导致binlog和pos发生变化
flush tables with read lock;
-- 查看当前MySQL的master_log_file和master_log_position的值
 
show master status ;
 
unlock tables ;

登陆B服务器执行如下操作


stop slave ;
change master to master_host='10.0.1.237',master_port=3306,master_user='repl',master_password='repl@123456',master_log_file='mysql-bin.000003',master_log_pos=1485;
start slave ;
show slave status\G;

登陆B服务器mysql -h10.0.1.237 -P3306 -uroot -p


CREATE USER repl identified with mysql_native_password by 'repl@123456';
GRANT REPLICATION SLAVE ON *.* to 'repl'@'%';
flush privileges;
 
-- 添加写锁，防止设置过程中，写入数据，导致binlog和pos发生变化
flush tables with read lock;
-- 查看当前MySQL的master_log_file和master_log_position的值
show master status ;
unlock tables ;

登陆A服务器执行如下操作


stop slave ;
change master to master_host='10.0.1.143',master_port=3306,master_user='repl',master_password='repl_yx21SA@WSX',master_log_file='mysql-bin.000004',master_log_pos=819;
start slave ;
show slave status\G;

安装keepalived

两台服务器安装keepalived

yum -y install keepalived

修改主keepalived配置文件（/etc/keepalived/keepalived.conf）


global_defs {
    router_id mysql_master
    script_user root
    enable_script_security
}
 
vrrp_script check_mysql {
   script "/etc/keepalived/chk_mysql.sh"
   interval 5
   weight -10
   fall 2
   rise 1
}
 
vrrp_instance mysql {
    state MASTER
    interface enp0s8
    virtual_router_id 51
    priority 150
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        10.0.1.204
    }
 
    track_script {
        chk_mysql
    }
}

上面修改VIP、网络名称enp0s8、检查脚本chk_mysql.sh


#!/bin/bash
counter=$(netstat -na | grep "LISTEN" | grep "3306" | wc -l)
if [ ${counter} -eq 0 ]; then
    killall keepalived
fi

修改备keepalived配置文件（/etc/keepalived/keepalived.conf）


global_defs {
    router_id mysql_master
    script_user root
    enable_script_security
}
 
vrrp_script check_mysql {
   script "/etc/keepalived/chk_mysql.sh"
   interval 5
   weight -10
   fall 2
   rise 1
}
 
vrrp_instance mysql {
    state BACKUP
    interface enp0s8
    virtual_router_id 51
    priority 130
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        10.0.1.204
    }
 
    track_script {
        chk_mysql
    }
}

上面修改VIP、网络名称enp0s8、检查脚本chk_mysql.sh


#!/bin/bash
counter=$(netstat -na | grep "LISTEN" | grep "3306" | wc -l)
if [ ${counter} -eq 0 ]; then
    killall keepalived
fi

两边启动并加入开机自启动


[root@mysql1 ~]# systemctl start keepalived
[root@mysql1 ~]# systemctl enable keepalived

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/2023面试高手/article/detail/587383