Mysql数据库高可用
Mysql数据库高可用
Mysql(5.7.19)+keepalive(1.3.0) +RHEL6.5
Mysql下载地址
https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz
Keepalived地址
http://www.keepalived.org/software/keepalived-1.3.0.tar.gz
规划如下
ip | 端口 | 备注 | |
Mysql1 | 172.25.254.120 | 3306 | |
Mysql2 | 172.25.254.130 | 3306 | |
keepalived | 172.25.254.111 |
一:首先搭建主主数据库
拷贝压缩包到系统/mnt下
cd /mnt/
tar xvf mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz
cp -a mysql-5.7.19-linux-glibc2.12-x86_64 /usr/local/mysql
useradd mysql
cd /usr/local/mysql/
cd bin/
./mysqld --initialize
./mysql_install_db --user=mysql --basedir=/var/lib/mysql --datadir=/var/lib/mysql/data
chown -R mysql /var/lib/mysql/data/
./mysqld_safe --user=mysql
cd ..
cp support-files/mysql.server /etc/init.d/mysqld
rm -rf /etc/my.cnf
cat <
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
eof
ln -s /usr/local/mysql/bin/mysql /bin/mysql
cd bin/
service mysqld start
sed -i '5askip-grant-tables' /etc/my.cnf
sed -i '$a[mysql]' /etc/my.cnf
sed -i '$asocket=/var/lib/mysql/mysql.sock' /etc/my.cnf
service mysqld restart
mysql -e "update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';"
sed -i '/skip-grant-tables/d' /etc/my.cnf
service mysqld restart
mysql -uroot -p123456
SQL>set password = password('123456');
sed -i '/\[mysqld\]$/alog-bin=/var/lib/mysql/log-bin' /etc/my.cnf
sed -i '/\[mysqld\]$/aserver_id=130' /etc/my.cnf #两个id不一样即可
service mysqld restart
至此两个数据库搭建完成
二：主主搭建
mysql -uroot -p123456#登陆数据库
grant replication slave on *.* to slaveuser@"172.25.254.120" identified by "123456";#130上配置
grant replication slave on *.* to slaveuser@"172.25.254.130" identified by "123456";#120上配置
登陆测试是否成功
mysql> show master status\G;#分别查看两个节点
****************** 1. row ***************************
File: log-bin.000001
Position: 154
change master to master_host="172.25.254.120",master_user="slaveuser",master_password="123456",master_log_file="log-bin.000001",master_log_pos=154;#130上操作
change master to master_host="172.25.254.130",master_user="slaveuser",master_password="123456",master_log_file="log-bin.000001",master_log_pos=154;#120上操作
start slave;#两节点分别开启
show slave status\G;
Slave_IO_Running: YES
Slave_SQL_Running: YES
可以自己测试
三：keepalived搭建
cd /mnt/
tar xvf keepalived-1.3.0.tar.gz
yum -y install gcc gcc-c++ openssl-devel
cd keepalived-1.3.0
./configure
make && make install
mkdir /etc/keepalived
cd /usr/local/etc/
cp -a keepalived/keepalived.conf /etc/keepalived/
cp sysconfig/keepalived /etc/sysconfig/
cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id
}
vrrp_sync_group VGM{
group{
HA
}
}
vrrp_script check_mysql {
script "/root/check_mysql.sh"#脚本文件
interval 5
}
vrrp_instance HA {
state MASTER#从BACKUP
interface eth0
virtual_router_id 51
priority 100#从99
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
172.25.254.111/24 dev eth0
}
track_script {
check_mysql
}
}
cat /root/check_mysql.sh #脚本文件
b=`ps aux | grep mysql | grep sqld | wc -l`
if [ $b == 0 ]
then
service mysqld restart
c=`ps aux | grep mysql | grep sqld | wc -l`
if [ $c == 0 ]
then
kill -9 `ps aux | grep keep | grep lived | awk '{print $2}'`
fi
fi
a=`ps aux | grep keep | grep lived |wc -l`
if [ $a == 0 ]
then
sleep 300
reboot
fi
测试，与开机启动不在次次试验中，可自行测试
