Mysql-atlas Installation and Use tutorial

Atlas is a MySQL protocol-based data mid-tier project developed and maintained by the infrastructure team of the Qihoo 360 corporate web platform. 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. At the same time, more than 50 companies have deployed Atlas in production environments, and more than 800 people have joined our developer Communications Group, and these numbers are growing.

GitHub Address
Introduced

First, installation

Installation

Before installing, MySQL must be installed on the server, and Altas will use the MySQL component.

At startup, you may be prompted to faild start Altas of test such as the following workaround

echo ‘/usr/local/mysql/lib/‘ >> /etc/ld.so.confldconfig

Second, configuration instructions

[Mysql-proxy] (Required, default) Manage interface username Admin-username = User (required, default) manage interface password Admin-password = pwd (required, configured according to actual situation) The main library's IP and port proxy-backend-addresses = 192.168.0.12:3306 (not required, according to the actual situation) from the library's IP and port, the number after the @ represents the weight, used for load balancing, if omitted the default is 1, 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.0.13:3306,192.168.0.14:3306 (must be configured according to the actual situation) user name and its corresponding encrypted MySQL password, The password is encrypted using an encryption program encrypt the Prefix/bin directory, separated by a colon between the user name and the password. 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). For example, the user name is MyUser, password is MyPwd, execute./encrypt mypwd result is hjboxfrsjei=. If more than one user is separated by commas. The following line is set as follows: PWDs = myuser:hjboxfrsjei=,myuser2:hjboxfrsjei= (required, default) the operation of Atlas, set to True when the daemon mode, set to False is the foreground mode, General development debugging is set to False, the online runtime is set to Truedaemon = True (required, default) set how Atlas runs, when set to True, Atlas starts two processes, one for monitor, one for worker, Monitor will automatically restart the worker after it exits unexpectedly, set to false when only worker, no monitor, general development debugging set to False, the line runtime is set to Truekeepalive = True (required, according to the actual situation configuration) Number of worker threads, recommended to be set to 2 to 4 times times the number of CPU cores of the system event-threads = 4 (required, default) log level, divided into message, warning, critical, error, debug five levels Log-level = Message (required, default) the path of log storage Log-path =/usr/local/mysql-proxy/log (necessary, according to the actual situation configuration) the SQL log switch, can be set to off, on, Realtime,off for not logging SQL logs, on for logging SQL logs, the mode of log refresh is buffer-based, log information is filled in the buffer before the logs to disk. Realtime is used for debugging, on behalf of logging SQL logs and writing to disk in real time, the default is Offsql-log = OFF (optional, not set) slow log output settings. When this parameter is set, the log only outputs log records with a execution time exceeding sql-log-slow (in ms). If you do not set this parameter, all logs are output. Sql-log-slow = 10 (optional, not set) turns off inactive client connection settings. When this parameter is set, Atlas actively shuts down connections that have not been active since the ' wait-timeout ' time. Units: Seconds Wait-timeout = 10 (required, default) the working interface IP and port of the Atlas listener, the address of the connection Altas proxy-address = 0.0.0.0:1234 (required, default) the management interface IP and port of the Atlas listener Admin-address = 0.0.0.0:2345, the address of the Connection Altas Management Service (optional, not set) table settings, in this case, the person is the library name, MT is the table name, the ID is a sub-table field, 3 is the number of children, you can set multiple items, separated by commas, If the table does not need to set the item, the child table needs to be built beforehand, the child table name is the table name _ number, the number range is [0, the number of sub-1], as in this example, the child table name is Mt_0, mt_1, mt_2tables = Person.mt.id.3 (optional, not set) The default character set, If this item is not set, the default character set is Latin1charset = UTF8 (optional, not set) The IP of the client that is allowed to connect to Atlas, which can be either an exact IP or an IP segment, separated by commas, which allows all IP connections if not set. Otherwise only the IP connection in the list is allowed client-ips = 127.0.0.1, 192.168.1 (optional, rarely needed) The IP of the LVS physical network card that is mounted in front of Atlas (note is not a virtual IP), if there is LVS and the client-ips is set, this key must be set , otherwise you may not set lvs-ips = 192.168.1.1

Third, Altas management

Perform mysql-h127.0.0.1-uuser-ppwd-p2345 to connect and enter Altas management

1. Query Help

SELECT * from help;+----------------------------+---------------------------------------------------------+| Command | Description |+----------------------------+----------------------------------- ----------------------+| SELECT * FROM Help | Show Help | | SELECT * from Backends | View back-end Server Status | | SET OFFLINE $backend _id | Offline backend server, $backend _id is backend_ndx ' s ID | | SET ONLINE $backend _id | On-line backend server, ... | | ADD MASTER $backend | Add master server, Example: "Add Master 127.0.0.1:3306", ... | | ADD SLAVE $backend | Add from server, example: "Add slave 127.0.0.1:3306", ... | | REMOVE Backend $backend _id | Remove back-end server Example: "Remove backend 1", ... | | ADD CLIENT $client | Add clients Example: "Add client 192.168.1.2", ... | | REMOVE CLIENT $client | Remove Client Example: "Remove clIent 192.168.1.2 ", ... | | SAVE CONFIG | Save config to file |+----------------------------+---------------------------------------------------------+

2. View back-end MySQL status information

mysql> select * from backends    -> ;+-------------+-------------------+-------+------+| backend_ndx | address           | state | type |+-------------+-------------------+-------+------+|           1 | 10.211.55.9:3306  | up    | rw   ||           2 | 10.211.55.10:3306 | up    | ro   |+-------------+-------------------+-------+------+2 rows in set (0.00 sec)

3. Offline MySQL server

mysql> set offline 2    -> ;+-------------+-------------------+---------+------+| backend_ndx | address           | state   | type |+-------------+-------------------+---------+------+|           2 | 10.211.55.10:3306 | offline | ro   |+-------------+-------------------+---------+------+1 row in set (0.00 sec)

4. On-line MySQL server

mysql> set online 2;+-------------+-------------------+---------+------+| backend_ndx | address           | state   | type |+-------------+-------------------+---------+------+|           2 | 10.211.55.10:3306 | unknown | ro   |+-------------+-------------------+---------+------+1 row in set (0.00 sec)

Mysql-atlas Installation and Use tutorial