Explore MySQL's high-availability architecture MHA (5)

Explore MySQL's high-availability architecture MHA (5)

-----build MySQL High-availability series (total 9 articles)

The previous article introduced the AB copy operation of this architecture!

This article mainly introduces the atlas read-write separation in this architecture!

Why separate libraries, sub-tables, read and write?

Now large-scale e-commerce system, at the database level most of the use of read-write separation technology, is a master database, multiple slave database.

Master Library is responsible for data update, slave library is of course responsible for non-real-time data query.

Because in the actual application, the database is read more write less (the frequency of reading data is high, the frequency of updating data is relatively small), and the reading data is usually time-consuming, occupy the database server more CPU, thereby affecting the user experience.

Our common practice is to extract queries from the main repository, using multiple slave libraries, and use load balancing to reduce the pressure on each query from the library.

The goal of using read-write separation technology is to reduce the pressure of master library effectively, and to distribute the requests of user query data to different slave libraries to ensure the robustness of the system.

Let's look at the background with read and write separations. As the business of the website expands continuously, the data increases unceasingly, the user is more and more, the pressure of the database is more and more big, adopt the traditional way.

For example: the database or SQL optimization has not reached the basic requirements, this time can be used to read and write separation strategy to change the status quo.

Common ways to implement read and write separations:

A. Development writes dead #指定修改操作 in code, connected to the main library. Query operation, which is connected from the library

B. Third-party tools for #Atlas, Cobar, Tddl, mysql-proxy, amoeba, etc.

The read and write separations in our architecture are implemented using Atlas, a MySQL protocol-based data middle-tier project developed and maintained by Qihoo, the Web platform Infrastructure team.

It is based on MySQL's official launch of the Mysql-proxy 0.8.2 version, which modifies a large number of bugs and adds many feature features.

At present, the project has been widely used within 360 companies, many MySQL services have been connected to the Atlas platform, the number of read and write requests per day up to billions of.

Main functions:

A. Read-Write separation

B. Load balancing from the library

C.ip Filtration

D.sql statement black and white list

E. Automatic sub-table

Let's start with the following steps:

Master Library and all operations from library

Mysql> GRANT All privileges on * * to ' root ' @ '% ' of ' identified by ' MySQL ';   #权限控制mysql > FLUSH privileges; #让权限修改生效

Installing Atlas

RPM-IVH atlas-2.2.1.el5.x86_64.rpm #rpm方式安装rpm-ql Atlas #查看Atlas安装的详细路径grep-V ' ^# '/usr/local/mysql-proxy/conf/test. CNF |grep-v ' ^$ ' >/usr/local/mysql-proxy/conf/lipengfei #过滤配置文件中的乱码, rewritten to Lipengfei file mv test.cnf test.cnf.old #备份 TEST.CNF file Cat/usr/local/mysql-proxy/conf/lipengfei >/usr/local/mysql-proxy/conf/test.cnf #将lipengfei文件输入, Redirect to Test.cnf/usr/local/mysql-proxy/bin/encrypt MySQL #mysql是我root用户的密码, encrypt the password, after encryption: twbz0dlu35u=

Modify the Atlas configuration file

vi /usr/local/mysql-proxy/conf/test.cnf [mysql-proxy]plugins = admin,proxy      #默认插件不用修改admin-username=admin      #Atlas管理员用户admin-password=admin       #Atlas管理员admin-lua-script = /usr/local/mysql-proxy/lib/mysql-proxy/lua/ admin.luaproxy-backend-addresses = 10.142.132.49:3306    # IP and Port proxy-read-only-backend-addresses = 10.142.132.47:3306,10.142.132.48:3306   # of the main library pwds = root:twbz0dlu35u=     #root用户与其对应的加密过的密码daemon  = true from the IP and port of the library       #设置Atlas的运行方式, set to truekeepalive = true   # on the online runtime 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, The general development debugging is set to False, and the online runtime is set to trueevent-threads = 8    #工作线程数, which has a great impact on the performance of Atlas. log-level = message    can be set appropriately according to the circumstances #日志级别, divided into message, warNing, critical, error, debug five levels log-path = /usr/local/mysql-proxy/log    #日志路径   instance = test    #实例的名称proxy-address = 0.0.0.0:1234   # The working interface IP and Port admin-address = 0.0.0.0:5678    of the Atlas listener are #Atlas监听的管理接口IP和端口charset  =  utf8    #默认字符集

Viewing the Listening port

NETSTAT-TANLP | grep MySQL

Open and Close Atlas

/usr/local/mysql-proxy/bin/mysql-proxyd Test start/usr/local/mysql-proxy/bin/mysql-proxyd Test Stop

Sign In and manage Atlas

 mysql -h 10.142.132.50 -p 5678 -u admin -padmin   # Login Management  mysql> select * from help;     #查看Atlas帮助 +---------------- ---+---------------------------------------+| command                   | description                                                  |+-------------------+---------------------------------------+|  Select * from help         | shows this  help                                           | |  select * from backends     | lists the backends  and their state                       | |  SET OFFLINE  $backend _id    | offline backend server, $ Backend_id is backend_ndx ' s id | |  SET ONLINE  $backend _id     | online backend server,  ...                               | |  ADD MASTER  $backend         | example:  "ADD  master 127.0.0.1:3306 ",  ...                | |  ADD SLAVE  $backend          | example:  " add slave 127.0.0.1:3306 ", ...                 | |  REMOVE BACKEND  $backend _id | example:  "remove backend 1", &nbsp ...                          | |  ADD CLIENT  $client          | example:  " add client 192.168.1.2 ", ...                   | |  REMOVE CLIENT  $client       | example:  "remove  client 192.168.1.2 ", &NBsp;...               | |  SAVE CONFIG                 | save the backends to config file                          |+-------------------+---------------------------------------+10 rows in set  (0.00  SEC)

Normal operation via proxy

Mysql-h 10.142.132.50-p 1234-u root-pmysql #通过代理机器登录mysql SHOW VARIABLES like ' server_id '; #查看当前Mysql的序号

Found above show VARIABLES like ' server_id '; operation, each time the results are different, the separation is 3 from the library server_id, indicating that our read and write separation function configuration succeeded!

So far, our read and write separation function has been configured to end!

As long as the friends carefully click on the article I wrote step-by-step operation, I believe you can also succeed, come on!

This article is from "Can't finish the road, can't read the book!" blog, be sure to keep this source http://51power.blog.51cto.com/3549599/1671899

Explore MySQL's high-availability architecture MHA (5)