- 论坛徽章:
- 1
|
找到这个文章里写的内容:
http://jan.kneschke.de/projects/ ... -more-r-w-splitting
Improvements
keeping default-db in sync
One 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
syncronizing
The 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 Statements
Not 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 similar
The 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. |
|