How can I skip all errors on an RDS replica instance?

Question

I use certain my.cnf settings like this. Does RDS instance allow such options?

slave-skip-errors = 1062,1054

replicate-ignore-db=verv_raw   

replicate-ignore-table=verv.ox_session

replicate-wild-ignore-table=verv_raw.ox%

replicate-wild-ignore-table=verv_raw.ox%

I am aware of the procedure that skips one error at a time.

CALL mysql.rds_skip_repl_error;

But what I am looking for is an option to skip all errors on slave. Is it possible in RDS environment?

Answer 1

I solved it by creating a mysql event scheduler like this :

CREATE EVENT repl_error_skipper 
ON SCHEDULE 
EVERY 15 MINUTE
COMMENT 'Calling rds_skip_repl_error to skip replication error'
Do
CALL mysql.rds_skip_repl_error;
/*also you can add other logic */

To set other global variables you can find and set those (if available for changing) in rds parameter group (you will have to create new parameter group and set the variable values).

Answer 2

As mentioned, this command only skips one replication error. I wrote a PHP script to loop through this and ran it once a minute via cron job (my replica was log jammed with a series of thousands of bad queries than went through on the main DB)

for($i = 1; $i <= 30; $i++) {
        $db = new mysqli('someserver.rds.amazonaws.com', 'root', 'password');
        $res = $db->query('CALL mysql.rds_skip_repl_error;');
        if(!$res) {
                //echo 'Query failed: ' . $db->error . "\n";
                return;
        }
        //var_dump($res->fetch_assoc());
        $db->close();
        sleep(1);
}

You'll need to tinker with this some (not every server would tolerate only one second between calls and 30 calls per minute), but it does work (albeit in a brute force manner). You must create a new DB connection every time to run this. The loop opens, runs and then closes the connection.