It is strongly recommended that you backup your database at regular intervals and before an upgrade.
Restoring your database from backup is then possible if something goes wrong.
cPanel is a popular control panel used by many web host. The backup feature can be used to backup your MySql database. Do not generate a full backup, as these are strictly for archival purposes and cannot be restored via cPanel. Look for 'Download a MySQL Database Backup' and click the name of the database. A *.gz file will be downloaded to your local drive.
There is no need to unzip this file to restore it. Using the same cPanel program, browse to the gz file and upload it. Once the upload is complete, the bottom of the browser will indicate dump complete. If you are uploading to a new host, you will need to recreate the database user along with the matching password. If you change the password, make the corresponding change in the wp-config.php file.
phpMyAdmin is the name of the program used to manipulate your database.
Information below has been tried and tested using phpMyAdmin versions 2.5.3, 2.5.7-pl1, and 2.6.1-pl3 running on Unix.
Detailed options are listed below, though in most cases you will be fine with the default export options.
It doesn't matter too much what you check here. So long as you check the DATA box itself, the Complete inserts and Extended Inserts are mostly cosmetic changes to the output style, though Extended Inserts can help if you are having issues with the file size.
phpMyAdmin cannot handle large databases so using straight MySQL code will help.
1. Change your directory to the directory you want to dump things to:
user@linux:~> cd files/blog
mysqldump to dump all database tables. To dump only certain tables from the database, give their names at the place shown by
(tablename tablename tablename), and omit the parentheses
( ) in any case. (For help, try:
user@linux:~/files/blog> mysqldump --add-drop-table -h mysqlhostserver -u mysqlusername -p databasename (tablename tablename tablename) | bzip2 -c > blog.bak.sql.bz2 Enter password: (enter your mysql password) user@linux~/files/blog>
Example: mysqldump --add-drop-table -h db01.example.net -u dbocodex -p dbwp | bzip2 -c > blog.bak.sql.bz2 Enter password: my-password user@linux~/files/blog>
bzip2 -c after the | (pipe) means the backup is compressed on the fly, and the
> blog.bak.sql.bz2 sends the
bzip output to a file named
blog.bak.sql.bz2. It does in one line the same thing that these two commands do:
mysqldump --add-drop-table -h db01.example.net -u dbocodex -p dbwp > blog.bak.sql bzip2 blog.bak.sql
Despite bzip2 being able to compress most files more effectively than the older compression algorithms (.Z, .zip, .gz), it is considerably slower (compression and decompression). If you have a large database to dump, gzip is a faster option to use.
mysqldump --add-drop-table -h db01.example.net -u dbocodex -p dbwp | gzip > blog.bak.sql.gz
MySQL Workbench (formerly known as MySQL Administrator) is a program for performing administrative operations, such as configuring your MySQL server, monitoring its status and performance, starting and stopping it, managing users and connections, performing backups, restoring backups and a number of other administrative tasks.
Note: The instruction below was written for older version (MySQL Administrator).
This assumes you have already installed MySQL Admin and set it up so that you can login to the MySQL Database Server either locally or remotely. Refer to the documentation that comes with the installation package of MySQL Admin for your platform for installation instructions.
In addition to MySQL Workbench, there are many GUI tools that let you backup (export) your database.
|Name||OS (Paid edition)||OS (Free edition)||Notes|
|MySQL Workbench||Windows/Mac/Linux||Windows/Mac/Linux||See above|
|EMS SQL Management Studio for MySQL||Windows||-|
|Aqua Data Studio||-||Windows/Mac/Linux||Available in 21 languages|
|Navicat for MySQL||Windows/Mac/Linux||Windows/Mac/Linux (Lite)||Available in 8 languages|
|Toad for MySQL||-||Windows|
|Sequel Pro||-||Mac||CocoaMySQL successor|
Austin Matzko maintains a WordPress plugin originally created by Skippy called WordPress Database Backup (WP-DB-Backup). It was bundled with WordPress 2.0 but is no longer included with WordPress 2.1.
/wp-content/backup-*/inside your WordPress directory. You may need to make
/wp-contentwritable (at least temporarily) for it to create this directory.
The file created is a standard SQL file. If you want information about how to upload that file, look at Restoring Your Database From Backup.