| Keith via plug on 6 Dec 2021 10:27:41 -0800 |
[Date Prev] [Date Next] [Thread Prev] [Thread Next] [Date Index] [Thread Index]
| Re: [PLUG] Thoughts on MySQL from a developer |
I don't generally do LVM snapshots but I do run nilfs2 volumes on systems where I need filesystem snapshotting. I haven't tested that with Pg or MySQL since I handle such tasks at the system or VM level but I like the idea of using quiesce. I'll have to read up more on that.Regarding the "special care" issue with MySQL, there is a nice method to avoid this, if you are using LVM... quiesce the DB, take an LVM snapshot, and then backup the snapshot. No need to shut down the DB, and you get a full, filesystem-level backup.
Instructions here (not min, but they look like the same approach as I use), and elsewhere, I am sure: https://www.lullabot.com/articles/mysql-backups-using-lvm-snapshots
--Matt
On Mon, Dec 6, 2021 at 11:24 AM Isaac Bennetch via plug <plug@lists.phillylinux.org> wrote:
___________________________________________________________________________Hi,
First a disclaimer: As some of you know, I'm the maintainer of phpMyAdmin (thanks for the mention, Keith!), so I have much more familiarity with MySQL than I do with PostgreSQL. None of my projects currently require the complexity of using PG, and as Brent said above, most things will work with MySQL but very few things will require only PG, so for me the ease of running only one outweighs the burden of maintaining both simultaneously. I'm not a MySQL apologist and think it was a positive change for distributions to switch to MariaDB, I just know MySQL better.
On Sun, Dec 5, 2021 at 5:26 PM Keith C. Perry via plug <plug@lists.phillylinux.org> wrote:
That said, I'll take Pg's data resiliency over MySQL's 1 billion times over. Last year when my Zimbra server crashed I lost a week's worth of data because the MySQL database would not open and could not be recovered. Fortunately, I had run a closed backup of the VM a week ago which saved my ass (otherwise it would have been closer to a 30 days loss). Apparently, MySQL still needs special care to be backed up while the system is up. As far as I can recall I've always using InnoDB tables instead of ISAM too (which are supposed to be more "durable"). Such issues for Pg were resolved 21 years ago. I'll admit there's a tricky balancing act no matter what you choose but personally, I'll always take database durability over performance and that skews me towards Pg out of the box.
According to the PostgreSQL manual, backups at the file system level should follow the same process MySQL does: (1) the daemon/service must be stopped and (2) take the entire data directory, not just one specific database (I'm simplifying a bit, when using no InnoDB tables, one can acquire a read lock and keep the server running, but it's a lot of "ifs" to ensure consistent data compared to simply stopping and restarting the server).
I still prefer to export SQL files to make sure I have a full and consistent backup, but it doesn't look like PG does anything special compared to MySQL to allow file system backups while the server is running. If there was some MySQL corruption in your attempt to restore, it strongly suggests that the server wasn't properly stopped, you didn't restore and replace the complete data directory, or the data was already silently corrupted at the time of the backup.
As an aside, I quite enjoy seeing this discussion here; I haven't had much time to participate on this mailing list and seeing some good discussion here is rewarding. Between work obligations and family, I'm rarely free during meeting times, so I enjoy reading these email threads.
Cheers,Isaac
Philadelphia Linux Users Group -- http://www.phillylinux.org
Announcements - http://lists.phillylinux.org/mailman/listinfo/plug-announce
General Discussion -- http://lists.phillylinux.org/mailman/listinfo/plug
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ Keith C. Perry, MS E.E. Managing Member, DAO Technologies LLC (O) +1.215.525.4165 x2033 (M) +1.215.432.5167 www.daotechnologies.com
___________________________________________________________________________ Philadelphia Linux Users Group -- http://www.phillylinux.org Announcements - http://lists.phillylinux.org/mailman/listinfo/plug-announce General Discussion -- http://lists.phillylinux.org/mailman/listinfo/plug