This article describes how to back up the databases MySQL, without interrupting the MySQL service. Usually, when you want to back up MySQL, you have to stop MySQL or solve a problem blocking tables MySQL, to create a valid backup, if you do not, you can get an incorrect backup. To get a correct backup without interrupting MySQL, I use the following trick: I'm replicating my MySQL database on second MySQL server, and on the second MySQL server I use a cron job to create a regular backup of the replicated database. This document is provided without any – any kind! I want to say that this is not the only way to create such a system. There are many ways to achieve this goal, but I chose this one. I do not give any guarantee that this will work for you! Preliminary notes to follow this decision, you need a second server MySQL (slave), and you must install the MySQL replication on your first MySQL server (a system with which you wish to use copies, master) to the slave, for example as described in this article:.
Installing MySQL replication is beyond the scope of this document. All the settings I describe here, should be made on the slave MySQL server! I tested this on a Debian system, this should work on other distributions, but it is quite possible that some way will be different (in a script / Usr / local / sbin / mysqlbackup.sh). Implementation of automated backup replicated database on the slave server Once you have created a working MySQL replication from master to slave, I assume that you want to do automatic backup of your database driven directory / home / sqlbackup. First, you must create this directory: mkdir / home / sqlbackup Next we create a shell script / usr / local / sbin / mysqlbackup.sh which will stop the slave database make an sql dump of all MySQL database in / home / sqlbackup (SQL dump file name will look like this: backup-20070423-18.sql; this dump received April 23, 2007, at 18.00h), and then restart the slave database (led by db catch up everything that happened on the main database so