Re: [PLUG] Thoughts on MySQL from a developer

[Rich Freeman via plug <plug@lists.phillylinux.org>]

On Mon, Dec 6, 2021 at 1:17 PM Keith via plug
<plug@lists.phillylinux.org> wrote:
>
>
> On 12/6/21 1:09 PM, Casey Bralla via plug wrote:
>
> I am not a database expert (although I was pretty good on FoxPro "back in the day"), but I have a cron job that backs up my music database (OK, certainly NOT mission critical) every night using the "mysqldump" command.  I do not shut down the database before running mysqldump.
>
> The on-line MySQL manual does not mention shutting down prior to running mysqldump.  I have had to restore the database about a half dozen times over the years, and never had any problems.  Am I just lucky?
>
> No, that's how it works.  That's the online backup tool which give you a file you can reload your database from.  If you are more paranoid you and go through all the mess I've been droning about put it is not necessary for doing a backup.
>
>

Honestly, I think using the official database backup tool is probably
the more conservative approach.  It is probably cleaner from a
restoration standpoint as well especially if the software version
changes.  Depending on your backup options and database feature use
the backup might even be portable to other database software.

As you say it is fine to run mysqldump on a running database.  I think
it is atomic but I'm not certain on that, for all table types.  For
InnoDB I'm sure it is atomic.

I think the confusion is that we were switching between talk of using
SQL exports which most database software supports officially, and
doing file-level backups which is often discouraged, but you can do it
as long as you know what you're doing.  Obviously shutting down the
database is safe in that case, but if your database software allows
you to ensure the disk files are quiescent then you can do it that
way.

What you do want to understand is if the backup is atomic.  An LVM
snapshot (on quiescent files) or offline backup can accomplish that.
So can a supported database backup utility that does the backup in a
transaction.  For that matter you could implement your own by starting
a transaction and doing a whole bunch of selects (I think transactions
provide read consistency).

Maybe somebody can get Bruce to do a talk on database backup best practices/etc.

-- 
Rich
___________________________________________________________________________
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