Backup options for the MySQL database
Hi Everybody, I recently started a new role at a company that uses Observium to monitor a fairly large estate. Observium (Professional edition) was originally installed on an EC2 instance running Debian 8 and MySQL 5.5 by someone who has long since departed the company. My task is to bring it all up to date and document it. I've created a new instance running Ubuntu 20.04 LTS and MySQL 8.0.23 and testing the migration process hasn't thrown up any problems. One of the things my predecessor didn't do was to set up any backup regime for the database. As it happens, /var/lib/mysql is a logical volume and I've been looking at using lvm snapshots as part of a backup process. The basic idea would be to: Connect to the mysql daemon and issue a 'FLUSH TABLES WITH READ LOCK' command. Create the LVM snapshot. Connect to the daemon and issue a 'UNLOCK TABLES' command. Do something with the snapshot.
My questions are: Is this a good idea? Will it give me a consistent backup? Is there a better way?
Thanks in advance, Michael.
Hi Michael,
Is this a good idea ? Kind of good. However, you are locking the database for no good reason, because the interesting data is mostly stored in the RRD files. Caring about the database's integrity at snapshot time is, IMHO, a bit too much effort. Also, if the same lvm volume stores the RRDs, you at least would have a consistent DB+RRDs snapshot/backup. Still, if you 'really' want to be consistent, you should stop the polling as well ... Given that these backups will probably be of use only in a sort of Disaster Recovery scenario, I guess that missing a sample on the RRDs or very latest info in the database is the least of your concerns. But I may be mistaken about your specific requirements, so feel free to ignore ;)
As you're already running in AWS,I would have 2 suggestions: 1. Use AWS RDS for your database. Snapshots are automated. Given that observium stores it's real metrics in RRD files, the database really is not changing that much that you need an absolute standstill for the snapshot. 2. If not using AWS RDS, a simple way to get consistent snapshots without actually interfering with the database is to have a mysql slave that you can pull (STOP SLAVE) and do your backup there. Again, RRD consistency may not be on par, and the gain may not be up to the price you'll pay for additional resources.
HTH, Ahmed.
Le lun. 1 mars 2021, à 13 h 19, Michael Kelly via observium < observium@observium.org> a écrit :
Hi Everybody, I recently started a new role at a company that uses Observium to monitor a fairly large estate. Observium (Professional edition) was originally installed on an EC2 instance running Debian 8 and MySQL 5.5 by someone who has long since departed the company. My task is to bring it all up to date and document it. I've created a new instance running Ubuntu 20.04 LTS and MySQL 8.0.23 and testing the migration process hasn't thrown up any problems. One of the things my predecessor didn't do was to set up any backup regime for the database. As it happens, /var/lib/mysql is a logical volume and I've been looking at using lvm snapshots as part of a backup process. The basic idea would be to: Connect to the mysql daemon and issue a 'FLUSH TABLES WITH READ LOCK' command. Create the LVM snapshot. Connect to the daemon and issue a 'UNLOCK TABLES' command. Do something with the snapshot.
My questions are: Is this a good idea? Will it give me a consistent backup? Is there a better way?
Thanks in advance, Michael. _______________________________________________ observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium
If you don’t have syslog in mysql, you can just do a straight mysqldump and back that up, much easier.
If you do have syslog in MySQL, you probably want to do a selective dump excluding the syslog table, and just dump the syslog table schema.
Adam.
From: observium observium-bounces@observium.org On Behalf Of Michael Kelly via observium Sent: 01 March 2021 18:19 To: observium@observium.org Cc: Michael Kelly michael@swiftserve.com Subject: [Observium] Backup options for the MySQL database
Hi Everybody,
I recently started a new role at a company that uses Observium to monitor a fairly large estate.
Observium (Professional edition) was originally installed on an EC2 instance running Debian 8 and MySQL 5.5 by someone who has long since departed the company.
My task is to bring it all up to date and document it.
I've created a new instance running Ubuntu 20.04 LTS and MySQL 8.0.23 and testing the migration process hasn't thrown up any problems.
One of the things my predecessor didn't do was to set up any backup regime for the database.
As it happens, /var/lib/mysql is a logical volume and I've been looking at using lvm snapshots as part of a backup process.
The basic idea would be to:
Connect to the mysql daemon and issue a 'FLUSH TABLES WITH READ LOCK' command.
Create the LVM snapshot.
Connect to the daemon and issue a 'UNLOCK TABLES' command.
Do something with the snapshot.
My questions are:
Is this a good idea?
Will it give me a consistent backup?
Is there a better way?
Thanks in advance,
Michael.
participants (3)
-
Adam Armstrong -
Ahmed Rahal -
Michael Kelly