Restoring MediaWiki Database from mysqldump - Ubuntu
MySQL configuration file location:
MySQL root password
Installing MySQL on Ubuntu
To install MySQL, run the following command from a terminal prompt:
sudo apt-get install mysql-server
During the installation process you will be prompted to enter a password for the MySQL root user.
Once the installation is complete, the MySQL server should be started automatically. You can run the following command from a terminal prompt to check whether the MySQL server is running:
sudo netstat -tap | grep mysql
When you run this command, you should see the following line or something similar:
tcp 0 0 localhost:mysql *:* LISTEN 2556/mysqld
If the server is not running correctly, you can type the following command to start it:
sudo /etc/init.d/mysql restart
Configuring MySQL on Ubuntu
You can edit the /etc/mysql/my.cnf file to configure the basic settings -- log file, port number, etc. For example, to configure MySQL to listen for connections from network hosts, change the bind-address directive to the server's IP address:
bind-address = 192.168.0.5
Note Replace 192.168.0.5 with the appropriate address.
After making a change to /etc/mysql/my.cnf the mysql daemon will need to be restarted:
sudo /etc/init.d/mysql restart
If you would like to change the MySQL root password, in a terminal enter:
sudo dpkg-reconfigure mysql-server-5.1
The mysql daemon will be stopped, and you will be prompted to enter a new password.
Configuring PHP on Ubuntu
Edit your PHP configuration file. It will be located somewhere like /etc/php5/apache2/php.ini or /etc/php5/php.ini or maybe /etc/apache2/php/php.ini
sudo gedit /etc/php5/apache2/php.ini
Maximum upload file size
Assuming that various files are going to be uploaded to the Wiki as content, the limit on the maximum size of an upload has to be adjusted (In Ubuntu Server 12.04 this is unnecessary, as it is set to 32M by default). About one-half way down is the File Uploads section. Change:
upload_max_filesize = 2M
to at least 100M. You may have to adjust this again in the future.
upload_max_filesize = 8M
Some PHP scripts require a lot of memory to run. To increase the maximum amount of memory a script can use, page down to about 21%, and change the following entry, if found, from
memory_limit = 8M
memory_limit = 32M
If it is already 32M or more, leave it as is.
The MySQL Handbook is also available in the mysql-doc-5.0 package. To install the package enter the following in a terminal:
sudo apt-get install mysql-doc-5.0
The documentation is in HTML format, to view them enter file:///usr/share/doc/mysql-doc-5.0/refman-5.0-en.html-chapter/index.html in your browser's address bar.
For general SQL information see Using SQL Special Edition by Rafe Colburn.
The Apache MySQL PHP Ubuntu Wiki page also has useful information.
MediaWiki Installation Steps
sudo apt-get install apache2 mysql-server php5 php5-mysql libapache2-mod-php5
sudo apt-get install php-apc php5-intl imagemagick phpmyadmin vsftpd
- download the official tarball, and extract in your Web directory (usually /var/www/):
- Also double check that Ubuntu is up-to-date by running:
sudo apt-get update && sudo aptitude safe-upgrade && sudo do-release-upgrade
Restoring MediaWiki Database from mysqldump
- 1- Re-create the database, user and permissions
- 2- Import the database backup
- 3- Import the MediaWiki files
- 4- Check the configuration file
- 5- Test
Re-create the database, user and permissions
On the server on which you are restoring MediaWiki, ensure you have
- a correctly-working instance of MySQL
- a user with appropriate permissions
If you are restoring from backup due to database corruption, consider reinstalling MySQL! Once MySQL is working properly, create a new MySQL database and grant your user account permissions on the database. SELECT, INSERT, UPDATE and DELETE permissions should suffice. You may need to consult the MySQL documentation, your hosting provider's control panel documentation, or the documentation of any other utilities you are using for information on how to do this. From the mysql prompt as root you can:
CREATE DATABASE restored_wikidb
CREATE USER johnowen
GRANT SELECT ON restored_wikidb.* TO johnowen
GRANT UPDATE ON restored_wikidb.* TO johnowen
GRANT INSERT ON restored_wikidb.* TO johnowen
GRANT DELETE ON restored_wikidb.* TO johnowen
Note: It doesn't matter if the database doesn't have the same name; indeed, in a commercial hosting environment, where database names are usually prefixed with a hosting account username, a different database name is almost guaranteed. In addition, the username can differ, as can that user's password. You will have to adjust the LocalSettings.php file on the new location accordingly.
Import the database backup
Next, import your database backup. This will create the tables in the database and populate them with data. Importing takes a variable amount of time, depending upon the number of pages, users, edits, etc. in your wiki.
From the command line using mysql
If a database exists and you want to entirely replace it from the backup. To destroy the database:
mysqladmin -u [userid] -p drop [wikidbname]
Substituting as apropriate for userid and wikidbname. the -p parameter will prompt you for the password.
Then to create a new database:
mysqladmin -u [userid] -p create [wikidbname]
For example after backing up with mysqldump, eg.
- don't do this now: this is how you might have created a backup earlier
mysqldump --default-character-set=binary --user=foobar --password=barbaz wikidb > dbdump.sql
Make sure to specify the correct character set or the restore may fail, check LocalSettings.php to find out which it is.
Note: After using mysqldump, you can't just use mysqlimport. Mysqlimport requires the data to be loaded in some delimited text format, eg. CSV, whereas the output of mysqldump is a sequence of SQL statements.
To import dbdump.sql from the command line do:
mysql -u [userid] -p [databasename] < dbdump.sql
and afterwards, if required do:
To wipe and restore the wiki file system
Remember to also restore the file system components of the wiki that might be required, eg. images, logo, and extensions. Especially to edit LocalSettings.php to check everything is correct. A sequence of Linux commands to wipe and restore the wiki file system could look like this:
tar -xpzf mediawiki-1.20.3.tar.gz
rm -fR wiki_folder/
mv mediawiki-1.20.3 wiki_folder
rm -fR wiki_folder/extensions/
cp -R backup/extensions wiki_folder/extensions
Open the wiki from the browser and click on the Set up the wiki first link. See Manual:Config script for details. If needed, you can run the command-line installer php wiki_folder/maintenance/install.php. After this is done edit LocalSettings.php to suit the fresh install, restoring lines for extensions, etc. Restore from backup any other files, such as a custom logo and favicon to the correct paths.
If you've not installed as a root user and the images and thumbnails don't work, you'll need to
chmod 777 wiki_folder/images
this creates a security risk, so add Options -Indexes to the wiki folder's .htaccess file, to prevent folder browsing.
See also Executing SQL Statements from a Text File
Import the MediaWiki files
Next, restore your backup of the MediaWiki filesystem: this is the final "large" step in the restore process. If you followed the backup manual instructions, and backed up the entire directory, this will include the images and extensions directories, plus custom skins, etc. and the configuration file. If you backed up only portions of the directory, e.g. images, extensions, etc. then you will need to first upload/copy a fresh install of the MediaWiki files, then transfer the backed-up directories and files into the correct locations in the new filesystem.
- If following the latter process, ensure that your "fresh install" consists of the same version of MediaWiki as the old one did
- Check that the upload directory (e.g. images in version 13) has the correct permissions set if using uploads; it needs to be writable by the web server.
- Make sure any extension directories have the correct permissions as well (e.g. if linux: chmod -R o=rx extensions or chmod -R o=rx includes)
- The best method to use when manually transferring a wiki from a windows platform to a Linux platform involves:
- Manually installing wikimedia on the Linux platform from a tar file as outlined here: Manual Install of Wiki (i.e. try not to use install from a automated package)
- Replacing the newly installed wikimedia folder (e.g. /var/www/mediawiki...) on your Linux machine with the wikimedia base folder from your windows machine
- Updating your Linux machine's MySQL Database (e.g. wikidb) with the wiki database from your windows machine (i.e. use the backup and restore features of mysql as outlined above)
- After importing a wiki database, even if the database can work, some minor problems may arise:
- Searchindex may need to be repaired: In phpMyAdmin, enter "REPAIR TABLE wikidb.searchindex; " for rebuilding the search index. wikidb is your database's name.
- The collation of some rows may be changed. Cross check it with the original database.
Check the configuration file
The final task involves verification of, and possibly modifying, the LocalSettings.php file. If you are restoring onto the same server from which you backed up, you probably need not change anything. If you are restoring onto a new server (i.e., if you are moving or duplicating the MediaWiki), certain entries will almost undoubtedly require changing, and you may need to change the database connection information as well.
At this point, attempt to access the wiki on the new server and use it. Log in as a sysop and a regular user and check that viewing, creating and editing pages and uploading files still works. You will need to fix any problems reported either by PHP or MediaWiki itself.