1):编辑mysql配置文件/etc/my.cnf,选定主主同步的数据库
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
log-bin=binlog
binlog_format=mixed
server_id=1//主备两台mysql的server_id须不同
binlog-do-db=render
replicate-do-db=render
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
2):操作主数据库," show master status "后记录下其 file 和p os ition
grant replication slave on *.* to 'slave'@'192.168.1.90' identified by '123456';
flush privileges;
show master status;
3):操作从数据库,如果show slave status正常,则主从同步机制建立。同时也在从数据库上开启 master ,以便主数据库也能从从数据库同步数据
change master to master_host='192.168.1.33',master_user='slave',master_password='123456',master_port=3306,master_log_file='binlog.000001',master_log_pos=207017;
start slave;
show slave status;
grant replication slave on *.* to 'slave'@'192.168.1.33' identified by '123456';
flush privileges;
show master status;
4):操作主数据库,如果show slave status正常,则主主同步机制建立完成。
master-mysql:
change master to master_host='192.168.1.90',master_user='slave',master_password='123456',master_port=3306,master_log_file='mysql-bin.000014',master_log_pos=2461;
start slave;
show slave status;
5):数据库主主机制建立后,还需要在两台机器上都安装keepalived,以便向外提供一个VIP,当某台机器挂的时候,不影响数据库的使用。
a)需要安装ipvsadm和keepalived:yum install ipvsadm;yum install keepalived;
b)编辑/etc/keepalived/keepalived.conf。
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.1.33
smtp_connect_timeout 30
router_id LVS_DEVEL
}
vrrp_script chk_mysqld{
script "/etc/keepalived/mysql.sh"
interval 3
weight -20
}
vrrp_instance VI_1 {
state MASTER//主备不同
interface eth1
virtual_router_id 51
priority 100//主备不同,备的比主的要小
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.1.13
}
}
virtual_server 192.168.1.13 3306 {
delay_loop 2
lb_algo rr
lb_kind DR
persistence_timeout 60
protocol TCP
real_server 192.168.1.90 3306{//主备两台机器的实IP不同
weight 3
notify_down /etc/keepalived/mysql.sh
TCP_CHECK{
connect_timeout 3
nb_get_retry 3
connect_port 3306
}
}
}
c)编辑/etc/keepalived/mysql.sh。keepalived会定时启动 mysql.s h脚本检查数据库状态,当数据库异常时,杀死当前机器的keepalived,VIP将被另一台数据库机器绑定,从而实现数据库的高可用,平滑切换。
#!/bin/bash
MYSQL_OK=1
function check_mysql_helth (){
mysql -e "show status;" &>/dev/null
if [ $? = 0 ] ;then
MYSQL_OK=1
else
MYSQL_OK=0
fi
return $MYSQL_OK
}
while [ $CHECK_TIME -ne 0 ]
do
let "CHECK_TIME -= 1"
check_mysql_helth
if [ $MYSQL_OK = 1 ] ; then
echo "mysql ok\n"
CHECK_TIME=0
exit 0
fi
if [ $MYSQL_OK -eq 0 ] && [ $CHECK_TIME -eq 0 ]
then
pkill keepalived
exit 1
fi
sleep 1
done
d)启动keepalived: /usr/sbin/keepalived -D 。使用VIP(这里是 192.168.1.13 )连接数据库即可。