Tag Archives: MySQL

Using Tcpdump for MySQL query logging

If you are a privileged user (i.e. root), you can use tcpdump on a database server to take a peek into a network stream and filter for packets that go to MySQL. Those packets contain queries. Here’s a quick one-liner :
# tcpdump -i eth0 -s 0 -l -w - dst port 3306 | strings | perl -e '
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) {
if (defined $q) { print "$qn"; }
$q=$_;
} else {
$_ =~ s/^[ t]+//; $q.=" $_";
}
}'

Thanks to: http://www.mysqlperformanceblog.com/2008/11/07/poor-mans-query-logging/

MySQL: Migrating Slave to New Master

Consider the following scenario:
Master’s IP address has been changed and the slave needs to be reconfigured for the New IP address.

How do we go about doing it ? Two ways.
One of them a bit hackish but the most
simple:

– Shut down slave MySQL server.
– Find master.info file, store current one in the backup and edit it
replacing the host name with new host name.
– Start slave MySQL server.

Second one is more complex but using only SQL commands:

– Run SLAVE STOP to stop slave thread;
– Run SHOW SLAVE STATUS and record the position in the master binary log
slave is currently located
– use CHANGE MASTER TO to point slave to the same position on the
different hostname
– Use SLAVE START to start slave back again.

We cannot change the IP that easily. We need to do things so complex since MySQL assumes we are
replicating from new slave if you’re changing host name and resets all
other parameters.