Mailing List Archive: MySQL backup and consistency of database and dump file

MySQL backup and consistency of database and dump file

Mar 31, 2023, 10:19 PM

Post #1 of 3 (166 views)

Hi Everyone,

We are trying to workaround failed backups due to
https://bugs.launchpad.net/ubuntu/+source/mysql-5.7/+bug/2003866 . We
used to use --single-transaction, but the option now requires RELOAD
or FLUSH_TABLES. We failed to enable the privileges for the mwuser.

MediaWiki's backup documentation is at
https://www.mediawiki.org/wiki/Manual:Backing_up_a_wiki . Here is the
command MediaWiki recommends:

mysqldump -h hostname -u userid -p \
--default-character-set=whatever dbname > backup.sql

But the article does not discuss how to ensure consistency for the
database and dump file. For MySQL, that should be --single-transaction
or --lock-tables. (If I am reading the MySQL documentation correctly).

My question is, what should we use to ensure consistency of the dump file?

--single-transaction or --lock-tables or something else?

Jeff
_______________________________________________
MediaWiki-l mailing list -- mediawiki-l@lists.wikimedia.org
To unsubscribe send an email to mediawiki-l-leave@lists.wikimedia.org
https://lists.wikimedia.org/postorius/lists/mediawiki-l.lists.wikimedia.org/

Re: MySQL backup and consistency of database and dump file [ In reply to ]

emufarmers at gmail

Mar 31, 2023, 11:21 PM

Post #2 of 3 (166 views)

Permalink

--lock-tables is enabled by default for mysqldump. --single-transaction
offers a way to get a consistent dump without locking tables, but the
tables have to support transactions (which InnoDB does and MyISAM does
not). You should probably use --single-transaction if you can.

The mediawiki.org article you reference tries to ensure consistency by
having you set $wgReadOnly to prevent writes to the database (at least from
within MediaWiki core). I'm not sure how much effect it actually has,
given that --lock-tables is enabled by default, but it's been on the page
for 15 years, so it must be right. :)

On Sat, Apr 1, 2023 at 1:20?AM Jeffrey Walton <noloader@gmail.com> wrote:

> Hi Everyone,
>
> We are trying to workaround failed backups due to
> https://bugs.launchpad.net/ubuntu/+source/mysql-5.7/+bug/2003866 . We
> used to use --single-transaction, but the option now requires RELOAD
> or FLUSH_TABLES. We failed to enable the privileges for the mwuser.
>
> MediaWiki's backup documentation is at
> https://www.mediawiki.org/wiki/Manual:Backing_up_a_wiki . Here is the
> command MediaWiki recommends:
>
> mysqldump -h hostname -u userid -p \
> --default-character-set=whatever dbname > backup.sql
>
> But the article does not discuss how to ensure consistency for the
> database and dump file. For MySQL, that should be --single-transaction
> or --lock-tables. (If I am reading the MySQL documentation correctly).
>
> My question is, what should we use to ensure consistency of the dump file?
>
> --single-transaction or --lock-tables or something else?
>
> Jeff
> _______________________________________________
> MediaWiki-l mailing list -- mediawiki-l@lists.wikimedia.org
> To unsubscribe send an email to mediawiki-l-leave@lists.wikimedia.org
>
> https://lists.wikimedia.org/postorius/lists/mediawiki-l.lists.wikimedia.org/
>

Re: MySQL backup and consistency of database and dump file [ In reply to ]

noloader at gmail

Apr 1, 2023, 11:48 AM

Post #3 of 3 (166 views)

Permalink

On Sat, Apr 1, 2023 at 2:21?AM Benjamin Lees <emufarmers@gmail.com> wrote:
>
> --lock-tables is enabled by default for mysqldump. --single-transaction offers a way to get a consistent dump without locking tables, but the tables have to support transactions (which InnoDB does and MyISAM does not). You should probably use --single-transaction if you can.
>
> The mediawiki.org article you reference tries to ensure consistency by having you set $wgReadOnly to prevent writes to the database (at least from within MediaWiki core). I'm not sure how much effect it actually has, given that --lock-tables is enabled by default, but it's been on the page for 15 years, so it must be right. :)

Thanks Benjamin.

I updated the Mediawiki backup article at [1]. I felt like the
discussion of Mysqldump was missing some important details. I used
configuration options we have been using for our backup script [2].

This is not my area of expertise. Please fix errors and omissions in
the article. And my apologies in advance.

Jeff

[1] https://www.mediawiki.org/wiki/Manual:Backing_up_a_wiki
[2] https://github.com/weidai11/website/blob/master/systemd/bitvise-backup

> On Sat, Apr 1, 2023 at 1:20?AM Jeffrey Walton <noloader@gmail.com> wrote:
>>
>> Hi Everyone,
>>
>> We are trying to workaround failed backups due to
>> https://bugs.launchpad.net/ubuntu/+source/mysql-5.7/+bug/2003866 . We
>> used to use --single-transaction, but the option now requires RELOAD
>> or FLUSH_TABLES. We failed to enable the privileges for the mwuser.
>>
>> MediaWiki's backup documentation is at
>> https://www.mediawiki.org/wiki/Manual:Backing_up_a_wiki . Here is the
>> command MediaWiki recommends:
>>
>> mysqldump -h hostname -u userid -p \
>> --default-character-set=whatever dbname > backup.sql
>>
>> But the article does not discuss how to ensure consistency for the
>> database and dump file. For MySQL, that should be --single-transaction
>> or --lock-tables. (If I am reading the MySQL documentation correctly).
>>
>> My question is, what should we use to ensure consistency of the dump file?
>>
>> --single-transaction or --lock-tables or something else?
>>
>> Jeff
_______________________________________________
MediaWiki-l mailing list -- mediawiki-l@lists.wikimedia.org
To unsubscribe send an email to mediawiki-l-leave@lists.wikimedia.org
https://lists.wikimedia.org/postorius/lists/mediawiki-l.lists.wikimedia.org/