MySQL read-write separation using atlas

I. INTRODUCTION of ATLAS

Atlas is a MySQL protocol-based data mid-tier project developed and maintained by Qihoo, a web platform infrastructure team. It was optimized on the basis of the Mysql-proxy 0.8.2 version, adding some new functional features. 360 internal use of the MySQL operation in Atlas, the number of read and write requests per day up to billions of.
At present, to achieve a more stable function is read-write separation. The sub-list is not so perfect.

Second, the environment:
Operating system:

RHEL6 Or CentOS6 x86_64

Atlas version:

2.1

MySQL version:

mysql-5.6.20

Server IP:

10.57.1.131 mysql-master10.57.1.132 mysql-slave10.57.1.133 Atlas

III. Installation and deployment steps

1, install deployment Mysql-master2, install deployment mysql-slave3, install deployment atlas

# Mysql-master/slave don't do this here. Installation configuration instructions. Refer to <<mysql5.6 Gtid-based multithreaded replication >>
1. Install and deploy Atlas and configure read/write separation
# for simplicity, use RPM Or yum to install Atlas
1.1. Download RPM Package
Download the software address please click to download the corresponding version https://github.com/Qihoo360/Atlas/releases
After the download. If you have a Yum repository, you can add the RPM of Atlas to the Yum Repository, which is installed using Yum,

RPM-IVH atlas-2.1.el6.x86_64.rpm

1.2. Configure Atlas
# Edit configuration file

Vim/usr/local/mysql-proxy/conf/test.cnf

# The content is as follows, end ends

[Mysql-proxy] #带 # for non-required configuration item # The user name of the management interface, define the password for the admin-username = admin# management interface according to your needs admin-password =  password#atlas backend connected to the MySQL main library IP and port, you can set a number of items, separated by commas proxy-backend-addresses = 10.57.1.131:3306# The Atlas backend is connected to MySQL from the library's IP and port, the number after the @ represents the weight, used for load balancing, if omitted the default is 1, you can set multiple items, separated by commas proxy-read-only-backend-addresses =  10.57.1.132:[email protected] #用户名与其对应的加密过的MySQL密码, the password is encrypted using the encryption program in the Prefix/bin directory encrypt, the downstream User1 and User2 are examples, Replace it with your MySQL username and encryption password! pwds = root:yqsbjy4g4oc=,test:yqsbjy4g4oc=#  need to be aware that these two users to create and open the corresponding access rights on the Mysql-master, Users here are required to open the use of the user # Set the operation mode of Atlas, set to True when the daemon mode, set to False as the foreground mode, general development debugging is set to False, the online runtime is set to truedaemon = true# Set how Atlas runs, set to True when Atlas starts two processes, one for monitor, one for worker,monitor automatically restarts after worker exits unexpectedly, set to false when only worker, no monitor, General development debugging is set to False, the online runtime is set to truekeepalive = true# number of worker threads, the performance of the Atlas has a great impact, according to the appropriate settings event-threads = 8 #日志级别, divided into message, warning, critical, error, debug five levels log-level = message# log storage path LOG-PATH = /USR /local/mysql-proxy/log#sql log switch, can be set to off, on, Realtime,off for not logging SQL log, on for logging SQL log, REALTIME for logging SQL log and write to disk in real time, the default is off#sql-log = The  off# instance name, which is used to differentiate between multiple Atlas instances on the same machine #instance = test#atlas the working interface IP and port of the listener proxy-address =  0.0.0.0:3306#atlas monitoring of the management interface IP and port admin-address = 0.0.0.0:2345# table settings, in this case the person is the library name, MT is the table name, the ID is a sub-table field, 3 is the number of children, You can set multiple items, separated by commas, without setting the #tables = person.mt.id.3# default character set for a table, and the client no longer needs to execute the Set names statement after setting the item charset =  utf8# allows the IP of the client connecting to Atlas, which can be either an exact IP or an IP segment, separated by commas, which allows all IP connections if not set, otherwise only the IP connection in the list is allowed client-ips =  127.0.0.1, 10.0.0#atlas the IP of the physical network card of the LVS attached to the front (note is not a virtual IP), if there is LVS and set the client-ips, this key must be set, otherwise you can not set #lvs-ips =  192.168.1.1# end

---------------

# Configure the ATLAS environment variable

Echo ' Export path= $PATH:/usr/local/mysql-proxy/bin/' >/etc/profile.d/atlas.shsource/etc/profile.d/atlas.sh

# Start Service

/usr/local/mysql-proxy/bin/mysql-proxyd Test Start

# set to auto start on boot

echo "/usr/local/mysql-proxy/bin/mysql-proxyd test Start" >>/etc/rc.local

# View Listening ports

NETSTAT-TANLP |        grep mysqltcp 0 0 0.0.0.0:2345 0.0.0.0:* LISTEN 9111/mysql-proxy TCP 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 9111/mysql-proxy

The 2345 port is the management interface for Atlas, which provides a view of MySQL status, set up on-line, offline backend MySQL server
Access method using MySQL command

Mysql-h 10.57.1.133-p2345-u Admin-p

Here the username password is admin-username = admin, Admin-password = password in the config file above
The following is the use Help for the management interface

SELECT * from Help; # View Help Information select * from Backends; # View back-end host status set OFFLINE $backend _id # offline One host, $backend _id = = SELECT * from backends; idset online $backend _id # on-line host a DD Master $backend # Add a master node, such as: Add Master 10.57.1.131; Add SLAVE $backend # Adds a SLAVE node, such as: Add SLAVE 10.57.1.132; Remove backend $backend _id # Deletes a node,

3306 Port is the client connection this port is used to send SQL instructions, the default port is 1234, here is modified in order to 3306, the configuration entry is proxy-address = 3306

This article from the "Past with the Wind" blog, declined reprint!

MySQL read-write separation using atlas