Mysql主从半同步及双机热备

Mysql主从半同步及双机热备

Mysql主从同步配置（mysql版本5.5以上、keepalived软件实现双机热备）：

基础：两台机子  主服务器：192.168.1.130 从服务器：192.168.1.131

1、修改mysql配置文件my.cnf（以下配置项，文件中有则根据下边要求修改，没有则自行添加）：

主：

server-id = 1(主数据库一般都是id为1)
log-bin=mysql-bin (必须的)
binlog_format=mixed （必须的，推荐类型为mixd）
expire_logs_days=5 （为避免日志文件过大，设置过期时间为5天）
binlog-ignore-db = mysql （忽略同步的数据库，也不记入二进制日志，可列多行)
replicate-do-db = test （需要同步的数据库，记入二进制日志，可列多行）

从：

server-id = 2
log-bin=mysql-bin
binlog_format=mixed
expire_logs_days=5

注1：MySQL进行主从复制是通过二进制的日志文件来进行，所以我们必须开启MySQL的日志功能，即我们上面的log-bin，同时每一台数据库服务器都需要指定一个唯一的server-id，通常主数据库服务器我们指定为1。

注2:Mysql5.5版本以上不再支持master的用法，比如5.1中可用的（master-user = repl；master-password = repl；master-port = 3307）这样的语法已经失效了，如果你使用了，还会报错。（我在这里载了跟头，检查mysql日志/usr/local/db/mysql/ 127.0.0.1.err——您的路径也许会稍有不同——才发现错误）如果你不小心配置了这些参数，MySQL服务器将无法正常启动
错误提示为：[ERROR] /usr/local/mysql/bin/mysqld: unknownvariable ‘master-host=192.168.1.130’，正确的办法如上文所写。

配置完成之后，将主从两机的mysql服务重新启动一下。

2、Mysql主(1.130)，执行：GRANT all privileges on *.* to 'test' @'192.168.1.131' IDENTIFIED BY '123456'(授予slave账号对所有数据库享有slave权限，密码为slave777)；

3、查看Mysql主 的状态：执行：show master status；这时会看到master数据库所处的位置，记录下来：

show master status;

| File | Position |

| mysql-bin.000011 | 383 |

4、Mysql从：

执行：slave stop；

执行：change master tomaster_host='192.168.1.130', master_port=3306, master_user=’test’,master_password=’123456’,master_log_file=’mysql-bin.000011′, master_log_pos=383;

执行：slave start；

5、查询slave的状态：mysql> show slavestatus \G；看下slave的状态:

Master_Log_File: mysql-bin.000011 (和主mysql一致)
Read_Master_Log_Pos: 383 （和主mysql一致）
Slave_IO_Running: Yes （读写）
Slave_SQL_Running: Yes （数据库状态）

还要注意状态中是否有error，如果没有的话，就差不多了。

6、测试数据库是否能够同步：略；

7、如果Slave复制失败，你可以根据错误信息进行修正，然后执行

mysql> slave stop;
mysql> slave start;
就可以把原来应该复制过来的数据都复制过来

8、几个常用的命令：

Slave start; --启动复制线程
Slave stop; --停止复制线程
Reset slave; --重置复制线程
Show slave status; --显示复制线程的状态
Show slave status\G; --显示复制线程的状态(分行显示)
Show master status\G; --显示主数据库的状态(分行显示)
Show master logs --显示主数据库日志，需在主数据库上运行
Change master to; --动态改变到主数据库的配置
Show processlist --显示有哪些线程在运行

由于Mysql的复制都是基于异步进行的，在特殊情况下不能保证数据的成功复制，因此在mysql 5.5之后使用了来自google补丁，可以将Mysql的复制实现半同步模式。所以需要为主服务器加载对应的插件。在Mysql的安装目录下的lib/plugin/目录中具有对应的插件semisync_master.so，semisync_slave.so

在Master和Slave的mysql命令行运行如下命令：

Master：

mysql> install pluginrpl_semi_sync_master soname 'semisync_master.so'; 

mysql> set globalrpl_semi_sync_master_enabled = 1; 

mysql> set globalrpl_semi_sync_master_timeout = 1000; 

mysql> show variables like '%semi%';

+------------------------------------+-------+

| Variable_name                      | Value |

+------------------------------------+-------+

| rpl_semi_sync_master_enabled       | ON   |

| rpl_semi_sync_master_timeout       | 1000 |

| rpl_semi_sync_master_trace_level   | 32   |

| rpl_semi_sync_master_wait_no_slave |ON    |

+------------------------------------+-------+

Slave：

mysql> install pluginrpl_semi_sync_slave soname 'semisync_slave.so'; 

mysql> set globalrpl_semi_sync_slave_enabled = 1; 

mysql> stop slave;

mysql> start slave;

mysql> show variables like '%semi%';

+---------------------------------+-------+

| Variable_name                   | Value |

+---------------------------------+-------+

| rpl_semi_sync_slave_enabled     | ON   |

| rpl_semi_sync_slave_trace_level | 32    |

+---------------------------------+-------+

检查半同步是否生效：

Master：

mysql> show global status like'rpl_semi%';

+--------------------------------------------+-------+

| Variable_name                              | Value |

+--------------------------------------------+-------+

| Rpl_semi_sync_master_clients               | 1    |

|Rpl_semi_sync_master_net_avg_wait_time    | 0     |

| Rpl_semi_sync_master_net_wait_time         | 0    |

| Rpl_semi_sync_master_net_waits             | 0     |

| Rpl_semi_sync_master_no_times              | 0     |

| Rpl_semi_sync_master_no_tx                 | 0     |

| Rpl_semi_sync_master_status                | ON    |

|Rpl_semi_sync_master_timefunc_failures    | 0     |

|Rpl_semi_sync_master_tx_avg_wait_time     | 0     |

| Rpl_semi_sync_master_tx_wait_time          | 0     |

| Rpl_semi_sync_master_tx_waits              | 0     |

|Rpl_semi_sync_master_wait_pos_backtraverse | 0     |

| Rpl_semi_sync_master_wait_sessions         | 0    |

| Rpl_semi_sync_master_yes_tx                | 0     |

+--------------------------------------------+-------+

说明半同步成功。

让半同步功能在MySQL每次启动都自动生效，在Master和Slave的my.cnf中编辑：

Master：

[mysqld] 

rpl_semi_sync_master_enabled=1 

rpl_semi_sync_master_timeout=1000     #1秒

Slave：

[mysqld] 

rpl_semi_sync_slave_enabled=1 

也可通过设置全局变量的方式来设置是否启动半同步插件：

Master：

mysql> set globalrpl_semi_sync_master_enabled=1

取消加载插件

mysql> uninstall pluginrpl_semi_sync_master;

Slave：

mysql> set globalrpl_semi_sync_slave_enabled = 1;

mysql> uninstall pluginrpl_semi_sync_slave;

安装keepalived软件实现双机热备

Keepalived是Linux下面实现VRRP备份路由的高可靠性运行件。基于Keepalived设计的服务模式能够真正做到主服务器和备份服务器故障时IP瞬间无缝交接。

主服务器

#tar zxvf keepalived-1.2.2.tar.gz
#cd keepalived-1.2.2

#mkdir /usr/local/keepalived
#./configure --prefix=/usr/local/keepalived
#make
#make install
#cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
#cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
#cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
修改keepalived配置文件

vim /usr/local/keepalived/etc/keepalived/keepalived.conf（删除多余内容，仅留以下部分）
! Configuration File for keepalived
global_defs {
 notification_email {
 yuhongchun027@163.com
 }
 notification_email_from keepalived@chtopnet.com
 smtp_server 127.0.0.1
 smtp_connect_timeout 30
 router_id LVS_DEVEL
}
vrrp_instance VI_1 {
 state MASTER
 interface eth0
 virtual_router_id 51
 priority 100
 advert_int 1
 authentication {
 auth_type PASS
 auth_pass chtopnet
 }
 virtual_ipaddress {
 192.168.93.132
 }
}

修改/etc/sysconfig/keepalived的内容：

KEEPALIVED_OPTIONS="-D -f/usr/local/keepalived/etc/keepalived/keepalived.conf"

#service keepalived start

从服务器

#tar zxvf keepalived-1.2.2.tar.gz

#mkdir /usr/local/keepalived
#cd keepalived-1.2.2
#./configure --prefix=/usr/local/keepalived
#make
#make install
#cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
#cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
#cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
#vim /usr/local/keepalived/etc/keepalived/keepalived.conf（删除多余内容，仅留以下部分）! Configuration File for keepalived
global_defs {
 notification_email {
 yuhongchun027@163.com
 }
 notification_email_from keepalived@chtopnet.com
 smtp_server 127.0.0.1
 smtp_connect_timeout 30
 router_id LVS_DEVEL
}
vrrp_instance VI_1 {
 state BACKUP
 interface eth0
 virtual_router_id 51
 priority 99
 advert_int 1
 authentication {
 auth_type PASS
 auth_pass chtopnet
 }
 virtual_ipaddress {
 192.168.93.132
 }
}

修改/etc/sysconfig/keepalived的内容：

KEEPALIVED_OPTIONS="-D -f/usr/local/keepalived/etc/keepalived/keepalived.conf"

#service keepalived start

设置定时任务脚本，定时检测mysql状态：

Vicheck_mysql_helth.sh

#!/bin/bash

MYSQL=/usr/local/mysql/bin/mysql

MYSQL_HOST=127.0.0.1

MYSQL_USER=root

MYSQL_PASSWORD=aibei1010

CHECK_TIME=3

#mysql  is working MYSQL_OK is 1 , mysql downMYSQL_OK is 0

MYSQL_OK=1

functioncheck_mysql_helth (){

$MYSQL-u$MYSQL_USER -p$MYSQL_PASSWORD -e "show status;" >/dev/null2>&1

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

     CHECK_TIME=0

     exit 0

fi

if [$MYSQL_OK -eq 0 ] &&  [$CHECK_TIME -eq 0 ]

then

     killall keepalived

exit1

fi

done