Confusion regarding linux/mysql replication and quiescense

[msidnam]

I've been searching the forums and vmware's site but i cant seem to find a solid answer.

We currently have an ubuntu 64 bit server with mySQL, apache (liferay), tomcat and a few other applications.

From what i've read, when replicating linux with mySQL its recommened to have the vmware tools installed and enable the vmware tools quiescense so that it runs the pre and post scripts. I've read the whitepaper Hot backup of MySQL on a Linux VM. The paper suggests two ways for the scripts. a stop and restart of the mysql service or a dump of the databases.

the server will be up 24/7 and will be a client portal, so stopping the database is out of the question. The sql dump isnt as bad but we would still need someone to maintain and remove the dumps and ensure that there is enough hard drive space available for the dumps.

My questions is, if we keep the vmware tools quiescense enabled but dont have a pre or post script what will happen? I see references to a crashed state sever but im not exactly sure what that will do.

We do have an app called monit on this machine and we were trying to use that to stop all services and then restart. But sometimes it doesnt restart the http services. we are also having issues with liferay starting back up when these services are stopped and then started. I'm not sure what user envokes the pre and post scripts either so im not sure if its a permissions issue.

When backing up linux with mysql, are the only two options above it?

[tsightler]

No, these are definitely not the only way, you have many options at your disposal. The simplest form of snapshot backup for MySQL is to write a pre-freeze and post-thaw script that doesn't the following:

1. Pre-freeze executes: FLUSH TABLES WITH READ LOCK
2. After this Veeam takes the snapshot
3. Post-thaw executes: UNLOCK TABLES

Important: I don't think this works if you happen to be using InnoDB databases.

So basically, this effectively puts the database into read only mode, takes the snapshot, and then unlocks the tables to return it to read-write.

These's a great presentation at http://forge.mysql.com/w/images/c/c1/My ... -02-26.pdf that should give you more detail and some other options but this is generally the easiest option to ensure consistent, recoverable backups.

The mysqldump is not as involved as you think though. You can just write a script to dump the mysql database to an area on the same server, and, on the next run, it deletes the previous dump and runs a new dump, that way no one has to keep deleting things.

If you just "don't do anything" then your backups will be "crash consistent". What does that mean? That means that the database will be in the same state as if your system "crashed" at the moment that the snapshot was take. By "crash" we mean if the system locked up, or was hard powered off. In most cases a system that expereince a hard power off will still power back up and continue on without any real issues. We've probably all rebooted a powered off or BSOD system and it booted up just fine, right? But of course this is not guaranteed and it's certainly not guaranteed to be 100%. MySQL is pretty robust in these cases, but you may need to run a mysqlcheck to repair any inconsistencies in the database.

[msidnam]

We are (for now anyway), trying the option of stopping and restarting the services via the pre and post scripts. We are using a program called monit to do this. It seems as though 1 out of 5 times the tomcat service fails to restart. we've added a sleep of 10 seconds and then 20 seconds. right now 20 seconds seems to work but we thought the same for 10 seconds.

[Maieu_san]

Hi all,

We have a customer who want to run Ubuntu with MySQL installed, and we want to backup this VM with Veeam.
Are there any best practices for this? Because we need to make sure all the data is consistent of course!

Thanks in advance for the help!

best regards,
Steven

[foggy]

In order to backup Linux VMs with MySQL you have to use pre-freeze/post-thaw scripts that freeze database activity before the snapshot is taken. Additionally, there is a dedicated white paper regarding backing up MySQL server residing on Linux VM. Thanks.