MySQL implementation read and write separation of the detailed

GRANT all privileges on * * to ' root ' @ '% ' with GRANT OPTION;

FLUSH privileges;

Use MySQL
Update user set Password=password (' MySQL ') where user= ' root ';
FLUSH privileges;

Do it all on the master/slave Library
--------------------------------------------------------------------
Yum Install-y gcc*
RPM-IVH atlas-2.2.1.el5.x86_64.rpm "RPM Package Direct Installation"
RPM-QL Atlas "View installation path"
Grep-v ' ^# '/usr/local/mysql-proxy/conf/test.cnf |grep-v ' ^$ ' >/usr/local/mysql-proxy/conf/lipengfei ' filter garbage prompt '
MV Test.cnf Test.cnf.old
Cat/usr/local/mysql-proxy/conf/lipengfei >/USR/LOCAL/MYSQL-PROXY/CONF/TEST.CNF
Rm-rf/usr/local/mysql-proxy/conf/lipengfei

/usr/local/mysql-proxy/bin/encrypt MySQL "MySQL is my password, password encryption"
twbz0dlu35u=

Vi/usr/local/mysql-proxy/conf/test.cnf

[Mysql-proxy]
Plugins = Admin,proxy
Admin-username=admin
Admin-password=admin
Admin-lua-script =/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua
Proxy-backend-addresses = 192.168.8.100:3306
Proxy-read-only-backend-addresses = 192.168.8.101:3306,192.168.8.102:3306
PWDs = root:twbz0dlu35u=
Daemon = True
KeepAlive = True
Event-threads = 8
Log-level = Message
Log-path =/usr/local/mysql-proxy/log
Instance = Test
Proxy-address = 0.0.0.0:1234
Admin-address = 0.0.0.0:5678
charset = UTF8

Viewing the Listening port
NETSTAT-TANLP | grep MySQL

Open
/usr/local/mysql-proxy/bin/mysql-proxyd Test Start
/usr/local/mysql-proxy/bin/mysql-proxyd Test Stop

Login Management
Mysql-h 192.168.8.103-p 5678-u Admin-padmin

Normal operation via proxy
Mysql-h 192.168.8.103-p 1234-u Root-pmysql

SHOW VARIABLES like ' server_id ';

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", ... |
| ADD CLIENT $client | Example: "Add client 192.168.1.2", ... |
| REMOVE CLIENT $client | Example: "Remove client 192.168.1.2", ... |
| SAVE CONFIG | Save the backends to config file |
+-------------------+---------------------------------------+
Rows in Set (0.00 sec)

Mysql> select * from Backends;
+-------------+-------------------+-------+------+
|           Backend_ndx | Address |  State | Type |
+-------------+-------------------+-------+------+
| 1 | 192.168.0.42:3306 | Down | RW |
| 2 | 192.168.0.44:3306 | Down | Ro |
| 3 | 192.168.0.43:3306 | Down | Ro |
+-------------+-------------------+-------+------+
3 Rows in Set (0.00 sec)

5678 Port: Is the management interface of Atlas, this interface provides a view of MySQL status, set up on-line, offline backend MySQL server
Access method using MySQL command

Mysql-h 10.142.132.50-p2345-u admin-p ' admin '
Here the username password is admin-username = admin, Admin-password = admin in the config file above
The following is the use Help for the management interface
SELECT * from Help; # View Help Information
SELECT * from Backends; # View back-end host status
SET OFFLINE $backend _id # offline One host, $backend _id = = SELECT * from backends; ID found
SET online $backend _id # on-line one console
Add Master $backend # Adds a master node, such as: Add Master 10.57.1.131;
Add SLAVE $backend # Adds a SLAVE node, such as: Add SLAVE 10.57.1.132;
Remove backend $backend _id # Deletes a node,

Automatic read and write separation is good, but sometimes I finish writing immediately want to read, in case of master-slave synchronization delay how to do?
Add/*master*/before A:sql statement to force a read request to the main library

---------------------------------------------------------------------

Set up Atlas as a service and enable automatic boot

# Description: The author has tested only on CentOS7.0 and has not been tested for reliability and stability!
Cp/tmp/atlas/etc/rc.d/init.d/atlas
chmod +x/etc/rc.d/init.d/atlas
Chkconfig--add Atlas
Chkconfig Atlas on

#!/bin/sh
#description: Atlas Autorun
#your Instance Name
Instance_name=test
Case "$" in
"Start")
/usr/local/mysql-proxy/bin/mysql-proxyd $instance _name Start
;;
"Stop")
/usr/local/mysql-proxy/bin/mysql-proxyd $instance _name Stop
;;
"Restart")
/usr/local/mysql-proxy/bin/mysql-proxyd $instance _name Restart
;;
"Status")
/usr/local/mysql-proxy/bin/mysql-proxyd $instance _name Status
;;
*)
echo "Usage:service Atlas Start/stop/restart/status"
;;
Esac

Use the following command:
Service Atlas Start
Service Atlas Stop
Service Atlas Status
Service Atlas Restart

----------------------------------------------------

Monitoring the Atlas script
cat/usr/local/mysql-proxy/bin/monitor_atlas.sh
#!/bin/bash
If [$ (ps-ef |grep ' mysql-proxy ' |grep-v ' grep ' |wc-l)-eq "0"];then
/usr/local/mysql-proxy/bin/mysql-proxyd Test Start
Sleep 5
If [$ (ps-ef |grep ' mysql-proxy ' |grep-v ' grep ' |wc-l)-eq "0"];then
Killall-9 keepalived
Service Network restart
Fi
Fi

chmod +x/usr/local/mysql-proxy/bin/monitor_atlas.sh

------------------------configuration File Description------------------------
[Mysql-proxy]
#管理接口的用户名, define it according to your needs
Admin-username = Admin
#管理接口的密码
Admin-password = password
#Atlas后端连接的MySQL主库的IP和端口, multiple items can be set, separated by commas
Proxy-backend-addresses = 10.57.1.131:3306
#Atlas后端连接的MySQL从库的IP和端口, the number at the end of the @ represents the weight, used for load balancing, and if omitted the default is 1, you can set multiple items, separated by commas
Proxy-read-only-backend-addresses = 10.57.1.132:[email protected]
#用户名与其对应的加密过的MySQL密码, password using the Prefix/bin directory encryption program encrypt encryption, downlink user1 and User2 For example, replace it with your MySQL username and encryption password!
PWDs = root:yqsbjy4g4oc=,test:yqsbjy4g4oc=
Note that these two users are required to create and open corresponding access rights on Mysql-master, where the user is the user who needs to be open to use
#设置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
Daemon = True
#设置Atlas的运行方式, set to True when Atlas starts two processes, one for monitor, one for worker,monitor automatically restarts after worker exits unexpectedly, set to false when only worker, no monitor, General development debugging is set to false, and the on-line runtime is set to True
KeepAlive = True
#工作线程数, there is a significant impact on the performance of Atlas, which can be set appropriately depending on the situation
Event-threads = 8
#日志级别, divided into a message, warning, critical, error, debug five levels
Log-level = Message
#日志存放的路径
Log-path =/usr/local/mysql-proxy/log
#SQL日志的开关, can be set to off, on, Realtime,off for not logging SQL logs, on for logging SQL logs, REALTIME for logging SQL logs and writing to disk in real time, default to OFF
#sql-log = OFF
#实例名称 for the distinction between multiple Atlas instances on the same machine
#instance = Test
#Atlas监听的工作接口IP和端口
Proxy-address = 0.0.0.0:3306
#Atlas监听的管理接口IP和端口
Admin-address = 0.0.0.0:2345
#连接池的最小空闲连接数, can be adjusted or resized according to the size of the business request volume
Min-idle-connections = 128
#分表设置, 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 child tables, you can set multiple items, separated by commas, if the table does not need to set the item
#tables = Person.mt.id.3
#默认字符集, the client no longer needs to execute the SET NAMES statement after setting the item
charset = UTF8
#允许连接Atlas的客户端的IP, can be a precise IP, can be an IP segment, comma-delimited, if not set the entry allows all IP connections, otherwise only allow IP connections in the list
Client-ips = 127.0.0.1, 10.0.0
#Atlas前面挂接的LVS的物理网卡的IP (note is not a virtual IP), if there is LVS and set client-ips, this key must be set, otherwise you can not set
#lvs-ips = 192.168.1.1
# End

This article is from the "Come Together" blog, please be sure to keep this source http://daxionglaiba.blog.51cto.com/11790757/1828103

MySQL implementation read and write separation of the detailed