Shulou(Shulou.com)06/02 Report--

This blog demonstrates the environment:

Centos7.2 x86x64 minimizes installation. 3 vm time synchronization, turn off iptables,selinux.,firewalld. 3 rpm packages install MariaDB10.5.1 .172.16.0.130 machines install maxscale-2.3.7 separately

172.16.0.130 master maxscale172.16.0.131 slave1172.16.0.132 slave2-.Maxscale:

How to configure MariaDB MaxScale and some possible usage scenarios are introduced. MariaDB MaxScale is designed with flexibility in mind. It consists of an event handling core and plug-in modules that support functions and customizable program behavior.

MariaDB MaxScale is a database agent that extends the high availability, scalability, and security of MariaDB Server while simplifying application development by separating it from the underlying database infrastructure.

MariaDB MaxScale is engineered with an extensible architecture to support plug-ins, thus extending its functionality beyond transparent load balancing to become, for example, a database firewall.

With built-in plug-ins for multiple routers, filters, and protocols, MariaDB MaxScale can be configured to forward database requests and modify database responses based on business and technical requirements, for example, to mask sensitive data or extend reads

MariaDB MaxScale is a database agent that forwards database statements to one or more database servers.

Forwarding is performed using semantic understanding based on database statements and rules for the role of the server in the database back-end cluster.

MariaDB MaxScale is designed to provide load balancing and high availability capabilities to applications transparently.

MariaDB MaxScale has an extensible and flexible architecture, and its plug-in components can support different protocols and routing methods

MariaDB MaxScale makes full use of the asynchronous I / O function of the Linux operating system and a fixed number of worker threads.

Epoll is used to provide an event-driven framework for input and output through sockets.

Many of the services provided by MariaDB MaxScale are implemented as external shared object modules that are loaded at run time.

These modules support fixed interfaces and pass entry points through a structure consisting of a set of functional pointers. This structure is called "module object". You can create additional modules to use with MariaDB MaxScale.

Common module types are protocols, routers and filters. The protocol module realizes the communication between client and MariaDB MaxScale and between MariaDB MaxScale and back-end server.

The router examines the query from the client and determines the target backend. These decisions are usually based on routing rules and back-end server status.

The filter processes the data passed through the MariaDB MaxScale.

Filters are typically used to record queries or modify server responses.

II. Maxscale installation:

Support source package compilation and installation, rpm package installation, and binary tar package installation

Official installation documentation:

Question address:

Https://github.com/mariadb-corporation/MaxScale/tree/2.3/Documentation

Feedback bug address:

Https://jira.mariadb.org/browse/MXS

This demo environment is installed with maxscale 2.3.7. Here is the official installation document.

Https://mariadb.com/kb/en/mariadb-maxscale-23-mariadb-maxscale-installation-guide/

This blog uses rpm package installation, which is the simplest.

Yum install libaio.x86_64 libaio-devel.x86_64 novacom-server.x86_64 libedit-y wget https://downloads.mariadb.com/MaxScale/centos/7/x86_64/maxscale-2.3.7-1.centos.7.x86_64.rpmrpm-ivh maxscale-2.3.7-1.centos.7.x86_64.rpm close and start command: service maxscale status | start | stop | restart systemctl enable maxscale systemctl status | start | stop maxscale maxadmin shutdown maxscalemaxscale management account login: default account: admin [root@mgr01] # maxadmin-h227.0.0.1-P6603-uadmin-pendant Mariadb 'III. Profile description:

The default profile is in / etc/maxscale.cnf

Official module parameter introduction address:

Https://mariadb.com/kb/en/mariadb-maxscale-23-contents/#routers

Profile parameters description:

Https://mariadb.com/kb/en/mariadb-maxscale-23-mariadb-maxscale-configuration-usage-scenarios/

The global settings in the section named [MaxScale] allow you to adjust various parameters that affect the entire MariaDB MaxScale. This section must be defined in the root configuration file that defaults to / etc/maxscale.cnf

Parameter: threads introduction: threads= [| auto] defaults to 1, which is best adjusted according to actual needs.

Increasing the number of worker threads to more than the number of processor cores does not improve performance, but may degrade performance and may consume resources unnecessarily.

Demonstrate the contents of a successful profile:

When [root@mgr03 ~] # cat / etc/maxscale.cnf# https://mariadb.com/kb/en/mariadb-maxscale-23-mariadb-maxscale-configuration-usage-scenarios/[maxscale]#threads=autothreads=1log_info=1logdir=/tmp/auth_read_timeout=10 # fetched user authentication data, the read timeout (in seconds) for the MySQL connection to the backend database. Increasing the value of this parameter will cause MariaDB MaxScale to wait longer for a response from the back-end server when actively fetching user data. If authentication fails, and you have a large number of database users and authorizations, or if the connection to the back-end server is slow, it is a good idea to increase this value. The default is 1 second. when auth_write_timeout=10# extracts user authentication data, the write of MySQL's connection to the back-end database times out (in seconds). Currently, MariaDB MaxScale does not write or modify data in the back-end server. The default value is 2 seconds. The number of internal queries officially recommended that 10squery_retries=1# was retried. The default value is to try again. Interrupt query refers to any query interrupted by a network error. The connection timeout is included in the network error, so it is recommended to make sure that the value of query_retry_timeout is set to the appropriate value. The total timeout (in seconds) for any retry queries in query_retry_timeout=5## that are opened by default after 2.3.0. The default is 5 seconds, ms_timestamp=1# ms_timestamp=. # enable or disable high-precision timestamps in log files. Enable this option to increase millisecond precision for all log file timestamps. Skip_permission_checks=false # defaults to false.#, which is useful when you know that permissions are determined and you want to speed up the startup process. This parameter is a Boolean and is disabled by default # it is recommended that you do not disable permission checking so that any missing privileges are detected when you start maxscale. If MaxScale starts slowly due to a large number of connection timeouts while checking permissions, disabling permission checking can speed up the startup process. Syslog=1# syslog=. # enable or disable logging of messages to syslog. By default, logging maxlog=1# to syslog is enabled to disable logging messages to MariaDB MaxScale log files. By default, logging for maxlog is enabled. # to enable logging to MariaDB MaxScale log files, use a value of 1 and disable it with a value of 0. Log_warning=1# log_warning=,# enables or disables the logging of messages whose Syslog priority is warning. Messages with this priority enabled by default. Log_notice=1# enables or disables logging of messages with Syslog priority of notice. Messages for this priority provide information about the functionality of MariaDB MaxScale and are enabled by default. # # to disable these messages, use a value of 0, and to enable them, use a value of 1 logically debug = 0 [server1] type=serveraddress=172.16.0.131port=3306#protocol=MySQLBackendprotocol=MariaDBBackend#server_weight=1serversize= 1 [server2] type=serveraddress=172.16.0.132port=3306#protocol=MySQLBackendprotocol=MariaDBBackend#server_weight=1serversize= 1 [server3] type=serveraddress=172.16.0.130port=3306#protocol=MySQLBackendprotocol=MariaDBBackend#server_weight=1serversize=1# https://mariadb.com/kb/en/mariadb-maxscale-23-mariadb-monitor/[MySQL Monitor] type=monitormodule=mariadbmonservers=server1,server2,server3user=maxmonuserpassword=maxmonpwdmonitor_interval=10000##monitor_interval=10000 # probe interval in milliseconds Whether the default 2000auto_failover=true# automatically switches over auto_rejoin=true# failed instances and automatically joins the cluster detect_standalone_master=true# to detect independent master after recovery, and whether the last instance in the cluster is allowed to become the master database # switchover_on_low_disk_space=true## in order for this parameter to take effect, you must specify a disk_space_threshold for the server or monitor. In addition, you must define for the monitor the number of times disk_space_check_interval#allow_cluster_recovery=truefailcount=3 # checks whether other slave libraries survive before the last instance in the cluster becomes the master library. By default, this parameter is enabled from version 2.0. Even if replication is stopped or misconfigured, the previous master server is allowed to be available. All secondary servers are inaccessible or replication is interrupted for some reason, and master can continue to provide services. Detect_stale_slave=true# this parameter is turned on by default, and a running slave server without a master server is regarded as a valid slave server. When slave loses master, slave is still available for reading. Failover_timeout=90# this parameter defaults to 90s, and if a failover / failover does not occur successfully within the configured time period, a message is logged and automatic failover is disabled. This prevents further automatic modifications to clusters that behave abnormally. Verify _ master_failure=truemaster_failure_timeout=10# enables other primary server failure verification for automatic failover. Invalid _ master_failure is on by default, which is a Boolean value, and master_failure_timeout defaults to 10.script=/tmp/reset_slave.shevents=master_down # if master dies Execute the script / tmp/reset_slave.sh# https://mariadb.com/kb/en/mariadb-maxscale-23-readconnroute/[Read-Only-Service]type=servicerouter=readconnrouteservers=server1,server2,server3user=maxuserpassword=maxpwdrouter_options=slave#router_options=master,slave#weightby=server_weight# ReadWriteSplit documentation:# https://mariadb.com/kb/en/mariadb-maxscale-23-readwritesplit/[Read-Write-Service]type=servicerouter=readwritesplitservers=server1,server2,server3user=maxuserpassword=maxpwd#max_slave_connections=100%max_slave_connections=2# above to set the maximum number of slaves used by the router session at any time. The default value is a maximum of 255 slave connections per client connection. In the old version, the default setting was to use all available slaves without restriction. # for example, if you configure one host and three slaves for MaxScale and set max_slave_connections = 2, for each client connection, the connection to the host and two slave connections will be opened. The load balance of the read query is then completed between the two secondary servers and writes are sent to the primary server.max _ slave_replication_lag=4# specifies how many seconds the slave is allowed to lag behind the master. If the latency is greater than the configured value, the slave station cannot be used for routing. The master _ accept_reads=true# default read is not routed to master. Allows the primary server to be used for reading. Enabling this option is useful if you are using a small number of servers and want to use the primary server for reading as well. # weightby=server_weight# This service enables the use of the MaxAdmin interface# MaxScale administration guide:# https://mariadb.com/kb/en/mariadb-maxscale-23-maxadmin-admin-interface/[MaxAdmin-Service]type=servicerouter=cli[Read-Only-Listener]type=listenerservice=Read-Only-Service#protocol=MySQLClientprotocol=MariaDBClientport=4008[Read-Write-Listener]type=listenerservice=Read-Write-Service#protocol=MySQLClientprotocol=MariaDBClientport=4006[MaxAdmin-Listener]type=listenerservice=MaxAdmin-Serviceprotocol=maxscaled#socket=defaultport=6603 IV. Configure Mariadb 1 Master 2 Slave ahead of time: 4. 1 configure gitd-based master-slave replication

Note: when configuring master-slave replication, the maxscale monitoring cluster does not recognize hostnames, only ip addresses

So when CHANGE MASTER TO MASTER_HOST='172.16.0.130',MASTER_PORT=3306,MASTER_USER='repuser',MASTER_PASSWORD='JuwoSdk21TbUser',master_use_gtid=slave_pos;start slave;show slave status\ G, remember that MASTER_HOST must fill in the ip address

Otherwise, the monitoring of the master-slave replication cluster will fail.

For example, on slave:

CHANGE MASTER TO MASTER_HOST='mgr01',MASTER_PORT=3306,MASTER_USER='repuser',MASTER_PASSWORD='JuwoSdk21TbUser',master_use_gtid=slave_pos;start slave Show slave status\ G [root@mgr01 ~] # maxadmin-h227.0.0.1-P6603-uadmin-p'mariadb'MaxScale > list serversServers.-+-Server | | Address | Port | Connections | Status-+-server1 | 172.16.0.131 | 3306 | 0 | Master | Slave of External Server, Runningserver2 | 172.16.0.132 | 3306 | 0 | Slave of External Server, Runningserver3 | 172.16.0.130 | 3306 | 0 | Slave Running-+-

Representatives like Master, Slave of External Server, and Running failed to monitor the master-slave replication cluster relationship.

Slave of External Server, Running

For example, on slave:

CHANGE MASTER TO MASTER_HOST='172.16.0.130',MASTER_PORT=3306,MASTER_USER='repuser',MASTER_PASSWORD='JuwoSdk21TbUser',master_use_gtid=slave_pos;start slave Show slave status\ G [root@mgr01 ~] # maxadmin-h227.0.0.1-P6603-uadmin-p'mariadb'MaxScale > list serversServers.-+-Server | | Address | Port | Connections | Status-+-server1 | 172.16.0.131 | 3306 | 0 | Slave | Runningserver2 | 172.16.0.132 | 3306 | 0 | Slave, Runningserver3 | 172.16.0.130 | 3306 | 0 | Master Running-+-

The above status indicates normal.

4.2 Database account creation and authorization:

1. Monitor the status of the back-end mysql service

two。 Replication cluster operation (failover,switchover,rejoin,reset-replication)

The following privileges are required for monitoring users:

Create user' maxmonuser'@'%' identified by 'maxmonpwd';grant super, REPLICATION SLAVE,replication client, reload, process, show databases,event on *. * to' maxmonuser'@'%'

Introduction to the role of permissions:

SUPER, to modify slave connections and set globals such as read_onlyREPLICATION CLIENT, to list slave connectionsRELOAD, to flush binary logsPROCESS, to check if the event_scheduler process is runningSHOW DATABASES and EVENT, to list and modify server events

Authorization considerations:

Grant super, replication client, reload, process, show databases,event on *. * to 'maxmonuser'@'172.16.0.%';MariaDB do not support authorization in the above way, and can only be authorized by the following methods. Grant super, replication client, reload, process, show databases,event on *. * to 'maxmonuser'@'%'; five. Read-write separation configuration and testing:

Create an account for the back-end mysql service of the read-write separation operation:

Create user' maxuser'@'172.16.0.%' identified by 'maxpwd';grant SELECT on mysql.* to' maxuser'@'172.16.0.%';GRANT SHOW DATABASES,super ON *. * TO 'maxuser'@'172.16.0.%';select user,host from mysql.user

Create a test user:

Grant all on *. * to 'maxscale'@'172.16.0.%' identified by' maxscalepass'; flush privileges; or grant all on *. * to 'maxscale'@'%' identified by' maxscalepass'; flush privileges; logs in to the read-write library mysql-umaxscale-pendant maxscalepass'-h 172.16.0.130-P4006 through port 4006 specified in the maxscale machine ip and maxscale.cnf configuration files

Read-write separation configuration:

Https://mariadb.com/kb/en/mariadb-maxscale-23-readwritesplit/#readwritesplit

Read-write split routing decision:

The following actions are routed to master:

Write statements,all statements within an open transaction,stored procedure callsuser-defined function callsDDL statements (DROP | CREATE | ALTER TABLE... Etc.) EXECUTE (prepared) statements that modify the databaseall statements using temporary tables

If the readwritesplit service is configured with the max_slave_replication_lag parameter, and if all secondary servers suffer too much replication latency, the statement will be routed to the master server or other slave.

Join is set to: max_slave_replication_lag=4

# specify how many seconds the slave station is allowed to lag behind the master station. If the latency is greater than the configured value, the slave station cannot be used for routing.

The warning of the log content is as follows:

2020-02-28 21 server3 52Autocommit 59.040 info: (4) Route query to master: server3 [172.16.0.130]: 3306 Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 29, type: QUERY_TYPE_READ, stmt: select * from test_event 2020-02-28 21V 52V 59.059 info: (4) Route query to slave: server1 [172.16.0.131]: 3306 Autocommit: [enabled] Trx is [not open], cmd: (0x03) COM_QUERY, plen: 90, type: QUERY_TYPE_WRITE, stmt: insert into test_event (username,password,create_time) values ("Li Si", "tomcat", now ()) 2020-02-28 21 purse 522purl 59.495 info: (4) Route query to master: server3 [172.16.0.130]: 3306 Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 29 Type: QUERY_TYPE_READ, stmt: select * from test_event 2020-02-28 21 stmt 52 stmt 59.513 warning: (4) Replication lag of 'server1' is 48s, which is above the configured limit 4s. 'server1' is excluded from query routing.2020-02-28 21 21 Route query to slave: server2 [172.16.0.132]: 3306 Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 90, type: QUERY_TYPE_WRITE, stmt: insert into test_event (username,password,create_time) values ("Li Si", "tomcat" Now () 2020-02-28 21 21 Route query to master: server3 [172.16.0.130]: 3306 Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 29, type: QUERY_TYPE_READ, stmt: select * from test_event 2020-02-28 21 52V 59.935 info: (4) Route query to slave: server2 [172.16.0.132]: 3306 Autocommit: [enabled] Trx is [not open], cmd: (0x03) COM_QUERY, plen: 90, type: QUERY_TYPE_WRITE, stmt: insert into test_event (username,password,create_time) values ("Li Si", "tomcat", now ())

How it is routed to the slave:

Queries that can be routed to slave stations must be automatically submitted and belong to one of the following groups, read-only database queries,read-only queries to system, or user-defined variables,SHOW statementssystem function calls. six。 Automatic failover: a: Failover automatic failover replaces the failed master node with the running slave node. It does the following:

6.1 Select the latest slave of the old host as the new host. The selection criteria are arranged in descending order

1.gtid_IO_pos (latest event in relay log) 2.gtid_current_pos (most processed events) 3.log_slave_updates is on4.disk space is not low

6.2 if the new primary server has unprocessed relay log entries, please cancel and try again later

6.3 prepare a new master

1. Removes the slave server connection used by the new master server to replicate from the old master server. two。 Turn off read_only parameter 3. 0 on the new master. Enables scheduled server events, if event handling is enabled. Only enable event 4 enabled on the old host to run command 5 in promotion_sql_file. If there is an external master server, start replication

6.4 redirect all other secondary servers to replicate from the new primary server

STOP SLAVE and RESET SLAVECHANGE MASTER TOSTART SLAVE

6.5 check whether all subordinates are replicating

If steps 6.1 to 6.3 are successful, the failover is considered successful because the cluster has at least one valid primary server

B: Switchover manual switch exchanges the running master with the running slave. It does the following:

1. Old master ready to be downgraded:

Stop any external replication.Kill connections from super-users since read_only does not affect them.Enable the read_only-flag to stop writes.Disable scheduled server events (if event handling is on). Run the commands in demotion_sql_file.Flush the binary log (FLUSH LOGS) so that all events are on disk.

two。 Waiting for the new master to catch up with the old master

3. As in failover steps 3 and 4, upgrade the new master server and redirect the slave server. Also redirect the degraded old primary server

4. Check if all subordinates are replicating

Hint: Failover/switchover requires MariaDB 10.0.2 or later

seven。 Automatically rejoin the cluster:

Rejoin joins a stand-alone server to the cluster or redirects a slave server that is replicated from a non-master server. Stand-alone servers join in the following ways:

Run the commands in demotion_sql_file.Enable the read_only-flag.Disable scheduled server events (if event handling is on). Start replication: CHANGE MASTER TO and START SLAVE.

Servers replicated from the wrong primary server are redirected only through the STOP SLAVE,RESET SLAVE,CHANGE MASTER TO and START SLAVE commands

eight。 Database account permissions:

Prompt database account permissions:

(root@'mgr03':mysql.sock) [(none)] > show grants for maxmonuser@'%' +- -- + | Grants for maxmonuser@% | +- - -+ | GRANT RELOAD SUPER, REPLICATION SLAVE REPLICATION CLIENT ON *. * TO `maxmonuser` @ `% `IDENTIFIED BY PASSWORD'* DE290C4A038E4E06BB0AF2C0B8B01ABB51572796' | +- -+ 1 row in set (0.00 sec) (root@'mgr03':mysql.sock) [(none)] > show grants for maxscale@'%' +-+ | Grants for maxscale@ % | + -- + | GRANT ALL PRIVILEGES ON *. * TO `maxscale` @ `% `ACDE28142BFC311A2CE191B3CB3E510A27EB60' | +-- -+ 1 row in set (0.00 sec) (root@'mgr03':mysql.sock) [(none)] > show grants for maxuser@'172.16.0.%' +- -+ | Grants for maxuser@172.16.0.% | + -+ | GRANT SHOW DATABASES SUPER ON *. * TO `maxuser` @ `172.16.0.% `IDENTIFIED BY PASSWORD'* 5EDBD32E469DAE0CE10E699C3899DEFCB9F12E0' | GRANT SELECT ON `mysql`. * TO `maxuser` @ `172.16.0.% `| +-- -+ 2 rows in set (0.00 sec) (root@'mgr03':mysql.sock) [(none)] > show grants for repuser@'172.16.0.%' +- -+ | Grants for repuser@172.16.0.% | + -+ | GRANT REPLICATION SLAVE ON *. * TO `repuser` @ `172.16.0% `IDENTIFIED BY PASSWORD'* 15BB449A1D5BDA166BB45F5989B2417834A13C23' | +- -+ 1 row in set (0.00 sec)

Reference documentation:

Https://blog.51cto.com/linzhijian/1913794

Http://udn.yyuap.com/thread-38241-1-1.html

Https://downloads.mariadb.com/MaxScale/centos/7/x86_64/

Https://blog.csdn.net/weixin_34194551/article/details/92492325

Https://blog.csdn.net/thundermeng/article/details/83900503

Https://www.jianshu.com/p/1ac435a6510e

Http://www.ttlsa.com/mysql/maxscale-install-read-write-split/

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.