How to validate selects / inserts are hitting the right server with MySQL Master/Slave

Question

I've got a rails app using the master_slave_adapter plugin (http://github.com/mauricio/master_slave_adapter/tree/master) to send all selects to a slave, and all other statements to the master. Replication is setup using Mysql master / slave. I'm trying to validate that all the SQL statements are indeed going to the right place. Selects to the slave (db2), inserts to the master (db1) but I'm not sure how to do it. I've tried using tcpdump on the webservers:

sudo /usr/sbin/tcpdump -q -i eth0 dst port 3306

and this is the output for a page request with a ton of selects:

10:32:36.570930 IP web2.mydomain.com.57524 > db1.mydomain.com.mysql: tcp 0
10:32:36.576805 IP web2.mydomain.com.57524 > db1.mydomain.com.mysql: tcp 0
10:32:36.577201 IP web2.mydomain.com.57524 > db1.mydomain.com.mysql: tcp 0
10:32:36.577980 IP web2.mydomain.com.57524 > db1.mydomain.com.mysql: tcp 86
10:32:36.578186 IP web2.mydomain.com.57524 > db1.mydomain.com.mysql: tcp 21
10:32:36.578359 IP web2.mydomain.com.57524 > db1.mydomain.com.mysql: tcp 27
10:32:36.578522 IP web2.mydomain.com.57524 > db1.mydomain.com.mysql: tcp 5
10:32:36.578741 IP web2.mydomain.com.57524 > db1.mydomain.com.mysql: tcp 13
10:32:36.579611 IP web2.mydomain.com.57524 > db1.mydomain.com.mysql: tcp 29
10:32:36.588201 IP web2.mydomain.com.45978 > db2.mydomain.com.mysql: tcp 0
10:32:36.588323 IP web2.mydomain.com.45978 > db2.mydomain.com.mysql: tcp 0
10:32:36.588677 IP web2.mydomain.com.45978 > db2.mydomain.com.mysql: tcp 0
10:32:36.588784 IP web2.mydomain.com.45978 > db2.mydomain.com.mysql: tcp 86

It doesn't look like all the selects are going to the slave. Maybe this isn't the right way to test, anyone know a better way?

Answer 1

either turn on query logging on both servers, or just look at the statistics. mysql has a set of global counters that keep track of how many of each kind of statement are executed.

Answer 2

Make a special user both on master and slave and grant him different privileges. Disable SELECT on master and enable only SELECT on slave. Then if something goes wrong, there will be a MySQL error.