MySQL High availability Study (ii) master-slave +mha+atlas

For more information about Atlas, visit: https://github.com/Qihoo360/Atlas/blob/master/README_ZH.md

Why use Atlas? is the application directly connected to the database bad? Also need to add a layer of agent in front, will not reduce the read and write performance of the application? Will the cost of maintenance management be increased? I think this is every question before using Atlas.

1. Why Use Atlas?

We use Atlas, which uses its read-write separation and load balancing function from the library primarily. Because we read a lot more business than write, it is appropriate to use the architecture of read-write separation. Before the implementation of read-write separation, is generally implemented through the application, which greatly increases the difficulty of development. With Atlas, applications and databases can be separated, programmers focus on business logic development, and databases are completely transparent to applications. In addition, the use of read-write separation, but also to increase redundancy, increase the processing capacity of the machine, for read-based applications, using read and write separation can ensure that the write server pressure is smaller.

2. Is the application directly connected to the database bad? Also need to add a layer of agent in front, will not reduce the read and write performance of the application?

Here are some special tests, please refer to: https://github.com/Qihoo360/Atlas/wiki/Atlas%E7%9A%84%E6%80%A7%E8%83%BD%E6%B5%8B%E8%AF%95

The following experiment introduces the deployment of Atlas for read-write separation and load balancing from the library.

One, Atlas installation

"Environmental description":

MYSQL5 is installed on all three machines by default. 6 , and the master-slave replication is configured to complete. The role                     host name      IP address            feature Main Library                     node1     192.168.  245.129    (w/R) candidate_master        node2     192.168.  245.131     (r)                     Node3     192.168 from the library.  245.132     (R)
Atlas                   Monitor   192.168.245.133     read-  write separation 192.168.  245.100

1, the use of the official recommended RPM installation method , relatively simple, download RPM package: https://github.com/Qihoo360/Atlas/releases

Yum Install atlas-2.2. 1. el6.x86_64.rpm

Installed directory in:/usr/local/mysql-proxy

Precautions :

(1). Atlas can only be installed on a system that is running on a 64-bit.

(2). Centos 5.X installation Atlas-xx.el5.x86_64.rpm,centos 6.X installation atlas-xx.el6.x86_64.rpm.

(3). The back-end MySQL version should be greater than 5.1, it is recommended to use MySQL 5.6

2. Configure Atlas

The Atlas runtime relies on a configuration file (TEST.CNF). Before you run Atlas, you need to configure the file. Atlas installation directory is/usr/local/mysql-proxy, into the installation directory of the Conf directory, you can see that there is already a default profile named Test.cnf, we only need to modify some of the configuration items inside, do not need to write a configuration file from scratch.

[mysql-Proxy] #带 # for non-required configuration Item # Admin interface User name Admin-username =Password admin for user# management interface-password =pwd#Atlas后端连接的MySQL主库的IP和端口, you can set multiple items, separate the proxy with commas-backend-addresses =127.0.0.1:3306#Atlas后端连接的MySQL从库的IP和端口, the number at the end of the @ represents the weight, used for load balancing, and if omitted the default is 1, you can set multiple items, separated by commas #proxy-read-only-backend-addresses =127.0.0.1:3305@1#用户名与其对应的加密过的MySQL密码, password use prefix/The encryption program under the Bin directory is encrypt encrypted, the downstream User1 and User2 are examples, replace it with your MySQL username and encryption password! PWDs = user1:+jksgb3yag8=, user2:gs+tr4tpgqc=#设置Atlas的运行方式, set to True when the daemon mode, set to False as the foreground mode, the general development debugging is set to False, the line run when set to true,true cannot have space behind. Daemon=true#设置Atlas的运行方式, when set to True, Atlas starts two processes, one for monitor, and one for Worker,monitor, which automatically restarts the worker when it exits unexpectedly, set to False when only the worker, Without monitor, the general development debugging is set to False, the line run time set to True,true after cannot have the space. KeepAlive=true#工作线程数, there is a big impact on the performance of Atlas, which sets the event-threads value to 2-4 times the number of CPUs if you pursue the QPS of the SQL request that Atlas handles. Set the Event-threads value to the number of CPUs if you are pursuing the completion time of the SQL request that Atlas handles. Event-threads =8#日志级别, divided into message, warning, critical, error, debug five levels of log-level =message# Log stored path-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 =off# slow log output settings. When this parameter is set, the log only outputs execution time beyond SQL-log-Slow (unit: MS) logging. If you do not set this parameter, all logs are output. #sql-log-slow =Ten#实例名称, for distinguishing between multiple Atlas instances on the same machine #instance=Test#atlas Monitor the working interface IP and Port proxy-address =0.0.0.0:1234#Atlas监听的管理接口IP和端口admin-address =0.0.0.0:2345#分表设置, 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 child tables, you can set multiple items, separated by commas, if the table does not need to set the item #tables= Person.mt.ID.3#默认字符集, 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,192.168.1#Atlas前面挂接的LVS的物理网卡的IP (note is not a virtual IP), if there is LVS and the client is set-IPs then this must be set, otherwise you can not set #lvs-ips =192.168.1.1

Precautions :

(1) PWDs parameter Encryption Password acquisition:

[Email protected] bin]# cd/usr/local/mysql-proxy/bin[[email protected] bin]#. /encrypt rootdajnl8cvzy8=

(2)event-threads settings:

Set the Event-threads value to 2-4 times the number of CPUs if you pursue the QPS of the SQL request that Atlas handles. Set the Event-threads value to the number of CPUs if you are pursuing the completion time of the SQL request that Atlas handles.

Ii. Atlas Launch

Enter the/usr/local/mysql-proxy/bin directory and execute the following command to start, restart, or stop atlas.

(1)./mysql-proxyd test start, launch Atlas.

(2)./mysql-proxyd test restart, restart Atlas.

(3)./mysql-proxyd test Stop, Stop Atlas.

[Email protected] bin]#./mysql-proxyd Test start# Check to see if [[email protected] bin]#PS-ef |grepmysql-Proxyroot13451     1  0  on: the?xx:xx:xx/usr/local/mysql-proxy/bin/mysql-proxy--defaults-file=/usr/local/mysql-proxy/conf/Test.cnfroot13452 13451  0  on: the?xx:xx:xx/usr/local/mysql-proxy/bin/mysql-proxy--defaults-file=/usr/local/mysql-proxy/conf/test.cnf

Third, test read-write separation

1, stop master-slave replication: In order to see whether the inset operation is assigned to the main library or from the library

mysql>0 rows affected (0.07 sec)

2. Connect to Atlas and test

(1) Test insert operation:

[Email protected] log]#/data/mysql/bin/mysql-uroot-proot-h192.168.245.133-P1234warning:using a password on the command line interface can is insecure.  Welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connectionIDIs1Server Version:5.0.Bayi-log MySQL Community Server (GPL) Copyright (c) -, the, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or Itsaffiliates. Other names trademarks of their respectiveowners. Type'Help ;'Or'\h'  forHelp. Type'\c'ToClearThe current input statement. MySQL > INSERT into backup.test (ID) VALUES (3); #测试插入操作Query OK,1Row affected (0.02Sec

Then go to the main library to view:

#这是没插入之前的数据 (product) [email protected]192.168.245.129[(None)]>Select*From Backup.test;+----+|ID|+----+|1||2||4||5|+----+4RowsinchSet (0.10sec) #插入后, there's data! Magic Bar (product) [email protected]192.168.245.129[(None)]>Select*From Backup.test;+----+|ID|+----+|1||2||3||4||5|+----+5RowsinchSet (0.00Sec

The data from the library remains unchanged, stating that the write operation is only assigned to the main library:

(product) [Email protected] 127.0. 0.1 Select * from test; ID |+----+|  1 | |  2 | |  4 | |  5 |+----+4 in Set (0.00 sec)

(2) test select operation, directly assigned to from the library, only 4 data:

Select * from backup.test; ID |+----+|  1 | |  2 | |  4 | |  5 |+----+4 in Set (0.02 sec)

MySQL High availability Study (ii) master-slave +mha+atlas