{"id":312,"date":"2023-05-31T15:45:25","date_gmt":"2023-05-31T15:45:25","guid":{"rendered":"https:\/\/info.ffteixeira.net\/?p=312"},"modified":"2023-05-31T15:45:25","modified_gmt":"2023-05-31T15:45:25","slug":"wp-db-backup","status":"publish","type":"post","link":"https:\/\/blog.ffteixeira.net\/?p=312","title":{"rendered":"WP DB Backup"},"content":{"rendered":"\n

It is strongly recommended that you backup your database at regular intervals and before an upgrade.<\/strong><\/p>\n\n\n\n\n\n\nRead more: WP DB Backup<\/span><\/a>\n\n\n

Restoring your database from backup<\/a> is then possible if something goes wrong. <\/p>\n\n\n\n

NOTE:<\/strong> 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<\/a>.<\/p>\n\n\n\n

Backup using cPanel X<\/a><\/h2>\n\n\n\n

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 \u2018Download a MySQL Database Backup\u2019 and click the name of the database. A *.gz file will be downloaded to your local drive.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

Using phpMyAdmin<\/a><\/h2>\n\n\n\n

phpMyAdmin<\/a> is the name of the program used to manipulate your database.<\/p>\n\n\n\n

Information below has been tried and tested using phpMyAdmin version 4.4.13 connects to MySQL version 5.6.28 running on Linux.<\/p>\n\n\n\n

\"phpmyadmin_top\"<\/a><\/figure>\n\n\n\n

Quick backup process<\/a><\/h3>\n\n\n\n

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.<\/p>\n\n\n\n

    \n
  1. Log into phpMyAdmin on your server<\/li>\n\n\n\n
  2. From the left side window, select your WordPress database. In this example, the name of database is \u201cwp\u201d.<\/li>\n\n\n\n
  3. The right side window will show you all the tables inside your WordPress database. Click the \u2018Export\u2019 tab on the top set of tabs.<\/li>\n<\/ol>\n\n\n\n
    \"\"<\/a><\/figure>\n\n\n\n

    4. Ensure that the Quick option is selected, and click \u2018Go\u2019 and you should be prompted for a file to download. Save the file to your computer. Depending on the database size, this may take a few moments.<\/p>\n\n\n\n

    \"phpmyadmin_quick_export\"<\/a><\/figure>\n\n\n\n

    Custom backup process<\/a><\/h3>\n\n\n\n

    If you want to change default behavior, select Custom backup.  In above Step 4, select Custom option. Detailed options are displayed.<\/p>\n\n\n\n

    \"phpmyadmin_custom_export\"<\/a><\/figure>\n\n\n\n

    The Table section<\/a><\/h4>\n\n\n\n

    All the tables in the database are selected. If you have other programs that use the database, then choose only those tables that correspond to your WordPress install. They will be the ones with that start with \u201cwp_\u201d or whatever \u2018table_prefix\u2019 you specified in your \u2018wp-config.php\u2019 file.<\/p>\n\n\n\n

    If you only have your WordPress blog installed, leave it as is (or click \u2018Select All\u2019 if you changed the selection)<\/p>\n\n\n\n

    The Output section<\/a><\/h4>\n\n\n\n

    Select \u2018zipped\u2019 or \u2018gzipped\u2019 from Compression box to compress the data.<\/p>\n\n\n\n

    \"phpmyadmin_export_output\"<\/a><\/figure>\n\n\n\n

    The Format section<\/a><\/h4>\n\n\n\n

    Ensure that the SQL is selected. Unlike CSV or other data formats, this option exports a sequence of SQL commands.<\/p>\n\n\n\n

    In the Format-specific options section, leave options as they are.<\/p>\n\n\n\n

    \"phpmyadmin_export_formatspecific\"<\/a><\/figure>\n\n\n\n

    The Object creation options section<\/a><\/h4>\n\n\n\n

    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 it exist.<\/p>\n\n\n\n

    \"phpmyadmin_export_object\"<\/a><\/figure>\n\n\n\n

    The Data creation options section<\/a><\/h2>\n\n\n\n

    Leave options as they are.<\/p>\n\n\n\n

    \"phpmyadmin_export_data\"<\/a><\/figure>\n\n\n\n

    Now click \u2018Go\u2019 at the bottom of the window and you should be prompted for a file to download. Save the file to your computer. Depending on the database size, this may take a few moments.<\/p>\n\n\n\n

    Remember<\/strong> \u2013 you have NOT backed up the files and folders \u2013 such as images \u2013 but all your posts and comments are now safe.<\/p>\n\n\n\n

    Using Straight MySQL\/MariaDB Commands<\/a><\/h2>\n\n\n\n

    phpMyAdmin cannot handle large databases so using straight MySQL\/MariaDB code will help.<\/p>\n\n\n\n

    Change your directory to the directory you want to export backup to:<\/p>\n\n\n\n

    user@linux:~> cd files\/blog\nuser@linux:~\/files\/blog><\/code><\/pre>\n\n\n\n
    Use the mysqldump<\/code> command with your MySQL server name, user name and database name. It prompts you to input password (For help, try: man mysqldump<\/code>).<\/p>\n\n\n\n
    To backup all database tables<\/strong><\/p>\n\n\n\n
    mysqldump --add-drop-table -h mysql_hostserver -u mysql_username -p mysql_databasename\ufeff<\/code><\/pre>\n\n\n\n
    To backup only certain tables from the database<\/strong><\/p>\n\n\n\n
    mysqldump --add-drop-table -h mysql_hostserver -u mysql_username -p mysql_databasename\ufeff<\/code><\/pre>\n\n\n\n
    Example:<\/p>\n\n\n\n
    user@linux:~\/files\/blog> mysqldump --add-drop-table -h db01.example.net -u dbocodex -p wp > blog.bak.sql\nEnter password: (type password)<\/code><\/pre>\n\n\n\n
    Use bzip2 to compress the backup file<\/strong><\/p>\n\n\n\n
    user@linux:~\/files\/blog> bzip2 blog.bak.sql<\/code><\/pre>\n\n\n\n
    You can do the same thing that above two commands do in one line:<\/p>\n\n\n\n
    user@linux:~\/files\/blog> mysqldump --add-drop-table -h db01.example.net -u dbocodex -p wp | bzip2 -c > blog.bak.sql.bz2\nEnter password: (type password)<\/code><\/pre>\n\n\n\n
    The 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.<\/p>\n\n\n\n
    Despite bzip2 being able to compress most files more effectively than the older compression algorithms (.Z, .zip, .gz), it is considerably slower<\/a> (compression and decompression). If you have a large database to backup, gzip is a faster option to use.<\/p>\n\n\n\n
    user@linux:~\/files\/blog> mysqldump --add-drop-table -h db01.example.net -u dbocodex -p wp | gzip > blog.bak.sql.gz<\/code><\/pre>\n\n\n\n
    Using MySQL Workbench<\/a><\/h2>\n\n\n\n
    MySQL Workbench<\/a> (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.<\/p>\n\n\n\n
    You can perform most of those tasks using a command line interface such as that provided by mysqladmin<\/a> or mysql<\/a>, but MySQL Workbench is advantageous in the following respects:<\/p>\n\n\n\n