mysql-proxy启动脚本:
LUA_PATH="/usr/local/mysql-proxy/share/mysql-proxy/?.lua" \
/usr/local/mysql-proxy/sbin/mysql-proxy \
--admin-address=127.0.0.1:3308 \
--proxy-address=0.0.0.0:3306 \
--proxy-backend-addresses=192.168.0.8:3307 \
--proxy-read-only-backend-addresses=192.168.0.9:3306 \
--proxy-lua-script=/usr/local/mysql-proxy/share/mysql-proxy/rw-splitting.lua \
--pid-file=/tmp/mysql-proxy.pid \
--daemon测试发现多连接会出现ERROR 1105 (07000): can't change DB to on slave 192.168.0.9:3306错误。
在mysql-shell里出现这个错误时,use dataname一下即不会报错。但是如果用工具的话,就无法正常使用了。[root@localhost ]# mysql -h xxx.xxx.xxx.xxx -u xxx -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.1.24-rc-logType 'help;' or 'h' for help. Type 'c' to clear the buffer.mysql> show databases;
ERROR 1105 (07000): can't change DB to on slave 192.168.0.9:3306
mysql> select count(*) from cjhjd.t_plan;
ERROR 1105 (07000): can't change DB to on slave 192.168.0.9:3306
mysql> use cjhjd;
Database changed
mysql> select count(*) from cjhjd.t_plan;
+----------+
| count(*) |
+----------+
| 1440 |
+----------+
1 row in set (1.10 sec)mysql> 在这里找到关于这个错误的有关解释,可是没有找到有解决方法方法,特请教,谢谢。
http://jan.kneschke.de/projects/ ... -more-r-w-splittingImprovements
keeping default-db in syncOne of the basic problems with rw-splitting is that each connection has a state, e.g. the default_db. If you switch to another backend you have to make sure that before we issue a SQL query, that also set the new default-db, if they are not in sync.[read_query]
current backend = 0
client default db = mysql
client username = root
query = select * from user
server default db: repl
client default db: mysql
syncronizingThe client-side did a USE mysql against the master and wanted to SELECT from the a slave afterwards. As the connection the slave was still using repl from the previous query we have apply the DB-change now. For achieve this we insert a USE mysql before sending the SELECT to the slave.In case the DB want to switch to doesn't exist on the slave, you will get an error like:ERROR 1000 (00S00): can't change DB 'norepl' to on slave ':3307'for the SELECT statement.
Stateful SQL StatementsNot all statements in MySQL are stateless and allow easy R/W splitting. Some of them need special support to make sure that still work: * SELECT SQL_CALC_FOUND_ROWS ... will lead to a SELECT FOUND_ROWS() which has to be executed on the same connection
* INSERTing into table with auto_increment fields might lead to a SELECT LAST_INSERT_ID() which has to be execute on the same master-connection.
* SHOW WARNINGS is similarThe solution is simple: don't give away the connection in that cases and don't send the SELECT FOUND_ROWS() to a slave. For sure there are some more statements which might not harmonize with r/w splitting. For example prepared statements. But that's another story.
LUA_PATH="/usr/local/mysql-proxy/share/mysql-proxy/?.lua" \
/usr/local/mysql-proxy/sbin/mysql-proxy \
--admin-address=127.0.0.1:3308 \
--proxy-address=0.0.0.0:3306 \
--proxy-backend-addresses=192.168.0.8:3307 \
--proxy-read-only-backend-addresses=192.168.0.9:3306 \
--proxy-lua-script=/usr/local/mysql-proxy/share/mysql-proxy/rw-splitting.lua \
--pid-file=/tmp/mysql-proxy.pid \
--daemon测试发现多连接会出现ERROR 1105 (07000): can't change DB to on slave 192.168.0.9:3306错误。
在mysql-shell里出现这个错误时,use dataname一下即不会报错。但是如果用工具的话,就无法正常使用了。[root@localhost ]# mysql -h xxx.xxx.xxx.xxx -u xxx -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.1.24-rc-logType 'help;' or 'h' for help. Type 'c' to clear the buffer.mysql> show databases;
ERROR 1105 (07000): can't change DB to on slave 192.168.0.9:3306
mysql> select count(*) from cjhjd.t_plan;
ERROR 1105 (07000): can't change DB to on slave 192.168.0.9:3306
mysql> use cjhjd;
Database changed
mysql> select count(*) from cjhjd.t_plan;
+----------+
| count(*) |
+----------+
| 1440 |
+----------+
1 row in set (1.10 sec)mysql> 在这里找到关于这个错误的有关解释,可是没有找到有解决方法方法,特请教,谢谢。
http://jan.kneschke.de/projects/ ... -more-r-w-splittingImprovements
keeping default-db in syncOne of the basic problems with rw-splitting is that each connection has a state, e.g. the default_db. If you switch to another backend you have to make sure that before we issue a SQL query, that also set the new default-db, if they are not in sync.[read_query]
current backend = 0
client default db = mysql
client username = root
query = select * from user
server default db: repl
client default db: mysql
syncronizingThe client-side did a USE mysql against the master and wanted to SELECT from the a slave afterwards. As the connection the slave was still using repl from the previous query we have apply the DB-change now. For achieve this we insert a USE mysql before sending the SELECT to the slave.In case the DB want to switch to doesn't exist on the slave, you will get an error like:ERROR 1000 (00S00): can't change DB 'norepl' to on slave ':3307'for the SELECT statement.
Stateful SQL StatementsNot all statements in MySQL are stateless and allow easy R/W splitting. Some of them need special support to make sure that still work: * SELECT SQL_CALC_FOUND_ROWS ... will lead to a SELECT FOUND_ROWS() which has to be executed on the same connection
* INSERTing into table with auto_increment fields might lead to a SELECT LAST_INSERT_ID() which has to be execute on the same master-connection.
* SHOW WARNINGS is similarThe solution is simple: don't give away the connection in that cases and don't send the SELECT FOUND_ROWS() to a slave. For sure there are some more statements which might not harmonize with r/w splitting. For example prepared statements. But that's another story.
解决方案 »
- 有个问题向高手请教,在线等
- MySQL中有没有类似@@ROWCOUNT的系统变量(存储过程)
- MYSQL 提供的C语言API不接受汉字,怎么办
- 求sql语句(mysql)
- mysql 中,Lost connection to server during query?
- mysql服务
- 求助mysql在多个表里查找同条件的内容
- VS2012连接数据库时更改数据源找不到mysql的数据源
- 问个简单的索引问题 在线等
- 如何利用触发器禁止删除表中的id为 1的数据
- mysql如何随机生成两位的整形的数值,请高手帮忙
- delete from password n where n.user_id in(select n.user_id from password n where n.uid='3584');
-- ignore the result of the USE <default_db>
-- 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.backends[proxy.connection.backend_ndx].address
} return proxy.PROXY_SEND_RESULT
end
end
return proxy.PROXY_IGNORE_RESULT
end