Mysql5.6+Keepalived双主热备高可用

最新推荐文章于 2021-01-19 07:05:00 发布

_BuLuLU

最新推荐文章于 2021-01-19 07:05:00 发布

阅读量181

点赞数

分类专栏： MySQL 文章标签：数据库 mysql

本文链接：https://blog.csdn.net/qq_37531186/article/details/109119423

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

MySQL5.6安装

链接：https://pan.baidu.com/s/1ZOgQCRtpSdoHxDEOSVQIBQ
提取码：2332

1.解压

#解压
tar -zxvf mysql-5.6.33-linux-glibc2.5-x86_64.tar.gz
#复制解压后的mysql目录
cp -r mysql-5.6.33-linux-glibc2.5-x86_64 /usr/local/mysql

2.添加用户及用户组

#添加用户组
groupadd mysql
#添加用户mysql 到用户组mysql
useradd -g mysql mysql

3.安装

[root@localhost ~]# cd /usr/local/mysql
[root@localhost mysql]# mkdir ./data/mysql
[root@localhost mysql]# chown -R mysql:mysql ./
[root@localhost mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data/mysql
[root@localhost mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@localhost mysql]# chmod 755 /etc/init.d/mysqld
[root@localhost mysql]# cp support-files/my-default.cnf /etc/my.cnf

##修改启动脚本
[root@localhost mysql]# vim /etc/init.d/mysqld
    --修改项
    basedir=/usr/local/mysql
    datadir=/usr/local/mysql/data/mysql
    
##启动mysql服务
[root@localhost mysql]# service mysqld start

##测试连接，顺便给远程连接的用户权限
[root@localhost mysql]# ./bin/mysql -uroot
mysql> grant all privileges on *.* to 'root'@'%' identified by '123456';

MySQL主主同步部署

master1服务器操作

--在/etc/my.cnf添加如下内容
[root@localhost ~]# vim /etc/my.cnf
server-id = 1         
log-bin = mysql-bin     
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2     
auto-increment-offset = 1    
slave-skip-errors = all 

--重启mysql数据库
[root@localhost ~]# service mysqld stop
[root@localhost ~]# service mysqld start

--数据同步授权，这样I/O线程就可以以这个用户的身份连接到主服务器，并且读取它的二进制日志。
[root@localhost mysql]# ./bin/mysql -uroot
mysql> grant replication slave,replication client on *.* to root@'172.16.33.%' identified by "123456";
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

--最好将库锁住，仅仅允许读，以保证数据一致性；待主主同步环境部署后再解锁
mysql> flush tables with read lock;

--查看下log bin日志和pos值位置
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000004 |      430 |              | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+

master2服务器操作

--在/etc/my.cnf添加如下内容
[root@localhost ~]# vim /etc/my.cnf
server-id = 2  #与master1不同
log-bin = mysql-bin     
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2     
auto-increment-offset = 2    #与master1不同
slave-skip-errors = all 

--重启mysql数据库
[root@localhost ~]# service mysqld stop
[root@localhost ~]# service mysqld start

--数据同步授权，这样I/O线程就可以以这个用户的身份连接到主服务器，并且读取它的二进制日志。
[root@localhost mysql]# ./bin/mysql -uroot
mysql> grant replication slave,replication client on *.* to root@'172.16.33.%' identified by "123456";
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

--最好将库锁住，仅仅允许读，以保证数据一致性；待主主同步环境部署后再解锁
mysql> flush tables with read lock;

--查看下log bin日志和pos值位置
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000004 |      430 |              | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+

master1服务器做同步操作

---------------master1服务器做同步操作---------------
mysql> unlock tables;     //先解锁，将对方数据同步到自己的数据库中
mysql> stop slave;
mysql> change master to master_host='172.16.33.176',master_user='root',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=430;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
  
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
  
--查看同步状态，如下出现两个“Yes”，表明同步成功！
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 182.148.15.237
                  Master_User: wang
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 430
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 279
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        .........................
        Seconds_Behind_Master: 0
        .........................
 
--这样，master1就和master2实现了主从同步，即master1同步master2的数据。

master2服务器做同步操作

mysql> unlock tables;     //先解锁，将对方数据同步到自己的数据库中
mysql> stop slave;
mysql> change  master to master_host='172.16.33.191',master_user='root',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=430;  
Query OK, 0 rows affected, 2 warnings (0.06 sec)
  
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
  
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 182.148.15.238
                  Master_User: wang
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 430
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 279
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        ........................
        Seconds_Behind_Master: 0
        ........................
  
--这样，master2就和master1实现了主从同步，即master2也同步master1的数据。

主主同步效果验证

--在master1数据库上写入新数据
mysql> create database db_zxzh1;
mysql> use db_zxzh1;
mysql> create table t_test(c_id int(10),c_name varchar(300));
mysql> insert into t_test values(1,'zhangsan');
--在master2数据库看是否同步

--在master2数据库上写入新数据
mysql> create database db_zxzh2;
mysql> use db_zxzh2;
mysql> create table t_test(c_id int(10),c_name varchar(300));
mysql> insert into t_test values(1,'lisi');
--在master1数据库看是否同步

至此，Mysql主主同步环境已经实现。

配置Mysql+Keepalived故障转移的高可用环境

在master1服务器加上虚拟IP

ifconfig eth0:1 172.16.33.200 netmask 255.255.128.0 up

安装Keepalived

##安装依赖，可能会少，后续安装过程可根据报错安装相应依赖
yum -y install libnl libnl-devel
yum -y install libnfnetlink-devel
yum install -y openssl-devel

##开始安装
[root@master1 ~]# cd /usr/local/src/
[root@master1 src]# wget http://www.keepalived.org/software/keepalived-1.3.5.tar.gz
[root@master1 src]# tar -zvxf keepalived-1.3.5.tar.gz
[root@master1 src]# cd keepalived-1.3.5
[root@master1 keepalived-1.3.5]# ./configure --prefix=/usr/local/keepalived
[root@master1 keepalived-1.3.5]# make && make install

[root@master1 keepalived-1.3.5]# cp /usr/local/src/keepalived-1.3.5/keepalived/etc/init.d/keepalived /etc/rc.d/init.d/
[root@master1 keepalived-1.3.5]# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
[root@master1 keepalived-1.3.5]# mkdir /etc/keepalived/
[root@master1 keepalived-1.3.5]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
[root@master1 keepalived-1.3.5]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
[root@master1 keepalived-1.3.5]# echo "/etc/init.d/keepalived start" >> /etc/rc.local

master1机器上的keepalived.conf配置

[root@master1 ~]# cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak
[root@master1 ~]# vim /etc/keepalived/keepalived.conf       --清空默认内容，直接采用下面配置：
! Configuration File for keepalived

global_defs {
notification_email {
ops@wangshibo.cn
tech@wangshibo.cn
}
notification_email_from ops@wangshibo.cn
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MASTER-HA
}

vrrp_script check_mysql {                     --检测mysql服务是否在运行。有很多方式，比如进程，用脚本检测等等
    script "/etc/keepalived/check_mysql.sh"   --这里通过脚本监测
    interval 3                                --脚本执行间隔，每3s检测一次
    weight -5                                 --脚本结果导致的优先级变更，检测失败（脚本返回非0）则优先级 -5
    fall 2                                    --检测连续2次失败才算确定是真失败。会用weight减少优先级（1-255之间）
    rise 1                                    --检测1次成功就算成功。但不修改优先级
}

vrrp_instance VI_1 {
    state BACKUP         --BACKUP MASTER 两台都配置为backup，非抢占模式
    interface eth0       --指定虚拟ip的网卡接口
    virtual_router_id 61 --路由器标识，MASTER和BACKUP必须是一致的
    priority 100         --优先级，另一台设为90
    advert_int 1         --组播信息发送间隔，两个节点需一致
    nopreempt            --不抢占，只在优先级高的机器上设置，优先级低的不设置
    authentication {     --设置密码验证信息，两个节点需一致
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {  --指定虚拟IP，两个节点需设置一样
        172.16.33.200
    }
   track_script {        --检测mysql状态
       check_mysql
    }
}

master2机器上的keepalived.conf配置

! Configuration File for keepalived

global_defs {
notification_email {
ops@wangshibo.cn
tech@wangshibo.cn
}

notification_email_from ops@wangshibo.cn
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MASTER-HA
}

vrrp_script check_mysql {
    script "/etc/keepalived/check_mysql.sh"
    interval 3
    weight -5
    fall 2
    rise 1
}


vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 61
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        172.16.33.200
    }
    track_script {
       check_mysql
    }
}

keepalived日志地址，/var/log/messages。

脚本检测Mysql的运行状态

[root@localhost bin]# vim /etc/keepalived/check_mysql.sh
#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
    /etc/init.d/keepalived stop
fi
--记得加权限
[root@localhost bin]# chmod 755 /etc/keepalived/check_mysql.sh

升级版检测Mysql脚本

#!/bin/bash
MYSQL=/usr/local/mysql/bin/mysql
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASSWORD=123456
CHECK_TIME=3  
#mysql  is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0
MYSQL_OK=1  
function check_mysql_helth (){    
	$MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p${MYSQL_PASSWORD} -e "show status;" >/dev/null 2>&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
    pkill keepalived    exit 1
fi
sleep 1
done

测试是否有效，可以将master1上的mysql停掉，用172.16.33.200看是否开可以连接

_BuLuLU

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
Mysql5.6+Keepalived双主热备高可用

MySQL5.6安装1.解压#解压tar -zxvf mysql-5.6.33-linux-glibc2.5-x86_64.tar.gz#复制解压后的mysql目录cp -r mysql-5.6.33-linux-glibc2.5-x86_64 /usr/local/mysql2.添加用户及用户组#添加用户组groupadd mysql#添加用户mysql 到用户组mysqluseradd -g mysql mysql3.安装[root@localhost ~]# cd /usr
复制链接

扫一扫