MySQL主主高可用(keepalive)
2台新的虚拟机172.16.1.1、172.16.1.2 (配置yum源 )
安装数据库服务
其中 172.16.1.1、172.16.1.2运行数据库服务并设置数据库管理员从本机登录的密码是xzw12345 （mysql-server.5.6）
tar -xvf MySQL-5.6.rpm.tar （RPM包安装）
rpm -Uvh MySQL-*.rpm
/etc/init.d/mysql start ; chkconfig mysql on
修改密码
[root@mail ~]# cat /root/.mysql_secret
[root@mail ~]# mysql -uroot -pjBJIfBwD
mysql> set password for root@"localhost"=password("xzw12345");
mysql> quit;
[root@mail ~]# mysql -uroot -pxzw12345
mysql>
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
主主结构
master203：
mysql -uroot -pxzw12345
mysql> grant replication slave on *.* to slaveuser@"%" identified by "123456";
mysql>quit;
vim /etc/my.cnf
[mysqld]
log-bin=master203
server_id=203
:wq
/etc/init.d/mysql restart
ls /var/lib/mysql/master203.*
mysql -uroot -pxzw12345
mysql>show master status;
master204：
mysql -uroot -pxzw123
mysql> grant replication slave on *.* to slaveuser@"%" identified by "123456";
mysql>quit;
vim /etc/my.cnf
[mysqld]
log-bin=master204
server_id=204
:wq
/etc/init.d/mysql restart
ls /var/lib/mysql/master204.*
mysql -uroot -pxzw12345
mysql>show master status;
master204向master203发起同步操作，指定相关master172.16.1.1的相关参数
mysql -uroot -pxzw12345
mysql> change master to master_host="172.16.1.1",master_user="slaveuser",master_password="123456",master_log_file="master203.000001",master_log_pos=120;
mysql> start slave;
mysql> show slave status\G;
IO yes
SQL yes
master203向master204发起同步操作，指定相关master172.16.1.2的相关参数
mysql -uroot -pxzw12345
mysql> change master to master_host="172.16.1.2",master_user="slaveuser",master_password="123456",master_log_file="master204.000001",master_log_pos=120;
mysql> start slave;
mysql> show slave status\G;
IO yes
SQL yes
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
测试
1.master203
mysql -uroot -pxzw12345
mysql> create database bbsdb;
mysql> grant all on bbsdb.* to student@"%" identified by "123";
mysql> quit;
2.master204
mysql -uroot -pxzw12345
mysql>use bbsdb;
mysql> create table bbsdb.t1(id int);
mysql> quit;
3.在客户端访问（192.168.1.200）:
ping x.x.x.1
mysql -hx.x.x.1 -ustudent -p123
mysql> insert into bbsdb.t1 values(101);
在master203、master204
mysql -uroot -pxzw12345
mysql > select * from bbsdb.t1 ; #也能够查看到记录
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
本文为大家讲解的是mysql错误：Last_IO_Error: error connecting to master解决方法，感兴趣的同学参考下。
错误提示：
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master 'slaveuser@172.16.1.1:3306' - retry-time: 60 retries: 1
错误原因：
主数据库服务器没有关闭防火墙。
解决措施：
1、去主服务器：172.16.1.1关闭防火墙。
[root@mysql204 ~]# service iptables stop;chkconfig iptables off
iptables: Setting chains to policy ACCEPT: filter [ OK ]
iptables: Flushing firewall rules: [ OK ]
iptables: Unloading modules: [ OK ]
2、去从服务器：stop slave;start slave;
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Keepalived高可用服务器（keepalived-1.2.7）
问题
两台Linux服务器（172.16.1.1、172.16.1.2），做MySQL服务器，并部署Keepalived高可用软件，一台作为客户端主机（192.168.1.200），实现如下功能：
- 使用Keepalived实现MySQL服务器的高可用
- Web服务器IP地址分别为172.16.1.1和172.16.1.2
- Web服务器的漂移VIP地址为172.16.1.3
- 客户端通过访问VIP地址访问MySQL服务
步骤
实现此案例需要按照如下步骤进行。
步骤一：配置网络环境
1）设置MySQL服务器网络参数
[root@mysql203 ~]# vim /etc/sysconfig/network-scripts/ifcfg-eth1
DEVICE=eth1
TYPE=Ethernet
ONBOOT=yes
NM_CONTROLLED=yes
BOOTPROTO=none
IPADDR=172.16.1.1
NETMASK=255.255.255.0
#DNS2=221.4.8.1
#GATEWAY=192.168.1.1
#DNS1=192.168.9.1
IPV6INIT=no
USERCTL=no
[root@mysql204 ~]# vim /etc/sysconfig/network-scripts/ifcfg-eth1
DEVICE=eth1
TYPE=Ethernet
ONBOOT=yes
NM_CONTROLLED=yes
BOOTPROTO=none
IPADDR=172.16.1.2
NETMASK=255.255.255.0
#DNS2=221.4.8.1
#GATEWAY=192.168.1.1
#DNS1=192.168.9.1
IPV6INIT=no
USERCTL=no
步骤二：安装Keepalived软件
注意：两台MySQL服务器做相同的操作。
[root@mysql203 package]# yum install -y gcc kernel-devel openssl-devel popt-devel
[root@mysql203 package]# tar -xzf keepalived-1.2.7.tar.gz
[root@mysql203 package]# cd keepalived-1.2.7
[root@mysql203 keepalived-1.2.7]# ./configure --sysconf=/etc
[root@mysql203 keepalived-1.2.7]# make && make install
[root@mysql203 keepalived-1.2.7]# ln -s /usr/local/sbin/keepalived /sbin/
[root@mysql203 keepalived-1.2.7]# chkconfig keepalived on
步骤三：部署Keepalived服务
1）修改web1服务器Keepalived配置文件
- [root@mysql203~]# vim /etc/keepalived/keepalived.conf
- global_defs {
- notification_email {
- admin@sina.com //设置报警收件人邮箱
- }
- notification_email_from root@localhost //设置发件人
- smtp_server 127.0.0.1 //定义邮件服务器
- smtp_connect_timeout 30
- router_id mysql203 //设置路由ID号
- }
- vrrp_instance VI_1 {
- state MASTER //主服务器为MASTER
- interface eth1 //定义网络接口
- virtual_router_id 51 //主辅VRID号必须一致
- priority 100 //服务器优先级
- advert_int 1
- authentication {
- auth_type pass
- auth_pass forlvs //主辅服务器密码必须一致
- }
- virtual_ipaddress { 172.16.1.3}
- }
2）修改mysql204服务器Keepalived配置文件
- [root@mysql204~]# vim /etc/keepalived/keepalived.conf
- global_defs {
- notification_email {
- admin@sina.com //设置报警收件人邮箱
- }
- notification_email_from root@localhost //设置发件人
- smtp_server 127.0.0.1 //定义邮件服务器
- smtp_connect_timeout 30
- router_id mysql204 //设置路由ID号
- }
- vrrp_instance VI_1 {
- state SLAVE //从服务器为SLAVE
- interface eth1 //定义网络接口
- virtual_router_id 51 //主辅VRID号必须一致
- priority 90 //服务器优先级
- advert_int 1
- authentication {
- auth_type pass
- auth_pass forlvs //主辅服务器密码必须一致
- }
- virtual_ipaddress { 172.16.1.3 }
- }
3）启动服务
- [root@mysql203~]# service keepalived start
- [root@mysql204~]# service keepalived start
步骤四：测试
1）登录两台Web服务器查看VIP信息
- [root@mysql203~]# ip addr show
- [root@mysql204~]# ip addr show
2) 客户端访问
客户端使用mysql命令连接172.16.1.3，查看mysql数据库；停掉MySQL203服务器，客户端再次查看mysql数据库，验证是否可以正常访问服务。
mysql -h172.16.1.3 -ustudent -p123
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
MySQL健康检查
一、
[root@mysql203 ~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
linux985@sina.com
}
notification_email_from root@localhost
smtp_server 172.0.0.1
smtp_connect_timeout 30
router_id mysql203
}
vrrp_script chk_mysql_proxy {
script "/etc/keepalived/mysql.sh"
interval 2 #间隔两秒
weight 2
}
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 {
172.16.1.3
}
track_script {
chk_mysql_proxy
}
}
二、
[root@mysql203 ~]# vim /etc/keepalived/mysql.sh
#!/bin/bash
MYSQL_OK=1
function check_mysql_helth (){
mysql -uroot -pxzw12345 -e "show status;" &>/dev/null
if [ $? = 0 ] ;then
MYSQL_OK=1
else
MYSQL_OK=0
fi
return $MYSQL_OK
}
check_mysql_helth
if [ $MYSQL_OK -eq 0 ]
then
pkill keepalived
exit 1
fi
