Read and write separation of proxy based on MySQL master-slave synchronization

Architecture

___ Master_mysql (10.10.10.3) |   |      Mysql-proxy (10.10.10.2) |           |___       | Slave_mysql (10.10.10.4)

(i) MySQL master-slave synchronization

1. Mysql-master setting

(1)

Server-id = 1//master End ID Number binlog-ignore-db =//set unsynchronized SQL Library binlog-do-db=//set synchronized data

(2) Restart MySQL after setting up the above

(3) Add user access to the main library, and only backup permissions

MySQL > Grant replication slave on * * to ' rsync ' @ ' 10.10.% ' identified by ' 1q2w3e4rys ' with Grant Option;mysql > FL Ush privileges;

(4) Lock table, main library data continue to write

MySQL > Flush tables with read lock;

(5) record the value of master file and position, slave the library configuration to use when synchronizing

(6) Export MySQL Master library data

Mysqldump-uuser-p databases > Databases.sql

Note: There is a problem here, when the main library data on the G, large need to do the following actions

Maximum size of buffers for--max_allowed_packet=xxx///client/server communication,--NET_BUFFER_LENGTH=XXX//TCP/IP and socket communication buffer size, creation length up to Net_buffer_ Length of the line;

Note: Max_allowed_packet and net_buffer_length cannot be larger than the target database configuration, or there may be an error.

First determine the parameter values of the target library

Mysql>show variables like ' max_allowed_packet ', mysql>show variables like ' net_buffer_length ';

Write mysqldump commands based on parameter values, such as:

MySQL > mysqldump-uuser-p? Database name-e--max_allowed_packet=1048576--net_buffer_length=16384 > Databases.sql

(7) Master library synchronized to slave server

# SCP Databases.sql [email Protected]:/path

2. Mysql-slave setting

(1)

Server-id = 2

(2) Log in to MySQL to create a SQL database

MySQL > CREATE DATABASE '? sql_db ' DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;

(3) Import the master library data into the slave library

Mysql-uuser-p? sql_db < Databases.sql

(4) Login MySQL stop slave

mysql > Stop slave;

(5) Configure sync from library

MySQL > Change master to master_host= ' 10.10.10.2 ', master_user= ' rsync ', master_password= ' 1q2w3e4rys ', master_port= 3306,master_log_file= ' mysql-bin.000047 ', master_log_pos=107;

(6) Start the sync function

mysql > Start slave;

(7) Unlock the main Library lock table

Unlock tables;

(8) View slave sync status

MySQL > show slave status\g//Run multiple times to see the state of the data after it is written

Note: Check the above Slave_io_running:yes and Slave_sql_running:yes 2 are Yes to prove that master-slave synchronization is normal, if any one shows no, it proves that there is a problem with synchronization. You can view the database log files, The inside will basically show the error, according to the error step by step troubleshooting, basically can be solved.

(ii) Mysql-proxy read-write separation

1, installation Mysql-proxy

(1)

# wget http://ftp.ntu.edu.tw/pub/MySQL/Downloads/MySQL-Proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz# Tar XF Mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz-c/usr/local# cd/usr/local# LN-SV Mysql-proxy-0.8.5-linux-el6-x86-64bit Mysql-proxy

2. Set the genus Mysql-proxy Group

(1)

# groupadd mysql-proxy# useradd-g mysql-proxy-s/sbin//nologin-m mysql-proxy

3, Configuration Mysql-proxy

(1) To provide the SysV service script for Mysql-proxy, the script is as follows:

#!/bin/bash## mysql-proxy this script starts and stops the mysql-proxy  daemon## chkconfig: - 78 30# processname: mysql-proxy# description:  mysql-proxy is a proxy daemon for mysql# Source function  Library:  /etc/rc.d/init.d/functionsprog= "/usr/local/mysql-proxy/bin/mysql-proxy" # Source networking  configuration.if [ -f /etc/sysconfig/network ]; then    .  /etc/sysconfig/networkfi# check that networking is up. [ ${networking} =  "No"  ] && exit 0# Set default  Mysql-proxy configuration. Admin_user= "admin" admin_passwd= "admin" admin_lua_script= "/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua "Proxy_options="--daemon "proxy_pid=/var/run/mysql-proxy.pidproxy_user=" Mysql-proxy "# Source  mysql-proxy configuration.if [ -f /etc/sysconfig/mysql-proxy ]; then    .  /etc/sysconfig/mysql-proxyfiretval=0start ()  {    echo -n $ "Starting   $prog:  "    daemon  $prog   $PROXY _options --pid-file= $PROXY _pid  --proxy-address= "$PROXY _address"  --user= $PROXY _user --admin-username= "$ADMIN _user"  -- admin-lua-script= "$ADMIN _lua_script"  --admin-password= "$ADMIN _password"     retval=$?     echo    if [  $RETVAL  -eq 0 ]; then         touch /var/lock/subsys/mysql-proxy     fi}stop ()  {    echo -n $ "stopping  $prog: "      killproc -p  $PROXY _pid -d 3  $prog     RETVAL=$?     echo    if [  $RETVAL  -eq 0 ]; then         rm -f /var/lock/subsys/mysql-proxy        rm -f   $PROXY _pid    fi}# see how we were called.case  "$"  in    start)         start         ;;     stop)         stop         ;;     restart)         stop         start        ;;     condrestart|try-restart)         if status  -p  $PROXY _pidfile  $prog  >&/dev/null; then            stop             start        fi        ;;     status)         status -p  $PROXY _pid   $prog         ;;     *)         echo  "usage: $0 { Start|stop|restart|reload|status|condrestart|try-restart} "         retval=1        ;; esacexit  $RETVAL

(2) Save the above content as/etc/rc.d/init.d/mysql-proxy, give execution permission, and then add to the list of services.

# chmod +x/etc/rc.d/init.d/mysql-proxy# chkconfig--add mysql-proxy

(3) Provide the configuration file/etc/sysconfig/mysql-proxy for the service script, as follows:

# Options for Mysql-proxy admin_user= "admin" admin_password= "admin" admin_address= "admin_lua_script="/usr/local/ Mysql-proxy/share/doc/mysql-proxy/admin.lua "proxy_address=" "proxy_user=" Mysql-proxy "PROXY_OPTIONS="--daemon-- Log-level=info--log-use-syslog--plugins=proxy--plugins=admin--proxy-backend-addresses=10.10.10.3:3306-- proxy-read-only-backend-addresses=10.10.10.4:3306--proxy-lua-script=/usr/local/mysql-proxy/share/doc/ Mysql-proxy/rw-splitting.lua "

(4) Mysql-proxy configuration option annotations

--help--help-admin--help-proxy--help-all  ————   above four options are available for help information ;--proxy-address=host:port  ————   Agent service listening address and port;--admin-address=host:port  ————   Management module listening address and port ;--proxy-backend-addresses=host:port  ————   back end MySQL server address and port;--proxy-read-only-backend-addresses=host: port  ————   Backend read-only MySQL server address and Port;--proxy-lua-script=file_name  ————   LUA script to complete the MySQL proxy function;--daemon   ————   Start mysql-proxy;--keepalive  in daemon mode ————   Attempt to restart when Mysql-proxy crashes;--log-file=/path/to/log_ file_name  ————   Log file name;--log-level=level  ————   Log level;--log-use-syslog  ————   Log logging based on syslog;--plugins=plugin,..   ————   Plug-in;--user=user_name  loaded at mysql-proxy startup ————   The user running the mysql-proxy process;--defaults-file=/path/ to/conf_file_name  ————   The configuration file path that is used by default, and its configuration segment uses the [Mysql-proxy] identity;--proxy-skip-profiling  ————   Disable profile;--pid-file=/path/to/pid_file_name  ————   process file name; 

(5) Copy the following to create the Admin.lua file and save it to the/usr/local/mysql-proxy/share/doc/mysql-proxy/directory.

--[[ $%beginlicense%$ copyright  (c)  2007, 2012, oracle and/or its  affiliates. All rights reserved. This program is free software;  you can redistribute it and/or modify it under the terms  of the GNU General Public License as published by the  Free software foundation; version 2 of the license. this program  is distributed in the hope that it will be useful, but  WITHOUT ANY WARRANTY; without even the implied warranty of  Merchantability or fitness for a particular purpose. see the gnu  General Public License for more details. You should have  Received a coPy of the gnu general public license along with this program ;  if not, write to the free software foundation, inc., 51  franklin st, fifth floor, boston, ma 02110-1301  usa $% Endlicense%$ --]]function set_error (errmsg)  proxy.response = {type = proxy. mysqld_packet_err,errmsg = errmsg or  "Error"}endfunction read_query (PACKET) if  Packet:byte ()  ~= proxy.com_query thenset_error ("[admin] we only handle  text-based queries  (com_query) ") Return proxy. Proxy_send_resultendlocal query = packet:sub (2) local rows = { }local  Fields = { }if query:lower ()  ==  "Select * from backends"   thenfields = { { name =  "Backend_ndx",  &NBSp; type = proxy. mysql_type_long },{ name =  "Address",   type = proxy. mysql_type_string },{ name =  "state",   type = proxy. mysql_type_string },{ name =  "TYPE",   type = proxy. mysql_type_string },{ name =  "UUID",   type = proxy. mysql_type_string },{ name =  "Connected_clients",    type = proxy. mysql_type_long },}for i = 1,  #proxy. global.backends dolocal states =  {"Unknown", "Up", "Down"}local types = {"Unknown", "RW", "RO"}local b =  proxy.global.backends[i]rows[#rows  + 1] = {i,b.dst.name,           -- configured backend addressstates[b.state + 1],  -- the c-id is pushed down starting at 0types[b.type + 1],   -- the c-id is pushed  down starting at 0b.uuid,               -- the mysql server ' s uuid if it is  Managedb.connected_clients  -- currently connected clients}endelseif query:lower ()  ==  "Select * from help"  thenfields = { { name =   "Command",    type = proxy. mysql_type_string },{ name =  "description",    type = proxy. mysql_type_string },}rows[#rows  + 1] = {  "Select * from help",   "Shows this help"  }rows[#rows  + 1] = {  "Select * from  backends ", " Lists the backends and their state " }elseset_Error ("use  ' Select * from help '  to see the supported commands") Return proxy. Proxy_send_resultendproxy.response = {type = proxy. Mysqld_packet_ok,resultset = {fields = fields,rows = rows}}return proxy. Proxy_send_resultend

(6) test

#mysql-uadmin-padmin-h110.10.10.2--port=4041 mysql > SELECT * from Backends;

+-------------+-------------------+-------+------+------+-------------------+| backend_ndx |  address           | state | type |  uuid | connected_clients |+-------------+-------------------+-------+------+------+------ -------------+|           1 | 10.10.10.3:3306    | up    | rw   | NULL |                  0 | |            2 | 10.10.10.4:3306    | up    | ro   | NULL |                  0 | +-------------+--- ----------------+-------+------+------+-------------------+2 rows in set  (0.00 sec) 

This article is from the "Chun Blog" blog, please be sure to keep this source http://ssc4469.blog.51cto.com/6315913/1854011

Read and write separation of proxy based on MySQL master-slave synchronization