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 <<eof >/etc/my.cnf

[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

测试，与开机启动不在次次试验中，可自行测试