linuxea: 基于Galera cluster的MaxScale 2.4读写分离笔记 在此前的两个版本中，不同于数据库集群大小和数据大小做了不同的线上测试，有一系列问题，最终被下架更换到haproxy。在新的环境中使用了2.4，因为2.4有很多变动。仍然保持热情的进行体验。Galera cluster集群模式中节点都可以作为读写，但是通常而言，会在一台节点进行写入，其他节点读取。因此选择读写分离模块readwritesplit路由模式。readwritesplit读写分离readwritesplit旨在提高群集的只读处理能力，同时保持一致性。不修改数据的数据分散在多个节点上，而所有写查询都将发送到单个节点上。那么，除了readwritesplit，maxscale还提供SchemaRouter和SmartRouter,有兴趣可以查看。此外，maxscale提供了新的Clustrix作为监视工具MaxCtrl安装建议下载rpm包，查看相应的版本下载即可。假如打不开，推荐到github下载压缩包进行安装即可下载完成在本地安装即可yum localinstall maxscale-2.4.7-1.centos.7.x86_64.rpm -y他的配置文件默认在/etc下[root@linuxea_Node ~]# rpm -ql maxscale |grep etc /etc/maxscale.cnf.template /etc/prelink.conf.d /etc/prelink.conf.d/maxscale.conf配置我现在有两台数据库是Galera cluster集群，现在我们配置成maxscale读写分离配置文件分几个部分，分别是全局，节点，service，监控模块和listener。相比之前的版本少了监控的端口配置。其他配置参考官网配置。全局配置全局配置中配置一些必要的配置信息节点配置除了必要的信息之外，可以添加priority设置优先级，如果为0，永远不会成为主节点用户请求从4006进入，将请求发给RW-Service，RW-Service根据读写分离策略将请求发给后端，Galera-Monitor作为管理和调度如下:# 全局配置 [maxscale] threads=auto ms_timestamp=1 syslog=0 maxlog=1 log_warning=1 log_notice=0 log_info=0 log_augmentation=1 # service配置 [RW-Service] type=service router=readwritesplit user=maxscale password=F3BF776B14BE7CAB61E83256E1C2B56C cluster=Galera-Monitor #listener [RW-Listener] type=listener service=RW-Service protocol=MariaDBClient address=0.0.0.0 port=4006 # 节点配置 [server1] type=server address=172.25.109.5 port=3306 protocol=MariaDBBackend [server2] type=server address=172.25.109.4 port=3306 protocol=MariaDBBackend # 监控模块 [Galera-Monitor] type=monitor module=galeramon servers=server1,server2 user=maxscale # maxkeys /var/lib/maxscale/ # maxpasswd /var/lib/maxscale KLJMKMXOPQOP1 KLJMKMXOPQOP1是授权的密码 password=F3BF776B14BE7CAB61E83256E1C2B56C monitor_interval=10000 disable_master_failback=true use_priority=true数据库授权授权给maxscaleCREATE USER 'maxscale'@'%' IDENTIFIED BY 'password'; GRANT SELECT ON mysql.user TO 'maxscale'@'%'; GRANT SELECT ON mysql.db TO 'maxscale'@'%'; GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'%'; GRANT SELECT ON mysql.roles_mapping TO 'maxscale'@'%'; GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%';完成授权后，修改maxscale的配置文件password字段[root@linuxea_node_maxscale-1 ~]# maxkeys /var/lib/maxscale/ [root@linuxea_node_maxscale-1 ~]# maxpasswd /var/lib/maxscale password 70DB8B57832941E87AB14C5BC47C6292 [root@linuxea_node_maxscale-1 ~]# sed -i 's/password=.*/password=70DB8B57832941E87AB14C5BC47C6292/g' /etc/maxscale.cnf而后启动[root@linuxea_node_maxscale-1 ~]# systemctl restart maxscale [root@linuxea_node_maxscale-1 ~]# tail -f /var/log/maxscale/maxscale.log MariaDB MaxScale /var/log/maxscale/maxscale.log Sat Mar 21 17:03:08 2020 ---------------------------------------------------------------------------- 2020-03-21 17:03:08 notice : (mxb_log_set_syslog_enabled): syslog logging is disabled. 2020-03-21 17:03:08 notice : (mxb_log_set_maxlog_enabled): maxlog logging is enabled. 2020-03-21 17:03:08.752 notice : (mxb_log_set_highprecision_enabled): highprecision logging is enabled. 2020-03-21 17:03:08.752 notice : (mxb_log_set_priority_enabled): The logging of warning messages has been enabled.监控模块[root@linuxea_node_maxscale-1 ~]# maxctrl list services ┌────────────┬────────────────┬─────────────┬───────────────────┬──────────────────┐ │ Service │ Router │ Connections │ Total Connections │ Servers │ ├────────────┼────────────────┼─────────────┼───────────────────┼──────────────────┤ │ RW-Service │ readwritesplit │ 0 │ 0 │ server1, server2 │ └────────────┴────────────────┴─────────────┴───────────────────┴──────────────────┘[root@linuxea_node_maxscale-1 ~]# maxctrl list servers ┌─────────┬──────────────┬──────┬─────────────┬─────────────────────────┬───────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼──────────────┼──────┼─────────────┼─────────────────────────┼───────┤ │ server1 │ 172.25.109.5 │ 3306 │ 0 │ Slave, Synced, Running │ │ ├─────────┼──────────────┼──────┼─────────────┼─────────────────────────┼───────┤ │ server2 │ 172.25.109.4 │ 3306 │ 0 │ Master, Synced, Running │ 0-1-6 │ └─────────┴──────────────┴──────┴─────────────┴─────────────────────────┴───────┘[root@linuxea_node_maxscale-1 ~]# maxctrl list listeners RW-Service ┌──────────────────────────────────────────────────┬──────────────────────────────────────────────────┬──────────────────────────────────────────────────┬──────────────────────────────────────────────────┐ │ Name │ Port │ Host │ State │ ├──────────────────────────────────────────────────┼──────────────────────────────────────────────────┼──────────────────────────────────────────────────┼──────────────────────────────────────────────────┤ │ { │ { │ { │ { │ │ "id": "RW-Listener", │ "id": "RW-Listener", │ "id": "RW-Listener", │ "id": "RW-Listener", │ │ "type": "listeners", │ "type": "listeners", │ "type": "listeners", │ "type": "listeners", │ │ "attributes": { │ "attributes": { │ "attributes": { │ "attributes": { │ │ "state": "Running", │ "state": "Running", │ "state": "Running", │ "state": "Running", │ │ "parameters": { │ "parameters": { │ "parameters": { │ "parameters": { │ │ "protocol": "MariaDBClient", │ "protocol": "MariaDBClient", │ "protocol": "MariaDBClient", │ "protocol": "MariaDBClient", │ │ "port": 4006, │ "port": 4006, │ "port": 4006, │ "port": 4006, │ │ "socket": null, │ "socket": null, │ "socket": null, │ "socket": null, │ │ "authenticator_options": "", │ "authenticator_options": "", │ "authenticator_options": "", │ "authenticator_options": "", │ │ "address": "0.0.0.0", │ "address": "0.0.0.0", │ "address": "0.0.0.0", │ "address": "0.0.0.0", │ │ "authenticator": null, │ "authenticator": null, │ "authenticator": null, │ "authenticator": null, │ │ "ssl": "false", │ "ssl": "false", │ "ssl": "false", │ "ssl": "false", │ │ "ssl_cert": null, │ "ssl_cert": null, │ "ssl_cert": null, │ "ssl_cert": null, │ │ "ssl_key": null, │ "ssl_key": null, │ "ssl_key": null, │ "ssl_key": null, │ │ "ssl_ca_cert": null, │ "ssl_ca_cert": null, │ "ssl_ca_cert": null, │ "ssl_ca_cert": null, │ │ "ssl_version": "MAX", │ "ssl_version": "MAX", │ "ssl_version": "MAX", │ "ssl_version": "MAX", │ │ "ssl_cert_verify_depth": 9, │ "ssl_cert_verify_depth": 9, │ "ssl_cert_verify_depth": 9, │ "ssl_cert_verify_depth": 9, │ │ "ssl_verify_peer_certificate": false │ "ssl_verify_peer_certificate": false │ "ssl_verify_peer_certificate": false │ "ssl_verify_peer_certificate": false │ │ }, │ }, │ }, │ }, │ │ "authenticator_diagnostics": [ │ "authenticator_diagnostics": [ │ "authenticator_diagnostics": [ │ "authenticator_diagnostics": [ │ │ { │ { │ { │ { │ │ "user": "maxscale", │ "user": "maxscale", │ "user": "maxscale", │ "user": "maxscale", │ │ "host": "%" │ "host": "%" │ "host": "%" │ "host": "%" │ │ }, │ }, │ }, │ }, │ │ { │ { │ { │ { │ │ "user": "maxscale", │ "user": "maxscale", │ "user": "maxscale", │ "user": "maxscale", │ │ "host": "%" │ "host": "%" │ "host": "%" │ "host": "%" │ │ } │ } │ } │ } │ │ ] │ ] │ ] │ ] │ │ } │ } │ } │ } │ │ } │ } │ } │ } │ └──────────────────────────────────────────────────┴──────────────────────────────────────────────────┴──────────────────────────────────────────────────┴──────────────────────────────────────────────────┘ [root@linuxea_node_maxscale-1 ~]# 用户授权数据库授权后，每一个登陆的授权用户同时仍然需要授权给maxscale。比如：maxscale的ip是172.25.109.8。假设现在授权一个用户sean用户从172.25.111.6来进行访问。使用maxscale，还需要将sean授权给maxscale的ip，权限及密码。CREATE DATABASE linuxea_com CHARACTER SET utf8mb4; GRANT ALL ON `linuxea_com`.* TO `sean`@172.25.111.6 IDENTIFIED BY 'password';MariaDB [(none)]> CREATE DATABASE linuxea_com CHARACTER SET utf8mb4; Query OK, 1 row affected (0.01 sec) MariaDB [(none)]> GRANT ALL ON `linuxea_com`.* TO `sean`@172.25.111.6 IDENTIFIED BY 'password'; Query OK, 0 rows affected (0.00 sec)假如不授权，是可以登陆，但是会提示没有权限[root@linuxea_node_maxscale-1 ~]# tail -f /var/log/maxscale/maxscale.log 2020-03-21 17:10:04.865 error : (129) [mariadbbackend] (handle_error_response): Invalid authentication message from backend 'server2'. Error code: 1045, Msg : #28000Access denied for user 'sean'@'172.25.109.8' (using password: YES) 2020-03-21 17:10:09.961 error : (132) [mariadbbackend] (handle_error_response): Invalid authentication message from backend 'server1'. Error code: 1045, Msg : #28000Access denied for user 'sean'@'172.25.109.8' (using password: YES) 2020-03-21 17:10:09.961 error : (132) [mariadbbackend] (handle_error_response): Invalid authentication message from backend 'server2'. Error code: 1045, Msg : #28000Access denied for user 'sean'@'172.25.109.8' (using password: YES)如下：[root@linuxea-node-172.25.111.6 ~]# mysql -usean -ppassword -h172.25.109.8 -P4006 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 124 Server version: 10.0.33-MariaDB-wsrep Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 129 Current database: *** NONE *** ERROR 2003 (HY000): Authentication with backend failed. Session will be closed. MariaDB [(none)]> show databases; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 132 Current database: *** NONE *** ERROR 2003 (HY000): Authentication with backend failed. Session will be closed.我们在数据库进行授权maxscale的ipMariaDB [(none)]> GRANT ALL ON `linuxea_com`.* TO `sean`@172.25.109.8 IDENTIFIED BY 'password'; Query OK, 0 rows affected (0.00 sec)回到172.25.111.6登陆[root@linuxea-node-172.25.111.6 ~]# mysql -usean -ppassword -h172.25.109.8 -P4006 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 124 Server version: 10.0.33-MariaDB-wsrep Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 129 Current database: *** NONE *** ERROR 2003 (HY000): Authentication with backend failed. Session will be closed. MariaDB [(none)]> show databases; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 132 Current database: *** NONE *** ERROR 2003 (HY000): Authentication with backend failed. Session will be closed. MariaDB [(none)]> show databases; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 174 Current database: *** NONE *** +--------------------+ | Database | +--------------------+ | information_schema | | linuxea_com | +--------------------+ 2 rows in set (0.01 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | linuxea_com | +--------------------+ 2 rows in set (0.00 sec) MariaDB [(none)]> 创建MariaDB [(none)]> use linuxea_com Database changed MariaDB [linuxea_com]> CREATE TABLE MyGuests ( -> id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, -> firstname VARCHAR(30) NOT NULL, -> lastname VARCHAR(30) NOT NULL, -> email VARCHAR(50), -> reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -> ) -> ; Query OK, 0 rows affected (0.02 sec) MariaDB [linuxea_com]> show tables; +-----------------------+ | Tables_in_linuxea_com | +-----------------------+ | MyGuests | +-----------------------+ 1 row in set (0.00 sec) MariaDB [linuxea_com]> desc MyGuests; +-----------+-----------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-----------------+------+-----+-------------------+-----------------------------+ | id | int(6) unsigned | NO | PRI | NULL | auto_increment | | firstname | varchar(30) | NO | | NULL | | | lastname | varchar(30) | NO | | NULL | | | email | varchar(50) | YES | | NULL | | | reg_date | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-----------+-----------------+------+-----+-------------------+-----------------------------+ 5 rows in set (0.00 sec)

linuxea:MaxScale binlog server2.2.6与读写分离失败实践 MaxScale是一个动态数据的路由平台，位于数据库层和该数据库的客户端之间。但是，这里描述的binlog路由器与原始概念有所不同。在传统的复制中，有一个主和多个从，但添加主机或者恢复主机，需要在从执行一些操作才能成为主在之前的mha中binlog日志是mha通过ssh识别最新的中继日志，配合配置中最新的主节点，同步到其他从站，并且能让从站重新开始复制而在maxscale中是在主从之间引入代理层解决，从只知道代理服务器，并不需要知道实际的主服务器位置，以此来简化复制环节中更换故障主服务器的过程。从服务器只知道中间服务器，也就是代理层。因此将一个现有的从库提升到主只涉及到从服务器和中间主服务器，一旦中间服务器更改完成，从站便是新的主节点。但是需要将中间服务器设置为新服务器日志中的正确的点，才可以继续复制但是，如果中间服务器本身发生故障，就会回到开始的问题。如果用多个中间服务器有会出现二进制日志不能一致的问题。还有一个问题是，如果中间节点故障，从节点是不能从故障节点转移到另外一个新的中间主节点,也就是说如果你双主，则会被认为是从maxscale接受主服务器的二进制日志缓存到本地，并将二进制日志提供给从服务器。这就意味着从站始终获得与主站写入的二进制事件相关的二进制日志。并与从站并行保持。事实上上面一大堆文字都是翻译的(有误处请指正)，我本想它是可以这样实现的，如下：maxscale不单单是binlog-server而且还是读写分离，借鉴：https://mariadb.com/resources/blog/mariadb-maxscale-setup-binlog-server-and-sql-query-routing文章中说是测试版本，我测试发现不行，只能做为binlog-server使用，有些扯犊子参考：参考：https://maxscale.readthedocs.io/en/stable/Documentation/Routers/Binlogrouter/ https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22-mariadb-maxscale-as-a-binlog-server/#binlog-router-compatibility 参考：https://mariadb.com/resources/blog/mariadb-maxscale-setup-binlog-server-and-sql-query-routing1，maxscale安装[root@Linuxea-VM-Node145 ~]# wget https://downloaLinuxea.mariadb.com/MaxScale/2.2.6/rhel/7/x86_64/maxscale-2.2.6-1.rhel.7.x86_64.rpm [root@Linuxea-VM-Node145 ~]# yum localinstall maxscale-2.2.6-1.rhel.7.x86_64.rpm [root@Linuxea-VM-Node145 ~]# mkdir /data/maxscale/data -p [root@Linuxea-VM-Node6 /data]# mkdir -p /data/maxscale/logs/trace/ [root@Linuxea-VM-Node6 /data]# mkdir -p /data/maxscale/cache/ [root@Linuxea-VM-Node145 ~]# maxkeys /data/maxscale/{data,cache} [root@Linuxea-VM-Node145 ~]# maxkeys /data/maxscale/data [root@Linuxea-VM-Node145 ~]# maxpasswd /data/maxscale/data password 63B4C3058D940CD68A6E8E0E11ABE2D7 [root@Linuxea-VM-Node145 ~]# chown -R maxscale.maxscale /data/maxscale/2，在主库上授权这里授权的是maxscale的用户，作为链接后端主机的用户，后面在创建一个用户作为主从同步，也可以使用此用户GRANT SELECT ON *.* TO 'maxscale'@'%' identified by 'password'; GRANT SHOW DATABASES ON *.* TO maxscale@'%'; GRANT REPLICATION SLAVE on *.* to maxscale@'%'; GRANT REPLICATION CLIENT on *.* to maxscale@'%'; flush privileges;slave用户作为复制用户GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'password'; flush privileges;主节点：MariaDB [(none)]> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 467 | +------------------+-----------+ 1 row in set (0.00 sec) MariaDB [(none)]> 3,在maxscale创建配置文件[root@Linuxea-VM-Node145 /data/maxscale]# mysql -umaxscale -ppassword -h10.10.240.145 -P3306 Welcome to the MariaDB monitor. CommanLinuxea end with ; or \g. Your MySQL connection id is 4 Server version: 10.2.12 2.2.6-maxscale Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='10.10.240.202',MASTER_USER='slave',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=467,MASTER_CONNECT_RETRY=5,MASTER_HEARTBEAT_PERIOD=2; ERROR 1234 (42000): Can not set MASTER_LOG_POS to 467: Permitted binlog pos is 4. Specified master_log_file=mysql-bin.000001 【这里MASTER_LOG_POS=4设置成4即可】 MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='10.10.240.202',MASTER_USER='slave',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=4,MASTER_CONNECT_RETRY=5,MASTER_HEARTBEAT_PERIOD=2; Query OK, 0 rows affected (0.00 sec) MySQL [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) 此时目录下产生master.ini[root@Linuxea-VM-Node145 /data/maxscale]# ll 总用量 20 drwxr-xr-x 2 maxscale maxscale 4096 6月 8 21:50 cache drwxr-xr-x 13 maxscale maxscale 4096 6月 9 09:23 data -rw-r--r-- 1 maxscale maxscale 4096 6月 9 09:25 gtid_maps.db drwxr-xr-x 3 maxscale maxscale 37 6月 8 20:21 logs -rw------- 1 maxscale maxscale 177 6月 9 09:25 master.ini -rw-r--r-- 1 maxscale maxscale 467 6月 9 09:25 mysql-bin.000001(可以直接用手撸一串配置)[root@Linuxea-VM-Node145 /data/maxscale]# cat master.ini [binlog_configuration] master_host=10.10.240.202 master_port=3306 master_user=slave master_password=password filestem=mysql-bin master_heartbeat_period=2 master_connect_retry=5 [root@Linuxea-VM-Node145 /data/maxscale]# 4,从库同步(两个从库都同步，同步的Ip是maxsclae blog的IP地址)MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.10.240.145',MASTER_USER='slave',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=467,MASTER_CONNECT_RETRY=5,MASTER_HEARTBEAT_PERIOD=2; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.10.240.145 Master_User: slave Master_Port: 3306 Connect_Retry: 5 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 467 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 535 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes5，查看节点信息[root@Linuxea-VM-Node145 /data/maxscale]# maxadmin --user=admin --password=mariadb --host=10.10.240.145 list servers Servers. -------------------+-----------------+-------+-------------+-------------------- Server | Address | Port | Connections | Status -------------------+-----------------+-------+-------------+-------------------- binlog_router_master_host | 10.10.240.202 | 3306 | 1 | Running server1 | 10.10.240.202 | 3306 | 0 | Master, Running server2 | 10.10.240.203 | 3306 | 0 | Slave, Running server3 | 10.10.240.146 | 3306 | 0 | Slave, Running -------------------+-----------------+-------+-------------+--------------------节点详情查看[root@Linuxea-VM-Node145 /data/maxscale]# maxadmin --user=admin --password=mariadb --host=10.10.240.145 show servers|egrep "Server|Status" Server 0xf95420 (binlog_router_master_host) Server: 10.10.240.202 Status: Running Server Version: 10.0.33-MariaDB-wsrep Server 0xf5dc60 (server1) Server: 10.10.240.202 Status: Master, Running Server Version: 10.0.33-MariaDB-wsrep Server Parameters: Server 0xf5c840 (server2) Server: 10.10.240.203 Status: Slave, Running Server Version: 10.0.33-MariaDB-wsrep Server Parameters: Server 0xf5b420 (server3) Server: 10.10.240.146 Status: Slave, Running Server Version: 10.0.33-MariaDB-wsrep Server Parameters: [root@Linuxea-VM-Node145 /data/maxscale]# 在maxscale上查看从节点信息[root@Linuxea-VM-Node145 /data/maxscale]# mysql -umaxscale -ppassword -h10.10.240.145 -P3306 Welcome to the MariaDB monitor. CommanLinuxea end with ; or \g. Your MySQL connection id is 4 Server version: 10.2.12 2.2.6-maxscale Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> show slave hosts; +-----------+------+------+-----------+------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+------+------+-----------+------------+ | 4 | | 3306 | 100 | | | 2 | | 3306 | 100 | | +-----------+------+------+-----------+------------+ 2 rows in set (0.00 sec)6，添加机器添加一台机器就如第4部分同步从节点一样，备份到新从节点，而后接入进来7，模拟一次切换登陆maxscale[root@Linuxea-VM-Node145 /data/maxscale]# mysql -uslave -ppassword -h10.10.240.145 -P3306 Welcome to the MariaDB monitor. CommanLinuxea end with ; or \g. Your MySQL connection id is 14 Server version: 10.2.12 2.2.6-maxscale MariaDB Server, wsrep_25.21.rc3fc46e Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> stop slave; Query OK, 0 rows affected (0.00 sec) MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='10.10.240.146',MASTER_USER='slave',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1195,MASTER_CONNECT_RETRY=5,MASTER_HEARTBEAT_PERIOD=2; ERROR 1234 (42000): Can not set MASTER_LOG_POS to 1195: Permitted binlog pos is 1340. Current master_log_file=mysql-bin.000001, master_log_pos=1340 这里会提示pos节点 MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='10.10.240.146',MASTER_USER='slave',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1340,MASTER_CONNECT_RETRY=5,MASTER_HEARTBEAT_PERIOD=2; Query OK, 0 rows affected (0.00 sec) MySQL [(none)]> start slave; Query OK, 0 rows affected (0.09 sec)在将新节点加入进来[root@Linuxea-VM-Node202 ~]# mysql -uroot -plookback Welcome to the MariaDB monitor. CommanLinuxea end with ; or \g. Your MariaDB connection id is 86 Server version: 10.0.33-MariaDB-wsrep MariaDB Server, wsrep_25.21.rc3fc46e Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.10.240.145',MASTER_USER='slave',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1195,MASTER_CONNECT_RETRY=5,MASTER_HEARTBEAT_PERIOD=2; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.10.240.145 Master_User: slave Master_Port: 3306 Connect_Retry: 5 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1340 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 680 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes在查看节点数[root@Linuxea-VM-Node145 ~]# maxadmin --user=admin --password=mariadb --host=10.10.240.145 show services|grep Slave_host_port Slave_host_port: [10.10.240.202]:21694 Slave_host_port: [10.10.240.145]:27544 Slave_host_port: [10.10.240.203]:2640 Slave_host_port: [10.10.240.146]:51492

linuxea:Cetus配置测试 简介Cetus是由C语言开发的关系型数据库MySQL的中间件，主要提供了一个全面的数据库访问代理功能。Cetus连接方式与MySQL基本兼容，应用程序几乎不用修改即可通过Cetus访问数据库，实现了数据库层的水平扩展和高可用。Cetus分为读写分离和分库两个版本。本篇主要实现读写分离，后端是MariaDB Galera Cluster而不是主从复制。大致模样如下：详情参阅：https://github.com/Lede-Inc/cetus开始安装1.1 下载安装安装依赖包[root@Linuxea-VM-Node145 /data]# yum install cmake gcc glib2-devel flex libevent-devel mysql-devel gperftools-libs -y克隆源码[root@Linuxea-VM-Node145 /data]# git clone https://github.com/Lede-Inc/cetus.git 正克隆到 'cetus'... remote: Counting objects: 1640, done. remote: Compressing objects: 100% (119/119), done. remote: Total 1640 (delta 104), reused 140 (delta 73), pack-reused 1448 接收对象中: 100% (1640/1640), 6.25 MiB | 1.26 MiB/s, done. 处理 delta 中: 100% (1002/1002), done. [root@Linuxea-VM-Node145 /data]# cd cetus [root@Linuxea-VM-Node145 /data/cetus]# mkdir build && cd build创建安装目录[root@Linuxea-VM-Node145 /data/cetus/build]# mkdir /usr/local/cetus开始编译[root@Linuxea-VM-Node145 /data/cetus/build]# cmake ../ -DCMAKE_BUILD_TYPE=Debug -DCMAKE_INSTALL_PREFIX=/usr/local/cetus -LinuxeaIMPLE_PARSER=ON [root@Linuxea-VM-Node145 /data/cetus/build]# make && make install1.2 准备配置文件[root@Linuxea-VM-Node145 /data/cetus/build]# cd /usr/local/cetus/conf [root@Linuxea-VM-Node145 /usr/local/cetus/conf]# cp proxy.conf.example proxy.conf [root@Linuxea-VM-Node145 /usr/local/cetus/conf]# cp users.json.example users.json启动命令如下，先不启动，配置配置文件bin/cetus --defaults-file=conf/proxy.conf [--conf-dir＝/home/user/cetus_install/conf/]准备配置文件[root@Linuxea-VM-Node145 /usr/local/cetus/conf]# egrep -v "^$|^#" proxy.conf [cetus] # For mode-switch daemon = true disable-sharding-mode=true # Loaded Plugins plugins=proxy,admin plugin-dir=/usr/local/cetus/lib/cetus/plugins # Proxy Configuration, For eaxmlpe: MySQL master and salve host ip are both 192.0.0.1 # 监听的IP和端口 proxy-address=0.0.0.0:4006 # 读写后端(主库)的IP和端口 proxy-backend-addresses=10.10.240.202:3306 # 只读后端(从库)的IP和端口 proxy-read-only-backend-addresses=10.10.240.203:3306,10.10.240.146:3306 # Admin Configuration # 管理模块的IP和端口 admin-address=0.0.0.0:7001 #管理模块的用户名 admin-username=admin #管理模块的密码明文 admin-password=admin # Backend Configuration, use test db and username created #默认数据库 #default-db= #默认登陆用户名 #default-username=cetus_app default-username=Ccetus # 设置刚启动的连接数量 default-pool-size=100 max-resp-len=10485760 long-query-time=100 # default-pool-size=<num> ，设置刚启动的连接数量 # max-pool-size=<num>，设置最大连接数量 # max-resp-size=<num>，设置最大响应大小，一旦超过此大小，则会报错给客户端 # enable-client-compress=[true|false]，支持客户端压缩 # enable-tcp-stream=[true|false]，启动tcp stream，无需等响应收完就发送给客户端 # master-preferred=[true|false]，除非注释强制访问从库，否则一律访问主库 # reduce-connections=True #[true|false]，自动减少过多的后端连接数量 # File and Log Configuration, put log in /data and marked by proxy port, /data/cetus neeLinuxea to be created manually and has rw authority for cetus os user max-open-files = 65536 pid-file = cetus6001.pid plugin-dir=lib/cetus/plugins # 日志文件路 log-file=/data/cetus/cetus_6001.log # info | message | warning | error | critical(default) log-level=debug ##Check salve delay 主从检测 （只适用于主从） # disable-threaLinuxea=false # check-slave-delay=true # slave-delay-down=5 # slave-delay-recover=1 # For trouble keepalive=true verbose-shutdown=true log-backtrace-on-crash=true1.3 准备用户授权这里的用户文件可以在后面进行添加，不需要修改也可以[root@Linuxea-VM-Node145 /usr/local/cetus/conf]# cat users.json { "users": [{ "user": "Ccetus", "client_pwd": "password", "server_pwd": "password" }, { "user": "scetus", "client_pwd": "password", "server_pwd": "password" } [root@Linuxea-VM-Node145 /usr/local/cetus/conf]# 注意：其中Ccetus用户需要在数据库进行创建,而后填写在配置中.在后端数据库进行授权GRANT ALL ON *.* TO 'Ccetus'@'%' identified by 'password'; GRANT SELECT ON *.* TO 'scetus'@'%' identified by 'password'; GRANT ALL ON linuxea.* TO 'linuxea'@'10.10.0.96' IDENTIFIED BY 'password';修改配置文件权限[root@Linuxea-VM-Node145 /usr/local/cetus/conf]# chmod 660 proxy.conf开始启动[root@Linuxea-VM-Node145 /usr/local/cetus/conf]# ../bin/cetus --defaults-file=/usr/local/cetus/conf/proxy.conf 管理端安装配置安装一个mysql的客户端[root@Linuxea-VM-Node145 ~]# cat << EOF > /etc/yum.repos.d/MariaDB.repo > [mariadb] > name = MariaDB > baseurl = https://yum.mariadb.org/10.1/centos7-amd64 > gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB > gpgcheck=1 > EOF [root@Linuxea-VM-Node145 ~]# yum install MariaDB-client -y 查看后端mysql --prompt="proxy> " --comments -h10.10.240.145 -P7001 -uadmin -padmin2.1 管理端使用说明注意：1，创建用户在Cetus中，账号大体可以分两类：监控后端DB状态的账号(default-username)业务端访问DB存取数据的账号。这两类账号都需要：1 在Cetus的users.json中配置 2 在MySQL中进行授权。 第2类账号的MySQL端授权根据业务访问的库表进行授权就行，注意授权时，IP授予Cetus的IP（user@'cetus-ip'）。MySQL目前支持IP白名单(show allow_ip proxy;)用来限制客户端连接Cetus，目前已经支持IP段的设置。IP白名单不设置，则对客户端IP无限制，如果设置了，则只允许在IP白名单中的客户端IP访问。2，cetus创建动态的增加账号信息mysql --prompt="admiin> " --comments -h10.10.240.145 -P7001 -uadmin -padmin3，用户创建这里的 update 命令虽然是“update”，但是当没哟匹配的条件时，功能类似“insert”update user_pwd set password='password' where user='mark'; update app_user_pwd set password='password' where user='mark';创建完成就会写入到文件中：[root@Linuxea-VM-Node145 /usr/local/cetus/conf]# cat users.json { "users": [{ "user": "Ccetus", "client_pwd": "password", "server_pwd": "password" }, { "user": "scetus", "client_pwd": "password", "server_pwd": "password" }, { "user": "mark", "client_pwd": "password", "server_pwd": "password" }] }4，mariadb创建用户MariaDB [(none)]> GRANT ALL ON *.* TO 'mark'@'10.10.240.145' identified by 'password'; Query OK, 0 rows affected (0.01 sec)6，拒绝所有并放行需要放行admiin> add deny_ip proxy *; Query OK, 0 rows affected (0.00 sec)7，Proxy创建白名单[root@Linuxea-VM-Node145 ~]# mysql --prompt="proxy> " --comments -h10.10.240.145 -P7001 -uadmin -padmin Welcome to the MariaDB monitor. CommanLinuxea end with ; or \g. Your MySQL connection id is 1 Server version: cetus 1.0.0 admin Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. proxy> add allow_ip proxy mark@10.0.1.61; Query OK, 1 row affected (0.00 sec) proxy> show allow_ip proxy; +--------+----------------+ | Plugin | Address | +--------+----------------+ | proxy | mark@10.0.1.61 | +--------+----------------+ 1 row in set (0.00 sec) proxy> 8，查看后端主机proxy> select * from backends; +-------------+--------------------+-------+------+-------------+------+------------+------------+-------------+ | backend_ndx | address | state | type | slave delay | uuid | idle_conns | used_conns | total_conns | +-------------+--------------------+-------+------+-------------+------+------------+------------+-------------+ | 1 | 10.10.240.202:3306 | up | rw | NULL | NULL | 2 | 0 | 2 | | 2 | 10.10.240.203:3306 | up | ro | 0 | NULL | 74 | 0 | 74 | | 3 | 10.10.240.146:3306 | up | ro | 0 | NULL | 54 | 24 | 78 | +-------------+--------------------+-------+------+-------------+------+------------+------------+-------------+ 3 rows in set (0.00 sec) proxy> 压测cetus与maxsclae比较,maxsclae使用readwritesplit用同一个库进行测试，db20180528_3337先在收据库授权：GRANT all ON db20180528_3337.* TO 'mark'@'%' identified by 'password'; flush privileges;cetus写入:sysbench /usr/share/sysbench/oltp_update_index.lua \ --mysql-host=10.10.240.145 \ --mysql-port=4006 \ --mysql-user=mark \ --mysql-password=password \ --mysql-db=db20180528_3337 \ --db-driver=mysql \ --tables=10 \ --table-size=100000 \ --report-interval=10 \ --threads=50 \ --time=60 \ --skip-trx=on \ runoltp_update_index[ 10s ] thds: 50 tps: 3498.36 qps: 3498.36 (r/w/o: 0.00/1181.95/2316.41) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 50 tps: 4159.14 qps: 4159.14 (r/w/o: 0.00/1387.71/2771.43) lat (ms,95%): 63.32 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 50 tps: 4188.54 qps: 4188.54 (r/w/o: 0.00/1391.28/2797.26) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 50 tps: 2151.35 qps: 2151.35 (r/w/o: 0.00/699.29/1452.07) lat (ms,95%): 69.29 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 50 tps: 4083.64 qps: 4083.64 (r/w/o: 0.00/1371.75/2711.89) lat (ms,95%): 66.84 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 50 tps: 4022.16 qps: 4022.16 (r/w/o: 0.00/1341.62/2680.54) lat (ms,95%): 74.46 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 73766 other: 147325 total: 221091 transactions: 221091 (3682.08 per sec.) queries: 221091 (3682.08 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 60.0433s total number of events: 221091 Latency (ms): min: 0.34 avg: 13.57 max: 4359.03 95th percentile: 64.47 sum: 3000618.25 Threads fairness: events (avg/stddev): 4421.8200/172.02 execution time (avg/stddev): 60.0124/0.01监控如下：读取：sysbench /usr/share/sysbench/oltp_read_only.lua \ --mysql-host=10.10.240.145 \ --mysql-port=4006 \ --mysql-user=mark \ --mysql-password=password \ --mysql-db=db20180528_3337 \ --db-driver=mysql \ --tables=10 \ --table-size=100000 \ --report-interval=10 \ --threads=50 \ --time=60 \ --skip-trx=on \ run oltp_read_only.lua[ 10s ] thds: 50 tps: 1856.11 qps: 26022.04 (r/w/o: 26022.04/0.00/0.00) lat (ms,95%): 31.94 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 50 tps: 1924.47 qps: 26939.92 (r/w/o: 26939.92/0.00/0.00) lat (ms,95%): 28.67 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 50 tps: 1863.81 qps: 26091.55 (r/w/o: 26091.55/0.00/0.00) lat (ms,95%): 29.72 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 50 tps: 1779.71 qps: 24918.53 (r/w/o: 24918.53/0.00/0.00) lat (ms,95%): 34.33 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 50 tps: 1771.68 qps: 24799.98 (r/w/o: 24799.98/0.00/0.00) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 50 tps: 1766.63 qps: 24729.84 (r/w/o: 24729.84/0.00/0.00) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 1535506 write: 0 other: 0 total: 1535506 transactions: 109679 (1826.73 per sec.) queries: 1535506 (25574.19 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 60.0393s total number of events: 109679 Latency (ms): min: 11.65 avg: 27.36 max: 146.12 95th percentile: 31.37 sum: 3000763.51 Threads fairness: events (avg/stddev): 2193.5800/35.56 execution time (avg/stddev): 60.0153/0.01监控如下：maxsclae:写入update:sysbench /usr/share/sysbench/oltp_update_index.lua \ --mysql-host=10.10.240.145 \ --mysql-port=3337 \ --mysql-user=mark \ --mysql-password=password \ --mysql-db=db20180528_3337 \ --db-driver=mysql \ --tables=10 \ --table-size=100000 \ --report-interval=10 \ --threads=50 \ --time=60 \ --skip-trx=on \ runoltp_update_index.lua[ 10s ] thds: 50 tps: 4059.71 qps: 4059.71 (r/w/o: 0.00/1343.11/2716.61) lat (ms,95%): 63.32 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 50 tps: 4107.74 qps: 4107.74 (r/w/o: 0.00/1351.08/2756.66) lat (ms,95%): 69.29 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 50 tps: 4111.42 qps: 4111.42 (r/w/o: 0.00/1376.64/2734.78) lat (ms,95%): 63.32 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 50 tps: 3931.22 qps: 3931.22 (r/w/o: 0.00/1313.37/2617.85) lat (ms,95%): 69.29 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 50 tps: 4245.93 qps: 4245.93 (r/w/o: 0.00/1421.71/2824.22) lat (ms,95%): 74.46 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 50 tps: 4058.91 qps: 4058.91 (r/w/o: 0.00/1342.77/2716.14) lat (ms,95%): 78.60 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 81518 other: 163691 total: 245209 transactions: 245209 (4083.81 per sec.) queries: 245209 (4083.81 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 60.0424s total number of events: 245209 Latency (ms): min: 0.32 avg: 12.24 max: 657.95 95th percentile: 69.29 sum: 3000433.84 Threads fairness: events (avg/stddev): 4904.1800/197.99 execution time (avg/stddev): 60.0087/0.01监控如下：读取:sysbench /usr/share/sysbench/oltp_read_only.lua \ --mysql-host=10.10.240.145 \ --mysql-port=3337 \ --mysql-user=mark \ --mysql-password=password \ --mysql-db=db20180528_3337 \ --db-driver=mysql \ --tables=10 \ --table-size=100000 \ --report-interval=10 \ --threads=50 \ --time=60 \ --skip-trx=on \ run oltp_read_only.lua[ 10s ] thds: 50 tps: 2746.72 qps: 38489.80 (r/w/o: 38489.80/0.00/0.00) lat (ms,95%): 38.25 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 50 tps: 2947.88 qps: 41265.98 (r/w/o: 41265.98/0.00/0.00) lat (ms,95%): 36.89 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 50 tps: 3281.79 qps: 45944.62 (r/w/o: 45944.62/0.00/0.00) lat (ms,95%): 23.95 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 50 tps: 2680.51 qps: 37528.49 (r/w/o: 37528.49/0.00/0.00) lat (ms,95%): 38.25 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 50 tps: 2672.41 qps: 37409.39 (r/w/o: 37409.39/0.00/0.00) lat (ms,95%): 38.25 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 50 tps: 2700.45 qps: 37811.13 (r/w/o: 37811.13/0.00/0.00) lat (ms,95%): 37.56 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 2384956 write: 0 other: 0 total: 2384956 transactions: 170354 (2835.47 per sec.) queries: 2384956 (39696.57 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 60.0777s total number of events: 170354 Latency (ms): min: 6.73 avg: 17.62 max: 325.16 95th percentile: 37.56 sum: 3001080.47 Threads fairness: events (avg/stddev): 3407.0800/1140.34 execution time (avg/stddev): 60.0216/0.03监控如下:

linuxea:MHA0.56配置实践 MHA专门用作多个MYSQL服务器(主从环境)提供master故障后转移，并保证数据一致性，由于MHA本身使用的资源很少，甚至于管理100对以上的。MHA分为manager和node,manager可以运行在node节点之上,manager负责监控和故障转移，其中包含一些脚本配置，如：发送邮件等。可以配置为如下：一主多从：一个主节点，多个slave单主多从：一个主节点，多个从节点，从节点不接替主节点单主多从，一个候选节点：一个主节点，多个slave，其中一个slave为候选主节点以及多主，三层复制等一般情况下，我们使用一个主，多个从节点，并且从节点中有一个节点是候选主节点，当主节点down掉后，候选节点便成为主节点对外提供服务。这个场景中会分配一个VIP（虚拟IP），主崩溃后候选节点接管，在MHA中可以通过配置文件参数调用外部脚本设置，也可以手动更新，更或者使用Keepalived来执行。当然，如果读并不大，使用一主一从更为稳妥，因为数据一致性得到了保障，但是同时牺牲了读性能和扩展空间，并且可用性大大降低在MHA场景中使用半同步复制大大降低了这种数据丢失的风险。通常MHA会试图从故障的主设备保存二进制文件，但是有些时候不总是成功，如果出现SSH链接不通的问题，则二进制文件会出现问题，将有丢失最新数据的分享MHA是可以使用半同步复制，它基于MySQL复制机制。值得注意的是，如果只有一个从站接收到最新的二进制日志事件，MHA可以将这些事件应用到所有其他从站，以便它们可以保持一致。优点： 主节点故障切换和从节点升级可以快速完成 主站崩溃不会导致数据不一致() 不用修改mysql配置 性能有一定的提升 适用场景多 自定义扩展(脚本) 基于GTID的故障转移要求：1，SSH公钥认证Manager通过SSH内部连接到MySQL服务器。最新从站上的MHA节点也通过SSH（scp）在内部将中继日志文件发送到其他从站（如果大于10个主机， 修改/etc/ssh/ sshd_config,默认为10）。为了使这些程序自动化，SSH公钥认证必不可少。而后使用MHA Manager中包含的masterha_check_ssh命令来检查SSH连接是否正常工作。2，MHA支持5.0或者以上的版本3，主节点必须启用log-bin4，备用写库需要授权5，禁用中继日志自动删除6，仅支持mysql为了使MHA真正起作用，大多数设置在启动masterha_manager或masterha_check_repl时自动检查。结构如下：前端使用LVS(DR)+keepalived代理端口到中间件Cetus，Cetus是网易的开源项目(后面会说cetus和maxscale)，LVS会将请求发送到两台Cetus，由Cetus发送到后端数据库，后端使用MHA，本篇关注红色区域内容即可篇幅太长，如果有误请指正。所有的内容来自https://github.com/yoshinorim/mha4mysql-manager/wikiMHA+-----------------+----------------+------------------+------------+ + IP + 类型 + 角色 + OS + +-----------------+----------------+------------------+------------+ + 10.0.1.61 + MHA + MHA manager(0.56)+ Centos7.2 + +-----------------+----------------+------------------+------------+ + VIP 10.0.1.161 + +-----------------+----------------+------------------+------------+ + 10.10.240.117 + Mysql-Master + MHA NODE + Centos7.2 + +-----------------+----------------+------------------+------------+ + 10.10.0.98 + Mysql-Slave + MHA NODE + Centos7.2 + +-----------------+----------------+------------------+------------+ + 10.0.1.49 + Mysql-Slave + MHA NODE + Centos7.2 + +-----------------+----------------+------------------+------------+一主多从环境准备1.1 免密码登陆10.10.240.117 master +ssh-keygen -t rsa +ssh-copy-id 10.10.0.98 +ssh-copy-id 10.0.1.49 +ssh-copy-id 10.0.1.61 +10.10.0.98 slave +ssh-keygen -t rsa +ssh-copy-id 10.10.240.117 +ssh-copy-id 10.0.1.49 +ssh-copy-id 10.0.1.61 +10.0.1.49 slave +ssh-keygen -t rsa +ssh-copy-id 10.10.240.117 +ssh-copy-id 10.10.0.98 +ssh-copy-id 10.0.1.61 +10.0.1.61 MHA +ssh-keygen -t rsa +ssh-copy-id 10.10.240.117 +ssh-copy-id 10.10.0.98 +ssh-copy-id 10.0.1.49 +### 1.2 三台机器安装好Mysql[root@Linuxea-VM-Node117 ~]# yum -y install make automake libtool pkgconfig libaio-devel mysql-devel[root@Linuxea-VM-Node117 ~]# axel -n 50 https://ftp.ntu.edu.tw/MySQL/DownloaLinuxea/MySQL-5.6/mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz[root@Linuxea-VM-Node117 ~]# tar xf mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz -C /usr/local[root@Linuxea-VM-Node117 ~]# mkdir /data/mysql -p[root@Linuxea-VM-Node117 ~]# groupadd -r -g 306 mysql[root@Linuxea-VM-Node117 ~]# useradd -g 306 -r -u 307 mysql [root@Linuxea-VM-Node117 ~]# chown -R mysql.mysql /data/mysql/[root@Linuxea-VM-Node117 ~]# cd /usr/local && ln -s mysql-5.6.40-linux-glibc2.12-x86_64 mysql && cd mysql[root@Linuxea-VM-Node117 /usr/local/mysql]# chown -R mysql.mysql /usr/local/mysql[root@Linuxea-VM-Node117 /usr/local/mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql --basedir=/usr/local/mysql[root@Linuxea-VM-Node117 /usr/local/mysql]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh && source /etc/profile.d/mysql.sh[root@Linuxea-VM-Node117 /usr/local/mysql]# cp support-files/mysql.server /etc/init.d/mysqld其中配置文件之一,10.10.240.117 master[root@Linuxea-VM-Node117 /etc]# cat /etc/my.cnf[client]port = 3306socket = /tmp/mysql.sockdefault-character-set = utf8[mysqld]relay_log = relay-binbasedir = /usr/local/mysqldatadir = /data/mysqlpid-file = /data/mysql/mysql.piduser = mysqlbind-address = 0.0.0.0server-id = 2init-connect = 'SET NAMES utf8'character-set-server = utf8default-time_zone = '-4:00'skip-name-resolveback_log = 300max_connections = 999max_connect_errors = 1024000open_files_limit = 65535table_open_cache = 256max_allowed_packet = 50Mmax_heap_table_size = 512Mtmp_table_size = 32Mread_buffer_size = 2Mread_rnd_buffer_size = 16Msort_buffer_size = 8Mjoin_buffer_size = 8Mkey_buffer_size = 16Mthread_cache_size = 16query_cache_type = 2query_cache_size = 16Mquery_cache_limit = 5Mthread_stack = 192kft_min_word_len = 4log_bin = mysql-binbinlog_format = ROWexpire_logs_days = 7log_error = /data/mysql/mysql-error.logslow_query_log = 1long_query_time = 1log_slow_verbosity=query_planslow_query_log_file = /data/mysql/mysql-slow.logperformance_schema = 0skip-external-locking #跳过外部锁定，避免external lockingmyisambulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 16Mmyisam_max_sort_file_size = 1Gmyisam_repair_threaLinuxea = 1InnoDBdefault_storage_engine = InnoDBinnodb_file_per_table = 1innodb_open_files = 500innodb_buffer_pool_size = 2Ginnodb_write_io_threaLinuxea = 8innodb_read_io_threaLinuxea = 8innodb_thread_concurrency = 16innodb_purge_threaLinuxea = 1innodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 8Minnodb_log_file_size = 256Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 10innodb_autoinc_lock_mode = 2innodb_doublewrite = 1innodb_rollback_on_timeout = 1innodb_additional_mem_pool_size = 16Minnodb_force_recovery=0innodb_locks_unsafe_for_binlog = 1interactive_timeout = 28800wait_timeout = 120[mysqldump]quickmax_allowed_packet = 16M[myisamchk]key_buffer_size = 16Msort_buffer_size = 8Mread_buffer = 4Mwrite_buffer = 4M[mysqld_safe]malloc_lib=/usr/lib64/libjemalloc.so.11.3 数据库初始化DELETE FROM mysql.user WHERE User=''; DELETE FROM mysql.db WHERE Db LIKE 'test%'; DROP DATABASE test; UPDATE mysql.user SET password = password('linuxea') WHERE user = 'root'; flush privileges;1.4 在master上创建用户进行同步10.10.240.117 master修改添加server-id = 1 # 主从中，这里的ID号不能一样 log_bin = master-bin binlog_format = ROW read-only = on 1.5 创建复制的用户mysql> grant replication slave on *.* to slave@'%' identified by "password"; Query OK, 0 rows affected (0.00 sec)主节点的logs位置mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 2510 | +------------------+-----------+ 1 row in set (0.00 sec)查看中继日志打开状态mysql> SHOW GLOBAL VARIABLES LIKE '%only'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | innodb_optimize_fulltext_only | OFF | | innodb_read_only | OFF | | read_only | ON | | tx_read_only | OFF | +-------------------------------+-------+ 4 rows in set (0.00 sec)开始主从同步2 在slave上进行同步10.10.0.98 slave可以使用SHOW GLOBAL VARIABLES LIKE '%only';查看状态，如果没有开启则需要开启mysql> SHOW GLOBAL VARIABLES LIKE '%only'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | innodb_optimize_fulltext_only | OFF | | innodb_read_only | OFF | | read_only | ON | | tx_read_only | OFF | +-------------------------------+-------+ 4 rows in set (0.00 sec)打开中继日志和修改idserver-id = 2 # 主从中，这里的ID号不能一样 relay-log = relay-bin 2.1 开始复制开始复制的配置，指明开始的log和点mysql> CHANGE MASTER TO MASTER_HOST='10.10.240.117',MASTER_USER='slave',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=2788,MASTER_CONNECT_RETRY=5,MASTER_HEARTBEAT_PERIOD=2; Query OK, 0 rows affected, 2 warnings (0.00 sec)打开slave io进程和sql进程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.10.240.117 Master_User: slave Master_Port: 3306 Connect_Retry: 5 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 2788 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes mysql> 重点关注这两个状态先 Slave_IO_Running: Yes Slave_SQL_Running: Yes2.2 在第二台从库授权(10.0.1.49)10.0.1.49 slave 同样执行和10.10.0.98一样的操作半复制状态3.半复制在上面已经配置好主从了，现在开启半复制mysql> show global variables like 'have_dynamic_loading'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | have_dynamic_loading | YES |row in set (0.00 sec)在所有的主从节点安装插件（master/slave）mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';Query OK, 0 rows affected (0.10 sec)mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';Query OK, 0 rows affected (0.01 sec)状态信息mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';Variable_nameValuerpl_semi_sync_master_enabledOFFrpl_semi_sync_master_timeout10000# 等待响应时间rpl_semi_sync_master_trace_level32# 跟踪级别rpl_semi_sync_master_wait_no_slaveONrpl_semi_sync_slave_enabledOFFrpl_semi_sync_slave_trace_level32rows in set (0.00 sec)3.1 主节点开启同步(master)配置SET GLOBAL rpl_semi_sync_master_enabled=1; 表示在 master 上已经开启半同步复制模式。mysql> SET GLOBAL rpl_semi_sync_master_enabled=1; Query OK, 0 rows affected (0.00 sec) mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1; Query OK, 0 rows affected (0.00 sec) mysql> SET GLOBAL rpl_semi_sync_master_timeout =4000; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GLOBAL VARIABLES LIKE '%semi%'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 4000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | | rpl_semi_sync_slave_enabled | ON | | rpl_semi_sync_slave_trace_level | 32 | +------------------------------------+-------+ 6 rows in set (0.00 sec) mysql> 3.2 从库半同步(slave)配置mysql> show variables like "rpl_semi_sync_slave_enabled"; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | rpl_semi_sync_slave_enabled | OFF | +-----------------------------+-------+ 1 row in set (0.00 sec)3.3 需要一次停止和开启IO_THREADmysql> STOP SLAVE IO_THREAD; Query OK, 0 rows affected (0.25 sec) mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1; Query OK, 0 rows affected (0.00 sec) mysql> START SLAVE IO_THREAD; Query OK, 0 rows affected (0.00 sec) mysql> show variables like "rpl_semi_sync_slave_enabled"; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | rpl_semi_sync_slave_enabled | ON | +-----------------------------+-------+ 1 row in set (0.00 sec)3.4 在查看主节点状态mysql> show global status like '%semi%'; +--------------------------------------------+---------+ | Variable_name | Value | +--------------------------------------------+---------+ | Rpl_semi_sync_master_clients | 2 | | Rpl_semi_sync_master_net_avg_wait_time | 472 | | Rpl_semi_sync_master_net_wait_time | 1972924 | | Rpl_semi_sync_master_net_waits | 4174 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 573 | | Rpl_semi_sync_master_tx_wait_time | 1578871 | | Rpl_semi_sync_master_tx_waits | 2752 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 2752 | | Rpl_semi_sync_slave_status | OFF | # 主节点上这个是关闭的 +--------------------------------------------+---------+ 15 rows in set (0.00 sec) mysql> 3.5 在查看从节点状态mysql> show global status like 'Rpl_semi_sync_%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 0 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | OFF | # 从节点上这个也是关闭的 | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | | Rpl_semi_sync_slave_status | ON | +--------------------------------------------+-------+ 15 rows in set (0.00 sec) mysql> 注意Rpl_semi_sync_master_yes_tx Semi-sync模式下，成功的事务数 Rpl_semi_sync_master_no_tx 库未及时响应的事务数,如果这个值很大就有问题写入配置文件写可以写入到配置文件中永久生效Master上：[mysqld] rpl_semi_sync_master_enabled=1 rpl_semi_sync_master_timeout=10000 # 10秒（默认）所有Slave上：[mysqld] rpl_semi_sync_slave_enabled=1注意：两台slave服务器设置read_only（由mha设置原master备机为read_only=0 ，从库对外提供读服务，只所以没有写进配置文件，是因为随时slave会提升为master）mysql> select @@read_only; +-------------+ | @@read_only | +-------------+ | 0 |row in set (0.00 sec)mysql> set global read_only=1;Query OK, 0 rows affected (0.00 sec)mysql> select @@read_only;@@read_only1row in set (0.00 sec)4，MHA安装在10.0.1.61 mha-manager安装4.1 mha4mysql-manager安装[root@Linuxea-VM-Node61 ~]# yum install perl-DBD-MySQL -y [root@Linuxea-VM-Node61 ~]# rpm -Uvh https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm [root@Linuxea-VM-Node61 ~]# yum -y install perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes perl-devel \ perl-Module-Install.noarch perl cpan perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager \ perl-Time-HiRes net-tools [root@Linuxea-VM-Node61 ~]# wget https://downloads.mariadb.com/MHA/mha4mysql-manager-0.56.tar.gz [root@Linuxea-VM-Node61 ~]# tar xf mha4mysql-manager-0.56.tar.gz [root@Linuxea-VM-Node61 ~]# cd mha4mysql-manager-0.56/安装便是一路回车和yes即可[root@Linuxea-VM-Node61 ~/mha4mysql-manager-0.56]# perl Makefile.PL *** Module::AutoInstall version 1.03 *** Checking for Perl dependencies...DBI ...loaded. (1.627)DBD::mysql ...loaded. (4.023)Time::HiRes ...loaded. (1.9725)Config::Tiny ...loaded. (2.14)Log::Dispatch ...loaded. (2.41)Parallel::ForkManager ...loaded. (1.18)MHA::NodeConst ...missing.==> Auto-install the 1 mandatory module(s) from CPAN? [y] y* Dependencies will be installed the next time you type 'make'.* Module::AutoInstall configuration finished.Checking if your kit is complete...Would you like to configure as much as possible automatically? [yes] yesWould you like me to automatically choose some CPAN mirrorsites for you? (This means connecting to the Internet) [yes] yesLooks goodWarning: prerequisite MHA::NodeConst 0 not found.Writing Makefile for mha4mysql::managerWriting MYMETA.yml and MYMETA.json[root@Linuxea-VM-Node61 ~/mha4mysql-manager-0.56]# make[root@Linuxea-VM-Node61 ~/mha4mysql-manager-0.56]# make install### 4.2 mha4mysql-node安装(所有MHA NODE节点都需要安装,包括MHA管理节点)rpm -Uvh https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpmyum -y install perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes perl-devel \perl-Module-Install.noarch perl cpan perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager \perl-Time-HiRes net-tools wget https://downloaLinuxea.mariadb.com/MHA/mha4mysql-node-0.56.tar.gztar xf mha4mysql-node-0.56.tar.gz cd mha4mysql-node-0.56/perl Makefile.PLmake make install4.3 所有机器授权mha账户创建mha用户grant all privileges on *.* to mha@'%' identified by 'password_123';4.4 管理配置文件配置(MHA 主机10.0.1.61 )创建工作目录mkdir /data/masterha/logs -p 配置文件如下vim /etc/masterha_default.cnf[server default] # 授权的账户和密码 user=mha password=password_123 # 工作目录和日志文件为孩子 manager_workdir=/data/masterha manager_log=/data/masterha/manager.log # bing-log的保存位置 master_binlog_dir=/data/mysql #设置远端mysql在发生切换时binlog的保存位置 remote_workdir=/data/masterha # 免密钥登录的账户名 ssh_user=root # 授权的数据库复制的账户和密码 repl_user=slave repl_password=password #设置监控主库，发送ping包的时间间隔，默认是3秒，尝试三次没有回应的时候自动进行railover ping_interval=1 #--------------------------------------------------------------------------------------------------- # 设置故障发生后关闭故障主机脚本（该脚本的主要作用是关闭主机放在发生脑裂,这里没有使用） # shutdown_script="" # 设置手动切换时候的切换脚本(可省略) # master_ip_online_change_script=/data/masterha/master_ip_failover # 设置发生切换后发送的报警的脚本(可省略) # report_script=/data/masterha/send_report # 设置自动failover时候的切换脚本(可省略) # master_ip_failover_script=/data/masterha/master_ip_failover #---------------------------------------------------------------------------------------------------- # secondary_check_script= /usr/local/bin/masterha_secondary_check -s server1 -s server2 --master_host=server1 --master_ip=10.10.240.117 --master6 [server1] hostname=10.10.240.117 #设置master 保存binlog master_binlog_dir=/data/mysql #设置为候选master，如果设置该参数以后，发生主从切换以后将会将此从库提升为主库，即使这个主库不是集群中时间最新的slave candidate_master=1 #relay_log_purge=0 port=3306 #-------------------------check_repl_delay=0-------------------------------- check_repl_delay=0 #默认情况下如果一个slave落后master 100M的relay logs的话，MHA将不会选择该 #slave作为一个新的master，因为对于这个slave的恢复需要花费很长时间通过设置 # check_repl_delay=0,MHA触发切换在选择一个新的master的时候将会忽略复制延时 # 这个参数对于设置了candidate_master=1的主机非常有用 # 因为这个候选主在切换的过程中一定是新的master #--------------------------------------------------------------------------- [server2] hostname=10.10.0.98 port=3306 master_binlog_dir=/data/mysql candidate_master=1 check_repl_delay=0 [server3] hostname=10.0.1.49 port=3306 master_binlog_dir=/data/mysql no_master=14.5验证互信[root@Linuxea-VM-Node146 ~/mha4mysql-node-0.56]# masterha_check_ssh --conf=/etc/masterha_default.cnf Sat May 26 13:29:31 2018 - [info] Reading default configuratoins from /etc/masterha_default.cnf.. Sat May 26 13:29:31 2018 - [info] Reading application default configurations from /etc/masterha_default.cnf.. Sat May 26 13:29:31 2018 - [info] Reading server configurations from /etc/masterha_default.cnf.. Sat May 26 13:29:31 2018 - [info] Starting SSH connection tests.. Sat May 26 13:29:31 2018 - [debug] Sat May 26 13:29:31 2018 - [debug] Connecting via SSH from root@10.10.240.117(10.10.240.117:22) to root@10.10.0.98(10.10.0.98:22).. Sat May 26 13:29:31 2018 - [debug] ok. Sat May 26 13:29:31 2018 - [debug] Connecting via SSH from root@10.10.240.117(10.10.240.117:22) to root@10.0.1.49(10.0.1.49:22).. Sat May 26 13:29:31 2018 - [debug] ok. Sat May 26 13:29:32 2018 - [debug] Sat May 26 13:29:31 2018 - [debug] Connecting via SSH from root@10.10.0.98(10.10.0.98:22) to root@10.10.240.117(10.10.240.117:22).. Sat May 26 13:29:32 2018 - [debug] ok. Sat May 26 13:29:32 2018 - [debug] Connecting via SSH from root@10.10.0.98(10.10.0.98:22) to root@10.0.1.49(10.0.1.49:22).. Sat May 26 13:29:32 2018 - [debug] ok. Sat May 26 13:29:33 2018 - [debug] Sat May 26 13:29:32 2018 - [debug] Connecting via SSH from root@10.0.1.49(10.0.1.49:22) to root@10.10.240.117(10.10.240.117:22).. Sat May 26 13:29:32 2018 - [debug] ok. Sat May 26 13:29:32 2018 - [debug] Connecting via SSH from root@10.0.1.49(10.0.1.49:22) to root@10.10.0.98(10.10.0.98:22).. Sat May 26 13:29:32 2018 - [debug] ok. Sat May 26 13:29:33 2018 - [info] All SSH connection tests passed successfully.4.6 检查配置[root@Linuxea-VM-Node61 ~]# masterha_check_repl --conf=/etc/masterha_default.cnf Sat May 26 14:31:39 2018 - [info] Reading default configuratoins from /etc/masterha_default.cnf.. Sat May 26 14:31:39 2018 - [info] Reading application default configurations from /etc/masterha_default.cnf.. Sat May 26 14:31:39 2018 - [info] Reading server configurations from /etc/masterha_default.cnf.. Sat May 26 14:31:39 2018 - [info] MHA::MasterMonitor version 0.56. Sat May 26 14:31:40 2018 - [info] Dead Servers: Sat May 26 14:31:40 2018 - [info] Alive Servers: Sat May 26 14:31:40 2018 - [info] 10.10.240.117(10.10.240.117:3306) Sat May 26 14:31:40 2018 - [info] 10.10.0.98(10.10.0.98:3306) Sat May 26 14:31:40 2018 - [info] 10.0.1.49(10.0.1.49:3306) Sat May 26 14:31:40 2018 - [info] Alive Slaves: Sat May 26 14:31:40 2018 - [info] 10.10.0.98(10.10.0.98:3306) Version=5.6.40-log (oldest major version between slaves) log-bin:enabled Sat May 26 14:31:40 2018 - [info] Replicating from 10.10.240.117(10.10.240.117:3306) Sat May 26 14:31:40 2018 - [info] Primary candidate for the new Master (candidate_master is set) Sat May 26 14:31:40 2018 - [info] 10.0.1.49(10.0.1.49:3306) Version=5.6.40-log (oldest major version between slaves) log-bin:enabled Sat May 26 14:31:40 2018 - [info] Replicating from 10.10.240.117(10.10.240.117:3306) Sat May 26 14:31:40 2018 - [info] Not candidate for the new Master (no_master is set) Sat May 26 14:31:40 2018 - [info] Current Alive Master: 10.10.240.117(10.10.240.117:3306) Sat May 26 14:31:40 2018 - [info] Checking slave configurations.. Sat May 26 14:31:40 2018 - [warning] relay_log_purge=0 is not set on slave 10.10.0.98(10.10.0.98:3306). Sat May 26 14:31:40 2018 - [warning] relay_log_purge=0 is not set on slave 10.0.1.49(10.0.1.49:3306). Sat May 26 14:31:40 2018 - [info] Checking replication filtering settings.. Sat May 26 14:31:40 2018 - [info] binlog_do_db= , binlog_ignore_db= Sat May 26 14:31:40 2018 - [info] Replication filtering check ok. Sat May 26 14:31:40 2018 - [info] Starting SSH connection tests.. Sat May 26 14:31:42 2018 - [info] All SSH connection tests passed successfully. Sat May 26 14:31:42 2018 - [info] Checking MHA Node version.. Sat May 26 14:31:42 2018 - [info] Version check ok. Sat May 26 14:31:42 2018 - [info] Checking SSH publickey authentication settings on the current master.. Sat May 26 14:31:42 2018 - [info] HealthCheck: SSH to 10.10.240.117 is reachable. Sat May 26 14:31:42 2018 - [info] Master MHA Node version is 0.53. Sat May 26 14:31:42 2018 - [info] Checking recovery script configurations on the current master.. Sat May 26 14:31:42 2018 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/mysql --output_file=/data/masterha/save_binary_logs_test --manager_version=0.56 --start_f Sat May 26 14:31:42 2018 - [info] Connecting to root@10.10.240.117(10.10.240.117).. Creating /data/masterha if not exists.. ok. Checking output directory is accessible or not.. ok. Binlog found at /data/mysql, up to mysql-bin.000001 Sat May 26 14:31:42 2018 - [info] Master setting check done. Sat May 26 14:31:42 2018 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers.. Sat May 26 14:31:42 2018 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=10.10.0.98 --slave_ip=10.10.0.98 --slave_port=3306 --workdir=/data/masterha --targetx Sat May 26 14:31:42 2018 - [info] Connecting to root@10.10.0.98(10.10.0.98:22).. Checking slave recovery environment settings.. Opening /data/mysql/relay-log.info ... ok. Relay log found at /data/mysql, up to relay-bin.000002 Temporary relay log file is /data/mysql/relay-bin.000002 Testing mysql connection and privileges..Warning: Using a password on the command line interface can be insecure. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Sat May 26 14:31:42 2018 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=10.0.1.49 --slave_ip=10.0.1.49 --slave_port=3306 --workdir=/data/masterha --target_vx Sat May 26 14:31:42 2018 - [info] Connecting to root@10.0.1.49(10.0.1.49:22).. Checking slave recovery environment settings.. Opening /data/mysql/relay-log.info ... ok. Relay log found at /data/mysql, up to relay-bin.000002 Temporary relay log file is /data/mysql/relay-bin.000002 Testing mysql connection and privileges..Warning: Using a password on the command line interface can be insecure. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Sat May 26 14:31:43 2018 - [info] Slaves settings check done. Sat May 26 14:31:43 2018 - [info] 10.10.240.117 (current master) +--10.10.0.98 +--10.0.1.49 Sat May 26 14:31:43 2018 - [info] Checking replication health on 10.10.0.98.. Sat May 26 14:31:43 2018 - [info] ok. Sat May 26 14:31:43 2018 - [info] Checking replication health on 10.0.1.49.. Sat May 26 14:31:43 2018 - [info] ok. Sat May 26 14:31:43 2018 - [warning] master_ip_failover_script is not defined. Sat May 26 14:31:43 2018 - [warning] shutdown_script is not defined. Sat May 26 14:31:43 2018 - [info] Got exit code 0 (Not master dead). MySQL Replication Health is OK.报错：Sat May 26 13:35:35 2018 - [error][/usr/local/share/perl5/MHA/Server.pm, ln383] 10.10.0.98(10.10.0.98:3306): User slave does not exist or does not have REPLICATION SLAVE privilege! Other slaves can not start replication from this host.10.10.0.98是候选master，所以要授权有复制的权限GRANT REPLICATION SLAVE ON . TO 'slave'@'%' IDENTIFIED by 'password';使用type查看mysql和mysqlbinlog位置，链接到/usr/bin下,示例如下：ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog高可用配置5.配置VIP到此位置，仅仅配置好了mha，我们测试他的高可用性配置中打开这个配置master_ip_failover_script=/data/masterha/master_ip_failover master_ip_online_change_script=/data/masterha/master_ip_failover脚本内容如下,要设置好VIP，不过一开始的VIP要在主机master上手动设置，脚本中设置VIP地址[root@Linuxea-Node61 /data/masterha]# cat master_ip_failover #!/usr/bin/env perl use strict; use warnings FATAL => 'all'; use Getopt::Long; my ( $command, $ssh_user, $orig_master_host, $orig_master_ip, $orig_master_port, $new_master_host, $new_master_ip, $new_master_port ); my $vip = '10.0.1.161/24'; # Virtual IP #设置VIP地址 my $key = "1"; my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip"; my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down"; $ssh_user = "root"; GetOptions( 'command=s' => \$command, 'ssh_user=s' => \$ssh_user, 'orig_master_host=s' => \$orig_master_host, 'orig_master_ip=s' => \$orig_master_ip, 'orig_master_port=i' => \$orig_master_port, 'new_master_host=s' => \$new_master_host, 'new_master_ip=s' => \$new_master_ip, 'new_master_port=i' => \$new_master_port, ); exit &main(); sub main { print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n"; if ( $command eq "stop" || $command eq "stopssh" ) { # $orig_master_host, $orig_master_ip, $orig_master_port are passed. # If you manage master ip address at global catalog database, # invalidate orig_master_ip here. my $exit_code = 1; eval { print "Disabling the VIP on old master: $orig_master_host \n"; &stop_vip(); $exit_code = 0; }; if ($@) { warn "Got Error: $@\n"; exit $exit_code; } exit $exit_code; } elsif ( $command eq "start" ) { # all arguments are passed. # If you manage master ip address at global catalog database, # activate new_master_ip here. # You can also grant write access (create user, set read_only=0, etc) here. my $exit_code = 10; eval { print "Enabling the VIP - $vip on the new master - $new_master_host \n"; &start_vip(); $exit_code = 0; }; if ($@) { warn $@; exit $exit_code; } exit $exit_code; } elsif ( $command eq "status" ) { print "Checking the Status of the script.. OK \n"; `ssh $ssh_user\@cluster1 \" $ssh_start_vip \"`; exit 0; } else { &usage(); exit 1; } } # A simple system call that enable the VIP on the new master sub start_vip() { `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`; } # A simple system call that disable the VIP on the old_master sub stop_vip() { `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`; } sub usage { print "Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n"; }脚本修改后在配置Master VIP（在主节点/W）[root@Linuxea-Node117 ~/mha4mysql-node-0.56]# /sbin/ifconfig eth0:1 10.0.1.161/24在mha-manager节点启动[root@Linuxea-Node61 /data/masterha]# nohup masterha_manager --conf=/etc/masterha_default.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /data/masterha/manager.log 2>&1 & 观察日志(日志太长，删了一些不需要的)[root@Linuxea-Node61 /data/masterha]# tail -f /data/masterha/manager.log Sun Jun 3 18:12:30 2018 - [info] read_only=1 is not set on slave 10.10.240.117(10.10.240.117:3306). Sun Jun 3 18:12:30 2018 - [warning] relay_log_purge=0 is not set on slave 10.10.240.117(10.10.240.117:3306). Sun Jun 3 18:12:30 2018 - [info] read_only=1 is not set on slave 10.0.1.49(10.0.1.49:3306). Sun Jun 3 18:12:30 2018 - [warning] relay_log_purge=0 is not set on slave 10.0.1.49(10.0.1.49:3306). Sun Jun 3 18:12:30 2018 - [info] Checking replication filtering settings.. Sun Jun 3 18:12:30 2018 - [info] binlog_do_db= , binlog_ignore_db= Sun Jun 3 18:12:30 2018 - [info] Replication filtering check ok. Sun Jun 3 18:12:30 2018 - [info] Starting SSH connection tests.. Sun Jun 3 18:12:31 2018 - [info] All SSH connection tests passed successfully. Sun Jun 3 18:12:31 2018 - [info] Checking MHA Node version.. Sun Jun 3 18:12:32 2018 - [info] Version check ok. Sun Jun 3 18:12:32 2018 - [info] Checking SSH publickey authentication settings on the current master.. Sun Jun 3 18:12:32 2018 - [info] HealthCheck: SSH to 10.10.0.98 is reachable. Sun Jun 3 18:12:32 2018 - [info] Master MHA Node version is 0.56. Sun Jun 3 18:12:32 2018 - [info] Checking recovery script configurations on the current master.. Sun Jun 3 18:12:32 2018 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/mysql --output_file=/data/masterha/save_binary_logs_test --manager_version=0.56 --start_file=mysql-bin.000002 Sun Jun 3 18:12:32 2018 - [info] Connecting to root@10.10.0.98(10.10.0.98).. Creating /data/masterha if not exists.. ok. Checking output directory is accessible or not.. ok. Binlog found at /data/mysql, up to mysql-bin.000002 Sun Jun 3 18:12:32 2018 - [info] Master setting check done. Sun Jun 3 18:12:32 2018 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers.. Sun Jun 3 18:12:32 2018 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=10.10.240.117 --slave_ip=10.10.240.117 --slave_port=3306 --workdir=/data/masterha --target_version=5.6.40-log --manager_version=0.56 --relay_log_info=/data/mysql/relay-log.info --relay_dir=/data/mysql/ --slave_pass=xxx Sun Jun 3 18:12:32 2018 - [info] Connecting to root@10.10.240.117(10.10.240.117:22).. Checking slave recovery environment settings.. Opening /data/mysql/relay-log.info ... ok. Relay log found at /data/mysql, up to relay-bin.000004 Temporary relay log file is /data/mysql/relay-bin.000004 Testing mysql connection and privileges..Warning: Using a password on the command line interface can be insecure. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Sun Jun 3 18:12:32 2018 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=10.0.1.49 --slave_ip=10.0.1.49 --slave_port=3306 --workdir=/data/masterha --target_version=5.6.40-log --manager_version=0.56 --relay_log_info=/data/mysql/relay-log.info --relay_dir=/data/mysql/ --slave_pass=xxx Sun Jun 3 18:12:32 2018 - [info] Connecting to root@10.0.1.49(10.0.1.49:22).. Checking slave recovery environment settings.. Opening /data/mysql/relay-log.info ... ok. Relay log found at /data/mysql, up to relay-bin.000004 Temporary relay log file is /data/mysql/relay-bin.000004 Testing mysql connection and privileges..Warning: Using a password on the command line interface can be insecure. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Sun Jun 3 18:12:32 2018 - [info] Slaves settings check done. Sun Jun 3 18:12:32 2018 - [info] 10.10.0.98 (current master) +--10.10.240.117 +--10.0.1.49 Sun Jun 3 18:12:32 2018 - [info] Checking master_ip_failover_script status: Sun Jun 3 18:12:32 2018 - [info] /data/masterha/master_ip_failover --command=status --ssh_user=root --orig_master_host=10.10.0.98 --orig_master_ip=10.10.0.98 --orig_master_port=3306 IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 10.0.1.161/24=== Checking the Status of the script.. OK ssh: Could not resolve hostname cluster1: Name or service not known Sun Jun 3 18:12:32 2018 - [info] OK. Sun Jun 3 18:12:32 2018 - [warning] shutdown_script is not defined. Sun Jun 3 18:12:32 2018 - [info] Set master ping interval 1 seconds. Sun Jun 3 18:12:32 2018 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes. Sun Jun 3 18:12:32 2018 - [info] Starting ping health check on 10.10.0.98(10.10.0.98:3306).. Sun Jun 3 18:12:32 2018 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..可以运行masterha_master_switch来测试[root@linuea-VM-Node145 /data/masterha]# masterha_master_switch --conf=/etc/masterha_default.cnf --master_state=alive Sun Jun 3 18:05:47 2018 - [info] MHA::MasterRotate version 0.56. Sun Jun 3 18:05:47 2018 - [info] Starting online master switch.. Sun Jun 3 18:05:47 2018 - [info] Sun Jun 3 18:05:47 2018 - [info] * Phase 1: Configuration Check Phase.. Sun Jun 3 18:05:47 2018 - [info] Sun Jun 3 18:05:47 2018 - [info] Reading default configuratoins from /etc/masterha_default.cnf.. Sun Jun 3 18:05:47 2018 - [info] Reading application default configurations from /etc/masterha_default.cnf.. Sun Jun 3 18:05:47 2018 - [info] Reading server configurations from /etc/masterha_default.cnf.. Sun Jun 3 18:05:48 2018 - [info] Current Alive Master: 10.10.240.117(10.10.240.117:3306) Sun Jun 3 18:05:48 2018 - [info] Alive Slaves: Sun Jun 3 18:05:48 2018 - [info] 10.10.0.98(10.10.0.98:3306) Version=5.6.40-log (oldest major version between slaves) log-bin:enabled Sun Jun 3 18:05:48 2018 - [info] Replicating from 10.10.240.117(10.10.240.117:3306) Sun Jun 3 18:05:48 2018 - [info] Primary candidate for the new Master (candidate_master is set) Sun Jun 3 18:05:48 2018 - [info] 10.0.1.49(10.0.1.49:3306) Version=5.6.40-log (oldest major version between slaves) log-bin:enabled Sun Jun 3 18:05:48 2018 - [info] Replicating from 10.10.240.117(10.10.240.117:3306) Sun Jun 3 18:05:48 2018 - [info] Not candidate for the new Master (no_master is set) It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 10.10.240.117(10.10.240.117:3306)? (YES/no): yes Sun Jun 3 18:05:50 2018 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time.. Sun Jun 3 18:05:50 2018 - [info] ok. Sun Jun 3 18:05:50 2018 - [info] Checking MHA is not monitoring or doing failover.. Sun Jun 3 18:05:50 2018 - [info] Checking replication health on 10.10.0.98.. Sun Jun 3 18:05:50 2018 - [info] ok. Sun Jun 3 18:05:50 2018 - [info] Checking replication health on 10.0.1.49.. Sun Jun 3 18:05:50 2018 - [info] ok. Sun Jun 3 18:05:50 2018 - [info] Searching new master from slaves.. Sun Jun 3 18:05:50 2018 - [info] Candidate masters from the configuration file: Sun Jun 3 18:05:50 2018 - [info] 10.10.240.117(10.10.240.117:3306) Version=5.6.40-log log-bin:enabled Sun Jun 3 18:05:50 2018 - [info] 10.10.0.98(10.10.0.98:3306) Version=5.6.40-log (oldest major version between slaves) log-bin:enabled Sun Jun 3 18:05:50 2018 - [info] Replicating from 10.10.240.117(10.10.240.117:3306) Sun Jun 3 18:05:50 2018 - [info] Primary candidate for the new Master (candidate_master is set) Sun Jun 3 18:05:50 2018 - [info] Non-candidate masters: Sun Jun 3 18:05:50 2018 - [info] 10.0.1.49(10.0.1.49:3306) Version=5.6.40-log (oldest major version between slaves) log-bin:enabled Sun Jun 3 18:05:50 2018 - [info] Replicating from 10.10.240.117(10.10.240.117:3306) Sun Jun 3 18:05:50 2018 - [info] Not candidate for the new Master (no_master is set) Sun Jun 3 18:05:50 2018 - [info] Searching from candidate_master slaves which have received the latest relay log events.. Sun Jun 3 18:05:50 2018 - [info] From: 10.10.240.117 (current master) +--10.10.0.98 +--10.0.1.49 To: 10.10.0.98 (new master) +--10.0.1.49 Starting master switch from 10.10.240.117(10.10.240.117:3306) to 10.10.0.98(10.10.0.98:3306)? (yes/NO): yes Sun Jun 3 18:05:53 2018 - [info] Checking whether 10.10.0.98(10.10.0.98:3306) is ok for the new master.. Sun Jun 3 18:05:53 2018 - [info] ok. Sun Jun 3 18:05:53 2018 - [info] ** Phase 1: Configuration Check Phase completed. Sun Jun 3 18:05:53 2018 - [info] Sun Jun 3 18:05:53 2018 - [info] * Phase 2: Rejecting updates Phase.. Sun Jun 3 18:05:53 2018 - [info] Sun Jun 3 18:05:53 2018 - [info] Executing master ip online change script to disable write on the current master: Sun Jun 3 18:05:53 2018 - [info] /data/masterha/master_ip_online_change_script --command=stop --orig_master_host=10.10.240.117 --orig_master_ip=10.10.240.117 --orig_master_port=3306 --orig_master_user='mha' --orig_master_password='password_123' --new_master_host=10.10.0.98 --new_master_ip=10.10.0.98 --new_master_port=3306 --new_master_user='mha' --new_master_password='password_123' --orig_master_ssh_user=root --new_master_ssh_user=root Unknown option: orig_master_user Unknown option: orig_master_password Unknown option: new_master_user Unknown option: new_master_password Unknown option: orig_master_ssh_user Unknown option: new_master_ssh_user IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 10.0.1.161/24=== Disabling the VIP on old master: 10.10.240.117 Sun Jun 3 18:05:53 2018 - [info] ok. Sun Jun 3 18:05:53 2018 - [info] Locking all tables on the orig master to reject updates from everybody (including root): Sun Jun 3 18:05:53 2018 - [info] Executing FLUSH TABLES WITH READ LOCK.. Sun Jun 3 18:05:53 2018 - [info] ok. Sun Jun 3 18:05:53 2018 - [info] Orig master binlog:pos is mysql-bin.000002:120. Sun Jun 3 18:05:53 2018 - [info] Waiting to execute all relay logs on 10.10.0.98(10.10.0.98:3306).. Sun Jun 3 18:05:53 2018 - [info] master_pos_wait(mysql-bin.000002:120) completed on 10.10.0.98(10.10.0.98:3306). Executed 0 events. Sun Jun 3 18:05:53 2018 - [info] done. Sun Jun 3 18:05:53 2018 - [info] Getting new master's binlog name and position.. Sun Jun 3 18:05:53 2018 - [info] mysql-bin.000001:637 Sun Jun 3 18:05:53 2018 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='10.10.0.98', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=637, MASTER_USER='slave', MASTER_PASSWORD='xxx'; Sun Jun 3 18:05:53 2018 - [info] Executing master ip online change script to allow write on the new master: Sun Jun 3 18:05:53 2018 - [info] /data/masterha/master_ip_online_change_script --command=start --orig_master_host=10.10.240.117 --orig_master_ip=10.10.240.117 --orig_master_port=3306 --orig_master_user='mha' --orig_master_password='password_123' --new_master_host=10.10.0.98 --new_master_ip=10.10.0.98 --new_master_port=3306 --new_master_user='mha' --new_master_password='password_123' --orig_master_ssh_user=root --new_master_ssh_user=root Unknown option: orig_master_user Unknown option: orig_master_password Unknown option: new_master_user Unknown option: new_master_password Unknown option: orig_master_ssh_user Unknown option: new_master_ssh_user IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 10.0.1.161/24=== Enabling the VIP - 10.0.1.161/24 on the new master - 10.10.0.98 Sun Jun 3 18:05:53 2018 - [info] ok. Sun Jun 3 18:05:53 2018 - [info] Sun Jun 3 18:05:53 2018 - [info] * Switching slaves in parallel.. Sun Jun 3 18:05:53 2018 - [info] Sun Jun 3 18:05:53 2018 - [info] -- Slave switch on host 10.0.1.49(10.0.1.49:3306) started, pid: 3835 Sun Jun 3 18:05:53 2018 - [info] Sun Jun 3 18:05:54 2018 - [info] Log messages from 10.0.1.49 ... Sun Jun 3 18:05:54 2018 - [info] Sun Jun 3 18:05:53 2018 - [info] Waiting to execute all relay logs on 10.0.1.49(10.0.1.49:3306).. Sun Jun 3 18:05:53 2018 - [info] master_pos_wait(mysql-bin.000002:120) completed on 10.0.1.49(10.0.1.49:3306). Executed 0 events. Sun Jun 3 18:05:53 2018 - [info] done. Sun Jun 3 18:05:53 2018 - [info] Resetting slave 10.0.1.49(10.0.1.49:3306) and starting replication from the new master 10.10.0.98(10.10.0.98:3306).. Sun Jun 3 18:05:53 2018 - [info] Executed CHANGE MASTER. Sun Jun 3 18:05:53 2018 - [info] Slave started. Sun Jun 3 18:05:54 2018 - [info] End of log messages from 10.0.1.49 ... Sun Jun 3 18:05:54 2018 - [info] Sun Jun 3 18:05:54 2018 - [info] -- Slave switch on host 10.0.1.49(10.0.1.49:3306) succeeded. Sun Jun 3 18:05:54 2018 - [info] Unlocking all tables on the orig master: Sun Jun 3 18:05:54 2018 - [info] Executing UNLOCK TABLES.. Sun Jun 3 18:05:54 2018 - [info] ok. Sun Jun 3 18:05:54 2018 - [info] All new slave servers switched successfully. Sun Jun 3 18:05:54 2018 - [info] Sun Jun 3 18:05:54 2018 - [info] * Phase 5: New master cleanup phase.. Sun Jun 3 18:05:54 2018 - [info] Sun Jun 3 18:05:54 2018 - [info] 10.10.0.98: Resetting slave info succeeded. Sun Jun 3 18:05:54 2018 - [info] Switching master to 10.10.0.98(10.10.0.98:3306) completed successfully.如果没有报错，我们需要重新组成mha，因为运行masterha_master_switch已经使mha主节点(10.10.240.117)发生改变，将剔除的机器重新同步主从即可，也就是说原来的主节点(10.10.240.117)已经手动切换到候选节点10.10.0.98了，10.10.240.117需要重新同步加入主从模拟故障现在主节点是10.10.0.98，关闭master（10.10.0.98）的mysql，模拟自动切换，脚本在上面已经有注意：现在已经将10.10.240.117组成一主两从了，配置中候选节点分别是10.10.240.117和10.10.0.98，现在关闭98，主节点将会切换到10.10.240.117，并且vip也会随之偏移[root@Linuxea-Node98 ~/mha4mysql-node-0.56]# /etc/init.d/mysqld stop Shutting down MySQL..... SUCCESS! 观察日志（日志贴的不完整）：Sun Jun 3 18:21:58 2018 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away) Sun Jun 3 18:21:58 2018 - [info] Executing SSH check script: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/mysql --output_file=/data/masterha/save_binary_logs_test --manager_version=0.56 --binlog_prefix=mysql-bin Sun Jun 3 18:21:58 2018 - [info] HealthCheck: SSH to 10.10.0.98 is reachable. Sun Jun 3 18:21:59 2018 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '10.10.0.98' (111)) Sun Jun 3 18:21:59 2018 - [warning] Connection failed 1 time(s).. Sun Jun 3 18:22:00 2018 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '10.10.0.98' (111)) Sun Jun 3 18:22:00 2018 - [warning] Connection failed 2 time(s).. Sun Jun 3 18:22:01 2018 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '10.10.0.98' (111)) Sun Jun 3 18:22:01 2018 - [warning] Connection failed 3 time(s).. Sun Jun 3 18:22:01 2018 - [warning] Master is not reachable from health checker! Sun Jun 3 18:22:01 2018 - [warning] Master 10.10.0.98(10.10.0.98:3306) is not reachable! Sun Jun 3 18:22:01 2018 - [warning] SSH is reachable. Sun Jun 3 18:22:01 2018 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/masterha_default.cnf again, and trying to connect to all servers to check server status.. Sun Jun 3 18:22:02 2018 - [info] 10.0.1.49(10.0.1.49:3306) Sun Jun 3 18:22:02 2018 - [info] Alive Slaves: Sun Jun 3 18:22:02 2018 - [info] 10.10.240.117(10.10.240.117:3306) Version=5.6.40-log (oldest major version between slaves) log-bin:enabled Sun Jun 3 18:22:02 2018 - [info] Replicating from 10.10.0.98(10.10.0.98:3306) Sun Jun 3 18:22:02 2018 - [info] Primary candidate for the new Master (candidate_master is set) Sun Jun 3 18:22:02 2018 - [info] 10.0.1.49(10.0.1.49:3306) Version=5.6.40-log (oldest major version between slaves) log-bin:enabled Sun Jun 3 18:22:02 2018 - [info] Replicating from 10.10.0.98(10.10.0.98:3306) Sun Jun 3 18:22:02 2018 - [info] Not candidate for the new Master (no_master is set) Sun Jun 3 18:22:02 2018 - [info] Checking slave configurations.. Sun Jun 3 18:22:02 2018 - [info] read_only=1 is not set on slave 10.10.240.117(10.10.240.117:3306) Sun Jun 3 18:22:03 2018 - [info] Dead Servers: Sun Jun 3 18:22:03 2018 - [info] 10.10.0.98(10.10.0.98:3306) Sun Jun 3 18:22:03 2018 - [info] Checking master reachability via mysql(double check).. Sun Jun 3 18:22:03 2018 - [info] ok. Sun Jun 3 18:22:03 2018 - [info] Alive Servers: Sun Jun 3 18:22:03 2018 - [info] 10.10.240.117(10.10.240.117:3306) Sun Jun 3 18:22:03 2018 - [info] 10.0.1.49(10.0.1.49:3306) Sun Jun 3 18:22:03 2018 - [info] Alive Slaves: Sun Jun 3 18:22:03 2018 - [info] 10.10.240.117(10.10.240.117:3306) Version=5.6.40-log (oldest major version between slaves) log-bin:enabled Sun Jun 3 18:22:03 2018 - [info] Replicating from 10.10.0.98(10.10.0.98:3306) Sun Jun 3 18:22:03 2018 - [info] Primary candidate for the new Master (candidate_master is set) Sun Jun 3 18:22:03 2018 - [info] 10.0.1.49(10.0.1.49:3306) Version=5.6.40-log (oldest major version between slaves) log-bin:enabled Sun Jun 3 18:22:03 2018 - [info] Replicating from 10.10.0.98(10.10.0.98:3306) Sun Jun 3 18:22:03 2018 - [info] Not candidate for the new Master (no_master is set) Sun Jun 3 18:22:03 2018 - [info] ** Phase 1: Configuration Check Phase completed. Sun Jun 3 18:22:03 2018 - [info] Sun Jun 3 18:22:03 2018 - [info] * Phase 2: Dead Master Shutdown Phase.. Sun Jun 3 18:22:03 2018 - [info] Sun Jun 3 18:22:03 2018 - [info] Forcing shutdown so that applications never connect to the current master.. Sun Jun 3 18:22:03 2018 - [info] Executing master IP deactivatation script: Sun Jun 3 18:22:03 2018 - [info] /data/masterha/master_ip_failover --orig_master_host=10.10.0.98 --orig_master_ip=10.10.0.98 --orig_master_port=3306 --command=stopssh --ssh_user=root IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 10.0.1.161/24=== Disabling the VIP on old master: 10.10.0.98 Sun Jun 3 18:22:03 2018 - [info] done Sun Jun 3 18:22:04 2018 - [info] Not candidate for the new Master (no_master is set) Sun Jun 3 18:22:04 2018 - [info] Searching from candidate_master slaves which have received the latest relay log events.. Sun Jun 3 18:22:04 2018 - [info] New master is 10.10.240.117(10.10.240.117:3306) Sun Jun 3 18:22:04 2018 - [info] Starting master failover.. Sun Jun 3 18:22:04 2018 - [info] From: 10.10.0.98 (current master) +--10.10.240.117 +--10.0.1.49 To: 10.10.240.117 (new master) +--10.0.1.49 Sun Jun 3 18:22:04 2018 - [info] Sun Jun 3 18:22:04 2018 - [info] * Phase 3.3: New Master Diff Log Generation Phase.. Sun Jun 3 18:22:04 2018 - [info] Read_Master_Log_Pos(mysql-bin.000002:120). No need to recover from Exec_Master_Log_Pos. Sun Jun 3 18:22:04 2018 - [info] Connecting to the target slave host 10.10.240.117, running recover script.. Sun Jun 3 18:22:04 2018 - [info] Executing command: apply_diff_relay_logs --command=apply --slave_user='mha' --slave_host=10.10.240.117 --slave_ip=10.10.240.117 --slave_port=3306 --apply_files=/data/masterha/saved_master_binlog_from_10.10.0.98_3306_20180603182202.binlog --workdir=/data/masterha --target_version=5.6.40-log --timestamp=20180603182202 --handle_raw_binlog=1 --disable_log_bin=0 --manager_version=0.56 --slave_pass=xxx Sun Jun 3 18:22:04 2018 - [info] MySQL client version is 5.6.40. Using --binary-mode. Applying differential binary/relay log files /data/masterha/saved_master_binlog_from_10.10.0.98_3306_20180603182202.binlog on 10.10.240.117:3306. This may take long time... Applying log files succeeded. Sun Jun 3 18:22:04 2018 - [info] All relay logs were successfully applied. Sun Jun 3 18:22:04 2018 - [info] Getting new master's binlog name and position.. Sun Jun 3 18:22:04 2018 - [info] mysql-bin.000002:532 Sun Jun 3 18:22:04 2018 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='10.10.240.117', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=532, MASTER_USER='slave', MASTER_PASSWORD='xxx'; Sun Jun 3 18:22:04 2018 - [info] Executing master IP activate script: Sun Jun 3 18:22:04 2018 - [info] /data/masterha/master_ip_failover --command=start --ssh_user=root --orig_master_host=10.10.0.98 --orig_master_ip=10.10.0.98 --orig_master_port=3306 --new_master_host=10.10.240.117 --new_master_ip=10.10.240.117 --new_master_port=3306 --new_master_user='mha' --new_master_password='password_123' Unknown option: new_master_user Unknown option: new_master_password IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 10.0.1.161/24=== Enabling the VIP - 10.0.1.161/24 on the new master - 10.10.240.117 Sun Jun 3 18:22:04 2018 - [info] OK. Sun Jun 3 18:22:06 2018 - [info] Slave started. Sun Jun 3 18:22:06 2018 - [info] End of log messages from 10.0.1.49. Sun Jun 3 18:22:06 2018 - [info] Master failover to 10.10.240.117(10.10.240.117:3306) completed successfully. Sun Jun 3 18:22:06 2018 - [info] Deleted server2 entry from /etc/masterha_default.cnf . Sun Jun 3 18:22:06 2018 - [info] ----- Failover Report ----- masterha_default: MySQL Master failover 10.10.0.98 to 10.10.240.117 succeeded Master 10.10.0.98 is down! Check MHA Manager logs at DS-VM-Node145.cluster.com:/data/masterha/manager.log for details. Started automated(non-interactive) failover. Invalidated master IP address on 10.10.0.98. The latest slave 10.10.240.117(10.10.240.117:3306) has all relay logs for recovery. Selected 10.10.240.117 as a new master. 10.10.240.117: OK: Applying all logs succeeded. 10.10.240.117: OK: Activated master IP address. 10.0.1.49: This host has the latest relay log events. Generating relay diff files from the latest slave succeeded. 10.0.1.49: OK: Applying all logs succeeded. Slave started, replicating from 10.10.240.117. 10.10.240.117: Resetting slave info succeeded. Master failover to 10.10.240.117(10.10.240.117:3306) completed successfully.如果配置没有问题,vip会如愿飘到配置文件中的备用master(10.10.240.117)上，而主从就剩下10.10.240.117(主)，10.0.1.49(从)[root@Linuxea-Node117 ~]# ip a|grep eth0 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP qlen 1000 inet 10.10.240.117/8 brd 10.255.255.255 scope global dynamic eth0 inet 10.0.1.161/24 brd 10.0.1.255 scope global eth0:0现在只有10.10.240.117(master)和10.0.1.49(slave),并且mha节点的配置文件已经发生变更,默认会将关闭的节点，或者说是宕机的mysql节点配置会被剔除，大概如下(如果恢复需要将配置加回来)：[server default] manager_log=/data/masterha/manager.log manager_workdir=/data/masterha master_binlog_dir=/data/mysql master_ip_failover_script=/data/masterha/master_ip_failover master_ip_online_change_script=/data/masterha/master_ip_online_change_script password=password_123 ping_interval=1 remote_workdir=/data/masterha repl_password=password repl_user=slave ssh_user=root user=mha [server1] candidate_master=1 check_repl_delay=0 hostname=10.10.240.117 master_binlog_dir=/data/mysql port=3306 [server3] hostname=10.0.1.49 master_binlog_dir=/data/mysql no_master=1 port=3306恢复节点删除mha上的/data/masterha/masterha_default.failover.complete6.恢复节点现在候选节点已经接替原有的master，原有的(10.10.0.98)master以从节点的角色上线即可(总之需要重新加入到主从中)，也就是说将10.10.0.98重新同步主节点mysql> reset slave; Query OK, 0 rows affected (0.00 sec) mysql> CHANGE MASTER TO -> MASTER_HOST='10.10.240.117', -> MASTER_USER='slave', -> MASTER_PASSWORD='password', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mysql-bin.000002', -> MASTER_LOG_POS=1653, -> MASTER_CONNECT_RETRY=20; 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.10.240.117 Master_User: slave Master_Port: 3306 Connect_Retry: 20 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 1653 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes注意：故障后配置文件/etc/masterha_default.cnf已经变更，需要添加原主节点的信息(10.10.0.98),因为在切换的过程中配置将被删除将配置添加[server2] candidate_master=1 check_repl_delay=0 hostname=10.10.0.98 master_binlog_dir=/data/mysql port=3306并且此IP地址是10.10.240.117，并不是VIP地址，VIP地址只会随着脚本飘逸供应用使用（也就是会飘逸到主节点）

linuxea:sysbench1.0.14压力测试对比 sysbench是基于LuaJIT的可脚本化多线程基准测试工具。它最常用于数据库基准测试，但也可用于创建不涉及数据库服务器的任意复杂工作负载。sysbench附带了以下功能：oltp_*.lua：类似OLTP的数据库基准测试的集合fileio：文件系统级基准测试cpu： 简单的CPU基准测试memory：内存访问基准测试threads：基于线程的调度程序基准测试mutex：一个POSIX互斥体基准测试特点：可以统计速率和延迟，包括延迟百分点数形成简单的排序即使有数千个并发，开销也很低。sysbench能够每秒生成，跟踪数以万计的事件通过lua脚本可以实现预定义的测试条件，能够轻松完成新的基础测试更多请访问：https://github.com/akopytov/sysbench1,安装curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash sudo yum -y install sysbench2，zabbix-agent安装curl -Lk https://raw.githubusercontent.com/LinuxEA-Mark/zabbix3.0.2-complete-works/master/zabbix-install/zabbix-install.sh|bash -s load 10.10.240.114授权用户监控GRANT SELECT ON *.* TO 'zabbix'@'127.0.0.1' IDENTIFIED BY 'password';下载监控脚本（zabbix模板在github上）curl -Lk https://raw.githubusercontent.com/LinuxEA-Mark/zabbix3.0.2-complete-works/master/mysql_QPTS/mariadb/install_db_status.sh|bash提供的lua脚本[root@DS-VM-Node49 ~]# ll /usr/local/sysbench/share/sysbench/ 总用量 60 -rwxr-xr-x 1 root root 1446 5月 21 14:45 bulk_insert.lua -rw-r--r-- 1 root root 14468 5月 21 14:45 oltp_common.lua -rwxr-xr-x 1 root root 1290 5月 21 14:45 oltp_delete.lua -rwxr-xr-x 1 root root 2415 5月 21 14:45 oltp_insert.lua -rwxr-xr-x 1 root root 1265 5月 21 14:45 oltp_point_select.lua -rwxr-xr-x 1 root root 1649 5月 21 14:45 oltp_read_only.lua -rwxr-xr-x 1 root root 1824 5月 21 14:45 oltp_read_write.lua -rwxr-xr-x 1 root root 1118 5月 21 14:45 oltp_update_index.lua -rwxr-xr-x 1 root root 1127 5月 21 14:45 oltp_update_non_index.lua -rwxr-xr-x 1 root root 1440 5月 21 14:45 oltp_write_only.lua -rwxr-xr-x 1 root root 1933 5月 21 14:45 select_random_points.lua -rwxr-xr-x 1 root root 2132 5月 21 14:45 select_random_ranges.lua3,创建一个表mysql> create database db_20180528; Query OK, 1 row affected (0.00 sec)4,授权压测用户权限GRANT all ON db_20180528.* TO 'mark'@'%' identified by 'password'; 5,压测的参数sysbench /usr/share/sysbench/oltp_read_write.lua \ --mysql-host=10.10.240.145 \ # IP --mysql-port=3336 \ # 端口 --mysql-user=mark \ # 用户名 --mysql-password=password \ # 密码 --mysql-db=db_20180528 \ # 库名 --db-driver=mysql \ # 数据库类型 --tables=10 \ # 生成 10 个测试表 --table-size=3000000 \ # 每个测试表填充数据量为 100000 --report-interval=10 \ # 定期以秒为单位报告具有指定间隔的中间统计信息 0禁用中间报告[0] --threads=250 \ # 线程数 --time=60 \ # 执行时长为 120秒 prepare其中run执行真正的压测，cleanup用来清除数据和表，一般先用prepare构建表结构,如下：sysbench /usr/share/sysbench/oltp_read_write.lua \ --mysql-host=10.10.240.145 \ --mysql-port=3336 \ --mysql-user=mark \ --mysql-password=password \ --mysql-db=db20180528_3336 \ --db-driver=mysql \ --tables=10 \ --table-size=1000000 \ --report-interval=10 \ --threads=80 \ --time=60 \ run实际prepare的表结构可能是这样的：MariaDB [sbtest]> desc sbtest.sbtest1; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | k | int(10) unsigned | NO | MUL | 0 | | | c | char(120) | NO | | | | | pad | char(60) | NO | | | |rows in set (0.00 sec)上面的测试命令代表的是：对mysql进行oltp基准测试，表数量10，每表行数约10w（几乎delete多少就会insert的多少），并且是非事务的只读测试，持续60s，并发线程数80个。6，负载均衡只读压测需要说明的选项：我们可以把threads依次递增（成倍数的递加），或者调整my.cnf参数，比较效果。另外需要注意的是，大部分mysql中间件对事务的处理，默认都是把sql发到主库执行，所以只读测试需要加上skip-trx=on来跳过测试中的显式事务。请注意：我们使用maxscale的Readconnroute路由负载进行测试，集群MariaDB Galera Clustersysbench /usr/share/sysbench/oltp_read_only.lua \ --mysql-host=10.10.240.145 \ --mysql-port=3336 \ --mysql-user=mark \ --mysql-password=password \ --mysql-db=db20180528_3336 \ --db-driver=mysql \ --tables=10 \ --table-size=1000000 \ --report-interval=10 \ --threads=80 \ --time=60 \ --skip-trx=on \ run注意：先使用prepare，完成后使用run，清楚数据使用cleanup，prepare一次可以多次重复使用结果如下：[ 10s ] thds: 80 tps: 2050.04 qps: 28756.83 (r/w/o: 28756.83/0.00/0.00) lat (ms,95%): 112.67 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 80 tps: 2322.46 qps: 32507.76 (r/w/o: 32507.76/0.00/0.00) lat (ms,95%): 121.08 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 80 tps: 2841.30 qps: 39772.25 (r/w/o: 39772.25/0.00/0.00) lat (ms,95%): 74.46 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 80 tps: 3247.04 qps: 45465.87 (r/w/o: 45465.87/0.00/0.00) lat (ms,95%): 66.84 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 80 tps: 3294.92 qps: 46130.73 (r/w/o: 46130.73/0.00/0.00) lat (ms,95%): 69.29 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 80 tps: 3324.43 qps: 46544.00 (r/w/o: 46544.00/0.00/0.00) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 2392432 write: 0 other: 0 total: 2392432 transactions: 170888 (2845.82 per sec.) queries: 2392432 (39841.44 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 60.0471s total number of events: 170888 Latency (ms): min: 6.66 avg: 28.09 max: 2301.15 95th percentile: 71.83 sum: 4801074.58 Threads fairness: events (avg/stddev): 2136.1000/730.42 execution time (avg/stddev): 60.0134/0.01在用sysbench压的时候，在mysql后端会话里有时看到大量的query cache lock，如果使用的是uniform取样，最好把查询缓存关掉。当然如果是做两组性能对比压测，因为都受这个因素影响，关心也不大。负载均衡的监控图指标如下6，混合读写混合读写我们使用maxscale的Readwritesplit读写分离进行测试，集群MariaDB Galera Cluster，逻辑主从如下：Server changed state: server1[10.10.240.202:3306]: new_master. [Running] -> [Master, Synced, Running] Server changed state: server2[10.10.240.203:3306]: new_slave. [Running] -> [Slave, Synced, Running] Server changed state: server3[10.10.240.146:3306]: new_slave. [Running] -> [Slave, Synced, Running]在1.0版本中混合读写oltp_read_write.lua这个脚本即可我们创建db20180528_3337库进行混合读写的测试sysbench /usr/share/sysbench/oltp_read_write.lua \ --mysql-host=10.10.240.145 \ --mysql-port=3337 \ --mysql-user=mark \ --mysql-password=password \ --mysql-db=db20180528_3337 \ --db-driver=mysql \ --tables=10 \ --table-size=1000000 \ --report-interval=10 \ --threads=80 \ --time=60 \ --skip-trx=on \ run注意：先使用prepare，完成后使用run，清楚数据使用cleanup，prepare一次可以多次重复使用注意：如果不加oltp-skip-trx=on，那么所有查询都会发往主库，但如果在有写入的情况下使用--oltp-skip-trx=on跳过BEGIN和COMMIT结果如下：[ 10s ] thds: 80 tps: 132.06 qps: 2495.40 (r/w/o: 1957.85/267.63/269.92) lat (ms,95%): 1506.29 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 80 tps: 161.21 qps: 2903.16 (r/w/o: 2258.92/325.62/318.62) lat (ms,95%): 1561.52 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 80 tps: 163.90 qps: 2948.16 (r/w/o: 2293.07/339.90/315.20) lat (ms,95%): 1836.24 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 80 tps: 191.70 qps: 3450.23 (r/w/o: 2685.83/397.60/366.80) lat (ms,95%): 1401.61 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 80 tps: 202.30 qps: 3640.71 (r/w/o: 2832.33/431.59/376.79) lat (ms,95%): 1479.41 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 80 tps: 242.71 qps: 4367.04 (r/w/o: 3394.31/513.12/459.61) lat (ms,95%): 1304.21 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 154266 write: 22895 other: 21181 total: 198342 transactions: 11019 (182.90 per sec.) queries: 198342 (3292.19 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 60.2444s total number of events: 11019 Latency (ms): min: 9.71 avg: 436.60 max: 4126.11 95th percentile: 1506.29 sum: 4810859.89 Threads fairness: events (avg/stddev): 137.7375/15.14 execution time (avg/stddev): 60.1357/0.06监控如下:试试updatesysbench /usr/share/sysbench/oltp_update_index.lua \ --mysql-host=10.10.240.145 \ --mysql-port=3337 \ --mysql-user=mark \ --mysql-password=password \ --mysql-db=db20180528_3337 \ --db-driver=mysql \ --tables=10 \ --table-size=1000000 \ --report-interval=10 \ --threads=80 \ --time=60 \ --skip-trx=on 注意：先使用prepare，完成后使用run，清楚数据使用cleanup，prepare一次可以多次重复使用生成报告执行结果sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2) Running the test with following options: Number of threads: 80 Report intermediate results every 10 second(s) Initializing random number generator from current time Initializing worker threads... Threads started! [ 10s ] thds: 80 tps: 1249.32 qps: 1249.32 (r/w/o: 0.00/482.19/767.13) lat (ms,95%): 320.17 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 80 tps: 1672.80 qps: 1672.80 (r/w/o: 0.00/646.40/1026.40) lat (ms,95%): 253.35 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 80 tps: 1909.94 qps: 1909.94 (r/w/o: 0.00/740.21/1169.72) lat (ms,95%): 227.40 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 80 tps: 2265.18 qps: 2265.18 (r/w/o: 0.00/868.45/1396.72) lat (ms,95%): 186.54 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 80 tps: 2587.02 qps: 2587.02 (r/w/o: 0.00/986.64/1600.37) lat (ms,95%): 186.54 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 80 tps: 2562.74 qps: 2562.74 (r/w/o: 0.00/990.11/1572.62) lat (ms,95%): 153.02 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 //总select数量 write: 47181 //总update、insert、delete语句数量 other: 75371 //commit、unlock tables以及其他mutex的数量 total: 122552 transactions: 122552 (2040.15 per sec.) //通常需要关注的数字(TPS) queries: 122552 (2040.15 per sec.) ignored errors: 0 (0.00 per sec.) //忽略的错误数 reconnects: 0 (0.00 per sec.) General statistics: total time: 60.0684s //即max-time指定的压测实际 total number of events: 122552 //总的事件数，一般与transactions相同 Latency (ms): min: 0.34 avg: 39.19 //95%的语句的平均响应时间 max: 2665.16 95th percentile: 215.44 sum: 4802756.15 Threads fairness: events (avg/stddev): 1531.9000/148.18 execution time (avg/stddev): 60.0345/0.02updater监控如下：sysbench还可以对文件系统IO测试，CPU性能测试，以及内存分配与传输速度测试这里并不做介绍，sysbench可以作为硬件参数的一个测试对比。至于吞吐连和读写tps需要自己计算

linuxea:Mariadb Galera与MaxScale代理配置参考 Galera集群参考 MariaDB Galera Cluster配置使用MaxScale配置参考 MaxScale1.4.5中间件读写分离示例 中的进行安装其他参考：https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-14/maxscale-administration-tutorial/ https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-14/maxscale-configuration-usage-scenarios/使用MaxScale来作为zabbix读写分离中间件，后面使用Galera集群来做，配置上和之前的示例中相比只需要修改Monitor的一段配置，大致如下：#[MySQL Monitor] #注释掉mysql [Galera Monitor] #启用Galera type=monitor #module=mysqlmon #注释掉mysql的模块 module=galeramon #启用Galera servers=server1,server2,server3 user=mybk passwd=D53C7669B34FF2AE96DB6E21C98A6A54 # 监控心态为 10s monitor_interval=10000 # 当复制slave全部断掉时，maxscale仍然可用，将所有的访问指向master节点 detect_stale_master=true # 监控主从复制延迟，可用后续指定router service的(配置此参数请求会永远落在 master) # detect_replication_lag=true完整的配置文件如下：[maxscale] threads=auto ms_timestamp=1 syslog=0 maxlog=1 log_to_shm=0 log_warning=1 log_notice=0 log_info=0 log_debug=0 log_augmentation=1 #相关目录设置 logdir=/mydata/maxscale/logs/trace/ datadir=/mydata/maxscale/data/ libdir=/usr/lib64/maxscale/ cachedir=/mydata/maxscale/cache/ piddir=/mydata/maxscale/pid/ execdir=/usr/bin/ [server1] type=server address=10.10.240.113 port=3306 protocol=MySQLBackend serv_weight=3 priority=1 [server2] type=server address=10.0.1.61 port=3306 protocol=MySQLBackend serv_weight=3 priority=2 [server3] type=server address=10.0.1.49 port=3306 protocol=MySQLBackend serv_weight=3 priority=3 #[MySQL Monitor] [Galera Monitor] type=monitor #module=mysqlmon module=galeramon servers=server1,server2,server3 user=mybk passwd=D53C7669B34FF2AE96DB6E21C98A6A54 # 监控心态为 10s monitor_interval=10000 # 当复制slave全部断掉时，maxscale仍然可用，将所有的访问指向master节点 detect_stale_master=true # 监控主从复制延迟，可用后续指定router service的(配置此参数请求会永远落在 master) # detect_replication_lag=true #[Read-Only Service] #type=service #router=readconnroute #servers=server1,server2 #user=mybk #passwd=D53C7669B34FF2AE96DB6E21C98A6A54 #router_options=slave #enable_root_user=1 #weightby=serv_weight [Read-Write Service] type=service router=readwritesplit servers=server1,server2,server3 user=mybk passwd=D53C7669B34FF2AE96DB6E21C98A6A54 max_slave_connections=100% # sql语句中的存在变量只指向master中执行 use_sql_variables_in=master # 允许root用户登录执行 enable_root_user=1 # 允许主从最大间隔(s) #max_slave_replication_lag=3600 weightby=serv_weight #maxscale管理节点信息 [MaxAdmin Service] type=service router=cli #各个请求的端口信息 #[Read-Only Listener] #type=listener #service=Read-Only Service #protocol=MySQLClient #port=4008 [Read-Write Listener] type=listener service=Read-Write Service protocol=MySQLClient port=4006 [MaxAdmin Listener] type=listener service=MaxAdmin Service protocol=maxscaled #socket=/mydata/maxscale/tmp/maxadmin.sock port=6603maxscale启动脚本这次添加了启动脚本[root@DS-VM-Linuxea-117 ~]# cat /etc/init.d/maxctld #!/bin/bash ####################Description######################## # You need to know the pid file location # # and the MAXSCALE_HOME location # # and the MAXSCALE_CNF location # # Set default variables # # Author: www.linuxea.com # # Created Time: 2017-07-25 19:53:25 # ####################################################### NOWAIT=0 HELP=0 QUIET=0 MAXSCALE_PID=0 MAXSCALE_PIDFILE=/mydata/maxscale/pid/maxscale.pid MAXSCALE_HOME=/usr/local/maxscale/usr MAXSCALE_CNF=/etc/maxscale.cnf # Get pid of MaxScale if it is running. # Check that the pidfile exists. if [ -e $MAXSCALE_PIDFILE ]; then MAXSCALE_PID=`cat $MAXSCALE_PIDFILE` # Check if the process is running. if [ `ps --no-heading -p $MAXSCALE_PID | wc -l` -eq 0 ]; then MAXSCALE_PID=0 fi fi # Function to print output printmax() { if [ $QUIET -eq 0 ]; then echo $* >&2 fi } # Function to print help helpmax() { echo "Usage: $0 start|stop|status|restart" echo "Options:" echo "-f - MaxScale config file" echo "-h - Show this help" echo "-n - Don't wait for operation to finish before exiting" echo "-q - Quiet operation" } # Function to start maxscale startmax() { # Check if MaxScale is already running. if [ $MAXSCALE_PID -ne 0 ]; then printmax "MaxScale is already running" exit 1 fi # Check that we are running as root if a user to run as is specified. if [ "x$MAXSCALE_USER" != "x" -a `id -u` -ne 0 ]; then printmax "$0 must be run as root" exit 1 fi # Check that we can find maxscale if [ ! -e $MAXSCALE_HOME/bin/maxscale ]; then printmax "Cannot find MaxScale executable ($MAXSCALE_HOME/bin/maxscale)" exit 1 fi # Check that the config file exists, if specified. if [ "x$MAXSCALE_CNF" != "x" -a ! -e "$MAXSCALE_CNF" ]; then printmax "MaxScale configuration file ($MAXSCALE_CNF) not found" exit 1 fi # Start MaxScale if [ "x$MAXSCALE_USER" == "x" ]; then $MAXSCALE_HOME/bin/maxscale -f $MAXSCALE_HOME ${MAXSCALE_CNF:+-f $MAXSCALE_CNF} # $MAXSCALE_HOME/bin/maxscale -c $MAXSCALE_HOME ${MAXSCALE_CNF:+-f $MAXSCALE_CNF} else su $MAXSCALE_USER -m -c "$MAXSCALE_HOME/bin/maxscale -c $MAXSCALE_HOME ${MAXSCALE_CNF:+-f $MAXSCALE_CNF}" fi } # Function to stop maxscale stopmax() { NOWAIT=1 if [ "x$1" == "-n" ]; then NOWAIT=0 fi # Check that we are running as root if a user to run as is specified. if [ "x$MAXSCALE_USER" != "x" -a `id -u` -ne 0 ]; then printmax "$0 must be run as root" exit 1 fi # Check that the pidfile exists. if [ ! -e $MAXSCALE_PIDFILE ]; then printmax "Can't find MaxScale pidfile ($MAXSCALE_PIDFILE)" exit 1 fi MAXSCALE_PID=`cat $MAXSCALE_PIDFILE` # Kill MaxScale kill $MAXSCALE_PID if [ $NOWAIT -ne 0 ]; then # Wait for maxscale to die. while [ `ps --no-heading -p $MAXSCALE_PID | wc -l` -ne 0 ]; do usleep 100000 done MAXSCALE_PID=0 fi } # Function to show the status of MaxScale statusmax() { # Check that the pidfile exists. if [ $MAXSCALE_PID -ne 0 ]; then printmax "MaxScale is running (pid: $MAXSCALE_PID user: `ps -p $MAXSCALE_PID --no-heading -o euser`)" exit 0 fi printmax "MaxScale is not running" exit 1 } # Process options. while getopts ":f:hnq" OPT; do case $OPT in f) MAXSCALE_CNF=$OPTARG ;; h) helpmax exit 0 ;; n) NOWAIT=1 ;; q) QUIET=1 ;; \?) echo "Invalid option: -$OPTARG" ;; esac done # Process arguments following options. shift $((OPTIND - 1)) OPER=$1 # Check that an operation was passed if [ "x$1" == "x" ]; then echo "$0: your must enter an operation: start|stop|restart|status" >&2 exit 1 fi # Handle the operations. case $OPER in start) startmax ;; stop) stopmax ;; status) statusmax ;; restart) if [ $MAXSCALE_PID -ne 0 ]; then NOWAITSAVE=$NOWAIT NOWAIT=0 stopmax NOWAIT=$NOWAITSAVE fi startmax ;; *) echo "Unknown operation: $OPER. Use start|stop|restart|status" exit 1 esac

linuxea:zabbix数据库使用mysql-proxy读写分离 最早使用程序实现的读写不利于扩展，依赖太高。mysql-proxy通过lua脚本来实现读写的分离，当访问数据库的过程中有一个中间件可以将增删改查的语句进行转发到后端的服务器上，如，写操作到主服务器，读操作到从服务器（暂且不提主从实现），那么读多写少的环境中，多台从库分摊到读操作，在这个lua脚本中明确的写明当什么样的条件下将语句发送到什么样的服务器上！我们借鉴上一篇的MariaDB Galera Cluster配置使用集群做读写分离，在之前是内网的zabbix，现在扩展集群后并加入mysql-proxy中间件测试。如下图download:https://downloads.mysql.com/archives/proxy/[root@LinuxEA ~]# wget https://cdn.mysql.com/archives/mysql-proxy/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz [root@LinuxEA ~]# useradd -r mysql-proxy -s /sbin/nologin [root@LinuxEA ~]# tar xf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz -C /usr/local/ [root@LinuxEA ~]# cd /usr/local/ [root@LinuxEA /usr/local]# ln -s mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit mysql-proxy配置启动可以直接在mysql-proxy后面加参数启动和指定配置文件启动--help-all ：获取全部帮助信息； -proxy-address-=host:port ：代理服务监听的地址和端口； --admin-address=host:port ：管理模块监听的地址和端口； --proxy-backend-addresses=host:port ：后端mysql服务器的地址和端口； --proxy-read-only-backend-addresses=host:port ：后端只读mysql服务器的地址和端口； --proxy-lua-script=file_name ：完成mysql代理功能的Lua脚本； --daemon ：以守护进程模式启动mysql-proxy； --keepalive ：在mysql-proxy崩溃时尝试重启之； --log-file=/path/to/log_file_name ：日志文件名称； --log-level=level ：日志级别； --log-use-syslog ：基于syslog记录日志； --plugins=plugin：在mysql-proxy启动时加载的插件； --user=user_name ：运行mysql-proxy进程的用户； --defaults-file=/path/to/conf_file_name ： 默认使用的配置文件路径；其配置段使用[mysql-proxy]标识； --proxy-skip-profiling ： 禁用profile； 启动参数： --plugins=proxy #指定proxy插件，该配置写入配置文件无法启动 --plugins=admin #指定admin插件 --defaults-file=/etc/mysql-proxy.conf #指定配置文件创建配置文件：/etc/mysql-proxy.conf[mysql-proxy] #以后台守护进程方式启动 daemon=true #当进程故障后自动重启 keepalive=true #设置日志级别为debug，可以在调试完成后改成info log-level=debug #设置日志文件路径 log-file=/var/log/mysql-proxy.log #设置mysql-proxy的家目录 basedir=/usr/local/mysql-proxy #指定mysql-proxy的监听地址 proxy-address=10.10.0.98:4040 #设置后台master服务器 proxy-backend-addresses=10.0.1.49:3306 #设置后台从服务器 proxy-read-only-backend-addresses=10.0.1.61:3306,10.10.240.113:3306 #设置读写分离脚本路径 proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua #设置mysql-proxy管理地址，需要admin插件 admin-address=10.10.0.98:4041 #设置登录管理地址用户和密码 admin-username=admin admin-password=admin #设置管理后台lua脚本路径，改脚本默认没有要自动定义 admin-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua启动[root@LinuxEA ~]# chmod 660 /etc/mysql-proxy.conf [root@LinuxEA ~]# chown -R mysql-proxy /usr/local/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/ [root@LinuxEA ~]# /usr/local/mysql-proxy/bin/mysql-proxy --plugins=proxy --plugins=admin --defaults-file=/etc/mysql-proxy.conf [root@LinuxEA ~]# ss -tlnp|grep mysql-proxy LISTEN 0 128 10.10.0.98:4040 *:* users:(("mysql-proxy",pid=15648,fd=10)) LISTEN 0 128 10.10.0.98:4041 *:* users:(("mysql-proxy",pid=15648,fd=11)) [root@LinuxEA ~]# 安装客户端测试：[root@LinuxEA ~]# cat > /etc/yum.repos.d/MariaDB.repo <<EOF [mariadb] name = MariaDB baseurl = https://yum.mariadb.org/10.1/centos7-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1 EOF [root@LinuxEA ~]# yum install MariaDB-client -y授权zabbix-server用户MariaDB [(none)]> GRANT ALL ON zabbix.* To 'zabbix'@'10.10.0.98' IDENTIFIED BY 'password'; Query OK, 0 rows affected (1.34 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.46 sec)测试登陆：[root@LinuxEA ~]# mysql -uzabbix -ppassword -h10.10.0.98 -P4040 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 398742 Server version: 10.0.30-MariaDB-wsrep MariaDB Server, wsrep_25.19.rc3fc46e Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | zabbix | +--------------------+ 2 rows in set (0.01 sec) MariaDB [(none)]> 修改zabbix配置，并且重启,进行测试[root@LinuxEA ~]# egrep -v "^$|^#" /etc/zabbix/zabbix_server.conf LogFile=/var/log/zabbix/zabbix_server.log LogFileSize=0 PidFile=/var/run/zabbix/zabbix_server.pid DBHost=10.10.0.98 DBName=zabbix DBUser=zabbix DBPassword=password DBPort=4040 SNMPTrapperFile=/var/log/snmptrap/snmptrap.log Timeout=4 AlertScriptsPath=/usr/lib/zabbix/alertscripts ExternalScripts=/usr/lib/zabbix/externalscripts LogSlowQueries=3000在查看，up有两个mysql-proxy会检测客户端连接，当连接没有超过min_idle_connections预设值时, 不会进行读写分离, 即查询操作会发生到Master上。我们可以修改，原来大概是这样的：[root@Linuxea ~]# vim /usr/local/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua 37 -- connection pool 38 if not proxy.global.config.rwsplit then 39 | proxy.global.config.rwsplit = { 40 | | min_idle_connections = 4, #超过4个才会进行读写分离 41 | | max_idle_connections = 8, #默认8 ，修改最大连接为2，当终端超过2个，就会开启读写分离 42 43 | | is_debug = false 44 | } 45 end[root@LinuxEA ~]# mysql -uadmin -padmin -h10.10.0.98 -P4041 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.99-agent-admin Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> select * from help; +------------------------+------------------------------------+ | command | description | +------------------------+------------------------------------+ | SELECT * FROM help | shows this help | | SELECT * FROM backends | lists the backends and their state | +------------------------+------------------------------------+ 2 rows in set (0.00 sec) MySQL [(none)]> select * from backends; +-------------+--------------------+-------+------+------+-------------------+ | backend_ndx | address | state | type | uuid | connected_clients | +-------------+--------------------+-------+------+------+-------------------+ | 1 | 10.0.1.49:3306 | up | rw | NULL | 0 | | 2 | 10.0.1.61:3306 | up | ro | NULL | 0 | | 3 | 10.10.240.113:3306 | up | ro | NULL | 0 | +-------------+--------------------+-------+------+------+-------------------+ 3 rows in set (0.00 sec)zabbix-server链接：[root@DS-VM-Linuxea ~]# egrep -v "^$|^#" /etc/zabbix/zabbix_server.conf LogFile=/var/log/zabbix/zabbix_server.log LogFileSize=0 PidFile=/var/run/zabbix/zabbix_server.pid DBHost=10.10.0.98 DBName=zabbix DBUser=zabbix DBPassword=password DBPort=4040 SNMPTrapperFile=/var/log/snmptrap/snmptrap.log Timeout=4 AlertScriptsPath=/usr/lib/zabbix/alertscripts ExternalScripts=/usr/lib/zabbix/externalscripts LogSlowQueries=3000