On Sat, Apr 1, 2023 at 2:21 AM Benjamin Lees <emufarmers(a)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(a)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