mysql 主主+keepalive HA 模式

数据库 waitig 556℃ 百度已收录 0评论

1.安装mysql数据库

2.修改参数文件

主库10.1.8.110

cat /etc/my.cnf

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

datadir=/opt/data

socket=/opt/mysql-5.6.25/mysql.sock

user=mysql

log-error=/opt/mysql-5.6.25/mysql_error.log

pid-file=/opt/data/mysql.pid

server_id =1//主备两台mysql的server_id须不同  

log_bin=mysql-bin

binlog_format=mixed

binlog-ignore-db = mysql,information_schema

 

备库10.1.8.111

cat /etc/my.cnf

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

datadir=/opt/data

socket=/opt/mysql-5.6.25/mysql.sock

user=mysql

log-error=/opt/mysql-5.6.25/mysql_error.log

pid-file=/opt/data/mysql.pid

server_id = 2

log-bin=mysql-bin

binlog_format=mixed

binlog-ignore-db = mysql,information_schema

 

操作主库10.1.8.110

创建replication用户

CREATE USER ‘replication’ IDENTIFIED BY’replication’;

GRANT  REPLICATION SLAVE ON *.* TO ‘replication’@’10.1.8.110’IDENTIFIED  BY ‘replication’;

GRANT  REPLICATION SLAVE ON *.* TO ‘replication’@’10.1.8.111’IDENTIFIED  BY ‘replication’;

GRANT  REPLICATION SLAVE ON *.* TO ‘replication’@’10.1.8.%’IDENTIFIED  BY ‘replication’;

flush privileges; 

 

mysql> change master tomaster_host=’10.1.8.111′,master_user=’replication’,master_password=’replication’,master_log_file=’mysql-bin.000001′,master_log_pos=120;

Query OK, 0 rows affected, 2 warnings (0.01 sec)

 

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show slave status\G

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

              Slave_IO_State: Waiting for master to send event

                 Master_Host: 10.1.8.111

                 Master_User: replication

                 Master_Port: 3306

               Connect_Retry: 60

             Master_Log_File: mysql-bin.000001

         Read_Master_Log_Pos: 120

              Relay_Log_File: mysql-relay-bin.000002

               Relay_Log_Pos: 283

       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:

                  Last_Errno: 0

                  Last_Error:

                 Skip_Counter: 0

         Exec_Master_Log_Pos: 120

             Relay_Log_Space: 456

             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: 2

                 Master_UUID: de413f47-b25a-11e7-a336-001631f3d6d4

             Master_Info_File:/opt/data/master.info

                   SQL_Delay: 0

         SQL_Remaining_Delay: NULL

     Slave_SQL_Running_State: Slave has read all relay log; waiting for theslave I/O thread to update it

          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

1 row in set (0.00 sec)

 

备机器上执行红色字体是处理问题执行的

mysql> stop slave;

Query OK, 0 rows affected (0.03 sec)

 

mysql> reset slave all ; 报错时执行初始化化

Query OK, 0 rows affected (0.08 sec)

 

mysql> reset master; 报错时执行初始化化

Query OK, 0 rows affected (0.09 sec)

 

mysql> show master status;

+——————+———-+————–+————————–+——————-+

| File             |Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |

+——————+———-+————–+————————–+——————-+

| mysql-bin.000001 |     120 |              |mysql,information_schema |                  |

+——————+———-+————–+————————–+——————-+

1 row in set (0.00 sec)

 

mysql> changemaster tomaster_host=’10.1.8.110′,master_user=’replication’,master_password=’replication’,master_log_file=’mysql-bin.000001′,master_log_pos=120;

Query OK, 0 rows affected, 2 warnings (0.14 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: 10.1.8.110

                  Master_User:replication

                 Master_Port: 3306

               Connect_Retry: 60

             Master_Log_File: mysql-bin.000001

         Read_Master_Log_Pos: 120

              Relay_Log_File: mysql-relay-bin.000002

               Relay_Log_Pos: 283

       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:

                  Last_Errno: 0

                  Last_Error:

                Skip_Counter: 0

         Exec_Master_Log_Pos: 120

             Relay_Log_Space: 456

             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: de413f47-b25a-11e7-a336-001631f3d7d4

            Master_Info_File: /opt/data/master.info

                   SQL_Delay: 0

         SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State:Slave has read all relay log; waiting for the slave I/O thread to update it

          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

1 row in set (0.00 sec)

 

 

 

 

 

 

报错内容:“Last_SQL_Errno: 1146

              Last_SQL_Error: Error ‘Table ‘report.hxy’ doesn’t exist’ on query.Default database: ‘report’. Query: ‘insert into hxy values(01,’zhangsan’)’

 Replicate_Ignore_Server_Ids: ‘”,由于从库已经有了一个叫report的数据库,而主库又建立了一个叫report的数据库,由此导致的错误。
修复方式一:

mysql> stop slave;

mysql> set global sql_slave_skip_counter = 1;

mysql> start slave;

 

create database report DEFAULT CHARSET utf8COLLATE utf8_general_ci;

 

yum install openssl-devel

 

安装keepalived

官网http://www.keepalived.org/download.html

 

wget http://www.keepalived.org/software/keepalived-1.3.8.tar.gz

tar –xf keepalived-1.3.8.tar.gz &&cd keepalived-1.3.8

./configure –prefix=/usr/local/keepalived&& make && make install

或者yum -y install keepalived

 

配置

安装完成后,进入安装目录的etc目录下,将keepalived相应的配置文件拷贝到系统相应的目录当中。keepalived启动时会从/etc/keepalived目录下查找keepalived.conf配置文件,如果没有找到则使用默认的配置。/etc/keepalived目录安装时默认是没有安装的,需要手动创建。配置文件目录结构如下所示:

 

[root@namenode01 keepalived-1.3.8]# tree -l/usr/local/keepalived/etc/

/usr/local/keepalived/etc/

├── keepalived

│   ├── keepalived.conf

│   └── samples

│       ├── client.pem

│       ├── dh1024.pem

│       ├── keepalived.conf.fwmark

│       ├── keepalived.conf.HTTP_GET.port

│       ├── keepalived.conf.inhibit

│       ├── keepalived.conf.IPv6

│       ├── keepalived.conf.misc_check

│       ├── keepalived.conf.misc_check_arg

│       ├── keepalived.conf.quorum

│       ├── keepalived.conf.sample

│       ├── keepalived.conf.SMTP_CHECK

│       ├── keepalived.conf.SSL_GET

│       ├── keepalived.conf.status_code

│       ├── keepalived.conf.track_interface

│       ├── keepalived.conf.virtualhost

│       ├── keepalived.conf.virtual_server_group

│       ├── keepalived.conf.vrrp

│       ├── keepalived.conf.vrrp.localcheck

│       ├── keepalived.conf.vrrp.lvs_syncd

│       ├── keepalived.conf.vrrp.routes

│       ├── keepalived.conf.vrrp.rules

│       ├── keepalived.conf.vrrp.scripts

│       ├── keepalived.conf.vrrp.static_ipaddress

│       ├── keepalived.conf.vrrp.sync

│       ├── root.pem

│       ├── sample.misccheck.smbcheck.sh

│       └── sample_notify_fifo.sh

└── sysconfig

└── keepalived

 

mkdir/etc/keepalived

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

 

 

 

 

[root@namenode02mysql-5.6.25]# chmod  -x/etc/keepalived/mysql.sh

[root@namenode02mysql-5.6.25]# cat /etc/keepalived/mysql.sh

#!/bin/bash 

 

MYSQL_OK=1 

 

functioncheck_mysql_helth (){ 

 

    mysql -e "show status;" &>/dev/null 

  

    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 

 

    echo "mysql ok\n" 

 

    CHECK_TIME=0 

 

    exit 0 

 

fi 

 

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

 

then 

 

    pkill keepalived 

 

    exit 1 

 

fi 

 

sleep 1 

 

done

 

[root@namenode02~]# cat /etc/keepalived/keepalived.conf

! ConfigurationFile for keepalived

 

global_defs {

   router_id namenode02 #主机名

}

 

vrrp_instanceVI_1 {

    state BACKUP #两台都设置BACKUP 

    interface enp5s0f0 //网卡接口用ifconfig查看

    virtual_router_id 51  #主备相同

    priority 90 #优先级,master设置100 

    advert_int 1

#    nopreempt  #不主动抢占资源,只在master这台优先级高的设置,backup不设置 

    authentication {

        auth_type PASS

        auth_pass 1111

    }

 

    virtual_ipaddress {

        10.1.8.113

    }

}

 

virtual_server10.1.8.113 3306 {

     delay_loop 2

     lb_algo wrr

     lb_kind DR

     persistence_timeout 60

     protocol TCP

     real_server 10.1.8.111 3306 {

         weight 3

         notify_down /etc/keepalived/mysql.sh

         TCP_CHECK {

             connect_timeout 10

             nb_get_retry 3

             delay_before_retry 3

             connect_port 3306

         }

     }

}

 

 

 

 

 

[root@datanode10~]# cat /etc/keepalived/keepalived.conf

! ConfigurationFile for keepalived

 

global_defs {

   router_id datanode10

}

 

vrrp_instanceVI_1 {

    state BACKUP

    interface ens4f0

    virtual_router_id 51

    priority 100

    advert_int 1

    nopreempt

    authentication {

        auth_type PASS

        auth_pass 1111

    }

 

    virtual_ipaddress {

        10.1.8.113

    }

}

 

virtual_server10.1.8.113 3306 {

     delay_loop 2

     lb_algo wrr

     lb_kind DR

     persistence_timeout 60

     protocol TCP

     real_server 10.1.8.110 3306 {

         weight 3

         notify_down /etc/keepalived/mysql.sh

         TCP_CHECK {

             connect_timeout 10

             nb_get_retry 3

             delay_before_retry 3

             connect_port 3306

         }

     }

}

 

 测试,停掉一台机器的keepalive进程,看vip10.1.8.113是否能自动漂移

若可以怎没有问题

 

 

 

 


本文由【waitig】发表在等英博客
本文固定链接:mysql 主主+keepalive HA 模式
欢迎关注本站官方公众号,每日都有干货分享!
等英博客官方公众号
点赞 (0)分享 (0)