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
--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.sqlBut 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/
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.sqlBut 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@lists.wikimedia.org