Shulou(Shulou.com)06/01 Report--

Preface

At present, the database of online system adopts the main main architecture. Among them, one master only switches to use in case of failure, (only a single server provides services, and when one server has a problem, switch to the other), it is difficult for this structure to support large concurrency. In addition, the other machine of the two hosts is not effectively used when there is no failure. Combined with the above situation, it is proposed to use database middleware to provide read-write separation function (one main read and write, one main read). It can not only improve the read concurrency ability, but also make full use of the database server, and can continue to increase the slave server read concurrency performance of the master cluster in the later stage. The following is a specific architectural diagram:

Official link to Atlas: https://github.com/Qihoo360/Atlas/blob/master/README_ZH.md

Atlas download link: https://github.com/Qihoo360/Atlas/releases

Atlas (normal): Atlas-2.2.1.el6.x86_64.rpm

Atlas (subtable): Atlas-sharding_1.0.1-el6.x86_64.rpm

Ip and software information

IP

System

Software version

Installation path

Remarks

192.168.9.150

CentOS release 6.8 (Final)

Atlas-2.2.1.el6.x86_64.rpm

/ usr/local/mysql-proxy

Atlas normal version

192.168.9.175

CentOS release 6.8 (Final)

Mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz

/ usr/local/mysql

Master cluster

192.168.9.176

CentOS release 6.8 (Final)

Mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz

/ usr/local/mysql

Master cluster

MYSQL master cluster erection

Basic principles:

For a mysql server, there are usually two threads responsible for copying and being replicated. When replication is turned on.

1. The IO thread on the Slave connects to the Master and requests the log contents from the specified location of the specified log file (or from the beginning of the log)

2. After Master receives the request from the IO thread of Slave, the IO thread responsible for replication reads the log information after the specified location of the log according to the request information, and returns it to the IO thread on the Slave side. In addition to the information contained in the log, the returned information also includes the name of the Binary Log file on the Master side and the location in the Binary Log.

3. After receiving the information, the IO thread of Slave writes the received log contents to the end of the Relay Log file (mysql-relay-bin.xxxxxx) on the Slave side, and records the file name and location of the read binlog on the Master side into the master-info file, so that it can clearly Master at a high speed on the next read. "where do I need to start the log content in a certain bin-log, please send it to me".

4. After Slave's SQL thread detects a new addition to Relay Log, it immediately parses the contents of the Log file into executable Query statements that are actually executed on the Master side, and executes these Query on its own. In this way, the same Query is actually executed on both the Master side and the Slave side, so the data on both sides is exactly the same.

As shown in the figure:

Master setting, that is, the configuration in which two servers are master and slave to each other.

Slightly (many related configurations on the Internet)

Installation of ATLAS database middleware

Download and install ATLAS database middleware

Cd / usr/local/src wget https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm rpm-ivh Atlas-2.2.1.el6.x86_64.rpm

Once installed, it will generate 4 folders for you by default under "/ usr/local/mysql-proxy", as well as the files to be configured, as follows:

[root@localhost mysql-proxy] # lsbin conf lib log

The executable files are all distributed in the 1.bin directory.

2. "encrypt" is used to generate MySQL password encryption, which is used in configuration.

3. "mysql-proxy" is MySQL's own read-write separation agent.

4. "mysql-proxyd" is created by 360. there is a "d" behind it to start, restart and stop the service. It's all carried out by him.

Edit configuration file

Cd / usr/local/mysql-proxy/conf/vim test.cnf [mysql-proxy] # with # is the non-essential configuration item # user name of the management interface admin-username = user # password of the management interface admin-password = pwd # the IP and port of the MySQL main library connected to the Atlas backend. Multiple entries can be set, separated by commas. Note that only the write function is supported in the main library. To read and write, set the library from the library at the same time. Read function is supported. Proxy-backend-addresses = 192.168.9.175 MySQL 3306 # Atlas backend connected MySQL is from the IP and port of the library, and the number after @ represents the weight, which is used for load balancer. If omitted, it defaults to 1. Multiple entries can be set and separated by commas. Here, the database of the main library is configured here again to support reading. To achieve the purpose of reading load balancing. Proxy-read-only-backend-addresses = 192.168.9.176PREFIX/bin 3306password 1192.168.9.175PREFIX/bin 3306encrypted 1 # user name corresponding to its encrypted MySQL password, password encrypted using the encryption program encrypt in the PREFIX/bin directory, downlink user1 and user2 as examples, replace it with your MySQL username and encrypted password! Pwds = root:/iZxz+0GRoA= # sets the running mode of Atlas, daemon when true, foreground when false, false when developing and debugging, and no spaces after true,true when running online. Daemon = true # sets the running mode of Atlas. When set to true, Atlas will start two processes, one is monitor, and the other is worker,monitor will restart it automatically after worker exits unexpectedly. When set to false, there is only worker and no monitor. Generally, it is set to false when developing and debugging, and there can be no spaces after true,true when running online. Keepalive = the number of worker threads of true #, which has a great impact on the performance of Atlas. You can set event-threads = 8 # log level appropriately according to the situation, which is divided into five levels: message, warning, critical, error, and debug. The switch of log-level = message # log storage path log-path = / usr/local/mysql-proxy/log # SQL log can be set to OFF, ON, REALTIME,OFF for not recording SQL log, and ON for recording SQL log. REALTIME stands for recording SQL logs and writing them to disk in real time. The default is OFF#sql-log = OFF# slow log output setting. When this parameter is set, the log only outputs log records whose execution time exceeds sql-log-slow (in ms). If this parameter is not set, all logs are output. # sql-log-slow = 10 # instance name, used to distinguish between multiple Atlas instances on the same machine # instance = test # Atlas snooping work interface IP and port proxy-address = 0.0.0.0instance 1234 # Atlas snooping management interface IP and port admin-address = 0.0.0.02345 # subtable setting, in this case person is the library name, mt is the table name, id is the subtable field, 3 is the number of child tables, multiple items can be set, separated by commas If you do not divide the table, you do not need to set the default character set of the item # tables = person.mt.id.3 #. After setting this item, the client no longer needs to execute the SET NAMES statement # charset = utf8 # allows the IP of the client to connect to Atlas, which can be exact IP or IP segments separated by commas. If this item is not set, all IP connections are allowed. Otherwise, only the IP connection in the list is allowed: # client-ips = 127.0.0.1, 192.168.1 # the IP of the physical Nic of the LVS attached in front of Atlas (note that it is not a virtual IP). If there is LVS and client-ips is set, this must be set, otherwise # lvs-ips = 192.168.1.1 can not be set.

Edit add startup script

Vim / EtcUnip init.dAccord Shutting down atlas Shutting down atlas: Atlas Daemon## chkconfig:-902 cycles 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 See how we were called.case "$1" in start) start ; stop) stop;; restart) stop sleep 3 start;; *) echo $"Usage: $0 {start | stop | restart}" exit 1esacexit 0

Give the startup script permission and add boot boot

Chmod-R 755 / etc/init.d/atlaschkconfig atlas on starts atlasservice atlas start

Port introduction

Two ports 1234 and 2345 are opened by default. (the above ports can be modified in the configuration file)

Working interface IP and port for Atlas snooping

Proxy-address = 0.0.0.0purl 1234

Management interface IP and port for Atlas snooping

Admin-address = 0.0.0.0purl 2345

Access the administrative background and view the administrative commands (note that the username and password is default, it is best to modify it for security):

Mysql-uuser-ppwd-P2345-h 127.0.0.1mysql > select * from help +-- +-- + | 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 example 3306",... | | ADD SLAVE $backend | example: "add slave 127.0.0.1 add slave 3306",... | REMOVE BACKEND $backend_id | example: "remove backend 1" | | SELECT * FROM clients | lists the clients | | ADD CLIENT $client | example: "add client 192.168.1.2",... | | REMOVE CLIENT $client | example: "remove client 192.168.1.2" | | SELECT * FROM pwds | lists the pwds | | ADD PWD $pwd | example: "add pwd user:raw_password",... | | ADD ENPWD $pwd | example: "add enpwd user:encrypted_password",... | | REMOVE PWD $pwd | example: "remove pwd user". | | SAVE CONFIG | save the backends to config file | | SELECT VERSION | display the version of Atlas | + -+ 16 rows in set (0.00 sec)

Access the work port

You can use the NAVICAT client for connection configuration

Read load balancer verification (experience will alternately use two back-end database servers to respond to requests)

Mysql > select @ @ hostname;+-+ | @ @ hostname | +-+ | db1 | +-+ 1 row in setmysql > select @ @ hostname;+-+ | @ @ hostname | +-+ | db2 | +-+ 1 row in set

Read-write separation verification

Mysql > begin;select @ @ hostname;commit;Query OK, 0 rows affected +-+ | @ @ hostname | +-+ | db1 | +-+ 1 row in set Query OK, 0 rows affected mysql > begin;select @ @ hostname;commit Query OK, 0 rows affected +-+ | @ @ hostname | +-+ | db1 | +-+ 1 row in set Query OK, 0 rows affected

Pressure testing

Read-write separation stress testing requires the use of Jmeter, which is the first open source stress testing tool for Java to write, because it is convenient. He has a module dedicated to testing MySQL, and needs to use MySQL's JDBC driver jar package. The configuration is very simple, and the things are very good, powerful and easy to use.

Jmeter download address: http://jmeter.apache.org/download_jmeter.cgi

JDBC of MySQL: http://dev.mysql.com/downloads/connector/j/

After downloading it, unpack it separately, open Jmeter (jmeter.bat under the bin road), and import JDBC's jar package in the test plan. A brief description of the specific steps

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.