After I have done a research on the web I have found that the best method is by using LVM. We will need to lock (temporarily) the tables, flush the changes to the disc, do a LVM snapshot of the directory that contains the MySQL data and unlock the tables again.
For this to work very quickly I’m presuming that the queries on the system are optimized to take a very short time to finish (< 0.001 s). The MySQL DB should have a LVM partition of its own.
There is also a GNU tool that does this: mylvmbackup (http://www.lenzg.net/mylvmbackup). We can configure it by /etc/mylvmbackup.conf or run it directly.
Sample configuration of “mylvmbackup”:
[mysql]
user=SQLBackup
password=xxxx
host=localhost
port=3306 #default install of MySQL
socket= /var/lib/mysql/mysql.sock #I’m presuming that we are using a default install and that we are using it for a local backup. For a remote backup this line will be ignored.
mycnf=/etc/my.cnf
[lvm]
vgname=mysql #the volume group name of the MySQL install
lvname=data #the logical volume name of the MySQL install
backuplv=bkpsql #the logical volume name of the MySQL backup
lvsize=10G #should be enough for our DB – we need to include all the undoes
We also need a user with some sets of permissions for the user that will backup the database.
Here you will find the permissions needed by this user:
http://code.openark.org/blog/mysql/mylvmbackup-howto-minimal-privileges-filesystem-copy
Now we can run mylvmbackup and he will do the backup.
Hope this was helpful…
Mihai out