Atlas for MySQL read-write separation (combined with MHA)

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 version: CentOS-6.5 64bit

Atlas Version: 2.2.1

MySQL version: mysql-5.6.32

192.168.2.45 mysql01 # MySQL

192.168.2.46 mysql02 # MySQL

192.168.2.47 MYSQL03 # MySQL

192.168.2.48 mysql04 # MySQL

192.168.2.40 Atlas # Atlas

III. Installation and deployment steps

1, install the deployment Mysql-master

2, install the deployment Mysql-slave

3, installation Deployment Atlas

# Mysql-master/slave don't do it here. Installation configuration instructions. Refer to <<centos 6.5 installation configuration MySQL mha>>

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.2.1.el6.x86_64.rpm

1.2. Configure Atlas

# Edit configuration file

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

# The content is as follows, end ends

[Mysql-proxy]admin-username = Adminadmin-password = Pwdproxy-backend-addresses = 192.168.2.30:3306proxy-read-only-backend-addresses = 192.168.2.47:3306pwds = root:csc39indnwy+dck5o+bcla==, Backup: Yu4tugo3req= # to authorize root user in MySQL database machine remote connection daemon = Truekeepalive = Trueevent-threads = 4log-level = Debuglog-path =/usr/loc al/mysql-proxy/logproxy-address = 0.0.0.0:33 06admin-address = 0.0.0.0:2345# 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

# Edit Boot Script

Cat/etc/init.d/atlas

#!/bin/bash# #atlas:    atlas daemon## chkconfig:    -  90 25# description:  atlas daemon## source function library.start () {         echo -n $ "starting atlas: "          /usr/local/mysql-proxy/bin/mysql-proxyd test start         echo }stop () {        echo  -n $ "shutting down atlas: "         /usr/ Local/mysql-proxy/bin/mysql-proxyd test stop        echo} atlas= "/usr/local/mysql-proxy/bin/mysql-proxyd" [ -f  $ATLAS  ] | |  exit 1# See how we were called.case  "$"  in         start)                 start                 ;;         stop)                  stop                 ;;         restart)                  stop                 sleep 3                 start                 ;;         *)                 echo $ "Usage: $0 {start|stop|restart}"                  exit 1esacexit 0

# set to auto start on boot

chmod +x/etc/init.d/atlaschkconfig--add Atlaschkconfig Atlas on

# 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-h127.0.0.1-p2345-uadmin-ppwd

Here the username password is admin-username = admin, Admin-password = pwd 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 192.168.2.30; Add SLAVE $backend # Adds a SLAVE node, such as: Add SLAVE 192.168.2.47; 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

Description

1, the main library bound VIP, in the Atlas configuration file is set to the VIP address, so no matter how the main library switch, the final VIP will not change. Atlas always treats the VIP server as the main library.

2, because the standby main library is originally from the library, that is, in the Atlas configuration file as a slave library, so when the takeover of the main library, then the library can be both written and readable. Therefore, it is necessary to manually remove the Read function from the library. (to admin interface, remove backend idx)

3. If the original repository needs to be repaired, the original repository will exist as the slave library for the new primary library. Can be manually added to Atlas (add slave ip:port)

[Conclusion]: can be very good support MHA switch, but need to pay attention to the above 2nd content.

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

Atlas for MySQL read-write separation (combined with MHA)