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使用，有些扯犊子参考：参考：http://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: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

MaxScale1.4.5中间件读写分离示例 当我们配置好主从后，我们要实现读写分离，把读操作分散到从服务器中，并且对多个从服务器能实现负载均衡如果不考虑sharding的话，maxscale算是一个比较好的中间件，方案的话无非就是两个驱动层做jdbc和php都有lb要么就是中间件maxscale或者atlas都可以，性能上 maxscale要好的多，以下是部署的示例10.10.240.114 slave 10.10.240.113 Master 10.10.240.117 MaxScale快速部署mariadb(需要安装docker):curl -LKs https://raw.githubusercontent.com/LinuxEA-Mark/docker-mariaDB/master/docker-mysql-create.sh|bash主从：master配置:MariaDB [(none)]> grant replication slave on *.* to 'mybk'@'10.10.240.114' identified by 'password'; MariaDB [(none)]> flush privileges; MariaDB [(none)]> select version(); +-----------------+ | version() | +-----------------+ | 10.1.19-MariaDB | +-----------------+ 1 row in set (0.00 sec)MariaDB [(none)]> show master status; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-bin.000001 | 620 | | | +-------------------+----------+--------------+------------------+在主从分别允许两台机器能互通iptables -I INPUT 4 -s 10.10.240.114 -p tcp --dport 3306 -j ACCEPT iptables -I INPUT 4 -s 10.10.240.113 -p tcp --dport 3306 -j ACCEPT在主从都需要放行117iptables -I INPUT 4 -s 10.10.240.117 -p tcp --dport 3306 -j ACCEPTslave配置:MariaDB [(none)]> change master to master_host='10.10.240.113',master_user='mybk',master_password='password',master_log_file='master-bin.000001',master_log_pos=924; 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.113 Master_User: mybk Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 1231 Relay_Log_File: DS-VM-Node114-relay-bin.000002 Relay_Log_Pos: 538 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: MariaDB [(none)]> SELECT VERSION(); +-----------------+ | VERSION() | +-----------------+ | 10.1.19-MariaDB | +-----------------+ 1 row in set (0.00 sec) MariaDB [(none)]> SHOW SLAVE HOSTS; +-----------+------+------+-----------+ | Server_id | Host | Port | Master_id | +-----------+------+------+-----------+ | 20 | | 3306 | 1 | +-----------+------+------+-----------+ 1 row in set (0.00 sec) MariaDB [(none)]> MaxScale配置：wget https://downloads.mariadb.com/MaxScale/1.4.5/centos/7Server/x86_64/maxscale-1.4.5-1.centos.7.x86_64.rpm yum install -y maxscale-1.4.5-1.centos.7.x86_64.rpm在主库上执行：GRANT SELECT ON *.* TO 'mybk'@'10.10.240.117' identified by 'password'; GRANT SHOW DATABASES ON *.* TO mybk@'10.10.240.117'; grant REPLICATION SLAVE on *.* to mybk@'10.10.240.117'; grant REPLICATION CLIENT on *.* to mybk@'10.10.240.117';创建目录groupadd maxscale useradd -g maxscale maxscale mkdir -p /mydata/maxscale/{data,cache,logs,tmp,pid} mkdir -p /mydata/maxscale/logs/{binlog,trace} chown -R maxscale:maxscale /mydata/maxscale /usr/local/maxscale/usr/bin/maxkeys /mydata/maxscale/data/ /usr/local/maxscale/bin/maxpasswd /mydata/maxscale/data/.secrets password D53C7669B34FF2AE96DB6E21C98A6A54MaxScale配置文件：[maxscale] threads=auto ms_timestamp=1 syslog=1 maxlog=1 log_to_shm=0 log_warning=1 log_notice=1 log_info=1 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 #读权重，值越大分配的查询就越多 [server2] type=server address=10.10.240.114 port=3306 protocol=MySQLBackend serv_weight=1 #读权重，值越大分配的查询就越多 [MySQL Monitor] type=monitor module=mysqlmon servers=server1,server2 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 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 #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=6603start:[root@DS-VM-LinuxEA /mydata/maxscale/logs/trace]# maxscale -f /etc/maxscale.cn [root@DS-VM-LinuxEA /mydata/maxscale/logs/trace]# maxadmin --user=admin --password=mariadb --host=10.10.240.117 MaxScale> list servers Servers. -------------------+-----------------+-------+-------------+-------------------- Server | Address | Port | Connections | Status -------------------+-----------------+-------+-------------+-------------------- server1 | 10.10.240.113 | 3306 | 0 | Master, Running server2 | 10.10.240.114 | 3306 | 0 | Slave, Running -------------------+-----------------+-------+-------------+-------------------- MaxScale> list services Services. --------------------------+----------------------+--------+--------------- Service Name | Router Module | #Users | Total Sessions --------------------------+----------------------+--------+--------------- Read-Only Service | readconnroute | 1 | 1 Read-Write Service | readwritesplit | 1 | 1 MaxAdmin Service | cli | 2 | 4 --------------------------+----------------------+--------+--------------- MaxScale> 测试登陆：mysql -umybk -ppassword -h10.10.240.117 -P4006 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 14685 Server version: 10.0.0 1.4.5-maxscale MariaDB Server Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> show grants for 'mybk'@'10.10.240.117'; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for mybk@10.10.240.117 | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'mybk'@'10.10.240.117' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' | | GRANT SELECT ON `mysql`.`db` TO 'mybk'@'10.10.240.117' | | GRANT SELECT ON `mysql`.`user` TO 'mybk'@'10.10.240.117' | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)读写测试：主库授权linuxea账户：GRANT ALL PRIVILEGES ON *.* TO 'linuxea'@'10.10.240.117' IDENTIFIED BY 'password';maxscale登陆：mysql -ulinuxea -ppassword -h10.10.240.117 -P4006查询