MySQL笔记-高可用方案

MySQL笔记-高可用方案

目录

一、概述

MYSQL高可用方案有多种，本次针对其中部分方案进行实践。包括主从，双主，myql+keepalived, mysql+mycat+keepalived。

纸上得来终觉浅，亲自实验一下。

环境信息

注意两台机器时间需要保持同步，网络和端口权限
临时关闭防火墙：systemctl stop firewalld.service

服务器环境:CentOS7.6
MySQL版本：mysql-community-server-5.7.28-1

mysql, master：192.168.1.100
mysql, slave：192.168.1.101

二、主从复制(一主一从)

2.1、配置参数(my.cnf)

注意修改后需要重启数据库服务

mysql主从复制重要配置参数说明(my.cnf)

#通用重要配置

server-id=1 # 不能和其他重复，必须全局唯一
log-bin=mysql-bin


#从库重要配置
relay_log=mysql-relay-bin
log-slave-updates=1

#其他重要参数
binlog-ignore-db=mysql
replicate-ignore-db=mysql
max_binlog_size=1G
expire_logs_days=15 #日志保留天数

我的Master完整配置(my.cnf)

[mysqld]
server-id=1
#binlog_format=ROW
#log-bin=mysql-bin
log-bin=/var/lib/mysql/mysql-bin

#innodb_flush_log_at_trx_commit=1
#sync_binlog=1

max_binlog_size=1G
expire_logs_days=15 #日志保留天数

binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

# 只同步哪些数据库，除此之外，其他不同步  
# binlog-do-db=testdb

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

max_connections=3000
lower_case_table_names=1

max_allowed_packet=40M
wait_timeout=1800
interactive_timeout=1800
max_connect_errors=3000
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

[mysql]
default-character-set=utf8mb4

[client]
default-character-set=utf8mb4

[mysqldump]
user=local
password=123456

我的Slave完整配置(my.cnf)

[mysqld]
server-id=2
#log-bin=mysql-bin
log-bin=/var/lib/mysql/mysql-bin

log-slave-updates=1 # 在作为从数据库的时候，有写入操作也要更新二进制日志文件(主主配置一定要)

#innodb_flush_log_at_trx_commit=1
#sync_binlog=1

max_binlog_size=1G
expire_logs_days=15 #日志保留天数

# 不同步mysql，information_schema，performance_schema和sys库
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys

relay_log=/var/lib/mysql/mysql-relay-bin
#read_only=1

#Enhanced Multi-Threaded Slave配置
#根据实际情况决定开启多少个线程用于主从复制
slave_parallel_workers=4 
#基于组提交的并行复制方式
slave_parallel_type=LOGICAL_CLOCK
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

max_connections=3000
lower_case_table_names=1

max_allowed_packet=40M
wait_timeout=1800
interactive_timeout=1800
max_connect_errors=3000
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

[mysql]
default-character-set=utf8mb4

[client]
default-character-set=utf8mb4

[mysqldump]
user=local
password=123456

2.2、Master数据库，创建复制用户并授权

mysql> create user repl identified by '123456';
Query OK, 0 rows affected (0.03 sec)

mysql> grant replication slave on *.* to repl@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)

mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000001 |     1078 |              | mysql,information_schema,performance_schema,sys |                   |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)

2.3、Slave数据库，指定Master及启用slave

mysql> change master to master_host='192.168.1.100',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=1078;
Query OK, 0 rows affected, 2 warnings (0.03 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: 192.168.1.100
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1078
               Relay_Log_File: 0002-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: mysql,information_schema,performance_schema,sys
           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: 1078
              Relay_Log_Space: 526
              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: 77f466ff-926e-11ea-bbba-408d5c963463
             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.3、验证主从复制

主库创建测试库、表和数据

mysql> CREATE DATABASE testdb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE TABLE `testdb`.`t_user`(`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) NULL,PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.21 sec)

mysql> INSERT INTO `testdb`.`t_user`(`name`) VALUES ('jack');
Query OK, 1 row affected (0.07 sec)

mysql> select * from `testdb`.`t_user`;
+----+------+
| id | name |
+----+------+
|  1 | jack |
+----+------+
1 row in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000001 |     1813 |              | mysql,information_schema,performance_schema,sys |                   |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)

从库查看同步情况

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
          Read_Master_Log_Pos: 1813
               Relay_Log_File: 0002-relay-bin.000002
                Relay_Log_Pos: 1055
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 1813
              Relay_Log_Space: 1261
...

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from `testdb`.`t_user`;
+----+------+
| id | name |
+----+------+
|  1 | jack |
+----+------+
1 row in set (0.00 sec)

三、双主配置

3.1 配置说明

实验环境：
服务器环境:CentOS7.6
MySQL版本：mysql-community-server-5.7.28-1

mysql,master1：192.168.1.100
mysql,master2：192.168.1.101


双主与一主一从，配置差不多，区别是：
1.两台机器都配置对方为从，即
在master1上执行
change master to master_host='192.168.1.101',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=1098;

在master2上执行
change master to master_host='192.168.1.100',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=1098;

2.两方都配置replicate-ignore-db，binlog-ignore-db
3.为保险起见，调整两方自增ID规则（非必须）；把两个节点的auto_increment_increment（自增步长）和auto_increment_offset（字增起始值）设置成不同值，其目的是为了避免master节点意外宕机时，可能会有部分binlog未能及时复制到slave上被应用，从而会导致slave新写入数据的自增值和原master上冲突，因此一开始就错开；

#masterA自增长ID

auto_increment_offset=1
auto_increment_increment=2 #奇数ID

#masterB自增加ID

auto_increment_offset=2
auto_increment_increment=2 #偶数ID

3.2 完整配置(my.cnf)，master1

[mysqld]
server-id=1
#binlog_format=ROW
#log-bin=mysql-bin
log-bin=/var/lib/mysql/mysql-bin

#innodb_flush_log_at_trx_commit=1
#sync_binlog=1

max_binlog_size=1G

#日志保留天数
expire_logs_days=15

binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

# 不同步mysql，information_schema，performance_schema和sys库
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys

auto_increment_offset=1
#奇数ID
auto_increment_increment=2

# 只同步哪些数据库，除此之外，其他不同步  
# binlog-do-db=testdb

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

max_connections=3000
lower_case_table_names=1

max_allowed_packet=40M
wait_timeout=1800
interactive_timeout=1800
max_connect_errors=3000
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

[mysql]
default-character-set=utf8mb4

[client]
default-character-set=utf8mb4

[mysqldump]
user=local
password=123456

3.2 完整配置(my.cnf)，master2

[mysqld]
server-id=2
#log-bin=mysql-bin
log-bin=/var/lib/mysql/mysql-bin

log-slave-updates=1 # 在作为从数据库的时候，有写入操作也要更新二进制日志文件(主主配置一定要)

#innodb_flush_log_at_trx_commit=1
#sync_binlog=1

max_binlog_size=1G
#日志保留天数
expire_logs_days=15

binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

# 不同步mysql，information_schema，performance_schema和sys库
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys

auto_increment_offset=2
#偶数ID
auto_increment_increment=2

relay_log=/var/lib/mysql/mysql-relay-bin
#read_only=1

#Enhanced Multi-Threaded Slave配置
#根据实际情况决定开启多少个线程用于主从复制
slave_parallel_workers=4 
#基于组提交的并行复制方式
slave_parallel_type=LOGICAL_CLOCK
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

max_connections=3000
lower_case_table_names=1

max_allowed_packet=40M
wait_timeout=1800
interactive_timeout=1800
max_connect_errors=3000
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

[mysql]
default-character-set=utf8mb4

[client]
default-character-set=utf8mb4

[mysqldump]
user=local
password=123456

3.3 配置同步

#master1 (192.168.1.100):
mysql> create user repl identified by '123456';
mysql> grant replication slave on *.* to repl@'%' identified by '123456';
mysql> change master to master_host='192.168.1.101',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=1098;
mysql> start slave;


#master1 (192.168.1.101):
mysql> create user repl identified by '123456';
mysql> grant replication slave on *.* to repl@'%' identified by '123456';
mysql> change master to master_host='192.168.1.100',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=1098;
mysql> start slave;

3.4 验证双主

# 连接 master1 (192.168.1.100):

mysql> CREATE DATABASE testdb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
mysql> CREATE TABLE `testdb`.`t_user`(`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) NULL,PRIMARY KEY (`id`));

mysql> INSERT INTO `testdb`.`t_user`(`name`) VALUES ('jack');
mysql> INSERT INTO `testdb`.`t_user`(`name`) VALUES ('rose');

# 连接 master2 (192.168.1.101):
mysql> select * from `testdb`.`t_user`;
+----+------+
| id | name |
+----+------+
|  1 | jack |
|  3 | rose |
+----+------+

INSERT INTO `testdb`.`t_user`(`name`) VALUES ('tony');
INSERT INTO `testdb`.`t_user`(`name`) VALUES ('alice');

# 连接 master1 (192.168.1.100):
mysql> select * from `testdb`.`t_user`;
+----+-------+
| id | name  |
+----+-------+
|  1 | jack  |
|  3 | rose  |
|  4 | tony  |
|  6 | alice |
+----+-------+
4 rows in set (0.00 sec)

至此，双主环境配置和验证完毕

四、基于Keeplived配置双主高可用

4.1、实验环境

实验环境：
基于上述双主配置，

服务器环境:CentOS7.6
MySQL版本：mysql-community-server-5.7.28-1

mysql,master1：192.168.1.100
mysql,master2：192.168.1.101
虚拟mysql-IP(VIP),192.168.1.102


验证方式：
通过虚拟IP(192.168.1.102:3306)连接访问mysql,然后插入记录，期望记录ID为奇数；
然后关闭master1,期望通过虚拟IP(192.168.1.102:3306)仍能连接访问MYSQL服务；
再插入记录，期望记录ID为偶数。

4.2、Keepalived安装

一、yum安装方式：
yum install keepalived -y


二、编译安装方式
# yum -y install gcc openssl-devel libnl libnl-devel libnfnetlink-devel net-tools
# cd /usr/local/src/
# wget https://www.keepalived.org/software/keepalived-2.0.20.tar.gz
# mv keepalived-2.0.20 /usr/local/keepalived
# cd /usr/local/keepalived/
# ./configure 
# make && make install
# mkdir /etc/keepalived
# cp /usr/local/keepalived/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
# cp /usr/local/keepalived/keepalived/etc/init.d/keepalived  /etc/rc.d/init.d/
# cp /usr/local/keepalived/keepalived/etc/sysconfig/keepalived  /etc/sysconfig/
# cp /usr/local/sbin/keepalived  /usr/sbin/
安装参考： CentOS安装Keepalived  https://www.cnblogs.com/kingsonfu/p/11392470.html

服务启动和暂停
# service keepalived start 启动服务
# service keepalived stop //停止服务
# service keepalived status //查看服务状态


查看keepalived日志
# tail -f /var/log/message

4.3 mysql-keepalived配置

采用keepalived作为高可用方案时，两个节点最好都设置成BACKUP模式，避免因为意外情况下相互抢占导致两个节点内写入相同的数据而引发冲突；

master1配置(/etc/keepalived/keepalived.conf)

! Configuration File for keepalived 
 
global_defs { 
   notification_email { 
     acassen@firewall.loc 
     failover@firewall.loc 
     sysadmin@firewall.loc 
   } 
   notification_email_from Alexandre.Cassen@firewall.loc 
   smtp_server 127.0.0.1 
   smtp_connect_timeout 30 
   router_id MYSQL_HA 
}
vrrp_instance VI_1 { 
    state BACKUP 
    interface p4p1  
    virtual_router_id 51  
    priority 100    
    advert_int 1   
    authentication { 
        auth_type PASS 
        auth_pass 1111 
    } 
    virtual_ipaddress { 
        192.168.1.102/24 
    } 
} 
virtual_server 192.168.1.102 3306 { 
    delay_loop 6 
    protocol TCP 
    real_server 192.168.1.100 3306 { 
        notify_down "kill -9 $(cat /var/run/keepalived.pid)" 
        TCP_CHECK { 
            connect_port 3306  
            connect_timeout 3  
            nb_get_retry 3  
            delay_before_retry 3  
        } 
    } 
}

master2配置(/etc/keepalived/keepalived.conf)

! Configuration File for keepalived 
 
global_defs { 
   notification_email { 
     acassen@firewall.loc 
     failover@firewall.loc 
     sysadmin@firewall.loc 
   } 
   notification_email_from Alexandre.Cassen@firewall.loc 
   smtp_server 127.0.0.1 
   smtp_connect_timeout 30 
   router_id MYSQL_HA 
}
vrrp_instance VI_1 { 
    state BACKUP 
    interface p4p1  
    virtual_router_id 51 
    priority 100    
    advert_int 1   
    authentication { 
        auth_type PASS 
        auth_pass 1111 
    } 
    virtual_ipaddress { 
        192.168.1.102/24 
    } 
} 
virtual_server 192.168.1.102 3306 { 
    delay_loop 6 
    protocol TCP 
    real_server 192.168.1.101 3306 { 
        notify_down "kill -9 $(cat /var/run/keepalived.pid)" 
        TCP_CHECK { 
            connect_port 3306  
            connect_timeout 3  
            nb_get_retry 3  
            delay_before_retry 3  
        } 
    } 
}

4.4、验证

-

五、基于Keepalived+MyCat配置双主高可用

5.1、实验环境

实验环境：
基于上述双主配置环境，

服务器环境:CentOS7.6
MySQL版本：mysql-community-server-5.7.28-1
MyCat版本：Mycat-server-1.6.7.4-release-20200105164103-linux

mysql, master1：192.168.1.100
mysql, master2：192.168.1.101
mycat, mycat1: 192.168.1.102

5.2、安装配置MyCat

wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
cd mycat

5.3、MyCat配置(最简)

mycat主要配置 server.xml和schema.xml两个即可。

这里需要注意：相应mysql用户需要能其他服务器可访问

-- 默认root用户，其他机器无法访问 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456';

/conf/server.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
	<user name="root">
		<property name="password">123456</property>
		<property name="schemas">testdb</property>
	</user>
</mycat:server>

/conf/schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="testdb" checkSQLschema="false" sqlMaxLimit="100" dataNode ="dn1"></schema>
    <dataNode name="dn1" dataHost="node1" database="testdb" />
    <dataHost name="node1" maxCon="1000" minCon="10" balance="1" writeType="0" switchType="1"
        dbType="mysql" dbDriver="native">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="192.168.1.100:3306" user="root" password="123456">
        </writeHost>

        <writeHost host="hostM2" url="192.168.1.101:3306" user="root" password="123456">
        </writeHost>
    </dataHost>
</mycat:schema>

dataHost标签上属性释义：

balance:负载均衡类型

  • 0：不开启读写分离机制，所有读操作都发送到当前可用的writeHost上
  • 1：全部的readHost与stand by writeHost参与select语句的负载均衡，
  • 2：所有读操作都随机在writeHost、readHost上分发
  • 3：所有读请求随机分发到writeHost对应的readHost执行，writeHost不负担读压力

writeType:负载均衡类型

  • 0：所有写操作发送到配置的第一个writeHost，当第一个writeHost宕机时，切换到第二个writeHost，重新启动后以切换后的为准，切换记录在配置文件：dnindex.properties中
  • 1：所有写操作都随发送到配置的writeHost
  • 2：尚未实现

switchType:切换方式

  • -1：不自动切换
  • 1：自动切换（默认）
  • 2：基于MySql主从同步的状态来决定是否切换

5.4、启动MyCat

./bin/mycat start

查看日志
logs目录下 有 mycat.log switch.log wrapper.log

5.5、验证MyCat

验证方式：
通过mycat端口8066连接mysql, 插入记录，期望记录ID为奇数；
然后停掉192.168.1.100上mysql服务,然后再插入记录，期望记录ID为偶数；



第一步：插入3条记录
mysql> INSERT INTO `testdb`.`t_user`(`name`) VALUES ('mycat1');
mysql> INSERT INTO `testdb`.`t_user`(`name`) VALUES ('mycat2');
mysql> INSERT INTO `testdb`.`t_user`(`name`) VALUES ('mycat3');
mysql> select * from `testdb`.`t_user`;
+----+--------+
| id | name   |
+----+--------+
|  1 | jack   |
|  3 | rose   |
|  4 | tony   |
|  6 | alice  |
|  7 | mycat1 |
|  9 | mycat2 |
| 11 | mycat3 |
+----+--------+
7 rows in set (0.00 sec)
ID都是奇数，说明请求在master1上

第二步：停止master1,再插入3条记录
mysql> INSERT INTO `testdb`.`t_user`(`name`) VALUES ('mycat4');
mysql> INSERT INTO `testdb`.`t_user`(`name`) VALUES ('mycat5');
mysql> INSERT INTO `testdb`.`t_user`(`name`) VALUES ('mycat6');

mysql> select * from `testdb`.`t_user`;
+----+--------+
| id | name   |
+----+--------+
|  1 | jack   |
|  3 | rose   |
|  4 | tony   |
|  6 | alice  |
|  7 | mycat1 |
|  9 | mycat2 |
| 11 | mycat3 |
| 12 | mycat4 |
| 14 | mycat5 |
| 16 | mycat6 |
+----+--------+
10 rows in set (0.00 sec)

ID都是偶数，说明请求都在master2

至此MyCat验证完毕

5.7、通过Keepalived配置MyCat高可用

实验环境：
服务器环境:CentOS7.6
MySQL版本：mysql-community-server-5.7.28-1
MyCat版本：Mycat-server-1.6.7.4-release-20200105164103-linux

mysql, master1：192.168.1.100
mysql, master2：192.168.1.101
mycat, mycat1: 192.168.1.103
mycat, mycat2: 192.168.1.104
虚拟mycat-IP(VIP): 192.168.1.105 

基于上面mysql双主和mycat配置(mycat1,mycat2配置完全相同)

验证方式：
通过连接 192.168.1.105:8066 访问MySQL服务，通过切换关闭mycat1、mycat2服务，验证mycat的高可用性。

5.8、附，mycat的keepalived配置

mycat1-keepalived配置，(/etc/keepalived/keepalived.conf)

! Configuration File for keepalived 
 
global_defs { 
   notification_email { 
     acassen@firewall.loc 
     failover@firewall.loc 
     sysadmin@firewall.loc 
   } 
   notification_email_from Alexandre.Cassen@firewall.loc 
   smtp_server 127.0.0.1 
   smtp_connect_timeout 30 
   router_id MYCAT_HA 
}
vrrp_instance VI_1 { 
    state MASTER 
    interface p4p1  
    virtual_router_id 53  
    priority 100    
    advert_int 1   
    authentication { 
        auth_type PASS 
        auth_pass 1111 
    } 
    virtual_ipaddress { 
        192.168.1.105/24 
    } 
} 
virtual_server 192.168.1.105 8066 { 
    delay_loop 6 
    protocol TCP 
    real_server 192.168.1.103 8066 { 
        notify_down "kill -9 $(cat /var/run/keepalived.pid)" 
        TCP_CHECK { 
            connect_port 8066  
            connect_timeout 3  
            nb_get_retry 3  
            delay_before_retry 3  
        } 
    } 
}

mycat2-keepalived配置，(/etc/keepalived/keepalived.conf)


! Configuration File for keepalived 
 
global_defs { 
   notification_email { 
     acassen@firewall.loc 
     failover@firewall.loc 
     sysadmin@firewall.loc 
   } 
   notification_email_from Alexandre.Cassen@firewall.loc 
   smtp_server 127.0.0.1 
   smtp_connect_timeout 30 
   router_id MYCAT_HA 
}
vrrp_instance VI_1 { 
    state BACKUP 
    interface p4p1  
    virtual_router_id 53  
    priority 50    
    advert_int 1   
    authentication { 
        auth_type PASS 
        auth_pass 1111 
    } 
    virtual_ipaddress {
        192.168.1.105/24 
    } 
} 
virtual_server 192.168.1.105 8066 { 
    delay_loop 6 
    protocol TCP 
    real_server 192.168.1.104 8066 { 
        notify_down "kill -9 $(cat /var/run/keepalived.pid)" 
        TCP_CHECK { 
            connect_port 8066  
            connect_timeout 3  
            nb_get_retry 3  
            delay_before_retry 3  
        } 
    } 
}

六、双主双从读写分离

<!--双主双从，每个mysql主都跟着一个从，基于mycat的高可用配置，配置相似，这里仅贴出mycat的schema.xml配置示例 -->

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="testdb" checkSQLschema="false" sqlMaxLimit="100" dataNode ="dn1"></schema>
    <dataNode name="dn1" dataHost="node1" database="testdb" />
    <dataHost name="node1" maxCon="1000" minCon="10" balance="1" writeType="0" switchType="1"
        dbType="mysql" dbDriver="native">
        <heartbeat>select user()</heartbeat>

        <writeHost host="hostM1" url="192.168.1.100:3306" user="root" password="123456">
            <readHost host="hostS1" url="192.168.1.106:3306" user="root" password="123456"/>
        </writeHost>

        <writeHost host="hostM2" url="192.168.1.101:3306" user="root" password="123456">
            <readHost host="hostS2" url="192.168.1.107:3306" user="root" password="123456"/>
        </writeHost>
    </dataHost>
</mycat:schema>

七、附，其他一些配置参考

mycat-server.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
	<!--<system>
		<property name="nonePasswordLogin">0</property>
		<property name="useHandshakeV10">1</property>
		<property name="useSqlStat">0</property>
		<property name="useGlobleTableCheck">0</property>
		<property name="sequnceHandlerType">2</property>
		<property name="subqueryRelationshipCheck">false</property>
		<property name="processorBufferPoolType">0</property>
		<property name="serverPort">8066</property>
		<property name="managerPort">9066</property> 
		<property name="handleDistributedTransactions">0</property>
		<property name="useOffHeapForMerge">1</property>
		<property name="memoryPageSize">64k</property>
		<property name="spillsFileBufferSize">1k</property>
		<property name="useStreamOutput">0</property>
		<property name="systemReserveMemorySize">384m</property>
		<property name="useZKSwitch">false</property>
	</system>-->
	<user name="root">
		<property name="password">123456</property>
		<property name="schemas">testdb</property>
	</user>
	<!--<user name="user">
		<property name="password">123456</property>
		<property name="schemas">testdb1,testdb2</property>
		<property name="readOnly">true</property>
	</user>-->
</mycat:server>

keepalived.conf

<!--keepalived.conf-->
! Configuration File for keepalived

global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id web1
   vrrp_skip_check_adv_addr
#   vrrp_strict
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}

vrrp_script check_mycat {
    script "/etc/keepalived/mycat_check.sh"
    interval 2
    weight 2
}

vrrp_instance VI_1 {  //实例，定义虚拟路由
    state MASTER      //节点的初始状态，主为master，备为backup
    interface ens160  //绑定虚拟ip的网络接口，必须与本地ip所在接口相同
    virtual_router_id 51  //虚拟路由id号，两个节点必须一样
    priority 100      //节点优先级，master要比backup高
    advert_int 1      //两个节点发送组播信息间隔时间，主备需要设置相同的时间，默认为秒
    authentication {  //配置认证
        auth_type PASS  //PASS即为密码认证
        auth_pass 1111  //设置密码，可自定义
    }
    track_script  {
        check_mycat
    }
    virtual_ipaddress { //设置虚拟ip（VIP），两个节点需要一样
        192.168.1.105
    }
}


------------------
mycat_check.sh

#!/bin/bash
result=`nc -v -z $1 $2`
flag="succeeded"
if [[ $result =~ $flag ]]
then
         exit 0
else;
         exit 1
fi

mycat_check.sh

#!/bin/bash
result=`nc -v -z $1 $2`
flag="succeeded"
if [[ $result =~ $flag ]]
then
         exit 0
else;
         exit 1
fi

八、其他MySQL高可用方案

1.MGR(MySQL Group Replication、Galera Cluster，MySQL Cluster、PXC(Percona XtraDB Cluster)
2.客户端解决方案（应用层）：TDDL、 Sharding-Jdbc (常用shardding-jdbc)
3.中间件解决方案（代理层）：mysql proxy、mycat、altas ,ProxySQL
4.Canal，Otter

九、相关参考

posted @ 2020-05-24 18:07  一片相思林  阅读(321)  评论(0)  编辑  收藏  举报