MySQL read-write separation using atlas

# #采用Atlas实现MySQL读写分离

First, the basic introduction

==========================================================================================

1. Background description

Currently, our highly available DB proxy layer uses 360 open source Atlas, which has been running stably for 2 months since it was launched. Both in terms of performance and stability,

It's great compared to other open source components (amoeba, Cobar, Maxscale, Mysql-proxy, etc.).

The reason we chose Atlas was that it had the following advantages:

(1), based on mysql-proxy-0.8.2 to modify, code completely open source;

(2), relatively lightweight, deployment configuration is relatively simple;

(3), support DB read and write separation;

(4), support read load balancer from DB, and automatically reject fault from DB;

(5), support smooth up and down the line db;

(6), have a better security mechanism (IP filtering, account authentication);

(7), version update, problem follow-up, communication circles are more active.

In the course of testing and online troubleshooting, we are grateful for the enthusiastic answers from Atlas author Zhu Su. For more information about Atlas, I don't have one by one examples, and you can refer to the following links:

Https://github.com/Qihoo360/Atlas/blob/master/README_ZH.md

3. System Environment

CentOS 7.2 x86_64

Network environment: Master 192.168.10.25

Slave 192.168.10.26

Mysql-atlas-proxy 192.168.10.28

# #安装mysql-server main server (192.168.10.25)

Service Firewalld Stop

Chkconfig Firewalld off

Setenforce 0

Getenforce

Sed-i ' s/^selinux=enforcing/selinux=permissive/'/etc/selinux/config

Yum install-y mariadb Mariadb-server

Service MARIADB Restart

Chkconfig mariadb on

Sed-i ' 1aserver_id=25 '/etc/my.cnf

Sed-i ' 2alog-bin=mysql-bin '/etc/my.cnf

Service MARIADB Restart

MYSQL-UROOT-E "Grant all on * * to [e-mail protected] '% ' identified by '"; flush privileges; "

MYSQL-UROOT-E "Grant replication Slave on * * to [e-mail protected] '% ' identified by '"; flush privileges; "

Mysql-uroot-e "Select User,host,password from Mysql.user;"

Mysql-uroot-e "Reset Master;show master status;"

# #安装mysql-server main server (192.168.10.26)

Service Firewalld Stop

Chkconfig Firewalld off

Setenforce 0

Getenforce

Sed-i ' s/^selinux=enforcing/selinux=permissive/'/etc/selinux/config

Yum install-y mariadb Mariadb-server

Service MARIADB Restart

Chkconfig mariadb on

Sed-i ' 1aserver_id=26 '/etc/my.cnf

Sed-i ' 2alog-bin=mysql-bin '/etc/my.cnf

Service MARIADB Restart

MYSQL-UROOT-E "Grant all on * * to [e-mail protected] '% ' identified by '"; flush privileges; "

MYSQL-UROOT-E "Grant replication Slave on * * to [e-mail protected] '% ' identified by '"; flush privileges; "

Mysql-uroot-e "Select User,host,password from Mysql.user;"

Mysql-uroot-e "Change Master to master_host= ' 192.168.100.11 ', master_user= ' rep ', Master_password= '", master_port= 3306,master_log_file= ' mysql-bin.000001 ', master_log_pos=245;

Sleep 30s

Mysql-uroot-e "Start slave;show slave status\g"

# #安装配置atlas读写分离 (192.168.10.28)

Service Iptables Stop

Chkconfig iptables off

Setenforce 0

Getenforce

Sed-i ' s/^selinux=enforcing/selinux=permissive/'/etc/selinux/config

grep ' ^selinux= '/etc/selinux/config

Installing and configuring the Atlas software

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

echo "path= $PATH:/usr/local/mysql-proxy/bin/" >>/etc/profile

Source/etc/profile

ll/usr/local/mysql-proxy/

# #mysql-proxy file Function Description:

The bin directory is all executable files.

1. "Encrypt" is used to generate the MySQL password encryption, when configured to use the

2. "Mysql-proxy" is MySQL's own read-write separation agent

3. "Mysql-proxyd" is 360 out, behind a "D", service start, restart, stop. It's all done with him.

Conf directory is a configuration file

1. "TEST.CNF" Only one file to configure the proxy, you can use vim to edit

The Lib directory is decentralized with some packages, and the dependencies of Atlas

Log directory is logged, such as error messages such as errors

Enter the bin directory, use encrypt to encrypt the database password, my MySQL data username is admin, password is 01, I need to encrypt the password

cd/usr/local/mysql-proxy/bin/

./encrypt 01 generate an encrypted password and copy this password

cd/usr/local/mysql-proxy/conf/

Cp-v test.cnf Test.cnf.bak//backup TEST.CNF configuration file

VI test.conf Full profile content of modified read and write sharing

[Mysql-proxy]

Admin-username = user

Admin-password = pwd

Proxy-backend-addresses = 192.168.100.11:3306

PWDs = admin:vfnep9p4vu4=, rep:vfnep9p4vu4=

Daemon = True

KeepAlive = True

Event-threads = 8

Log-level = Message

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

Proxy-address = 0.0.0.0:3306

Admin-address = 0.0.0.0:2345

TEST.CNF Read and Write separation profile function Description:

1:[mysql-proxy]//read/write separation agent configuration

6:admin-username = Username of user//management interface

9:admin-password = password for pwd//admin interface

12:proxy-backend-addresses = 192.168.100.11:3306//Primary database IP address and port number (readable writable)

18:pwds = admin:vfnep9p4vu4=, rep:vfnep9p4vu4=//back-end MySQL username and encrypt command generated encryption password

21:daemon = TRUE//set to daemon mode (background run)

24:keepalive = TRUE//Allow KeepAlive

27:event-threads = 8//number of worker threads is 8

30:log-level = message//log level is MSG

33:log-path =/usr/local/mysql-proxy/log//log file path

45:proxy-address = 0.0.0.0:3306//atlas Monitoring management interface IP and port

48:admin-address = 0.0.0.0:2345//atlas Monitoring management interface IP and port

Restart the Atlas service:/usr/local/mysql-proxy/bin/mysql-proxyd Test start

Check status:./ETC/INIT.D/MYSQLD status

Set Mysql-proxyd boot:

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

Source/etc/profile

Login test: Mysql-uadmin-p01-h 192.168.10.28-p3306

Log on to the Atlas management side: Mysql-uuser-ppwd-h 192.168.10.28-p2345

MySQL read-write separation using atlas