Use Atlas to implement MySQL read/write splitting and implement mysql read/write through atlas

Use Atlas to implement MySQL read/write splitting and implement mysql read/write through atlas

1. MySQL Machine

192.168.29.128 (Master)

192.168.29.129 (Slave)

Configure master-slave synchronization, refer to http://www.cnblogs.com/luxh/p/4088420.html

2. Download and install Atlas

Https://github.com/Qihoo360/Atlas/releases

Note the version of the operating system and install the corresponding version of Atlas. I downloaded a Atlas-2.2.el6.x86_64.rpm.

Install it on 192.168.29.130.

rpm -i Atlas-2.2.el6.x86_64.rpm 

 

3. Configure Atlas

The installation directory of Atlas is/usr/local/mysql-proxy. Enter the conf directory under the installation directory and edit the test. cnf file. You can use the default values for many options.

1) ip addresses and ports of the master database. Multiple ip addresses and ports can be set and separated by commas (,).

proxy-backend-addresses = 192.168.29.128:3306

2) (optional, configured according to the actual situation) the slave database IP address and port. The number following @ indicates the weight and is used for load balancing. If this parameter is omitted, the default value is 1, you can set multiple entries separated by commas. To allow the master database to share read requests, you only need to add the master database information to the following configuration items.

proxy-read-only-backend-addresses = 192.168.29.129:3306

3) (required, configured according to the actual situation) the user name corresponds to the encrypted MySQL password. The password is encrypted using the encryption program encrypt in the installation path/bin directory, user names and passwords are separated by colons. You must first create the user in the master-slave database and set the password (the user name and password must be consistent on the master-slave database ).

Encrypted password:

./encrypt 123456

Configure Password

pwds = root:/iZxz+0GRoA=

4) (required, only available by default) run mode of Atlas. If it is set to true, the daemon mode is used. If it is set to false, the foreground mode is used. Generally, the development debugging mode is set to false, and the online running mode is set to true.

daemon = true

5) (required, default value) IP address and port of the working interface of the Atlas listener. proxy-address = 0.0.0.0: 1234 indicates that the client should connect to the Atlas port 1234 to send SQL requests.

proxy-address = 0.0.0.0:1234

6) (required, default value) the IP address and port of the management interface of the Atlas listener. admin-address = 0.0.0.0: 2345 indicates that the DBA should use the port 2345 to connect to the Atlas for O & M management.

admin-address = 0.0.0.0:2345

7) (optional) default character set. If this item is not set, the default character set is latin1.

charset = utf8

8) (required, configured according to the actual situation) the SQL log switch can be set to OFF, ON, REALTIME, OFF indicates that SQL logs are not recorded, and ON indicates that SQL logs are recorded, in this mode, log refresh is based on the buffer. When the log fills the buffer, the log information is flushed to the disk. REALTIME is used for debugging. It indicates that SQL logs are recorded and written to the disk in real time. The default value is OFF.

sql-log = REALTIME

The log Path is under/usr/local/mysql-proxy/log.

4. Run Atlas

Go to the/usr/local/mysql-proxy/bin directory and run the following command to start, restart, or stop Atlas.

./Mysql-proxyd test start to start Atlas. ./Mysql-proxyd test restart and restart Atlas. ./Mysql-proxyd test stop to stop Atlas.

Start:

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

Note:

(1) The Running file is mysql-proxyd (not mysql-proxy ).

(2). test is the name of the configuration file in the conf directory and the name of the instance item in the configuration file.

(3) You can use ps-ef | grep mysql-proxy to check whether Atlas has been started or stopped.

5. Connection

The client uses the configured Atlas listening port to connect to the Atlas proxy service.

[root@Luxh-03 ~]# mysql -h192.168.29.130 -P1234 -uroot -p123456

Run the SQL statement for testing. You can see from the log that all write operations are on the Master, and all read operations are connected to the Slave.

 

Connect to the Management port

[root@Luxh-03 conf]# mysql -h192.168.29.130 -P2345 -uuser -ppwd

Run select * from help to view the commands used to manage the database.