mysql主从单向同步＋mysql-proxy数据库读写分离

mysql主从单向同步
-
主服务器（master）A：
vim /etc/mysql/my.cnf
-
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog-do-db=proxy
注：binlog-do-db提供数据同步服务的数据库
#忽略的数据库复制
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
-
从服务器(slave)B：
vim /etc/mysql/my.cnf
-
server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log
master-host=192.168.1.80
replicate-do-db=proxy
master-user=backup
master-password=123456
master-connect-retry=10
注：replicate-do-db同步的数据库
-
配置好了配置文件还要执行以下命令搭建起桥梁。
-
主服务器的日志表名字查询:
mysql> show master status;
+------------------+----------+--------------+--------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         |
+------------------+----------+--------------+--------------------------+
| mysql-bin.000030 |       98 | proxy        | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
1 row in set (0.00 sec)
-
从服务器:
mysql>slave stop;
mysql>CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000030',MASTER_LOG_POS=98;
mysql>slave start;
mysql>show slave status\G;
-
如果看到下面的，就说明已经可以数据库同步了。
-
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
-
如果slave start; 不了，用
change master to master_host='192.168.1.80',master_user='backup',
master_password='123456',master_log_file='mysql2-bin.000001' ,master_log_pos=98;

mysql-proxy数据库读写分离

创建mysql-proxy登录用户
mysql>GRANT all privileges ON *.* TO [email=[ft=,2,]backup@'Apache']backup@'Apache'[/email] IDENTIFIED BY '123456';
mysql>flush privileges;
-
远程登录mysql-proxy：
mysql>mysql -h 192.168.1.201 -P 4040 -ubackup -p
-
查看用户是否有登录权限：
mysql> use mysql;
Database changed
mysql> select host,user from user;
-
安装mysql-proxy：
apt-get install mysql-proxy
-
查找mysql-proxy安装文件路径：
-
find / -name mysql-proxy
如：
#find / -name mysql-proxy
/usr/sbin/mysql-proxy
/usr/share/doc/mysql-proxy
/usr/share/mysql-proxy
-
lua文件设置：
# find / -name rw-splitting.lua
/usr/share/mysql-proxy/rw-splitting.lua
-
注意：官方的 rw-splitting.lua 是限制了 4个以下的连接读写分离是不起作用的。多余四个连接才会 启到读写分离的作用!
-
启动mysql-proxy
-
/usr/sbin/mysql-proxy --proxy-read-only-backend-addresses=192.168.1.201:3306 --proxy-backend-addresses=192.168.1.80:3306 --proxy-lua-script=/usr/share/mysql-proxy/rw-splitting.lua &
-
读写测试: (读写分离测试的时候请不要先弄主从同步，这样看不出效果)
mysql>create database proxy;
mysql>use proxy;
create table proxy(id int(5),name
char(10));
mysql>insert into proxy(name) values("10");
mysql>select * from proxy;

图：



rw-splitting.lua 修改版,这样4个以下连接也会读写分离.(用的时候最好去掉里面的中文!)
vim /usr/share/mysql-proxy/rw-splitting.lua
-
--
-- version 0.01
-- SYNOPSIS:
---  1.维护了一个连接池
---  2.读写分离，简单的将select开头的语句放到slave上执行
---  3.事务支持，所有事务放到master上执行，事务中不更改连接
---  4.简单日志
--
--- config vars
local min_idle_connections = 4
local max_idle_connections = 8
local log_level=1
local encoding="utf8"
--- end of config
-
-- 事务标识，在事务内不归还连接
local transaction_flags={}
setmetatable(transaction_flags,{__index=function() return 0 end})
-- log system
log={
   level={debug=1,info=2,warn=3,error=4},
   funcs={"debug","info","warn","error"},
}
local log_file=io.open("/var/log/mysql-proxy.log","w")
function log.log(level,m)
   if level >= log_level then
      local msg="[" .. os.date("%Y-%m-%d %X") .."] ".. log.funcs[level] .. ": " .. tostring(m)
log_file:write(msg)
  log_file:flush()
   end
end
for i,v in ipairs(log.funcs) do
   log[v]=function(m) log.log(log.level[v],m) end
end
-- connect to server
function connect_server()
   log.info(" starting connect_server ... ")
   local least_idle_conns_ndx = 0
   local least_idle_conns = 0
  
   for i = 1, #proxy.backends do
      local s = proxy.backends
      local pool = s.pool
      local cur_idle = pool.users[""].cur_idle_connections
      log.debug("[".. s.address .."].connected_clients = " .. s.connected_clients)
      log.debug("[".. s.address .."].idling_connections = " .. cur_idle)
      log.debug("[".. s.address .."].type = " .. s.type)
      log.debug("[".. s.address .."].state = " .. s.state)
      if s.state ~= proxy.BACKEND_STATE_DOWN then
         -- try to connect to each backend once at least
         if cur_idle == 0 then
            proxy.connection.backend_ndx = i
            log.info("server [".. proxy.backends.address .."] open new connection")
            return
         end
         -- try to open at least min_idle_connections
         if least_idle_conns_ndx == 0 or
            ( cur_idle < min_idle_connections and
              cur_idle < least_idle_conns ) then
            least_idle_conns_ndx = i
            least_idle_conns = cur_idle
         end
      end
   end
   if least_idle_conns_ndx > 0 then
      proxy.connection.backend_ndx = least_idle_conns_ndx
   end
  
   if proxy.connection.backend_ndx > 0 then
      local s = proxy.backends[proxy.connection.backend_ndx]
      local pool = s.pool
      local cur_idle = pool.users[""].cur_idle_connections
      if cur_idle >= min_idle_connections then
         -- we have 4 idling connections in the pool, that's good enough
         log.debug("using pooled connection from: " .. proxy.connection.backend_ndx)
         return proxy.PROXY_IGNORE_RESULT
      end
   end
   -- open a new connection
   log.info("opening new connection on: " .. proxy.backends[proxy.connection.backend_ndx].address)
end
---
-- auth.packet is the packet
function read_auth_result( auth )
   if auth.packet:byte() == proxy.MYSQLD_PACKET_OK then
      -- 连接正常
      proxy.connection.backend_ndx = 0
   elseif auth.packet:byte() == proxy.MYSQLD_PACKET_EOF then
      -- we received either a
      -- * MYSQLD_PACKET_ERR and the auth failed or
      -- * MYSQLD_PACKET_EOF which means a OLD PASSWORD (4.0) was sent
      log.error("(read_auth_result) ... not ok yet");
   elseif auth.packet:byte() == proxy.MYSQLD_PACKET_ERR then
      log.error("auth failed!")
   end
end
-
---
-- read/write splitting
function read_query( packet )
   log.debug("[read_query]")
   log.debug("authed backend = " .. proxy.connection.backend_ndx)
   log.debug("used db = " .. proxy.connection.client.default_db)
   if packet:byte() == proxy.COM_QUIT then
      proxy.response = {
         type = proxy.MYSQLD_PACKET_OK,
      }
      return proxy.PROXY_SEND_RESULT
   end
   if proxy.connection.backend_ndx == 0 then
      local is_read=(string.upper(packet:sub(2))):match("^SELECT")
      local target_type=proxy.BACKEND_TYPE_RW
      if is_read then target_type=proxy.BACKEND_TYPE_RO end
      for i = 1, #proxy.backends do
         local s = proxy.backends
         local pool = s.pool
         local cur_idle = pool.users[proxy.connection.client.username].cur_idle_connections
        
         if cur_idle > 0 and
            s.state ~= proxy.BACKEND_STATE_DOWN and
            s.type == target_type then
            proxy.connection.backend_ndx = i
            break
         end
      end
   end
   proxy.queries:reset()
   -- sync the client-side default_db with the server-side default_db
   if proxy.connection.server and proxy.connection.client.default_db ~= proxy.connection.server.default_db then
      local server_db=proxy.connection.server.default_db
      local client_db=proxy.connection.client.default_db
      local default_db= (#client_db > 0) and client_db or server_db
      if #default_db > 0 then
         proxy.queries:append(2, string.char(proxy.COM_INIT_DB) .. default_db)
         proxy.queries:append(2, string.char(proxy.COM_QUERY) .. "set names '" .. encoding .."'")
         log.info("change database to " .. default_db);
      end
   end
   if proxy.connection.backend_ndx > 0 then
      log.debug("Query[" .. packet:sub(2) .. "] Target is [" .. proxy.backends[proxy.connection.backend_ndx].address .."]")
   end
   proxy.queries:append(1, packet)
   return proxy.PROXY_SEND_QUERY
end
---
-- as long as we are in a transaction keep the connection
-- otherwise release it so another client can use it
function read_query_result( inj )
   local res      = assert(inj.resultset)
   local flags    = res.flags
   if inj.id ~= 1 then
      -- ignore the result of the USE
      return proxy.PROXY_IGNORE_RESULT
   end
   is_in_transaction = flags.in_trans
   if flags.in_trans then
      transaction_flags[proxy.connection.server.thread_id] = transaction_flags[proxy.connection.server.thread_id] + 1
   elseif inj.query:sub(2):lower():match("^%s*commit%s*$") or inj.query:sub(2):lower():match("^%s*rollback%s*$") then
      transaction_flags[proxy.connection.server.thread_id] = transaction_flags[proxy.connection.server.thread_id] - 1
      if transaction_flags[proxy.connection.server.thread_id] < 0 then transaction_flags[proxy.connection.server.thread_id] = 0 end
   end
  
   log.debug("transaction res : " .. tostring(transaction_flags[proxy.connection.server.thread_id]));
   if transaction_flags[proxy.connection.server.thread_id]==0 or transaction_flags[proxy.connection.server.thread_id] == nil then
      -- isnot in a transaction, need to release the backend
      proxy.connection.backend_ndx = 0
   end
end
---
-- close the connections if we have enough connections in the pool
--
-- @return nil - close connection
-- IGNORE_RESULT - store connection in the pool
function disconnect_client()
   log.debug("[disconnect_client]")
   if proxy.connection.backend_ndx == 0 then
      for i = 1, #proxy.backends do
         local s = proxy.backends
         local pool = s.pool
         local cur_idle = pool.users[proxy.connection.client.username].cur_idle_connections
        
         if s.state ~= proxy.BACKEND_STATE_DOWN and
            cur_idle > max_idle_connections then
            -- try to disconnect a backend
            proxy.connection.backend_ndx = i
            log.info("[".. proxy.backends.address .."] closing connection, idling: " .. cur_idle)
            return
         end
      end
      return proxy.PROXY_IGNORE_RESULT
   end
end