Maxscale installation Configuration

Brief introduction:

As a database middleware, Maxscale has the function of high-performance read-write separation and load balancing db router. Since MARIADB adopted the deferred open source policy after the 1.4.4 release, there has been no plan to deploy the test online, and recently discovered that Atlas on the line has the disadvantage of PHP connectivity, so the installation tested Maxscale.

Please refer to the official wiki for the introduction of Maxscale.

Note: The company has recently fully open source for its internal use of Dbproxy (based on Atlas), and subsequent testing

Environment (TEST):

OS:CENTOS6 Percona server5.6 maxscale1.4.4

Hardware:

Cpu:intel (R) Xeon (r) CPU e5-2620 v2 @ 2.10GHz 24core

DISK:SSD---data files on SSDs

m-s:192.168.1.21:3306 (M)---->192.128.1.22:3306 (S)

maxscale:192.168.1.23

Note: The Primary and standby environment to build this article does not introduce, Maxscale can be combined with MHA to deploy

Installation:

1. Installation method:

RPM Package Installation--This article uses the way

SOURCE Installation

Binary installation

2. Installation Steps

(1) Get RPM package

wget https://downloads.mariadb.com/MaxScale/1.4.4/centos/6Server/x86_64/maxscale-1.4.4-1.centos.6.x86_64.rpm

RPM-IVH maxscale-1.4.4-1.centos.6.x86_64.rpm

(2) Create Maxscale user, Main library execution

CREATE USER [email protected] ' 192.168.1.% ' identified by "maxscaletest";

GRANT replication Slave, replication Client on *. * to [email protected] ' 192.168.1.% ';

GRANT SELECT on mysql.* to [email protected] ' 192.168.1.% ';

GRANT all on maxscale_schema.* to [email protected] ' 192.168.1.% ';

GRANT SHOW DATABASES On * * to [email protected] ' 192.168.1.% ';

Note: This user is used to monitor and implement operations such as Maxscale internal call get user

(3) Create the required directory for Maxscale

Mkdir-p/data/maxscale/{data,cache,logs,tmp}

Mkdir-p/data/maxscale/logs/{trace,binlog}

(4) Generate encryption password

maxkeys/data/maxscale/data/

Maxpasswd/data/maxscale/data/.secrets Maxscaletest

49066584626e94ea24a963164e5aa5f6

(5) Generate configuration file

Cat/etc/maxscale.cnf

# Maxscale documentation on GITHUB:

# HTTPS://GITHUB.COM/MARIADB-CORPORATION/MAXSCALE/BLOB/MASTER/DOCUMENTATION/DOCUMENTATION-CONTENTS.MD

# Global Parameters

#

# complete list of configuration options:

# HTTPS://GITHUB.COM/MARIADB-CORPORATION/MAXSCALE/BLOB/MASTER/DOCUMENTATION/GETTING-STARTED/CONFIGURATION-GUIDE.MD

[Maxscale]

# Number of threads opened, default is 1. Set to auto in the same number of CPU cores

Threads=auto

# timestamp Accuracy

Ms_timestamp=1

# Write logs to Syslog

Syslog=1

# Write logs to the Maxscale log file

Maxlog=1

# do not write logs to the shared cache, turn on speed when debug mode is turned on

Log_to_shm=0

# Record Alarm Information

Log_warning=1

# Record Notice Information

Log_notice=1

# Log Info Info

Log_info=1

# do not turn on debug mode

Log_debug=0

# log Increment

Log_augmentation=1

# Related Directory settings

basedir=/usr/bin/maxscale/

logdir=/data/maxscale/logs/trace/

datadir=/data/maxscale/data/

cachedir=/data/maxscale/cache/

piddir=/data/maxscale/tmp/

[Server1]

Type=server

address=192.168.1.21

port=3306

Protocol=mysqlbackend

Serv_weight=1

[Server2]

Type=server

address=192.168.1.22

port=3306

Protocol=mysqlbackend

Serv_weight=3

[MySQL Monitor]

Type=monitor

Module=mysqlmon

Servers=server1,server2

User=maxscale

Passwd=49066584626e94ea24a963164e5aa5f6

# Monitor mentality of 10s

monitor_interval=10000

# when replication slave is all broken, Maxscale is still available, pointing all access to the master node

Detect_stale_master=true

# Monitor Master-slave replication delay, which can be specified for subsequent router service (configure this parameter request will always fall on master)

# Detect_replication_lag=true

[Read-only Service]

Type=service

Router=readconnroute

Servers=server1,server2,server3

User=maxscale

Passwd=49066584626e94ea24a963164e5aa5f6

Router_options=slave

# allow root user to log in and execute

Enable_root_user=1

# Query Weights

Weightby=serv_weight

[Read-write Service]

Type=service

Router=readwritesplit

Servers=server1,server2

User=maxscale

Passwd=49066584626e94ea24a963164e5aa5f6

max_slave_connections=100%

# The existing variable in the SQL statement only points to the execution in master

Use_sql_variables_in=master

# allow root user to log in and execute

Enable_root_user=1

# Allow master/slave maximum interval (s)

max_slave_replication_lag=3600

[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=/data/maxscale/tmp/maxadmin.sock

port=6603

Note: As can be seen from the configuration file, Maxscale is supported in ReadWrite and read only two modes. Each monitor different ports.

(6) Start:

Maxscale-f/etc/maxscale.cnf && Tailf/data/maxscale/logs/trace/maxscale1.log

If there is an error message, the log will print out. General Maxscale user Rights and firewall ports open errors

(7) Management

View background status

Maxadmin-uadmin-pmariadb

maxscale> list Servers

Servers.

-------------------+-----------------+-------+-------------+--------------------

Server | Address | Port | Connections | Status

-------------------+-----------------+-------+-------------+--------------------

Server1 |  192.168.1.21 |           3306 | 0 | Master, Running

Server2 |  192.168.1.22 |           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 | 2

(8) test of reading and writing separation

Sign in with business user Proin

Mysql-uproin-p-p4008-h192.168.1.23
Enter Password:

mysql> Use test; CREATE TABLE Test_maxscale (id int), insert into Test_maxscale values, select * from Test_maxscale;

View background log (need to open info)

Tailf/data/maxscale/logs/trace/maxscale1.log

2017-01-03 23:17:02.516   [9]   info  : (route_single_stmt): > Autocommit: [Enabled], TRX is [not open], cmd:com_query, type:query_t Ype_write, stmt:  create table test_maxscale (ID  int)

-01-Geneva : 516 [9] Info: (route_single_stmt): Route query to Master 192.168.1.21:3306 <

2017-01-03 23:17:06.517 [9] Info: (route_single_stmt): > Autocommit: [Enabled], TRX is [not open], cmd:com_query , Type:query_type_write, stmt: insert INTO Test_maxscale values ()

2017-01-03 23:17:06.517 [9] Info: (route_single_stmt): Route query to Master 192.168.1.21:3306 <

2017-01-03 23:17:08.518 [9] Info: (route_single_stmt): > Autocommit: [Enabled], TRX is [not open], cmd:com_query, Type:query_type_write, stmt: select * from Test_maxscale

2017-01-03 23:17:08.518 [9] Info: (route_single_stmt): Route query to Slave 192.168.1.22:3306 <

Read/write separation is normal

(9) Performance test

Performance testing using the company's automated testing tools

Core code principle: Python Short connection interface concurrently executes SQL, can control concurrency.

QPS Test Results:

Direct Connect 22:

Test Date: 2017-01-04-12:56:40

Total number of concurrent concurrency: 200

Tested Interface: Testmysql

Concurrent users are: 200

Length of test: 36s

Total number of request sent: 64156

Number of success: 64177

Number of failed: 0

Number of error: 0

Total Failures: 0

Failure rate is: 0.000

Test average QPS is: 1982.69

Maxscale:

Test Date: 2017-01-04-12:58:28

Total number of concurrent concurrency: 200

Tested Interface: Testmysql

Concurrent users are: 200

Length of test: 36s

Total number of request sent: 64599

Number of success: 64650

Number of failed: 0

Number of error: 0

Total Failures: 0

Failure rate is: 0.000

Test average QPS is: 1795.83

Note: From the test results, when the short connection is 200, the Maxscale performance drops within an acceptable range, and is indeed a high-performance db router.

Observe the Maxscale host during the test, the overall maxscale to eat CPU resources, the pressure is still from the back of the MySQL server

Conclusion:

Maxscale is superior in performance and can be deployed as an alternative to Atlas

Enclosure: PHP Connection issues mentioned at the beginning

The online architecture is ATLAS+MHA architecture, the main library error, data file corruption. After notifying the business party to restrict part of the write entry, backends the RW to do repair in the Atlas management. Found a large number of PHP query error, java no error. After the recurrence problem (PHP small Program Circular query hostname), found that as long as the main library (RW) offline, then error, MySQL server gone away, has not found a specific reason, if known, can be informed in the comments. Thank you

This article is from the "hiny_t blog" blog, make sure to keep this source http://hinyt.blog.51cto.com/12442113/1890143

Maxscale installation Configuration