Using Atlas for MySQL read-write separation

1. The machine where MySQL is located

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, install the appropriate version of Atlas. I downloaded the atlas-2.2.el6.x86_64.rpm.

It is mounted directly on the 192.168.29.130 machine.

3. Configure Atlas

The installation directory for Atlas is/usr/local/mysql-proxy, go to the installation directory conf directory, edit test.cnf files, many options with default values.

1) The main library's IP and port, you can set multiple items, separated by commas

Proxy-backend-addresses = 192.168.29.128:3306

2) (non-essential, according to the actual situation configuration) from the library's IP and port, the number after the @ represents the weight, used for load balancing, if omitted the default is 1, you can set a number of items, separated by commas. If you want the main library to be able to share read requests, you only need to add the main library information to the following configuration items.

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

3) (necessary, according to the actual situation configuration) the user name and its corresponding encrypted MySQL password, password using the installation path/bin directory encryption program encrypt encryption, the user name and password separated by a colon. You need to create the user and set the password on the master/slave database (the user name and password are consistent on the master-slave database).

Encrypt password:

./encrypt 123456

Configure password

PWDs = root:/izxz+0groa=

4) (required, default) the operation mode of Atlas, set to True when the daemon mode, set to False as the foreground mode, general development debugging is set to False, the online runtime is set to True

True

5) (required, default) the working interface IP and port of the Atlas Listener, proxy-address = 0.0.0.0:1234 on behalf of the client should use the 1234 port to connect to Atlas to send SQL requests.

Proxy-address = 0.0.0.0:1234

6) (required, default) the management interface IP and port of the Atlas Listener, admin-address = 0.0.0.0:2345, means that the DBA should use the 2345 port to connect to Atlas to perform operational management operations.

Admin-address = 0.0.0.0:2345

7) (optional, not set) default character set, if not set, the default character set is Latin1

charset = UTF8

8) (must, according to the actual situation configuration) the SQL log switch, can be set to off, on, Realtime,off for not logging SQL log, on for logging SQL log, the mode of log refresh is buffer-based, log information is filled in the buffer before it is flushed to disk. Realtime for debugging, representing logging of SQL logs and writing to disk in real time, off by default

Sql-log = REALTIME

Log path under/usr/local/mysql-proxy/log

4. Running Atlas

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

./mysql-./mysql-proxyd Test Restart, restart Atlas . /mysql-proxyd test Stop, Stop Atlas.

Start:

[[email protected] bin] # ./mysql-proxyd Test Start ok:mysql-Proxy of test is started[root@Luxh -01 bin]#

Attention:

(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, and the three need to be unified.

(3). Ps-ef can be used | grep mysql-proxy to see if Atlas has started or stopped.

5. Connection

The client connects to the Atlas Proxy service using the Atlas listener port just configured

[[Email protected] ~] # mysql-h192.168.29.130-p1234-uroot-p123456

Execute SQL to test, you can see through the log write operations are in master, read operations are connected to the slave

Connect to the management port

[[email protected] conf] # mysql-h192.168.29.130-p2345-uuser-ppwd

Execute after entry: SELECT * from Help, view various commands for managing db.

　　

Using Atlas for MySQL read-write separation