Home / server / Questions / 640201
In Process
mc0e
Asked: 2014-10-29 02:18:09 +0800 CST

Can I do zero downtime MySQL replication if I have archive tables present?

  • 772

I've read the question and answers at How to setup MySQL replication with minimal downtime. It looks like I can get a slave DB into sync without down time if I am using only Innodb tables.

My primary tables use the Innodb engine, but I also have a set of tables that records the history of the primary tables. A set of triggers intercept updates, and take a copy of the old data before it gets modified, along with a time-stamp. This data is stored in archive tables, which are only ever appended to.

So can I use a similar technique to get replication going without downtime?

I don't mind if the archive tables are temporarily out of sync, and can live with it if they are not a perfect record of changes that happened during the sync process. My first concern is that the availability of the innodb tables should have little or no interruption.

The number of tables involved is quite large, and somewhat dynamic. Solutions that don't involve manually making lists of tables would be much better.

I'm moving from mariadb 5.5. I'm currently planning moving to the same version on a new server, and then treat that update separately, but if upgrading the target version would make a significant difference, please say so.

mysql

1 Answers

  1. You can try using percona xtradb cluster , but have in mind that you may need some code changes to handle COMMIT failures, as explained here:

    All queries are executed locally on the node, and there is a special handling only on COMMIT. When the COMMIT is issued, the transaction has to pass certification on all the nodes. If it does not pass, you will receive ERROR as a response on that query. After that, transaction is applied on the local node.

    • 0