Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a mediawiki running on my local machine and just started running mysqldump last night:

$ mysqldump -h localhost -u wikiuser --default-character-set=UTF8 wikidb --add-locks --skip-lock-table> ~/Dropbox/admin/wikimedia_backup/backup.sql

But now mediawiki protests

Warning: The database has been locked for maintenance, so you will not be able to save your edits right now. You may wish to cut-n-paste the text into a text file and save it for later. The administrator who locked it offered this explanation: Dumping Database, Access will be restored shortly

This sent me looking for locks in the wikidb database. I ran

UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

, but problem persists.

My sqldump statement runs with the --skip-lock-table options (I think I should add or replace with --add-locks?)

I reran the command with both options.

I also tried to kill the process outright:

mysql> SHOW FULL PROCESSLIST \G;
*************************** 1. row ***************************
     Id: 77
   User: wikiuser
   Host: localhost
     db: wikidb
Command: Query
   Time: 0
  State: NULL
   Info: SHOW FULL PROCESSLIST
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> kill query 77;
ERROR 1317 (70100): Query execution was interrupted

So, two questions:

  1. how do I lift the existing lock;
  2. how should my options be in the mysqldump call to avoid the problem, prospectively?
share|improve this question
    
what I think is in the first part you got that message because the DB was writing the information, in your example, when you make the show .... and then kill ... you are killing the query to show the processlist list, you are not killing any 'real' query. –  jcho360 Oct 9 '13 at 19:37
add comment

1 Answer

up vote 1 down vote accepted

The commenter was correct -- your kill query 77 was killing your own thread's query... as was indicated by the fact that the row from the processlist indicated that thread 77 (you) was the thread that was currently running the SHOW FULL PROCESSLIST command.

The way you fix your problem is by finding the step you took before running mysqldump, and undoing it. This does not appear to be a MySQL issue.

From the Mediawiki Manual: Backing Up a Wiki...

Mysqldump from the command line

The most convenient way to create a dump file of the database you want to back up is to use the standard MySQL dump tool mysqldump from the command line. Be sure to get the parameters right or you may have difficulty restoring the database.

First insert the following line into LocalSettings.php

$wgReadOnly = 'Dumping Database, Access will be restored shortly';

this can be removed as soon as the dump is completed.

Your database isn't locked... your wiki is locked, because you locked it before you ran the backup.

If the $wgReadOnly variable in Mediawiki's LocalSettings.php configuration file is set, this locks the wiki against writes and offers the message you put in the string as an explanation to users of the wiki.

This suggests that you added this line while reading the Mediawiki docs but did not remove the line when you were done with the backup, unrelated to mysqldump or MySQL. When mysqldump (or any other client) disconnects, it is impossible for locks to linger even accidentally inside MySQL because locks can only be held as long as the session/thread holding the locks is still connected.

If your wiki isn't massive, and having your wiki become briefly unresponsive while the dump is running isn't a problem, then you don't need to lock the wiki at all. I have a scheduled job that runs against the MySQL server that runs my Mediawiki installation and I don't lock the wiki to take backups. If your wiki is mission-critical and you want zero potential disruption, you could also set up a MySQL replication slave and make backups against the slave instead of the master. If your server has the resources for it, you could run the slave on the same machine as the master, on a different port other than the default 3306.

share|improve this answer
    
the replication/master/slave strategy sounds compelling, but overkill for a one person research project, which is all I use it for. I don't need the $wgReadOnly parameter, then. I understood it to just lock momentarily; I didn't think I had to remove it from LocalSettings.php after each mysqldump, which I set up as a cron job. –  ako Oct 10 '13 at 4:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.