mysql gid_mysql主从复制5.6基于GID及多线程的复制笔记
mysql:數(shù)據(jù)庫復(fù)制過濾
#show grobal variables like 'binlog%';
注意：不能在主服務(wù)器上做過濾，二進(jìn)制不完整性；它將危害到其他的數(shù)據(jù)庫；在從庫過濾會(huì)占用過多的帶寬,還會(huì)復(fù)制過多的跟數(shù)據(jù)庫無關(guān)的內(nèi)容
主端:
binlog-do-db:僅將指定數(shù)據(jù)庫的相關(guān)修改操作記為二進(jìn)制日志(白名單)
binlog-ignore-db
從端:
replicate-do-db= ? ?#僅復(fù)制指定數(shù)據(jù)庫的二進(jìn)制日志文件
replicate-ignore-db= #忽略那數(shù)據(jù)庫
replicate-do-table=
replicate-ignore-table=
replicate-wild-do-table=
replicate-wild-ignore-table=
在從庫中：
修改配置文件：vim /etc/my.cnf
replicate-do-db = discuz
#重啟mysql
mysql> show variables like 'rep%';#查看rep
mysql> show slave status\G #查看slave的狀態(tài)
從庫測試結(jié)果：
mysql> show databases;
+--------------------+
| Database ? ? ? ? ? |
+--------------------+
| information_schema |
| discuz
主庫：創(chuàng)建一個(gè)數(shù)據(jù)庫discuz；從庫同步成功看上面的結(jié)果：
2.1.1.mysql5.6
數(shù)據(jù)庫中有多個(gè)數(shù)據(jù)庫時(shí)，多線程復(fù)制才有意義；
MySQL-5.6: GTID
slave-parallel-workers=0
0: 表示禁用多線程功能；
MySQL 5.6引入的GTID(Global Transaction IDs)使得其復(fù)制功能的配置、監(jiān)控及管理變得更加易于實(shí)現(xiàn)，且更加健壯。
要在MySQL 5.6中使用復(fù)制功能，其服務(wù)配置段[mysqld]中于少應(yīng)該定義如下選項(xiàng)：
binlog-format：二進(jìn)制日志的格式，有row、statement和mixed幾種類型；
需要注意的是：當(dāng)設(shè)置隔離級別為READ-COMMITED必須設(shè)置二進(jìn)制日志格式為ROW，現(xiàn)在MySQL官方認(rèn)為STATEMENT這個(gè)已經(jīng)不再適合繼續(xù)使用；但mixed類型在默認(rèn)的事務(wù)隔離級別下，可能會(huì)導(dǎo)致主從數(shù)據(jù)不一致；
log-slave-updates、gtid-mode、enforce-gtid-consistency、report-port和report-host：用于啟動(dòng)GTID及滿足附屬的其它需求；
master-info-repository和relay-log-info-repository：啟用此兩項(xiàng)，可用于實(shí)現(xiàn)在崩潰時(shí)保證二進(jìn)制及從服務(wù)器安全的功能；
sync-master-info：啟用之可確保無信息丟失；
slave-paralles-workers：設(shè)定從服務(wù)器的SQL線程數(shù)；0表示關(guān)閉多線程復(fù)制功能；
binlog-checksum、master-verify-checksum和slave-sql-verify-checksum：啟用復(fù)制有關(guān)的所有校驗(yàn)功能；
binlog-rows-query-log-events：啟用之可用于在二進(jìn)制日志記錄事件相關(guān)的信息，可降低故障排除的復(fù)雜度；
log-bin：啟用二進(jìn)制日志，這是保證復(fù)制功能的基本前提；
server-id：同一個(gè)復(fù)制拓?fù)渲械乃蟹?wù)器的id號必須惟一；
一、簡單主從模式配置步驟
1、配置主從節(jié)點(diǎn)的服務(wù)配置文件
1.1、配置master節(jié)點(diǎn)：
[mysqld]
binlog-format=ROW
log-bin=master-bin
log-slave-updates=true
gtid-mode=on ? #開啟gtid功能
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=1
report-port=3306
port=3306
datadir=/mydata/data
socket=/tmp/mysql.sock
report-host=master.magedu.com
1.2、配置slave節(jié)點(diǎn)：
[mysqld]
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=11
report-port=3306
port=3306
log-bin=mysql-bin.log
datadir=/mydata/data
socket=/tmp/mysql.sock
report-host=slave.magedu.com
2、創(chuàng)建復(fù)制用戶
mysql> GRANT REPLICATION SLAVE ON *.* TO repluser@172.16.100.7 IDENTIFIED BY 'replpass';
說明：172.16.100.7是從節(jié)點(diǎn)服務(wù)器；如果想一次性授權(quán)更多的節(jié)點(diǎn)，可以自行根據(jù)需要修改；
3、為備節(jié)點(diǎn)提供初始數(shù)據(jù)集
鎖定主表，備份主節(jié)點(diǎn)上的數(shù)據(jù)，將其還原至從節(jié)點(diǎn)；如果沒有啟用GTID，在備份時(shí)需要在master上使用show master status命令查看二進(jìn)制日志文件名稱及事件位置，以便后面啟動(dòng)slave節(jié)點(diǎn)時(shí)使用。
4、啟動(dòng)從節(jié)點(diǎn)的復(fù)制線程
如果啟用了GTID功能，則使用如下命令：
mysql> CHANGE MASTER TO MASTER_HOST='master.magedu.com', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_AUTO_POSITION=1;
沒啟用GTID，需要使用如下命令：
slave> CHANGE MASTER TO MASTER_HOST='172.16.100.6',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='replpass',
-> MASTER_LOG_FILE='master-bin.000003',
-> MASTER_LOG_POS=1174;
二、半同步復(fù)制
1、分別在主從節(jié)點(diǎn)上安裝相關(guān)的插件
master> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
slave> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
2、啟用半同步復(fù)制
在master上的配置文件中，添加
rpl_semi_sync_master_enabled=ON
在至少一個(gè)slave節(jié)點(diǎn)的配置文件中添加
rpl_semi_sync_slave_enabled=ON
而后重新啟動(dòng)mysql服務(wù)即可生效。
或者，也可以mysql服務(wù)上動(dòng)態(tài)啟動(dòng)其相關(guān)功能：
master> SET GLOBAL rpl_semi_sync_master_enabled = ON;
slave> SET GLOBAL rpl_semi_sync_slave_enabled = ON;
slave> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;
3、確認(rèn)半同步功能已經(jīng)啟用
master> CREATE DATABASE magedudb;
master> SHOW STATUS LIKE 'Rpl_semi_sync_master_yes_tx';
slave> SHOW DATABASES;
三、MySQL Proxy
1、源碼安裝時(shí)，MySQL proxy的依賴關(guān)系：
libevent 1.x or higher (1.3b or later is preferred).
lua 5.1.x or higher.
glib2 2.6.0 or higher.
pkg-config.
libtool 1.5 or higher.
MySQL 5.0.x or higher developer files.
2、安裝
# tar zxf mysql-proxy-0.8.2.tar.gz
# cd mysql-proxy-0.8.2
# ./configure
# make
# make check
如果管理員有密碼，上面的步驟則需要使用如下格式進(jìn)行：
# MYSQL_PASSWORD=root_pwd make check
# make install
默認(rèn)情況下， mysql-proxy安裝在/usr/local/sbin/mysql-proxy，而Lua示例腳本安裝在/usr/local/share目錄中。
3、配置指令
mysql proxy的各配置參數(shù)請參見官方文檔，http://dev.mysql.com/doc/refman/5.6/en/mysql-proxy-configuration.html
使用rpm包在rhel6上安裝mysql-proxy-0.8.2，其會(huì)提供配置文件及服務(wù)腳本，但沒有提供讀寫分享的腳本。
/etc/sysconfig/mysql-proxy文件用于定義mysql-proxy的啟動(dòng)參數(shù)。
ADMIN_USER – the user for the proxy's admin interface. You can leave the default admin user.
ADMIN_PASSWORD – the password for the admin user in clear text. Change the default password for better security.
ADMIN_LUA_SCRIPT – the admin script in the Lua programming language. Without this script the admin interface cannot work. You can leave the default value.
PROXY_USER – the system user under which the proxy will work. By default it is mysql-proxy, and it's safe to leave it as is.
PROXY_OPTIONS – proxy options such as logging level, plugins, and Lua scripts to be loaded.
其中PROXY_OPTIONS是最常用的一個(gè)選項(xiàng)，用于定義mysql-proxy工作時(shí)的重要參數(shù)，例如：
PROXY_OPTIONS="--daemon --log-level=info --log-use-syslog --plugins=proxy --plugins=admin --proxy-backend-addresses=192.168.1.102:3306 --proxy-read-only-backend-addresses=192.168.1.105:3306 --proxy-lua-script=/usr/lib/mysql-proxy/lua/proxy/rw-splitting.lua"
四、安裝配置mysql-proxy:
4.1 下載所需要的版本，這里的系統(tǒng)平臺(tái)為rhel6.4 32位系統(tǒng)，因此就以mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit.tar.gz為例。
# tar xf mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit.tar.gz -C /usr/local
# cd /usr/local
# ln -sv mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit mysql-proxy
添加代理用戶
# useradd mysql-proxy
4.2 為mysql-proxy提供SysV服務(wù)腳本，內(nèi)容如下所示
#!/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/functions
prog="/usr/local/mysql-proxy/bin/mysql-proxy"
# Source networking configuration.
if [ -f /etc/sysconfig/network ]; then
. /etc/sysconfig/network
fi
# 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.pid
PROXY_USER="mysql-proxy"
# Source mysql-proxy configuration.
if [ -f /etc/sysconfig/mysql-proxy ]; then
. /etc/sysconfig/mysql-proxy
fi
RETVAL=0
start() {
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 "$1" 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
;;
esac
exit $RETVAL
將上述內(nèi)容保存為/etc/rc.d/init.d/mysql-proxy，給予執(zhí)行權(quán)限，而后加入到服務(wù)列表。
# chmod +x /etc/rc.d/init.d/mysql-proxy
# chkconfig --add mysql-proxy
4.3 為服務(wù)腳本提供配置文件/etc/sysconfig/mysql-proxy，內(nèi)容如下所示：
# 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"
其中最后一行，需要按實(shí)際場景進(jìn)行修改，例如：
PROXY_OPTIONS="--daemon --log-level=info --log-use-syslog --plugins=proxy --plugins=admin --proxy-backend-addresses=172.16.100.6:3306 --proxy-read-only-backend-addresses=172.16.100.7:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"
其中的proxy-backend-addresses選項(xiàng)和proxy-read-only-backend-addresses選項(xiàng)均可重復(fù)使用多次，以實(shí)現(xiàn)指定多個(gè)讀寫服務(wù)器或只讀服務(wù)器。
4.4 mysql-proxy的配置選項(xiàng)
mysql-proxy的配置選項(xiàng)大致可分為幫助選項(xiàng)、管理選項(xiàng)、代理選項(xiàng)及應(yīng)用程序選項(xiàng)幾類，下面一起去介紹它們。
--help
--help-admin
--help-proxy
--help-all ———— 以上四個(gè)選項(xiàng)均用于獲取幫助信息；
--proxy-address=host:port ———— 代理服務(wù)監(jiān)聽的地址和端口；
--admin-address=host:port ———— 管理模塊監(jiān)聽的地址和端口；
--proxy-backend-addresses=host:port ———— 后端mysql服務(wù)器的地址和端口；
--proxy-read-only-backend-addresses=host:port ———— 后端只讀mysql服務(wù)器的地址和端口；
--proxy-lua-script=file_name ———— 完成mysql代理功能的Lua腳本；
--daemon ———— 以守護(hù)進(jìn)程模式啟動(dòng)mysql-proxy；
--keepalive ———— 在mysql-proxy崩潰時(shí)嘗試重啟之；
--log-file=/path/to/log_file_name ———— 日志文件名稱；
--log-level=level ———— 日志級別；
--log-use-syslog ———— 基于syslog記錄日志；
--plugins=plugin,.. ———— 在mysql-proxy啟動(dòng)時(shí)加載的插件；
--user=user_name ———— 運(yùn)行mysql-proxy進(jìn)程的用戶；
--defaults-file=/path/to/conf_file_name ———— 默認(rèn)使用的配置文件路徑；其配置段使用[mysql-proxy]標(biāo)識(shí)；
--proxy-skip-profiling ———— 禁用profile；
--pid-file=/path/to/pid_file_name ———— 進(jìn)程文件名；
5、復(fù)制如下內(nèi)容建立admin.lua文件，將其保存至/usr/local/mysql-proxy/share/doc/mysql-proxy/目錄中。
--[[ $%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"
}
end
function read_query(packet)
if packet:byte() ~= proxy.COM_QUERY then
set_error("[admin] we only handle text-based queries (COM_QUERY)")
return proxy.PROXY_SEND_RESULT
end
local query = packet:sub(2)
local rows = { }
local fields = { }
if query:lower() == "select * from backends" then
fields = {
{ name = "backend_ndx",
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 do
local states = {
"unknown",
"up",
"down"
}
local types = {
"unknown",
"rw",
"ro"
}
local b = proxy.global.backends[i]
rows[#rows + 1] = {
i,
b.dst.name, -- configured backend address
states[b.state + 1], -- the C-id is pushed down starting at 0
types[b.type + 1], -- the C-id is pushed down starting at 0
b.uuid, -- the MySQL Server's UUID if it is managed
b.connected_clients -- currently connected clients
}
end
elseif query:lower() == "select * from help" then
fields = {
{ 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" }
else
set_error("use 'SELECT * FROM help' to see the supported commands")
return proxy.PROXY_SEND_RESULT
end
proxy.response = {
type = proxy.MYSQLD_PACKET_OK,
resultset = {
fields = fields,
rows = rows
}
}
return proxy.PROXY_SEND_RESULT
end
6、測試
6.1 管理功能測試
# mysql -uadmin -padmin -h172.16.100.107 --port=4041
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT * FROM backends;
+-------------+-------------------+-------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+-------------------+-------+------+------+-------------------+
| 1 | 172.16.100.6:3306 | up | rw | NULL | 0 |
| 2 | 172.16.100.7:3306 | up | ro | NULL | 0 |
+-------------+-------------------+-------+------+------+-------------------+
2 rows in set (0.00 sec)
6.2 讀寫分離測試
# mysql -uroot -pmagedu.com -h172.16.100.107 --port=4040
下面的讀寫分享腳本是由mysql-proxy-0.8.3提供了，將其復(fù)制保存為/usr/lib/mysql-proxy/lua/proxy/rw-splitting.lua，就可以啟動(dòng)服務(wù)了。
--[[ $%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%$ --]]
---
-- a flexible statement based load balancer with connection pooling
--
-- * build a connection pool of min_idle_connections for each backend and maintain
-- its size
-- *
--
local commands = require("proxy.commands")
local tokenizer = require("proxy.tokenizer")
local lb = require("proxy.balance")
local auto_config = require("proxy.auto-config")
--- config
--
-- connection pool
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 4,
max_idle_connections = 8,
is_debug = false
}
end
---
-- read/write splitting sends all non-transactional SELECTs to the slaves
--
-- is_in_transaction tracks the state of the transactions
local is_in_transaction = false
-- if this was a SELECT SQL_CALC_FOUND_ROWS ... stay on the same connections
local is_in_select_calc_found_rows = false
---
-- get a connection to a backend
--
-- as long as we don't have enough connections in the pool, create new connections
--
function connect_server()
local is_debug = proxy.global.config.rwsplit.is_debug
-- make sure that we connect to each backend at least ones to
-- keep the connections to the servers alive
--
-- on read_query we can switch the backends again to another backend
if is_debug then
print()
print("[connect_server] " .. proxy.connection.client.src.name)
end
local rw_ndx = 0
-- init all backends
for i = 1, #proxy.global.backends do
local s = proxy.global.backends[i]
local pool = s.pool -- we don't have a username yet, try to find a connections which is idling
local cur_idle = pool.users[""].cur_idle_connections
pool.min_idle_connections = proxy.global.config.rwsplit.min_idle_connections
pool.max_idle_connections = proxy.global.config.rwsplit.max_idle_connections
if is_debug then
print(" [".. i .."].connected_clients = " .. s.connected_clients)
print(" [".. i .."].pool.cur_idle = " .. cur_idle)
print(" [".. i .."].pool.max_idle = " .. pool.max_idle_connections)
print(" [".. i .."].pool.min_idle = " .. pool.min_idle_connections)
print(" [".. i .."].type = " .. s.type)
print(" [".. i .."].state = " .. s.state)
end
-- prefer connections to the master
if s.type == proxy.BACKEND_TYPE_RW and
s.state ~= proxy.BACKEND_STATE_DOWN and
cur_idle < pool.min_idle_connections then
proxy.connection.backend_ndx = i
break
elseif s.type == proxy.BACKEND_TYPE_RO and
s.state ~= proxy.BACKEND_STATE_DOWN and
cur_idle < pool.min_idle_connections then
proxy.connection.backend_ndx = i
break
elseif s.type == proxy.BACKEND_TYPE_RW and
s.state ~= proxy.BACKEND_STATE_DOWN and
rw_ndx == 0 then
rw_ndx = i
end
end
if proxy.connection.backend_ndx == 0 then
if is_debug then
print(" [" .. rw_ndx .. "] taking master as default")
end
proxy.connection.backend_ndx = rw_ndx
end
-- pick a random backend
--
-- we someone have to skip DOWN backends
-- ok, did we got a backend ?
if proxy.connection.server then
if is_debug then
print(" using pooled connection from: " .. proxy.connection.backend_ndx)
end
-- stay with it
return proxy.PROXY_IGNORE_RESULT
end
if is_debug then
print(" [" .. proxy.connection.backend_ndx .. "] idle-conns below min-idle")
end
-- open a new connection
end
---
-- put the successfully authed connection into the connection pool
--
-- @param auth the context information for the auth
--
-- auth.packet is the packet
function read_auth_result( auth )
if is_debug then
print("[read_auth_result] " .. proxy.connection.client.src.name)
end
if auth.packet:byte() == proxy.MYSQLD_PACKET_OK then
-- auth was fine, disconnect from the server
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
print("(read_auth_result) ... not ok yet");
elseif auth.packet:byte() == proxy.MYSQLD_PACKET_ERR then
-- auth failed
end
end
---
-- read/write splitting
function read_query( packet )
local is_debug = proxy.global.config.rwsplit.is_debug
local cmd = commands.parse(packet)
local c = proxy.connection.client
local r = auto_config.handle(cmd)
if r then return r end
local tokens
local norm_query
-- looks like we have to forward this statement to a backend
if is_debug then
print("[read_query] " .. proxy.connection.client.src.name)
print(" current backend = " .. proxy.connection.backend_ndx)
print(" client default db = " .. c.default_db)
print(" client username = " .. c.username)
if cmd.type == proxy.COM_QUERY then
print(" query = " .. cmd.query)
end
end
if cmd.type == proxy.COM_QUIT then
-- don't send COM_QUIT to the backend. We manage the connection
-- in all aspects.
proxy.response = {
type = proxy.MYSQLD_PACKET_OK,
}
if is_debug then
print(" (QUIT) current backend = " .. proxy.connection.backend_ndx)
end
return proxy.PROXY_SEND_RESULT
end
-- COM_BINLOG_DUMP packet can't be balanced
--
-- so we must send it always to the master
if cmd.type == proxy.COM_BINLOG_DUMP then
-- if we don't have a backend selected, let's pick the master
--
if proxy.connection.backend_ndx == 0 then
proxy.connection.backend_ndx = lb.idle_failsafe_rw()
end
return
end
proxy.queries:append(1, packet, { resultset_is_needed = true })
-- read/write splitting
--
-- send all non-transactional SELECTs to a slave
if not is_in_transaction and
cmd.type == proxy.COM_QUERY then
tokens = tokens or assert(tokenizer.tokenize(cmd.query))
local stmt = tokenizer.first_stmt_token(tokens)
if stmt.token_name == "TK_SQL_SELECT" then
is_in_select_calc_found_rows = false
local is_insert_id = false
for i = 1, #tokens do
local token = tokens[i]
-- SQL_CALC_FOUND_ROWS + FOUND_ROWS() have to be executed
-- on the same connection
-- print("token: " .. token.token_name)
-- print(" val: " .. token.text)
if not is_in_select_calc_found_rows and token.token_name == "TK_SQL_SQL_CALC_FOUND_ROWS" then
is_in_select_calc_found_rows = true
elseif not is_insert_id and token.token_name == "TK_LITERAL" then
local utext = token.text:upper()
if utext == "LAST_INSERT_ID" or
utext == "@@INSERT_ID" then
is_insert_id = true
end
end
-- we found the two special token, we can't find more
if is_insert_id and is_in_select_calc_found_rows then
break
end
end
-- if we ask for the last-insert-id we have to ask it on the original
-- connection
if not is_insert_id then
local backend_ndx = lb.idle_ro()
if backend_ndx > 0 then
proxy.connection.backend_ndx = backend_ndx
end
else
print(" found a SELECT LAST_INSERT_ID(), staying on the same backend")
end
end
end
-- no backend selected yet, pick a master
if proxy.connection.backend_ndx == 0 then
-- we don't have a backend right now
--
-- let's pick a master as a good default
--
proxy.connection.backend_ndx = lb.idle_failsafe_rw()
end
-- by now we should have a backend
--
-- in case the master is down, we have to close the client connections
-- otherwise we can go on
if proxy.connection.backend_ndx == 0 then
return proxy.PROXY_SEND_QUERY
end
local s = proxy.connection.server
-- if client and server db don't match, adjust the server-side
--
-- skip it if we send a INIT_DB anyway
if cmd.type ~= proxy.COM_INIT_DB and
c.default_db and c.default_db ~= s.default_db then
print(" server default db: " .. s.default_db)
print(" client default db: " .. c.default_db)
print(" syncronizing")
proxy.queries:prepend(2, string.char(proxy.COM_INIT_DB) .. c.default_db, { resultset_is_needed = true })
end
-- send to master
if is_debug then
if proxy.connection.backend_ndx > 0 then
local b = proxy.global.backends[proxy.connection.backend_ndx]
print(" sending to backend : " .. b.dst.name);
print(" is_slave : " .. tostring(b.type == proxy.BACKEND_TYPE_RO));
print(" server default db: " .. s.default_db)
print(" server username : " .. s.username)
end
print(" in_trans : " .. tostring(is_in_transaction))
print(" in_calc_found : " .. tostring(is_in_select_calc_found_rows))
print(" COM_QUERY : " .. tostring(cmd.type == proxy.COM_QUERY))
end
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 is_debug = proxy.global.config.rwsplit.is_debug
local res = assert(inj.resultset)
local flags = res.flags
if inj.id ~= 1 then
-- ignore the result of the USE -- the DB might not exist on the backend, what do do ?
--
if inj.id == 2 then
-- the injected INIT_DB failed as the slave doesn't have this DB
-- or doesn't have permissions to read from it
if res.query_status == proxy.MYSQLD_PACKET_ERR then
proxy.queries:reset()
proxy.response = {
type = proxy.MYSQLD_PACKET_ERR,
errmsg = "can't change DB ".. proxy.connection.client.default_db ..
" to on slave " .. proxy.global.backends[proxy.connection.backend_ndx].dst.name
}
return proxy.PROXY_SEND_RESULT
end
end
return proxy.PROXY_IGNORE_RESULT
end
is_in_transaction = flags.in_trans
local have_last_insert_id = (res.insert_id and (res.insert_id > 0))
if not is_in_transaction and
not is_in_select_calc_found_rows and
not have_last_insert_id then
-- release the backend
proxy.connection.backend_ndx = 0
elseif is_debug then
print("(read_query_result) staying on the same backend")
print(" in_trans : " .. tostring(is_in_transaction))
print(" in_calc_found : " .. tostring(is_in_select_calc_found_rows))
print(" have_insert_id : " .. tostring(have_last_insert_id))
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()
local is_debug = proxy.global.config.rwsplit.is_debug
if is_debug then
print("[disconnect_client] " .. proxy.connection.client.src.name)
end
-- make sure we are disconnection from the connection
-- to move the connection into the pool
proxy.connection.backend_ndx = 0
end
閱讀(3406) | 評論(0) | 轉(zhuǎn)發(fā)(1) |
總結(jié)
以上是生活随笔為你收集整理的mysql gid_mysql主从复制5.6基于GID及多线程的复制笔记的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： k倍区间
- 下一篇： SpringBoot学习笔记(9)---