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

The read-write separation architecture built by maxscale can be combined with MHA to do master failover at a later stage, so that no changes are needed at the business level.

Don't use it based on connect. Delay from the library he will continue to distribute requests in the past, which is not suitable for production for the time being.

Lab demonstration:

The current master-slave structure:

Node93 10.1.20.93 master

Node94 10.1.20.94 slave

Node95 10.1.20.95 slave

Node96 10.1.20.96 maxscale

First, create the relevant account on the main master library:

Before starting the configuration, you need to create two users for the MaxScale in master for the monitoring module and the routing module.

Create a monitoring user to use in the configuration of the [MySQL Monitor] section:

> create database maxscale_schema; # the heartbeat information used for maxscale monitoring will be written to this library

> create user scalemon@'%' identified by "111111"

> grant replication slave, replication client on*.* to scalemon@'%'

> grant all on maxscale_schema.* to scalemon@'%'

Create a routing user to use in the configuration of the [Read-Write Service] section:

> create user maxscale@'%' identified by "111111"

> grant select on mysql.* to maxscale@'%'

Maxscale deployment:

Rpm-ivh maxscale-2.0.5-1.rhel.6.x86_64.rpm

The main generated files are as follows:

/ etc/maxscale.cnf

/ etc/maxscale.cnf.template

/ usr/bin/cdc.py

/ usr/bin/cdc_kafka_producer.py

/ usr/bin/cdc_last_transaction.py

/ usr/bin/cdc_users.py

/ usr/bin/maxadmin

/ usr/bin/maxavrocheck

/ usr/bin/maxbinlogcheck

/ usr/bin/maxkeys

/ usr/bin/maxpasswd

/ usr/bin/maxscale

/ var

/ var/lib

/ var/lib/maxscale

Create a key file:

[root@maxscale / root] # maxkeys / var/lib/maxscale

Generate an encrypted password:

[root@maxscale / root] # maxpasswd/var/lib/maxscale/.secrets 123456

We don't need the two steps crossed out above, just write the plaintext password in the configuration file / etc/maxscale.cnf. (there is a hole here. Configure this parameter so that after maxscale is up, you will not be able to connect to other libraries. Prompt access denied)

Vim/etc/security/limits.conf:

* softnofile 65535

* hardnofile 65535

Vim/etc/sysctl.conf:

Fs.file-max=655350

Net.ipv4.ip_local_port_range= 1025 65000

Net.ipv4.tcp_tw_reuse= 1

After modifying the internal and parameters, you need to restart the server.

Modify the configuration file:

Cat / etc/maxscale.cnf

[maxscale]

Threads=auto

Ms_timestamp=1 # timestamp precision

Syslog=1 # writes logs to syslog

Maxlog=1 # writes logs to the log file of maxscale

Log_to_shm=0 # does not write logs to the shared cache, but can be opened faster when debug mode is turned on

Log_warning=1 # record alarm information

Log_notice=1 # record notice

Log_info=1 # record info

Log_debug=0 # does not turn on debug mode

Log_augmentation=1 # log increment

# Server definitions

#

# Set the address of the server to the network

# address of a MySQL server.

#

# you need to match both master and slave addresses, and maxscale will automatically tell which is master and slave

[server1]

Type=server

Address=10.1.20.93

Port=3306

Protocol=MySQLBackend

[server2]

Type=server

Address=10.1.20.94

Port=3306

Protocol=MySQLBackend

[server3]

Type=server

Address=10.1.20.95

Port=3306

Protocol=MySQLBackend

# Monitor for the servers

#

# This will keep MaxScale aware of the state of theservers.

# MySQL Monitor documentation:

# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Monitors/MySQL-Monitor.md

[MySQL Monitor]

Type=monitor

Module=mysqlmon

Servers=server1,server2,server3 # all server should be written here so that maxscale can monitor it.

User=scalemon

Passwd=111111

Monitor_interval=10000 # check every 10s

Detect_replication_lag=true # check for replication delays

Detect_stale_master=true # when all slave is unavailable, the select query request is forwarded to master.

# Service definitions

#

# Service Definition for a read-only service and

# a read/write splitting service.

#

# ReadConnRoute documentation:

# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Routers/ReadConnRoute.md

Because we use [Read-Write Service], we can delete another service [Read-Only Service] and comment out the whole block below.

# need to match both master and slave addresses

# [Read-Only Service]

# type=service

# router=readconnroute

# servers=server1,server2,server3

# user=maxscale # read-write separated account and password

# passwd=111111 # read-write separated account and password

# router_options=slave

# ReadWriteSplit documentation:

# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Routers/ReadWriteSplit.md

# configure read-write separation, which requires both master and slave addresses

[Read-Write Service]

Type=service

Router=readwritesplit

Servers=server1,server2,server3

User=maxscale # read-write separated accounts and passwords

Passwd=111111 # read-write separated accounts and passwords

Max_slave_connections=100% # all slave provide select query service

Max_slave_replication_lag = 5 # slave timeout 5 seconds, forward the request to another slave

Use_sql_variables_in = all #

# This service enables the use of the MaxAdmininterface

# MaxScale administration guide:

# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Reference/MaxAdmin.md

[MaxAdmin Service]

Type=service

Router=cli

# Listener definitions for the services

#

# These listeners represent the ports the

# services will listen on.

#

# [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=default

Start maxscale:

Maxscale-f/etc/maxscale.cnf

Ss-lnt can see that port 4006 is up.

You can use your previous business account to connect to port 4006 of maxscale, for example:

Mysql-utest-ptest-P 4006-h 10.1.20.96

Note that select such as begin;select @ @ hostname;commit; is executed on the main library. In addition, when a stored procedure or function is executed, it is automatically executed in the main library.

Execute SQL > begin;select@@hostname; commit; insert into T2 select 3; the corresponding select@@hostname; is recorded in / var/log/maxscale/maxscale.log as follows:

It is obvious that operations such as opening transactions, inserts, etc., will be forwarded to the main library for processing. The simple select will be forwarded to some slave library for processing.

Maxscale cannot fail over master and can be used in conjunction with MHA. After the failover of MHA, maxscale can automatically identify which machine is master. The request is then automatically sent to the new master-slave structure.

Delay detection of maxscale:

The principle is similar to that of pt-heartbeat. Maxscale compares the master_timestamp timestamps of the replication_heartbeat table on master and slave and subtracts the difference. This difference is the delay value of MySQL master-slave synchronization.

Select * frommaxscale_schema.replication_heartbeat

From the library failure or delay will be automatically eliminated:

We can wait a moment on stop slave; on 192.168.2.12, and then execute the query operation, and we will find that the request will not be forwarded to the mysql of 192.168.2.12, and the machine will be automatically kicked off the line in maxscale. As shown below:

If there is a delay in 192.168.2.12, it appears in the maxscale log as follows:

If port 3306 of 192.168.2.12 is not available (maybe mysqld is down or the server is down), the following is shown in the maxscale log:

Other commands for managing maxscale later added to the notes:

Maxadmin-S / tmp/maxadmin.sock

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.