MySQL + Atlas, mysqlatlas

MySQL + Atlas, mysqlatlas
Collation

Atlas is a set of proxies developed by the 360 team based on MySQL-Proxy. It modified some MySQL-Proxy bugs and optimized many things. It is also easy to install.

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

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

 

Environment:

System	IP	Configuration
CentOS 1, 6.7	192.168.246.143	Atlas proxy service
CentOS 1, 6.7	192.168.246.134	Primary MySQL database
CentOS 1, 6.7	192.168.246.135	From MySQL database

 

1. Database Configuration

You need to configure the user name and password in the 134 and 135 databases. The user must be remotely accessible. The configuration method is as follows:

 

First, go to MySQL database 134, and create the user "buck" to set the password to "hello". The following marked red is the user and password.

mysql> grant all on *.* to buck@'127.0.0.1' identified by "hello";Query OK, 0 rows affected (0.00 sec)

 

To modify the access permission of buck, you must first enter the mysql database to modify the host permission information.

# Go To mysql> use mysqlDatabase changed # change the host permission to "%" mysql> update user set host = '%' where user = 'buck '; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

 

Check the user table to see if the modification is successful. We can see that the host of buck has been modified into a semicolon.

mysql> select user, host from user;+------+-----------------------+| user | host                  |+------+-----------------------+| buck | %                     || root | 127.0.0.1             ||      | localhost             || root | localhost             ||      | localhost.localdomain || root | localhost.localdomain |+------+-----------------------+6 rows in set (0.00 sec)

 

Update the database information. If the database information is not updated and The modification does not take effect immediately, restart the database. You can directly update the database information to avoid restarting the database.

mysql>  flush privileges;Query OK, 0 rows affected (0.00 sec)

 

You need to create a database for both the master and slave MySQL databases. The database I created here is "test". In order to facilitate the test of read/write splitting

mysql> create database test;Query OK, 1 row affected (0.00 sec)

 

[Idea: 135 databases are configured the same as 134 databases. Remember to create the same database]

 

2. Master-slave database connection

To configure the master-slave server, you need to compile the MySQL configuration file. The detailed configuration steps are as follows:

Master server (192.168.246.134), configured using vim

[Mysqld] datadir =/data/mysql socket =/var/lib/mysql. sock user = mysql # Master-slave replication configuration innodb_flush_log_at_trx_commit = 1 sync_binlog = 1 # binlog-do-db = test # binlog-ignore-db = mysql # start the binary file log-bin = mysql-bin # server ID server-id = 1 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links = 0 [mysqld_safe] log- error =/var/log/mysqld. log pid-file =/var/run/mysqld. pid

[Idea: If binlog-do-db and binlog_ignore_db are not configured, all databases are backed up .]

 

Restart the mysqld service

[root@localhost bin]# /etc/init.d/mysqld restart

 

Go to the database and configure master-slave replication permissions.

mysql> grant replication slave on *.* to 'buck'@'127.0.0.1' identified by 'hello';Query OK, 0 rows affected (0.00 sec)

 

Lock Database

mysql> flush tables with read lock;Query OK, 0 rows affected (0.00 sec)

 

View the master database information and remember the following "File" and "Position" information. They are used to configure the key information of the slave database. We can see the "test" database of the database to be synchronized below. If the data of the master and slave databases is different, we need to manually synchronize the data. In Windows, I only use the data copied by Navicat, this is not demonstrated here.

mysql> show master status;+------------------+----------+--------------+------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000002 | 17620976 | test         | mysql            |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)

 

The slave server (192.168.246.135) also needs to be configured using vim. You only need to add server-id = 2 under [mysqld]. All configurations are as follows:

[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysqlserver-id=2# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

 

Enter the database and configure the information from the database. Enter the recorded "File" and "Position" and execute the following on the slave server:

Mysql> change master to master_host = '192. 168.246.134'	IP address of the master server
Master_user = 'buck'	Configure the User Name of the master server
Master_password = 'hello'	Password of the corresponding user
Master_port = 3306	Mysql port of the master server
Master_log_file = 'mysql-bin.000002'	Log File Name, which must correspond to the master server
Master_log_pos = 17620976	Log location, which must correspond to the master server
Master_connect_retry = 10	Reconnect times

mysql> change master to master_host='192.168.246.134',    -> master_user='buck',    -> master_password='hello',    -> master_port=3306,    -> master_log_file='mysql-bin.000002',    -> master_log_pos=17620976,    -> master_connect_retry=10;Query OK, 0 rows affected (0.01 sec)

Start Process

mysql> start slave;Query OK, 0 rows affected (0.00 sec)

 

Check the master-slave replication status. If both of the two values are Yes, the master-slave connection is correct. If one value is No, you need to confirm the log information you just recorded, stop "stop slave" and reconfigure the master-slave connection.

mysql> show slave status \G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.246.134                  Master_User: buck                  Master_Port: 3306                Connect_Retry: 10              Master_Log_File: mysql-bin.000002          Read_Master_Log_Pos: 17620976               Relay_Log_File: mysqld-relay-bin.000002                Relay_Log_Pos: 251        Relay_Master_Log_File: mysql-bin.000002             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 17620976              Relay_Log_Space: 407              Until_Condition: None               Until_Log_File:                 Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error: 1 row in set (0.00 sec)ERROR: No query specified

 

 

3. Atlas configuration

There will be two versions to download Atlas, one of which is the table sharding version, but this requires other dependencies. I do not need to split the table here, So install the normal version

Atlas (common ):Atlas-2.2.1.el6.x86_64.rpm
Atlas (Table sharding ):Atlas-sharding_1.0.1-el6.x86_64.rpm

First, go to the Home directory of Linux and download the installation package for non-Table sharding.

[root@localhost ~]# cd /home/[root@localhost home]# wget https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm

 

After the download, install

[root@localhost home]# rpm -ivh Atlas-2.2.1.el6.x86_64.rpm Preparing...                ########################################### [100%]   1:Atlas                  ########################################### [100%]

 

After installation, it will generate Four folders and the files to be configured under "/usr/local/mysql-proxy" by default, as shown below:

[root@localhost home]# ll /usr/local/mysql-proxy/total 16drwxr-xr-x. 2 root root 4096 Dec 28 10:47 bindrwxr-xr-x. 2 root root 4096 Dec 28 10:47 confdrwxr-xr-x. 3 root root 4096 Dec 28 10:47 libdrwxr-xr-x. 2 root root 4096 Dec 17  2014 log

All executable files under the bin directory
1. "encrypt" is used to generate MySQL password encryption, which will be used during configuration
2. "mysql-proxy" is the MySQL read/write splitting proxy.
3. "mysql-proxyd" is 360, followed by a "d" to start, restart, and stop the service. They are all used for execution.
 
Configuration files are put under the conf directory.
1. "test. cnf" has only one file to configure the proxy. You can use vim to edit it.
 
Some packages and Atlas dependencies are put under the lib directory.
Logs are stored in the log directory, such as errors.

 

Go to the bin directory and use encrypt to encrypt the Database Password. My MySQL Data username is buck and the password is hello. I need to encrypt the password.

[root@localhost bin]# ./encrypt helloRePBqJ+5gI4=

 

Configure Atlas and use vim for editing

[root@localhost conf]# cd /usr/local/mysql-proxy/conf/[root@localhost conf]# vim test.cnf

 

After entering, you can configure in Atlas. The Chinese comments written in 360 are very detailed. Configure the information according to the comments. The important information is as follows:

This is the account and password used to log on to the administrator of Atlas. It corresponds to "# management interface IP address and port of Atlas listener". That is to say, you need to set the administrator login port, to enter the administrator interface. The default port is 2345. You can also specify an IP address to log on to the console. After an IP address is specified, other IP addresses cannot access the Administrator's command interface. For ease of test, I have not specified an IP address or port to log on.
# Admin-username = user # admin-password = pwd
 
This is used to configure the master data address and slave database address. The configured master database is 135, and the slave database is 134.
# The IP address and port of the MySQL master database connected to the backend of Atlas. You can set multiple IP addresses. Use commas to separate proxy-backend-addresses = 192.168.246.134: 3306 # the IP address and port of the MySQL slave database connected to the backend of Atlas. @ indicates the weight, which is used for load balancing. If this parameter is omitted, the default value is 1. You can set multiple entries, use commas to separate proxy-read-only-backend-addresses = 192.168.246.135: 3306 @ 1
 
This is used to configure the MySQL account and password. My MySQL user is buck and the password is hello. I just used the tool provided by Atlas to generate the corresponding encryption password.
# The user name corresponds to the encrypted MySQL password. The password is encrypted using the encryption program encrypt In the PREFIX/bin directory. The following are examples of user1 and user2, replace it with your MySQL user name and encrypted password! Pwds = buck: RePBqJ + 5gI4 =
 
This is to set the work interface and management interface. If "0.0.0.0" is set for the IPaddress, it means that any IP Address can access this interface. Of course, you can also specify the IP address and port to facilitate the test, the user name and password of the work interface correspond to the MySQL account, and the administrator user password corresponds to the user password configured above.
# Working interface IP address and port proxy-address = 0.0.0.0: 1234 # management interface IP address and port admin-address = 0.0.0.0: 2345 of the Atlas listener
 

 

Start Atlas

[root@localhost bin]# ./mysql-proxyd test startOK: MySQL-Proxy of test is started

 

Test the MySQL status of the Atlas server. Make sure it is disabled and you cannot enter the database using the mysql command.

[root@localhost bin]# /etc/init.d/mysqld statusmysqld is stopped

[root@localhost bin]# mysqlERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

 

Make sure that the MySQL in the system cannot be entered. Run the following command to go to the management mode "mysql-h127.0.0.1-P2345-uuser-ppwd" of Atlas. If the command is displayed, it indicates that Atlas is running normally, because it regards itself as a MySQL database, you can also enter the MySQL database mode without the database environment.

[root@localhost bin]# mysql -h127.0.0.1 -P2345 -uuser -ppwdWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.0.99-agent-adminCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

 

You can access the "help" table to see what MySQL administrator can do. You can use SQL statements to access

mysql> 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:3306", ...               || ADD SLAVE $backend         | example: "add slave 127.0.0.1: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)mysql>

 

You can also use the work interface to access it and run the command "mysql-h127.0.0.1-P1234-ubuck-phello"

[root@localhost bin]# mysql -h127.0.0.1 -P1234 -ubuck -phelloWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.0.81-logCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

 

If you can access the work interface, you can use Navicat on the Windows platform to connect to the database, and enter the corresponding host, Port, user name, and password.

4. read/write Splitting Test

Jmeter is used to test read/write splitting. Jmeter is the first open-source stress testing tool written in Java, because it is convenient. He has a module dedicated to MySQL testing and needs to use the JDBC driver jar package of MySQL. The configuration is very simple, and things are very powerful and easy to use.

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

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

 

After downloading it, decompress it and open Jmeter (jmeter. bat under bin Road). In the test plan, the JDBC jar package

Configure the JDBC driver

Separate query and insert statements

 

After the configuration is complete, run the query operation, and then monitor the traffic of the master database and the slave database to determine whether to read or write data, use the "sar-n DEV 1 10000" command to monitor read/write

Test writing first. Currently, there is no information in the database. Enable the configured Jmeter to test data writing.

 

Primary database (192.168.246.134)

Slave database (192.168.246.135)

We can see that when the data insertion test is performed, the NIC traffic of the primary database is large and the traffic from the database is small. The primary data is mainly responsible for writing data, the slave database is mainly responsible for synchronization.

 

Check the database and find that more than pieces of data have been inserted.

 

To test data reading, you only need to execute the query and execute "select * from sbtest;" to query the data table.

Primary database (192.168.246.134)

Slave database (192.168.246.135)

We can see that 135 of the database traffic is very large, and 134 does not have any traffic, so we can confirm that the data is read from the database. Read/write splitting has been implemented.