How to filter queries from Mysql Replication

So this morning we faced a slave issue (duplicate entry) due to bin log corruption (slave machine unexpectedly died and on reboot had a corrupt table that was causing the duplicate entry error on replicated insert statements). After trying everything to resolve the issue, we ended up with deciding to scrap the slave table and copy the table new from Master. In order to do that we still needed to solve this slave issue and get the slave going and some later time do the master to slave copy. We decided to empty the current slave table and let the slave run however the issue was that this Master - Slave setup is actually a Master <-> Master setup and delete statement wud be replicated to master and we didn't want that. Ideally we wanted the delete statement in this case to not replicate. We achieved this by setting the 'sql_log_bin' session variable to 0 before running the delete statement. Since this is a session based variable it only affects the current session (we tested to make sure. see below). So the steps were

- open a new mysql session with a user that has SUPER privileges
- confirm sql_log_bin is set to 1 using comment "select @@sql_log_bin;"
- set the sql_log_bin to 0 using command "set sql_log_bin=0;"
- confirm variable is correctly set using command "select @@sql_log_bin;"
- run the statements that you don't want replicated
- Quit the mysql client session

Testing to ensure its really session based :)

############## Session 1 #############################

mysql> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)

############## Session 2 #############################

mysql> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)

Setting up passwordless access on CentOs 5

This has been detailed in various places however the part about setting the right permissions on the .ssh entries made it work for. Here's a step by step procedure


- Generate the keys
ssh-keygen -t rsa -P ""

- copy the key to authorized keys
cat $HOME/.ssh/id_rsa.pub >> $HOME/.ssh/authorized_keys

- ensure only the user has read/write/execute permission on the .ssh dir
chmod 700 $HOME/.ssh

- ensure only the user has read/write permission on authorized keys file
chmod 600 $HOME/.ssh/authorized_keys