Re: MySQL backup and consistency of database and dump file
Jeffrey Walton <[email protected]>
| Newsgroups | gmane.org.wikimedia.mediawiki |
|---|---|
| Message-ID | <CAH8yC8m9nG9KjG_mDEun5jOQyiYAjg=0eV-_aqETu9Cttoa3+Q@mail.gmail.com> |
On Sat, Apr 1, 2023 at 2:21 AM Benjamin Lees <[email protected]> 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 <[email protected]> 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 -- [email protected] To unsubscribe send an email to [email protected] https://lists.wikimedia.org/postorius/lists/mediawiki-l.lists.wikimedia.org/