mysql主主同步+keepalived
1.安装数据库，配置主主，然后在keepalived数据库安装完毕
yum install mysql-server yum install mysql-devel
========================================================
# rpm -qa|grep mysql
mysql-devel-5.1.73-3.el6_5.x86_64
mysql-libs-5.1.73-3.el6_5.x86_64
mysql-5.1.73-3.el6_5.x86_64
mysql-server-5.1.73-3.el6_5.x86_64
mysqladmin -uroot password yaoshao123
主主同步
http://www.cnblogs.com/xiaoit/p/3977843.html
http://www.linuxidc.com/Linux/2013-05/83784.htm
（1）同步帐号
yao yaoshaoyun
grant replication slave on *.* to 'yao'@'%'> create database zabbix;
(2)vi /etc/my.conf
主
port = 3306
log_bin=/var/lib/mysql/mysql-binlog
server-id=1
binlog_do_db=zabbix
从
server-id=2
master-host=192.168.31.154
master-user=yao
master-password=yaoshao123
master-port=3306
master-connect-retry=5
replicate-do-db=zabbix
（3）主主新增添加以下
####server-id=1 原本为主########
#
master-host=192.168.31.154
master-user=yao
master-password=yaoshao123
master-port=3306
master-connect-retry=5
replicate-do-db = zabbix
log-slave-updates
sync_binlog=1
auto_increment_offset=1
auto_increment_increment=2
####server-id=2 原本为备########
#
log_bin=/var/lib/mysql/mysql-binlog
binlog_do_db=zabbix
log-slave-updates
sync_binlog=1
auto_increment_offset=2
auto_increment_increment=2
开始同步，最好锁表，好让从服务器定位同步位置。初次同步完成后，记得解锁。
flush tables with read lock;
unlock tables;
分别查看两台mysql>show slave status是看到
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
说明同步成功
下面是两台的my.conf
A 192.168.31.154
#---------------byyao-------------
port = 3306
log_bin=/var/lib/mysql/mysql-binlog
server-id=1
binlog_do_db=zabbix
#------------zhuzhu--------------
master-host=192.168.31.109
master-user=yao
master-password=yaoshao123
master-port=3306
master-connect-retry=5
replicate-do-db = zabbix
log-slave-updates
sync_binlog=1
auto_increment_offset=1
auto_increment_increment=2
#--------------byyao--------------
B192.168.31.109
#--------------byyao------------
server-id=2
master-host=192.168.31.154
master-user=yao
master-password=yaoshao123
master-port=3306
master-connect-retry=5
replicate-do-db=zabbix
#-------------zhuzhu------------
log_bin=/var/lib/mysql/mysql-binlog
log-slave-updates
sync_binlog=1
auto_increment_offset=2
auto_increment_increment=2
#--------------byyao------------
主主同步效果图
往从数据库新建一个表yaoshao，在主数据库查看
----------------------------------------keepalived 配置-----------------------------------
vi /etc/keepalived/keepalived.conf
主AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
vrrp_instance VI_1 {
state backup #两台配置此处均是BACKUP
interface eth0
virtual_router_id 51
priority 100 #优先级，另一台改为90
advert_int 1
nopreempt #不抢占，只在优先级高的机器上设置即可，优先级低的机器不设置
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.31.122
}
}
virtual_server 192.168.31.122 3306 {
delay_loop 6
lb_algo wrr
lb_kind DR
persistence_timeout 50 #会话保持时间
protocol TCP
real_server 192.168.31.154 3306 {
weight 3
notify_down /usr/shell/killkeepalived.sh #检测到mysql服务挂了就执行这个脚本（脚本
要自己写哈）
TCP_CHECK {
connect_timeout 10 #连接超时时间
nb_get_retry 3 #重连次数
delay_before_retry 3 #重连间隔时间
connect_port 3306 #健康检查端口
}
}
}
备BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
vrrp_instance VI_1 {
state backup
interface eth0
virtual_router_id 51
priority 90
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.31.122
}
}
virtual_server 192.168.31.122 3306 {
delay_loop 6
lb_algo wrr
lb_kind DR
persistence_timeout 50
protocol TCP
real_server 192.168.31.109 3306 {
weight 3
notify_down /usr/shell/killkeepalived.sh
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
------------------------脚本--两台都一样-----直接关闭，然后在corntab里尝试对mysqld和keepalived重启--------------
# cat /usr/shell/killkeepalived.sh
#/bin/sh
/sbin/service keepalived stop
-----------------或者先判断尝试重启MYSQL不行在关闭keepalived--------
#!/bin/bash
#PATH=/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
A=`ps -C mysqld --no-header|wc -l`
if [ $A -eq 0 ];then
/etc/init.d/mysqld start ##这个地方写你nginx命令的路径
sleep 3
if [`ps -C mysqld --no-header|wc -l` -eq 0 ];then
killall keepalived
fi
fi
------------------------测试效果--------------------
两台机子都开起来，mysql -h 192.168.32.122 -uroot -pyaoshao123
mysql> show databases;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection> Current database: *** NONE ***
+--------------------+
| Database |
+--------------------+
| information_schema |
| B |
| test |
| zabbix |
+--------------------+
4 rows in set (0.04 sec)
===========关闭B这一台================
mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection> Current database: *** NONE ***
+--------------------+
| Database |
+--------------------+
| information_schema |
| A |
| test |
| zabbix |
+--------------------+
稍微延迟
OK搞定了。。。。。
页:
[1]