MySQL Database Backup & Restore

The database used for Movable Type contains settings, data, templates… basically everything except uploaded asset files (jpg, gif, pdf, etc) and the scripts that make up the application.

Warning: Files (including images) uploaded as assets in Movable Type aren’t saved in the database, thus they are not part of a MySQL backup. Files are kept on the filesystem where the mt-static directory is and in the directories where Movable Type is configured to publish blogs. To have a comprehensive backup of all your data, all files should be backed up as well.

Most web hosts back up server data on a regular basis but do not always provide customers access to these backups.

With a Movable Type database backup, if the data gets accidentally lost during upgrade, for example, having a MySQL backup will make it easy to restore to the state of the data when the backup was created.

A backup of the Movable Type database should be performed in the case that data is lost, damaged, or moved to another database. A backup should be created:

• before upgrading
• before customizing or adding new plugins
• when migrating to a different database or web host
• on a regular basis (never hurts!)

Creating a MySQL Database Backup

Backup via Command Line

With shell access via telnet or ssh, use the following commands to create a backup. This backup is often called a “MySQL Dump” because the command to create a backup is called “mysqldump”. The file created is a text file with all the commands necessary to populate an empty database with the proper tables and then place the data into those tables.

Here is an example of how to create a database backup called BACKUP.mysql by setting the database name to DATABASE and the username to USERNAME. After submitting this command, you will be prompted for the password (Database name, user name, and password can all be found in the mt-config.cgi file):

$ mysqldump -a --user=USERNAME --password DATABASE > BACKUP_FILE.mysql

This will output file “BACKUP_FILE.mysql” in the current directory. Change the name of mysql file from “BACKUP_FILE” to something more useful, perhpas add the date as well; something like “mt_2009-10-31.mysql” adds more context to the file.

Web Host’s Tools

Hosting companies offering MySQL databases often offer a way download to backup (such as access to phpMyAdmin) or offer shell access for making backups. Please refer to manuals provided by your hosting server for more information regarding this process.

Backup via phpMyAdmin

phpMyAdmin may be installed by your webhost or you may be able to install it.

1. Login to phpMyAdmin.
2. Select the database to back up from the menu on the left side of the phpMyAdmin screen.
3. Choose the “Export” tab at the top of the screen.
4. In “Export” under “DB dump (schema) Display”, choose “Select All” to select all the tables, and choose “SQL”.
5. Check the box next to “Save to File”.
6. Click the “Run” button at the bottom of the screen to start downloading the backup files and save them to the location of your choice.

Note: This document is available in Japanese

Restoring a MySQL Database

Restoring via Command Line

If you can access your server with telnet or ssh, you can use mysql commands to restore the MySQL database. If, for example, your database name was “mt”, username was “mtuser”, password was “mtpasswd”, and the database backup (or “MySQL dump”) file is called “BACKUP_FILE.mysql” you could run the following to restore the database from the BACKUP.mysql backup file:

$ mysql --user=mtuser --password=mtpasswd mt < BACKUP_FILE.mysql

Restoring With Tools Provided by the Hosting Server

Operators of hosting servers that accommodate MySQL often provide a way to restore (in addition to backing up), or provide shells. You can use these tools to restore your database. Please contact your hosting provider or refer to the manual provided for more information regarding how to use these tools.

Restoring With phpMyAdmin

phpMyAdmin, a tool for managing MySQL in the browser, is also handy for restoring MySQL. For more information, please refer to the document, “Reference: phpMyAdmin” (http://www.movabletype.jp/documentation/mt5/reference/phpmyadmin.html).

Note: This document is available in Japanese