MYsql主主+keepalived高可用

时间:2022-09-15 07:26:01

mysql安装

1. 下载mysql安装包

官方下载地址:https://dev.mysql.com/downloads/mysql/5.7.html#downloads

 

2. 将安装包上传到指定位置

#mkdir /opt/mysql

#cd /opt/mysql

#ls

 MYsql主主+keepalived高可用

 

 

3. 解压安装包

 MYsql主主+keepalived高可用

 

 

4. 卸载SUSE自带的mariadb

 MYsql主主+keepalived高可用

 

用rpm -e卸载即可

 

 MYsql主主+keepalived高可用

 

5. 安装mysql-server服务

只需安装如下4个软件包,使用rpm -ivh 安装即可(需要按顺序安装,因为后面的服务依赖于前面的服务)

 MYsql主主+keepalived高可用

 

顺序:

rpm -ivh mysql-community-common-5.7.23-1.sles12.x86_64.rpm

rpm -ivh mysql-community-libs-5.7.23-1.sles12.x86_64.rpm(报错)

yast2

MYsql主主+keepalived高可用

MYsql主主+keepalived高可用

安装部分依赖

rpm -ivh mysql-community-client-5.7.23-1.sles12.x86_64.rpm

rpm -ivh mysql-community-server-5.7.23-1.sles12.x86_64.rpm

即完成安装。

 

6. 初始化数据库

#mysqld --initialize

注:初始化后会在/var/log/mysql/mysqld.log中生成随机密码

 

7. 修改mysql数据库目录的属主属组,启动mysql数据库

#chown mysql:mysql /var/lib/mysql -R

启动:

#systemctl start mysqld.service

查看mysql状态

#systemctl status mysqld.service

 MYsql主主+keepalived高可用

 

 

8. 登录mysql并修改root用户的密码(这是系统强制要求的,否则不能启动mysql)

(1) 登录的初始密码在/var/log/mysql/mysqld.log中,先查看初始密码:

#grep 'temporary password' /var/log/mysql/mysqld.log

 

注1:初始密码一般会很复杂,复制比较稳妥;且一般会有特殊符号,会导致密码不对登录失败,两种方法可以解决:

方法1:

     MYsql主主+keepalived高可用

根据提示填写密码。

方法2:

 MYsql主主+keepalived高可用

 添加单引号。

注2:查看初始密码,SUSE系统在/var/log/mysql/mysqld.log中

但是RHEL, Oracle Linux, CentOS, and Fedora 平台,在/var/log/mysqld.log中。

(2) 进入mysql以后,修改密码

 MYsql主主+keepalived高可用

 

     

9. 修改访问权限

修改访问权限可以让任何主机通过用户root 和密码123456连接到mysql数据库。

#mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

 

三、mysql主主同步

10. 修改两台服务器配置文件

修改主服务器配置文件,增加如下

#vi /etc/my.cnf

[mysqld]

server-id=1

log-bin=mysql-bin

log-slave-updates

binlog-ignore-db = mysql

binlog-ignore-db = information_schema

binlog-ignore-db = performance_schema

replicate-wild-ignore-table = mysql.%

replicate-wild-ignore-table = information_schema.%

replicate-wild-ignore-table = performance_schema.%

expire_logs_days=5

 

          

 

 

 

 

 

 

 

 

修改从服务器配置文件,增加如下 

#vi /etc/my.cnf      

[mysqld]
             server-id=2

log-bin=mysql-bin

log-slave-updates

binlog-ignore-db = mysql

binlog-ignore-db = information_schema

binlog-ignore-db = performance_schema

replicate-wild-ignore-table = mysql.%

replicate-wild-ignore-table = information_schema.%

replicate-wild-ignore-table = performance_schema.%

expire_logs_days=5

               

 

 

 

 

 

 

 

 

 

11. 重启mysql服务(两台都需要重启)

#systemctl restart mysqld

12. 配置主主

(1) [FH-UMP1]mysql 为主库

FH-UMP1

#mysql -p(123456)

mysql> show master status;

 

FH-UMP2

#mysql -p(123456)

mysql> show slave status;

mysql> stop slave;

mysql> change master to master_host='192.168.198.149',master_user='root',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154;

mysql> start slave;

mysql> show slave status\G

 

详细信息如下:

mysql> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.1.183

                  Master_User: root

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 154

               Relay_Log_File: FH-UMP2-relay-bin.000002

                Relay_Log_Pos: 320

        Relay_Master_Log_File: mysql-bin.000001

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB:

          Replicate_Ignore_DB:

           Replicate_Do_Table:

       Replicate_Ignore_Table:

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table: mysql.%,information_schema.%,performance_schema.%

                   Last_Errno: 0

                   Last_Error:

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 154

              Relay_Log_Space: 529

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 1

                  Master_UUID: 3b9bc7b3-d6a0-11e8-9ee1-000c29a816df

             Master_Info_File: /var/lib/mysql/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set:

            Executed_Gtid_Set:

                Auto_Position: 0

         Replicate_Rewrite_DB:

                 Channel_Name:

           Master_TLS_Version:

1 row in set (0.00 sec)

(2) [FH-UMP2]mysql 为主库

类似的,可以配置FH-UMP2上的mysql为主库

FH-UMP2

#mysql -p(123456)

mysql> show master status;

 MYsql主主+keepalived高可用

FH-UMP1

#mysql -p(123456)

mysql> show slave status;

mysql> stop slave;

mysql> change master to master_host='192.168.1.184',master_user='root',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154;

mysql> start slave;

mysql> show slave status\G

 

13. 设置开机自启动(两台都需要)

#vim /etc/init.d/boot.local添加

systemctl start mysql.service

 

四、Keepalived实现高可用

14. Keepalive 安装

#tar –zxvf  keepalived-1.3.4.tar.gz

#cd keepalive-1.3.4/

#./configure  --prefix=/usr/local/keepalived

#make

#make install

UMP1,UMP2两台机器同样都要安装

 

15. 修改keepalive配置文件

(1) 修改UMP1的 keepalived配置文件

#mkdir /etc/keepalived/

#cp /usr/local/keepalived/etc/keepalived/keepalived.conf  /etc/keepalived/

#vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

    router_id HA_MySQL_1   #标识,双主相同

}

vrrp_script check_mysql   #vrrp脚本命名

{

        script "/etc/keepalived/bin/mysql.sh" #要执行的脚本

        interval 10  #脚本指定间隔,秒

        weight -50

#优先级(如果脚本执行结果为0,并且weight配置的值大于0,则优先级相应的增加,如果脚本执行结果非0,并且weight配置的值小于0,则优先级相应的减少)

        fall   3     #尝试三次都成功才成功

        rise   3     #尝试三次都失败才失败

}

 

vrrp_instance VI_1 {

     state MASTER   #注意,主从两端都配置成了MASTER 和BACKUP

     interface br0

     virtual_router_id 51   #分组,主备相同

     priority 100 #优先级,这个高一点则先把它作为master

     advert_int 1

     nopreempt   #不主动抢占资源,设置非抢占模式                                   

     authentication {

         auth_type PASS

         auth_pass 1111

     }

track_script

    {

        check_mysql

    }

     virtual_ipaddress {

         192.168.1.180/24  #vip

     }

}

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

(2) 修改UMP2 keepalived配置文件

#mkdir /etc/keepalived/

#cp /usr/local/keepalived/etc/keepalived/keepalived.conf  /etc/keepalived/

#vim  /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

     router_id HA_MySQL_2

}

vrrp_script check_mysql   #vrrp脚本命名

{

        script "/etc/keepalived/bin/mysql.sh" #要执行的脚本

        interval 10  #脚本指定间隔,秒

        weight -50

#优先级(如果脚本执行结果为0,并且weight配置的值大于0,则优先级相应的增加,如果脚本执行结果非0,并且weight配置的值小于0,则优先级相应的减少)

        fall   3     #尝试三次都成功才成功

        rise   3     #尝试三次都失败才失败

}

vrrp_instance VI_1 {

     state BACKUP

     interface br0

     virtual_router_id 51

     priority 90      #优先级,这个低一点

     advert_int 1

     nopreempt

     authentication {

         auth_type PASS

         auth_pass 1111

     }

     virtual_ipaddress { #虚拟IP (VIP)

         192.168.1.180/24

     }

}

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

16. mysql宕掉后,keepalived自动停止的脚本

vim /etc/keepalived/bin/mysql.sh

脚本内容如下:

#!/bin/bash

MYSQL=/usr/bin/mysql                        #mysql的位置

MYSQL_HOST=localhost                    

MYSQL_USER=root                           #mysql用户

MYSQL_PASSWORD=123456                   #mysql密码

CHECK_TIME=3

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

MYSQL_OK=1

function check_mysql_health (){

    $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_health

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

 

17. 启动keepalive服务

1)检查相关的内核参数

开启IP转发功能

#sysctl -a | grep  net.ipv4.ip_forward

net.ipv4.ip_forward = 1

开启允许绑定非本机的IP   
#sysctl -a | grep  net.ipv4.ip_nonlocal_bind = 1    

注:如未开启,即:

net.ipv4.ip_forward = 0

net.ipv4.ip_nonlocal_bind = 0

开启的方法为:

#sysctl  -w  net.ipv4.ip_forward=1

#sysctl  -w net.ipv4.ip_nonlocal_bind=1

如使用LVS的DR或者TUN模式结合Keepalived需要在后端服务器上,特别设置两个arp相关的参数。
#sysctl  -w  net.ipv4.conf.lo.arp_ignore=1
#sysctl  -w  net.ipv4.conf.lo.arp_announce=2
#sysctl  -w  net.ipv4.conf.all.arp_ignore=1
#sysctl  -w  net.ipv4.conf.all.arp_announce=2

 

2)启动keepalived

#/usr/local/keepalive/sbin/keepalived  -D

日志在/var/log/messages,如有报错可以查看排查

 

3)测试keepalived的虚拟IP

连接虚拟ip ,先查看下虚拟ip有没有,在设置的priority值高,也就是主机的那台机器上,这里是UMP1机器,调用如下命令ip  a

 MYsql主主+keepalived高可用

 

 

停止APP1机器的mysql会发现APP2上的br0网卡会出现虚拟IP 192.168.1.180

 MYsql主主+keepalived高可用

 

ps:需要重新启动mysql以及keepalived才可以切换,如下:

 MYsql主主+keepalived高可用

 

 

4)配置keepalived开机自启动

#vim /etc/init.d/boot.local添加

/usr/local/keepalived/sbin/keepalived

 

问题:ip_vs与内核升级后的问题