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.
NOTE: Below steps backup core WordPress database that include all your posts, pages and comments, but DO NOT backup the files and folders such as images, theme files on the server. For whole WordPress site backup, refer WordPress Backups.
cPanel is a popular control panel used by many web hosts. 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 version 4.4.13 connects to MySQL version 5.6.28 running on Linux.
When you backup all tables in the WordPress database without compression, you can use simple method. To restore this backup, your new database should not have any tables.
If you want to change default behavior, select Custom backup.
Select 'zipped' or 'gzipped' from Compression box to compress the data.
Ensure that the SQL is selected. Unlike CSV or other data formats, this option exports a sequence of SQL commands.
Leave options as they are.
Select Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT / TRIGGER statement. Before table creation on target database, it will call DROP statement to delete the old existing table if exist.
Leave options as they are.
Remember - you have NOT backed up the files and folders - such as images - but all your posts and comments are now safe.
phpMyAdmin cannot handle large databases so using straight MySQL/MariaDB code will help.
1. Change your directory to the directory you want to export backup to:
user@linux:~> cd files/blog user@linux:~/files/blog>
mysqldump command with your MySQL server name, user name and database name. It prompts you to input password. (For help, try:
mysqldump --add-drop-table -h mysql_hostserver -u mysql_username -p mysql_databasename
mysqldump --add-drop-table -h mysql_hostserver -u mysql_username -p mysql_databasename mysql_tablename1 mysql_tablename2 mysql_tablename3
user@linux:~/files/blog> mysqldump --add-drop-table -h db01.example.net -u dbocodex -p wp > blog.bak.sql Enter password: (type password)
bzip2 to compress the backup file
user@linux:~/files/blog> bzip2 blog.bak.sql
You can do the same thing that above two commands do in one line:
user@linux:~/files/blog> mysqldump --add-drop-table -h db01.example.net -u dbocodex -p wp | bzip2 -c > blog.bak.sql.bz2 Enter password: (type password)
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
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 backup, gzip is a faster option to use.
user@linux:~/files/blog> mysqldump --add-drop-table -h db01.example.net -u dbocodex -p wp | gzip > blog.bak.sql.gz
MySQL Workbench (formerly known as My SQL 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.
Information below has been tried and tested using MySQL Workbench version 6.3.6 connects to MySQL version 5.6.28 running on Linux.
This assumes you have already installed MySQL Workbench 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 Workbench for your platform for installation instructions or online document.
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||Windows/Mac/Linux (14 days trial)||Available in 9 languages|
|Navicat for MySQL||Windows/Mac/Linux||Windows/Mac/Linux (14 days trial)||Available in 8 languages|
|Toad for MySQL||-||Windows|
|Sequel Pro||-||Mac||CocoaMySQL successor|
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.