mysql 主主+keepalive HA 模式

来源：互联网发布：内外网络切换器原理编辑：程序博客网时间：2024/06/05 04:16

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;

+------------------+----------+--------------+--------------------------+-------------------+

+------------------+----------+--------------+--------------------------+-------------------+

+------------------+----------+--------------+--------------------------+-------------------+

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

CHECK_TIME=1

functioncheck_mysql_helth (){

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

if [ $? = 0 ] ;then

MYSQL_OK=1

else

MYSQL_OK=0

return $MYSQL_OK

}

while [$CHECK_TIME -ne 0 ]

let "CHECK_TIME -= 1"

check_mysql_helth

if [ $MYSQL_OK =1 ] ; then

echo "mysql ok\n"

CHECK_TIME=0

exit 0

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

then

pkill keepalived

exit 1

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是否能自动漂移

若可以怎没有问题

阅读全文

0 0