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