Re: [PLUG] Thoughts on MySQL from a developer

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.

https://www.postgresql.org/docs/13/backup-file.html
https://dev.mysql.com/doc/refman/5.7/en/replication-snapshot-method.html#replication-howto-rawdata

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