如何使用 MySQL Master/Slave 验证选择/插入是否命中正确的服务器
我有一个使用 master_slave_adapter 插件的 Rails 应用程序 (http://github.com/mauricio/ master_slave_adapter/tree/master) 将所有选择发送到从属设备，并将所有其他语句发送到主设备。复制是使用 Mysql 主/从设置的。我正在尝试验证所有 SQL 语句确实都到达了正确的位置。选择从站（db2），插入主站（db1），但我不知道该怎么做。我尝试在网络服务器上使用 tcpdump：
sudo /usr/sbin/tcpdump -q -i eth0 dst port 3306
这是带有大量选择的页面请求的输出：
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
看起来并不是所有的选择都会发送到从属服务器。也许这不是正确的测试方法，有人知道更好的方法吗？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在master和slave上创建一个特殊用户并授予他不同的权限。在主设备上禁用 SELECT，仅在从设备上启用 SELECT。那么如果出现问题，就会出现MySQL错误。
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.
要么在两台服务器上打开查询日志记录，要么只查看统计信息。 mysql 有一组全局计数器，用于跟踪每种语句的执行数量。
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.