当前位置： 代码迷 >> 综合 >> 【Mysql】Mysql + Keepalived 实现主从高可用
  详细解决方案

【Mysql】Mysql + Keepalived 实现主从高可用

热度：44   发布时间：2023-10-28 21:59:44.0

Mysql + Keepalived 实现主从高可用

系统环境及架构

服务器ip 系统版本 Mysql版本 部署内容
192.168.70.10 CentOS7 5.7 Mysql主1
192.168.70.20 CentOS7 5.7 Mysql主2
192.168.70.30 CentOS7 5.7 Mysql从

Keepalived VIP：192.168.70.66

Mysql

Mysql安装（master和slave上都进行安装）

# yum install epel*  -y && yum clean all && yum makecache 
# rpm -Uvh http://repo.mysql.com/mysql57-community-release-el7.rpm
# yum clean all && yum makecache
# yum install gcc gcc-c++ openssl-devel mysql mysql-server mysql-devel -y

修改Mysql配置文件

Mysql主1（192.168.70.10）

[mysqld]
server-id=1                                         #全局唯一，每台都不能一样
log-bin=mysql-bin                                   #开启二进制日志记录，日志文件命名格式为mysql-bin
relay-log=mysql-relay-bin                           #指定从库生成日志格式
replicate-wild-ignore-table=mysql.%                 #指定哪些库在同步时忽略
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
datadir=/data/mysql
socket=/data/mysql/mysql.sock
user=mysql
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character-set-server=utf8[mysql]
socket=/data/mysql/mysql.sock
default-character-set=utf8[client]                                            #增加这个字段，解决“提示默认路径/var/lib/mysql/mysql.sock找不到”问题
socket=/data/mysql/mysql.sock
default-character-set=utf8
user=root
password=Mysql123!@#

Mysql主2（192.168.70.20）

[mysqld]
server-id=2
log-bin=mysql-bin
relay-log=mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
datadir=/data/mysql
socket=/data/mysql/mysql.sock
user=mysql
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character-set-server=utf8[mysql]
socket=/data/mysql/mysql.sock
default-character-set=utf8[client]
socket=/data/mysql/mysql.sock
default-character-set=utf8
user=root
password=Mysql123!@#

Mysql从（192.168.70.30）

[mysqld]
server-id=3
log-bin=mysql-bin
relay-log=mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
datadir=/data/mysql
socket=/data/mysql/mysql.sock
user=mysql
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character-set-server=utf8[mysql]
socket=/data/mysql/mysql.sock
default-character-set=utf8[client]
socket=/data/mysql/mysql.sock
default-character-set=utf8
user=root
password=Mysql123!@#

注意：不要在主库上使用 binlog-do-db 或　binlog-ignore-db 选项，也不要在从库上使用 replicate-do-db 或　replicate-ignore-db 选项。
因为这有可能产生跨库更新失败的问题．推荐从库上使用　replicate_wild_do_table 和　replicate_wild_ignore_table 这两个选项来解决复制过滤问题。

创建数据库文件存放路径

# mkdir /data/mysql -p
# chown -R mysql:mysql /data/mysql

初始化数据库

# mysqld --initialize --user=mysql --datadir=/data/mysql
# systemctl restart mysqld
# systemctl enable mysqld

关闭selinux

# vim /etc/selinux/config

或

# setenforce 0

防火墙开放3306端口

# firewall-cmd --add-port=3306/tcp --permanent
# firewall-cmd --reload
# firewall-cmd --list-all

修改Mysql管理员root账户密码

5.7之前的版本默认是没有密码的，只需在服务器上直接登录，甚至用户名都不用指定，但除本机外网络是登不上的。
5.7版本的话安装完后会生成一个随机密码，密码记录在log日志里，日志文件在/var/log/mysqld.log，用vim或者其他编辑器打开后搜索root@localhost，后面跟着的无规则字符串就是密码。
使用该密码进入Mysql之后，修改root用户密码。

# cat /var/log/mysqld.log | grep pass
# mysql -u root -p
> set password for root@localhost = password('Mysql123!@#');

配置Mysql主从

Mysql主1（192.168.70.10）：将主1设为主2的从库

# mysql -u root -p
> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'Mysql123!@#' WITH GRANT OPTION;
> GRANT REPLICATION SLAVE ON *.* to 'repl'@'%' identified by 'Mysql123!@#';
> FLUSH PRIVILEGES;-------> show master status;-------> change master to master_host="192.168.70.20",master_port=3306,master_user="repl",master_password="Mysql123!@#",master_log_file="File值",master_log_pos=Position值;
> start slave;
> show slave status\G
> show processlist\G

Mysql主2（192.168.70.20）：将主2设为主1的从库

# mysql -u root -p
> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'Mysql123!@#' WITH GRANT OPTION;
> GRANT REPLICATION SLAVE ON *.* to 'repl'@'%' identified by 'Mysql123!@#';
> FLUSH PRIVILEGES;-------> show master status;-------> change master to master_host="192.168.70.10",master_port=3306,master_user="repl",master_password="Mysql123!@#",master_log_file="File值",master_log_pos=Position值;
> start slave;
> show slave status\G
> show processlist\G

Mysql从（192.168.70.30）

# mysql -u root -p
> change master to master_host="192.168.70.66",master_port=3306,master_user="repl",master_password="Mysql123!@#",master_log_file="File值",master_log_pos=Position值;
> start slave;
> show slave status\G

Keepalived

安装Keepalived [Mysql主1（192.168.70.10），Mysql主2（192.168.70.20）]

# yum install keepalived -y
# touch /etc/keepalived/mysql_check.sh
# chmod +x /etc/keepalived/mysql_check.sh

mysql_check.sh脚本内容 [Mysql主1（192.168.70.10），Mysql主2（192.168.70.20）]

#!/bin/bash
slave_is=( $(mysql -uroot -pMysql123\!\@\# -e "show slave status\G" | grep "Slave_.*_Running" | awk '{print $2}') )
if [ "${slave_is[0]}" = "Yes" -a "${slave_is[1]}" = "Yes" ]thenexit 0
elseexit 1
fi

修改Keepalived配置文件

Mysql主1（192.168.70.10）

# mv /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak
# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived
global_defs {notification_email {**@qq.com}notification_email_from **@163.comsmtp_server smtp.163.comsmtp_connect_timeout 30router_id mysql_master
}
vrrp_script chk_mysql {script "/etc/keepalived/mysql_check.sh"interval 2                                          #执行脚本间隔，每2s检测一次weight 2
}
vrrp_instance mysql-ha {state BACKUPinterface ens33                                     #通过 ip a 命令查看网卡信息virtual_router_id 66                                #路由器标识，两台机器必须是一致的priority 100                                        #定义优先级，数字越大优先级越高advert_int 1nopreemptauthentication {auth_type PASSauth_pass centos}track_script {chk_mysql}virtual_ipaddress {192.168.70.66                                       #VIP}
}

Mysql主2（192.168.70.20）

# mv /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak
# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalivedglobal_defs {notification_email {**@qq.com}notification_email_from **@163.comsmtp_server smtp.163.comsmtp_connect_timeout 30router_id mysql_backup
}
vrrp_script chk_mysql {script "/etc/keepalived/mysql_check.sh"interval 2weight 2
}
vrrp_instance mysql-ha {state BACKUPinterface ens33virtual_router_id 66priority 99advert_int 1nopreemptauthentication {auth_type PASSauth_pass centos}track_script {chk_mysql}virtual_ipaddress {192.168.70.66}
}

重启Keepalived服务 [Mysql主1（192.168.70.10），Mysql主2（192.168.70.20）]

# systemctl restart keepalived
# systemctl enable keepalived
# systemctl status keepalived
  相关解决方案

代码迷 - 您身边的软件异常，代码异常，编程异常助手（发现，解决，分享）
Copyright © 2009-2013 DAIMAMI.COM. All rights reserved.