MySQL Utilities 高可用工具体验

本文涉及的产品
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: MySQL Utilities 高可用工具体验 MySQL Utilities是MySQL官方的工具集,其中包括高可用相关的几个工具。 以下是对当前最新版本1.6的使用体验。

MySQL Utilities 高可用工具体验

MySQL Utilities是MySQL官方的工具集,其中包括高可用相关的几个工具。 以下是对当前最新版本1.6的使用体验。

前提条件

  • MySQL Server 5.6+
  • 基于GTID的复制
  • Python 2.6+
  • Connector/Python 2.0+

环境准备

在1台机器准备3个不同端口的MySQL实例用于测试

  • 192.168.107.211:9001(master)
  • 192.168.107.211:9002(slave1)
  • 192.168.107.211:9003(slave2)

软件

  • OS: CentOS 7.1
  • MySQL: Percona Server 5.7.19
  • Python: 2.7.5
  • Connector/Python:2.1.7
  • mysql-utilities:1.6.5

创建MySQL实例1

生成实例1的配置文件my1.cnf

su - mysql
vi my1.cnf
[mysqld]
port=9001
datadir=/var/lib/mysql/data1
socket=/var/lib/mysql/data1/mysql.sock
basedir=/usr/

innodb_buffer_pool_size=128M
explicit_defaults_for_timestamp
skip-name-resolve
lower-case-table-names
expire-logs-days=7
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_wait_point=AFTER_SYNC
rpl_semi_sync_master_wait_no_slave=ON
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_slave_enabled=ON
rpl_semi_sync_master_timeout=5000

server-id=9001
log_bin=binlog
gtid-mode=ON
enforce-gtid-consistency=ON
log-slave-updates=ON
master-info-repository=TABLE
relay-log-info-repository=TABLE
report-host=192.168.107.211

log-error=/var/lib/mysql/data1/mysqld.log
pid-file=/var/lib/mysql/data1/mysqld.pid
general-log=ON
general-log-file=/var/lib/mysql/data1/node1.log

[mysqld_safe]
pid-file=/var/lib/mysql/data1/mysqld.pid
socket=/var/lib/mysql/data1/mysql.sock
nice     = 0 

创建MySQL实例

mysqld --defaults-file=my1.cnf  --initialize-insecure
mysqld --defaults-file=my1.cnf &
mysql -S data1/mysql.sock -uroot -e "set sql_log_bin=OFF;GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY '12345' WITH GRANT OPTION" 

创建MySQL实例2

sed s/9001/9002/g my1.cnf | sed s/data1/data2/g >my2.cnf
mysqld --defaults-file=my2.cnf  --initialize-insecure
mysqld --defaults-file=my2.cnf &
mysql -S data2/mysql.sock -uroot -e "set sql_log_bin=OFF;GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY '12345' WITH GRANT OPTION" 

创建MySQL实例3

sed s/9001/9003/g my1.cnf | sed s/data1/data3/g >my3.cnf
mysqld --defaults-file=my3.cnf  --initialize-insecure
mysqld --defaults-file=my3.cnf &
mysql -S data3/mysql.sock -uroot -e "set sql_log_bin=OFF;GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY '12345' WITH GRANT OPTION" 

利用mysqlreplicate建立复制

-bash-4.2$ mysqlreplicate --master=admin:12345@192.168.107.211:9001 --slave=admin:12345@192.168.107.211:9002 --rpl-user=repl:repl -v
WARNING: Using a password on the command line interface can be insecure.
# master on 192.168.107.211: ... connected.
# slave on 192.168.107.211: ... connected.
# master id = 9001
#  slave id = 9002
# master uuid = b8ca6259-ab80-11e7-91fc-000c296dd240
#  slave uuid = d842240c-ab80-11e7-960f-000c296dd240
# Checking InnoDB statistics for type and version conflicts.
# Checking storage engines...
# Checking for binary logging on master...
# Setting up replication...
# Granting replication access to replication user...
# Connecting slave to master...
# CHANGE MASTER TO MASTER_HOST = '192.168.107.211', MASTER_USER = 'repl', MASTER_PASSWORD = 'repl', MASTER_PORT = 9001, MASTER_AUTO_POSITION=1
# Starting slave from master's last position...
# IO status: Waiting for master to send event
# IO thread running: Yes
# IO error: None
# SQL thread running: Yes
# SQL error: None
# ...done. 

除去各种检查,mysqlreplicate真正做的事很简单。如下

先在master上创建复制账号

CREATE USER 'repl'@'192.168.107.211' IDENTIFIED WITH 'repl'
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.107.211' IDENTIFIED WITH 'repl' 

mysqlreplicate会为每个Slave创建一个复制账号,除非通过以下SQL发现该账号已经存在。

SELECT * FROM mysql.user WHERE user = 'repl' and host = '192.168.107.211' 

然后在slave上设置复制

CHANGE MASTER TO MASTER_HOST = '192.168.107.211', MASTER_USER = 'repl', MASTER_PASSWORD = 'repl', MASTER_PORT = 9001, MASTER_AUTO_POSITION=1 

在启用GTID的情况的下,从哪儿开始复制完全由GTID决定,所以mysqlreplicate中的那些和复制起始位点相关的参数,比如-b,统统被无视,其效果相当于-b。

注意:mysqlreplicate不会理会当前的复制拓扑,所以如果把master和slave对调再执行一次,就变成主主复制了。

slave1的复制配置好后,用同样的方法配置slave2的复制

mysqlreplicate --master=admin:12345@192.168.107.211:9001 --slave=admin:12345@192.168.107.211:9003 --rpl-user=repl:repl -v 

通过mysqlrplshow查看复制拓扑

-bash-4.2$ mysqlrplshow --master=admin:12345@192.168.107.211:9001 --discover-slaves-login=admin:12345 -v 
WARNING: Using a password on the command line interface can be insecure.
# master on 192.168.107.211: ... connected.
# Finding slaves for master: 192.168.107.211:9001

# Replication Topology Graph
192.168.107.211:9001 (MASTER)
   |
   +--- 192.168.107.211:9002 [IO: Yes, SQL: Yes] - (SLAVE)
   |
   +--- 192.168.107.211:9003 [IO: Yes, SQL: Yes] - (SLAVE) 

mysqlrplshow通过在master上执行SHOW SLAVE HOSTS发现初步的复制拓扑。 由于Slave停止复制或改变复制源时不能立刻反应到master的SHOW SLAVE HOSTS上,所以初步获取的复制拓扑可能存在冗余, 因此,mysqlrplshow还会再连到slave上执行SHOW SLAVE STATUS进行确认。

通过mysqlrpladmin检查集群健康状态

-bash-4.2$ mysqlrpladmin --master=admin:12345@192.168.107.211:9001 --slaves=admin:12345@192.168.107.211:9002,admin:12345@192.168.107.211:9003 health
WARNING: Using a password on the command line interface can be insecure.
# Checking privileges.
#
# Replication Topology Health:
+------------------+-------+---------+--------+------------+---------+
| host             | port  | role    | state  | gtid_mode  | health  |
+------------------+-------+---------+--------+------------+---------+
| 192.168.107.211  | 9001  | MASTER  | UP     | ON         | OK      |
| 192.168.107.211  | 9002  | SLAVE   | UP     | ON         | OK      |
| 192.168.107.211  | 9003  | SLAVE   | UP     | ON         | OK      |
+------------------+-------+---------+--------+------------+---------+
# ...done. 

通过mysqlrpladmin elect挑选合适的新主

-bash-4.2$ mysqlrpladmin --master=admin:12345@192.168.107.211:9001 --slaves=admin:12345@192.168.107.211:9002,admin:12345@192.168.107.211:9003 elect
WARNING: Using a password on the command line interface can be insecure.
# Checking privileges.
# Electing candidate slave from known slaves.
# Best slave found is located on 192.168.107.211:9002.
# ...done. 

然而,elect只是从slaves中选出第一个合格的slave,并不考虑复制是否已停止,以及哪个节点的日志更全。

下面把slave1的复制停掉

mysql -S data2/mysql.sock -uroot -e "stop slave" 

再在master执行一条SQL

mysql -S data1/mysql.sock -uroot -e "create database test" 

现在slave1上少了一个事务

-bash-4.2$ mysqlrpladmin --master=admin:12345@192.168.107.211:9001 --slaves=admin:12345@192.168.107.211:9002,admin:12345@192.168.107.211:9003 gtid
WARNING: Using a password on the command line interface can be insecure.
# Checking privileges.
#
# UUIDS for all servers:
+------------------+-------+---------+---------------------------------------+
| host             | port  | role    | uuid                                  |
+------------------+-------+---------+---------------------------------------+
| 192.168.107.211  | 9001  | MASTER  | 5daf1e10-ac41-11e7-bcc4-000c296dd240  |
| 192.168.107.211  | 9002  | SLAVE   | fe084f45-ac43-11e7-a343-000c296dd240  |
| 192.168.107.211  | 9003  | SLAVE   | d0af3a6a-ac41-11e7-85e0-000c296dd240  |
+------------------+-------+---------+---------------------------------------+
#
# Transactions executed on the server:
+------------------+-------+---------+-------------------------------------------+
| host             | port  | role    | gtid                                      |
+------------------+-------+---------+-------------------------------------------+
| 192.168.107.211  | 9001  | MASTER  | 5daf1e10-ac41-11e7-bcc4-000c296dd240:1-3  |
| 192.168.107.211  | 9002  | SLAVE   | 5daf1e10-ac41-11e7-bcc4-000c296dd240:1-2  |
| 192.168.107.211  | 9003  | SLAVE   | 5daf1e10-ac41-11e7-bcc4-000c296dd240:1-3  |
+------------------+-------+---------+-------------------------------------------+
# ...done. 

但elect仍然会选slave1

-bash-4.2$ mysqlrpladmin --master=admin:12345@192.168.107.211:9001 --slaves=admin:12345@192.168.107.211:9002,admin:12345@192.168.107.211:9003 elect
WARNING: Using a password on the command line interface can be insecure.
# Checking privileges.
# Electing candidate slave from known slaves.
# Best slave found is located on 192.168.107.211:9002.
# ...done. 

通过mysqlrpladmin switchover在线切换主备

-bash-4.2$ mysqlrpladmin --master=admin:12345@192.168.107.211:9001 --slaves=admin:12345@192.168.107.211:9002,admin:12345@192.168.107.211:9003 --new-master=admin:12345@192.168.107.211:9002 switchover
WARNING: Using a password on the command line interface can be insecure.
# Checking privileges.
# Performing switchover from master at 192.168.107.211:9001 to slave at 192.168.107.211:9002.
# Checking candidate slave prerequisites.
# Checking slaves configuration to master.
# Waiting for slaves to catch up to old master.
Slave 192.168.107.211:9002 did not catch up to the master.
ERROR: Slave 192.168.107.211:9002 did not catch up to the master. 

switchover会连接到每一个节点并等待所有slave回放完日志才执行切换,因此有任何一个节点故障或任何一个slave复制故障都不会执行switchover。

启动刚才停掉的slave1的复制

mysql -S data2/mysql.sock -uroot -e "start slave" 

再次执行switchover,成功

-bash-4.2$ mysqlrpladmin --master=admin:12345@192.168.107.211:9001 --slaves=admin:12345@192.168.107.211:9002,admin:12345@192.168.107.211:9003 --new-master=admin:12345@192.168.107.211:9002 --demote-master switchover
WARNING: Using a password on the command line interface can be insecure.
# Checking privileges.
# Performing switchover from master at 192.168.107.211:9001 to slave at 192.168.107.211:9002.
# Checking candidate slave prerequisites.
# Checking slaves configuration to master.
# Waiting for slaves to catch up to old master.
# Stopping slaves.
# Performing STOP on all slaves.
# Demoting old master to be a slave to the new master.
# Switching slaves to new master.
# Starting all slaves.
# Performing START on all slaves.
# Checking slaves for errors.
# Switchover complete.
#
# Replication Topology Health:
+------------------+-------+---------+--------+------------+---------+
| host             | port  | role    | state  | gtid_mode  | health  |
+------------------+-------+---------+--------+------------+---------+
| 192.168.107.211  | 9002  | MASTER  | UP     | ON         | OK      |
| 192.168.107.211  | 9001  | SLAVE   | UP     | ON         | OK      |
| 192.168.107.211  | 9003  | SLAVE   | UP     | ON         | OK      |
+------------------+-------+---------+--------+------------+---------+
# ...done. 

执行switchover时,有一段Waiting for slaves to catch up to old master.,如果任何一个slave有故障无法同步到和master相同的状态,switchover会失败。即switchover的前提条件是所有节点(包括master和所有salve)都是OK的。

通过mysqlrpladmin failover故障切换主备

-bash-4.2$ mysqlrpladmin --slaves=admin:12345@192.168.107.211:9001,admin:12345@192.168.107.211:9003 failover
WARNING: Using a password on the command line interface can be insecure.
# Checking privileges.
# Performing failover.
# Candidate slave 192.168.107.211:9001 will become the new master.
# Checking slaves status (before failover).
# Preparing candidate for failover.
# Creating replication user if it does not exist.
# Stopping slaves.
# Performing STOP on all slaves.
# Switching slaves to new master.
# Disconnecting new master as slave.
# Starting slaves.
# Performing START on all slaves.
# Checking slaves for errors.
# Failover complete.
#
# Replication Topology Health:
+------------------+-------+---------+--------+------------+---------+
| host             | port  | role    | state  | gtid_mode  | health  |
+------------------+-------+---------+--------+------------+---------+
| 192.168.107.211  | 9001  | MASTER  | UP     | ON         | OK      |
| 192.168.107.211  | 9003  | SLAVE   | UP     | ON         | OK      |
+------------------+-------+---------+--------+------------+---------+
# ...done. 

failover时要求所有slave的SQL线程都是正常的,IO线程可以停止或异常。 如果未指定--candidates,一般会以slaves中第1个slave作为新主。 如果新主的binlog不是最新的,会先向拥有最新日志的slave复制,并等到binlog追平了再切换。

小结

从上面操作过程来看,借助MySQL Utilities管理MySQL集群还比较简便,但结合代码考虑到各种场景,这套工具和MHA比起来还不够严谨。

  1. 没有把从库的READ_ONLY设置集成到脚本里
  2. switchover时没有终止运行中的事务,实际也没有有效的手段阻止新的写事务在旧master上执行。
  3. failover不检查master死活,需要DBA在调用failover前自己检查,否则会引起脑裂。
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
25天前
|
关系型数据库 MySQL
elasticsearch对比mysql以及使用工具同步mysql数据全量增量
elasticsearch对比mysql以及使用工具同步mysql数据全量增量
20 0
|
1月前
|
关系型数据库 MySQL 数据库
rds安装数据库客户端工具
安装阿里云RDS的数据库客户端涉及在本地安装对应类型(如MySQL、PostgreSQL)的客户端工具。对于MySQL,可选择MySQL Command-Line Client或图形化工具如Navicat,安装后输入RDS实例的连接参数进行连接。对于PostgreSQL,可以使用`psql`命令行工具或图形化客户端如PgAdmin。首先从阿里云控制台获取连接信息,然后按照官方文档安装客户端,最后配置客户端连接以确保遵循安全指引。
82 1
|
2月前
|
关系型数据库 MySQL 数据库
rds迁移数据迁移工具选择
rds迁移数据迁移工具选择
67 3
|
2月前
|
监控 容灾 关系型数据库
rds容灾与高可用
rds容灾与高可用
47 6
|
2月前
|
存储 关系型数据库 MySQL
Mysql高可用|索引|事务 | 调优
Mysql高可用|索引|事务 | 调优
|
3月前
|
SQL 容灾 关系型数据库
rds容灾与高可用
rds容灾与高可用
28 4
|
3月前
|
关系型数据库 MySQL
电子好书发您分享《MySQL MGR 8.0高可用实战》
电子好书发您分享《MySQL MGR 8.0高可用实战》 电子好书发您分享《MySQL MGR 8.0高可用实战》
90 1
|
3月前
|
SQL 关系型数据库 MySQL
Mysql高可用,索引,事务与调优:提高数据库性能的关键技术
在当今互联网时代,高可用性、稳定性和性能是数据库的三大关键要素。本文将深入探讨Mysql高可用、索引、事务和调优等方面的技术,为读者提供实用的解决方案和经验。
24 0
|
3月前
|
监控 关系型数据库 MySQL
MySQL高可用与性能优化综述
MySQL作为一种常用的关系型数据库管理系统,高可用性、索引优化、事务处理和性能调优一直是开发人员和运维人员关注的重点。本文将从MySQL高可用性解决方案、索引设计与优化、事务处理最佳实践以及性能调优策略等方面进行探讨,为读者提供全面的MySQL技术知识概览。
|
3月前
|
SQL 缓存 关系型数据库
MySQL高可用与性能优化——从索引到事务
MySQL是一款常用的关系型数据库,但在实际应用中,如何保证其高可用性与性能优化依然是一个值得探讨的话题。本文将从索引的创建和优化、事务的处理以及常见的性能瓶颈入手,为读者提供MySQL高可用与性能优化的实践经验。