Mysql双主配置+虚拟IP+自定义脚本 高可用

mysql双主配置+虚拟IP+自定义脚本 高可用

一、安装mysql

二、准备两台测试虚拟机

准备两台测试的虚拟机，如上安装mysql环境，并开启mysql服务

mysql-vip:192.168.194.102

master 1: 192.168.194.100

master 2 : 192.168.194.101

三、主机master1配置

​ 1.添加数据同步配置

vi /etc/my.cnf
#在[mysqld]中增加以下配置:
# 服务器的ID,必须唯一，一般设置自己的IP
server_id=205
# 复制过滤：不需要备份的数据库（MySQL库一般不同步）
binlog-ignore-db=mysql
# 开启二进制日志功能，名字可以随便取，最好有含义（比如项目名）
log-bin=edu-mysql-bin
# 为每个 session 分配的内存,在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
# 主从复制的格式(mixed,statement,row,默认格式是 statement)
binlog_format=mixed
# 二进制日志自动删除/过期的天数。默认值为 0,表示不自动删除。
expire_logs_days=7
## 跳过主从复制中遇到的所有错误或指定类型的错误,避免 slave 端复制中断。 
## 如:1062 错误是指一些主键重复,1032 错误是因为主从数据库数据不一致
slave_skip_errors=1062
# 作为从服务器时的中继日志
relay_log=edu-mysql-relay-bin
# log_slave_updates 表示 slave 将复制事件写进自己的二进制日志
log_slave_updates=1
# 主键自增规则，避免主从同步ID重复的问题
auto_increment_increment=2  # 自增因子（每次加2）
auto_increment_offset=1     # 自增偏移（从1开始），单数

​ 2.Master配置

#进入数据库
mysql -uroot -p

#创建数据库同步用户，并授予相应的权限
grant replication slave,replication client on *.* to 'repl'@'192.168.194.101' identified by 'root123456';

#可能会报如下错误
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
#解决
set global validate_password_policy=0;
set global validate_password_length=4;
#刷新授权表信息
flush privileges;

#查看binlog文件的position（偏移）和File（日志文件）的值，从机上需要用到
show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| edu-mysql-bin.000001 |      120 |              | mysql            |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

​ 3.Slave配置

# master_user和master_password：在101上执行grant replication slave...创建的用户和密码
# master_log_file和master_log_pos：在101上运行show master status;命令执行结果对应File和Position字的#值
change master to master_host='192.168.194.101',master_user='repl', master_password='root123456', master_port=3306, master_log_file='edu-mysql-bin.000001', master_log_pos=439;
#如果遇到以下错误,先执行stop slave
#ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP #SLAVE IO_THREAD FOR

​ 注意：要在master2(101)上创建同步帐户后再启动，否则会报连不上master错误

​ 4.启动Slave

start slave;
#查看slave状态
show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.194.101
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: edu-mysql-bin.000001
          Read_Master_Log_Pos: 439
               Relay_Log_File: edu-mysql-relay-bin.000002
                Relay_Log_Pos: 287
        Relay_Master_Log_File: edu-mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
            # ...省略其它配置  
  #其中Slave_IO_Running: Yes,Slave_SQL_Running: Yes,代表服务器正常连接主服务器          

四、主机master2配置

​ 1.添加数据同步配置

vi /etc/my.cnf
server_id=206
binlog-ignore-db=mysql
log-bin=edu-mysql-bin
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=7
slave_skip_errors=1062
relay_log=edu-mysql-relay-bin
log_slave_updates=1
#ID自增从2开始，双数
auto_increment_increment=2
auto_increment_offset=2

​ 2.Master配置

#进入数据库
mysql -uroot -p

#创建数据库同步用户，并授予相应的权限
grant replication slave, replication client on *.* to 'repl'@'192.168.194.100' identified by 'root123456';

#刷新授权表信息
flush privileges;

#查看binlog文件的position（偏移）和File（日志文件）的值，从机上需要用到
show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| edu-mysql-bin.000001 |      439 |              | mysql            |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

​ 这时可以启动master1(100)的slave服务

​ 3.Slave配置

# master_user和master_password：在100上执行grant replication slave...创建的用户和密码
# master_log_file和master_log_pos：在100运行show master status;命令执行结果对应File和Position字的#值
change master to master_host='192.168.194.100',master_user='repl', master_password='root123456', master_port=3306, master_log_file='edu-mysql-bin.000001', master_log_pos=120;
#如果遇到以下错误,先执行stop slave
#ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP #SLAVE IO_THREAD FOR

​ 4.启动Slave

start slave;
#查看slave状态
show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.194.100
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: edu-mysql-bin.000001
          Read_Master_Log_Pos: 120
               Relay_Log_File: edu-mysql-relay-bin.000002
                Relay_Log_Pos: 4
        Relay_Master_Log_File: edu-mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
            # ...省略其它配置  
  #其中Slave_IO_Running: Yes,Slave_SQL_Running: Yes,代表服务器正常连接主服务器          

五、双主验证

# 登录100创建一个数据库
shell> mysql -u root -p
mysql> create database if not exists mydb;
mysql> create table user (id int, username varchar(30), password varchar(30));
mysql> insert into user values (1, 'zhyc', '123456');
# 下面是在101节点上的操作
#1、登录101查询所有库，是否包含mydb数据库
#2、切换到mydb库，是否包含user表，并有一条数据
#3、在101的mydb.user表插入一条数据，查看100是否同步过去
mysql> insert into user values (2,'zhyc2','123456')

六、keepalived 安装与配置

1. keepalived 安装

#下载
wget http://www.keepalived.org/software/keepalived-1.2.23.tar.gz
#解压
tar -zxvf keepalived-1.2.23.tar.gz
#进入文件
cd keepalived-1.2.23
#安装
./configure --prefix=/usr/local/keepalived   #prefix指定安装目录
make && make install

如果执行 ./configure --prefix=/usr/local/keepalived 报错

!!! OpenSSL is not properly installed on your system. !!!
!!! Can not include OpenSSL headers files. 

# 解决方法：
yum -y install openssl-devel

2. keepalived 配置

配置keepalived.conf

vi /usr/local/keepalived/etc/keepalived/keepalived.conf

! Configuration File for keepalived


vrrp_script check_mysql {                    
	script "/usr/local/keepalived/bin/check_mysql.sh"  
	interval 2                                                         
} 
vrrp_instance VI_1 {
    state BACKUP
    interface eth0    
    virtual_router_id 51
    priority 100 #master2 配置50
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
	track_script {                    
		check_mysql  
    } 
    virtual_ipaddress {
        192.168.194.194
    }
}

指定keepalived配置文件路径

vi /usr/local/keepalived/etc/sysconfig/keepalived

# 指定keepalived配置文件路径
KEEPALIVED_OPTIONS="-D -f /usr/local/keepalived/etc/keepalived/keepalived.conf"

因为我们使用非默认路径（/usr/local）安装keepalived，需要设置一些软链接以保证keepalived能正常启动

#将keepalived主程序加入到环境变量(创建软链接就是："ln –s 源文件 目标文件")
ln -s /usr/local/keepalived/sbin/keepalived  /usr/bin 

#keepalived启动脚本，放到/etc/init.d/目录下就可以使用service命令便捷调用
ln -s /usr/local/keepalived/etc/rc.d/init.d/keepalived  /etc/init.d/ 

#keepalived启动脚本变量引用文件，默认文件路径是/etc/sysconfig/，也可以不做软链接，直接修改启动脚本中文件路径即可
ln -s /usr/local/keepalived/etc/sysconfig/keepalived  /etc/sysconfig/ 

3.命令

# 设置开机启动
chkconfig keepalived on

# 启动keepalived服务
service keepalived start
# 停止keepalived服务
service keepalived stop
# 重启keepalived服务
service keepalived restart

七、自定义脚本

1.创建shell脚本，然后编辑

vi /usr/local/keepalived/bin/check_mysql.sh

#!/bin/sh
mysql -uroot -proot -e "show status;" >/dev/null 2>&1  
if [ $? == 0 ]  
then  
    echo " $host mysql login successfully "  
    exit 0  
else  
    service keepalived stop  
    exit 2  
fi