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

This article mainly tells you about using Atles to achieve read-write separation of MySQL database. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope that using Atles to achieve the separation of reading and writing in MySQL database can bring you some practical help.

Atlas official document: https://github.com/Qihoo360/Atlas/wiki

Atlas is a data middle tier project based on MySQL protocol developed and maintained by Qihoo 360, the infrastructure team of Web platform Department. It modifies a lot of bug and adds a lot of features on the basis of the official version of MySQL-Proxy 0.8.2 released by MySQL. At present, the project has been widely used within the company, and many MySQL services have been connected to the Atlas platform, carrying billions of read and write requests every day.

Main functions:

* Separation of read and write

* load balancing from slave database

* IP filtering

* blacklist and whitelist of SQL statements

* automatic sub-table

-

I. Environment

# the test environment is a multi-instance CVM: 3306 (master), 3308 (slave)

# CentOS version:

[root@m01/] # cat / etc/redhat-release

CentOSrelease 6.7 (Final)

[root@m01/] # uname-r

2.6.32-573.el6.x86_64

[root@m01/] # / etc/init.d/iptables stop

[root@m01/] # getenforce

Disabled

II. MySQL database creates users and authorizes them

# after the master and slave of MySQL have been configured, the master and slave authorize the Atlas

Mysql > GRANT UPDATE,DELETE,INSERT,SELECT ON *. * TO 'atlas'@'%' IDENTIFIED BY' atlas'

III. Atlas installation

CentOS-5.x download address

Https://github.com/Qihoo360/Atlas/releases/download/2.0.2/Atlas-2.0.2.el5.x86_64.rpm

Rpm-ivh Atlas-2.0.2.el5.x86_64.rpm

CentOS-6.x download address

Https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el5.x86_64.rpm

Rpm-ivh Atlas-2.2.1.el5.x86_64.rpm

File path after Atlas installation

/ usr/local/mysql-proxy/

IV. Atlas configuration

1. Encrypt the MySQL user password, and fill in the encrypted string obtained in the pwds parameter of the configuration file.

/ usr/local/mysql-proxy/bin/encrypt atlas

KsWNCR6qyNk=

two。 Edit configuration file

View the contents of the source profile

[root@db01 ~] # egrep-v "^ # | ^ $" / usr/local/mysql-proxy/conf/test.cnf

[mysql-proxy]

Admin-username = user

Admin-password = pwd

Proxy-backend-addresses = 127.0.0.1purl 3306

Pwds = user1:+jKsgB3YAG8=, user2:GS+tr4TPgqc=

Daemon = true

Keepalive = true

Event-threads = 8

Log-level = message

Log-path = / usr/local/mysql-proxy/log

Proxy-address = 0.0.0.0purl 1234

Admin-address = 0.0.0.0purl 2345

The configuration file edited in this article is as follows:

[root@db01 conf] # vim test.cnf [mysql-proxy] admin-username = atlasadmin-password = atlesproxy-backend-addresses = 172.168.1.51:3306proxy-read-only-backend-addresses = 172.16.1.51:3308@1pwds = atlas:KsWNCR6qyNk=daemon = truekeepalive = trueevent-threads = 8log-level = messagelog-path = / usr/local/mysql-proxy/logsql-log = ONsql-log-slow = 1000instance = testproxy-address = 0.0.0.0:1234admin-address = 0.0.0.0:2345charset = utf8

Description of the parameters in the configuration file (non-required configuration items with a # sign)

User name of the management interface

Admin-username = user

Password for the management interface

Admin-password = pwd

IP and port of the MySQL master library connected to the Atlas backend. Multiple entries can be set, separated by commas.

Proxy-backend-addresses = 172.16.1.51purl 3306

The MySQL connected to the Atlas backend is from the IP and port of the library, and the number after @ represents the weight, which is used for load balancing. If omitted, it defaults to 1. Multiple entries can be set, separated by commas.

Proxy-read-only-backend-addresses = 172.16.1.51mm 3306mm 1

The username and its corresponding encrypted MySQL password are encrypted using the encryption program encrypt in the PREFIX/bin directory. Take the following user1 and user2 as examples and replace them with your MySQL username and encrypted password!

Pwds = atlas:KsWNCR6qyNk=

Set the operation mode of Atlas to daemon mode when true, foreground mode when false, false when development and debugging, and no spaces after true,true when running online.

Daemon = true

Set the operation mode of Atlas. When set to true, Atlas will start two processes, one is monitor, and the other is worker,monitor will restart it automatically after worker exits unexpectedly. When set to false, there is only worker, no monitor. Generally, it is set to false when developing and debugging, and there can be no spaces after true,true when running online.

Keepalive = true

The number of worker threads has a great impact on the performance of Atlas, which can be set appropriately according to the situation.

Event-threads = 8

Log level, which is divided into five levels: message, warning, critical, error and debug

Log-level = message

The path where the log is stored

Log-path = / usr/local/mysql-proxy/log

The switch for SQL log can be set to OFF, ON, REALTIME,OFF for not recording SQL log, ON for recording SQL log, and REALTIME for recording SQL log and writing to disk in real time. Default is OFF.

# sql-log = OFF

Slow log output settings. When this parameter is set, the log only outputs log records whose execution time exceeds sql-log-slow (in ms). If this parameter is not set, all logs are output.

# sql-log-slow = 8

Instance name, used to distinguish between multiple Atlas instances on the same machine

# instance = test

Working interface IP and port for Atlas snooping

Proxy-address = 0.0.0.0purl 1234

Management interface IP and port for Atlas snooping

Admin-address = 0.0.0.0purl 2345

Sub-table setting. In this case, person is the library name, mt is the table name, id is the sub-table field, and 3 is the number of sub-tables. Multiple items can be set, separated by commas. If the table is not divided, you do not need to set this item.

# tables = person.mt.id.3

Default character set, after setting this item, the client no longer needs to execute SET NAMES statements

Charset = utf8

The IP of clients that are allowed to connect to Atlas, either exact IP or IP segments separated by commas. If this item is not set, all IP connections are allowed, otherwise only IP connections in the list are allowed

# client-ips = 127.0.0.1, 192.168.1

The IP of the physical ENI of the LVS attached to the front of the Atlas (note that it is not a virtual IP). If there is a LVS and client-ips is set, this must be set, otherwise it may not be set.

# lvs-ips = 192.168.1.1

Five: start Atlas

Start

[root@db01 conf] # / usr/local/mysql-proxy/bin/mysql-proxyd test start OK: MySQL-Proxy of test is started [root@db01 conf] # ps-ef | grep mysql-proxyroot 21447 10 05:45? 00:00:00 / usr/local/mysql-proxy/bin/mysql-proxy-- defaults-file=/usr/local/mysql-proxy/conf/test.cnfroot 21448 21447 0 05:45? 00:00 : 00 / usr/local/mysql-proxy/bin/mysql-proxy-- defaults-file=/usr/local/mysql-proxy/conf/test.cnfroot 21517 21324 0 06:06 pts/0 00:00:00 grep mysql-proxy

Close

/ usr/local/mysql-proxy/bin/mysql-proxyd test stop

Note:

The running file is: mysql-proxyd (not mysql-proxy).

Test is the name of the configuration file in the conf directory and the name of the instance entry in the configuration file (no suffix). The three need to be unified. (test.cnf = = test of instance = = start the test used)

You can use ps-ef | grep mysql-proxy to see if Atlas has been started or stopped.

VI. Testing

Mysql-h20.0.0.51-P1234-uatlas-patlas-e "select user,host from mysql.user;" mysql-h227.0.0.1-P2345-uuser-ppwdmysql > select * from help;mysql > SELECT * FROM backends +-+ | backend_ndx | address | state | type | +-+ | 1 | 172. 16.1.51up 3306 | up | rw | | 2 | 172.16.1.51up | ro | +-+

Test read-write separation, executed on the back-end node

# the master library only receives write requests tcpdump-I any-s 0-A-n-p port 3306 and src 172.16.1.51 | grep-I-E'SELECT | INSERT | UPDATE | DELETE'# slave library only accepts read requests tcpdump-I any-s 0-A-n-p port 3308 and src 172.16.1.51 | grep-I-E'SELECT | INSERT | UPDATE | DELETE

7. Remarks on Atlas parameters

Instance parameters:

Multiple Atlas instances on the same machine use different ports, correspond to different databases, and use their own configuration files

Default blacklist and whitelist:

Delete without where will be blocked, and "sleep" will be blocked.

Note:

Version 1.03 already supports set autocommit statements, and for python, you need to execute a conn.autocommit (True) after the connection.

Pwds parameter

Configure the MySQL user name and the corresponding encrypted MySQL password, which is added using the / usr/local/mysql-proxy/bin/encrypt program

Secret, user name and password are separated by colons, multiple users can be used, separated by comma spaces.

Client-ips parameter

After the introduction of the middle tier, because the DB is connected to the Atlas, DB is changed to authorize access to the IP of the machine where the Atlas is deployed, while the client-ips parameter

Number of IP used to control clients connecting to Atlas

Lvs-ips parameter

For example, two Atlas are connected behind the LVS, which are called An and B respectively to circulate to check the survival of An and B. when the Atlas detects that it is the IP of LVS to request

Different values are returned to the LVS detection script depending on the online or offline status, here is the IP of the physical Nic that sets the LVS in front of the Atlas.

8. Use of Atlas management interface

Syntax: mysql-u administrative user-p administrative password-h227.0.0.1-P administrative port

Mysql-uuser-ppwd-h227.0.0.1-P2345

View the administrative statement parameters:

Mysql > select * from help

View DB status:

Mysql > select * from backends +-+ | backend_ndx | address | state | type | uuid | connected_clients | +-+- -+ | 1 | 172.16.1.20 NULL 3306 | up | rw | NULL | 0 | 2 | 172.16.1.30 NULL 3306 | up | ro | NULL | 0 | | +-+ |

Note:

The parameter connected_clients has bug and cannot be displayed properly.

9. Frequently asked questions on Atlas

1. Atlas multi-instance startup problem:

If instance = instance in the configuration file, the startup parameters are as follows:

/ usr/local/mysql-proxy/bin/mysql-proxyd instance start

If instance = test in the configuration file, the startup parameters are as follows:

/ usr/local/mysql-proxy/bin/mysql-proxyd test start

You can use the mysql-proxy command to start

/ usr/local/mysql-proxy/bin/mysql-proxy-- defaults-file=/usr/local/mysql-proxy/conf/test.cnf

Note:

When starting with scripts, be careful to distinguish between mysql-proxy and mysql-proxyd under the bin directory.

2. "too many connections" appears when using Atlas

The reason is that the max_connections setting of DB is too small.

3. The status of the backend DB is found to be down in the management interface.

A. Whether the backend DB has authorized the IP of the machine where the Atlas resides

B. It is recommended that the max_connect_errors in DB's my.cnf is set to 100000, and the default value is 10. Too small a value will affect the survival detection of DB by Atlas. You can modify the max_connect_errors parameter of the my.cnf file, or use the following instruction to modify it directly.

Mysql > set global max_connect_errors=100000

4. Master-slave synchronization delay, can not immediately read data, you can add / * master*/ before the SQL statement, you can force the read request to the master database.

Use Atles to achieve MySQL database read-write separation first to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.