Ignore database tables when replicating MySQL databases

After my previous post about duplicate entries in on my slave server (http://michielonline.nl/?q=mysql_drupal_duplicate_entry) I have decided I wanted to fix this because this error appeared almos every couple of weeks :-(

So after a bit of research I decided to remove the temporary tables which causes all these messages.

The cache, watchdog and accesslog tables are not necessary to display the site without any errors. These tables are always causing this messages.

Apache does a lot of accesslogging and the cache tables are only for fast responses etc, so they can be missed in a disaster situation.

Note: I am sure there are better ways to fix this, but this is my choice.

 

So let's get rid of the replication of these tables:

On the slave, edit the my.cnf config:

Add these lines: (note the wildcards!)

replicate-wild-ignore-table = drupal_db.%cache%
replicate-wild-ignore-table = drupal_db.%watchdog%
replicate-wild-ignore-table = drupal_db.%accesslog%

Restart MySQL/MariaDB:

systemctl restart mysql

Login to the MySQL console on the slave server and enter:

MariaDB [(none)]> show slave status\G

Now watch the line:

  Replicate_Wild_Ignore_Table: drupal_db.%cache%,drupal_db.%watchdog%,drupal_db.%accesslog%

Now the slave replication ignores all cache, watchdog and accesslog tables.

 

Of course, the master server still logs these tables in de binlog, so if necessary they can be used for "a moment in time" restore if the database goes corrupt.