During a recent WordPress upgrade I was performing I noticed an issue with the mysqldump of the WordPress database which was causing the actual .sql file backup of the database to not read properly. After investigating I noticed that the atpp_page_cache table from Angsuman’s Translator Plugin Pro included Blob columns that were not dumping properly with the default mysqldump syntax as shown below.
- mysqldump -u root -p databasename > db_backup_file.sql
The above syntax will typically backup a MySQL database without issue however because of the Blob field the dump file was corrupted which would then not allow it to be imported properly. After some research I came up with two different solutions and I would suggest backing up your database with both just in case there is an issue with either one you will have another backup. When it comes to all of the data from your WordPress site you can not be to safe and never have too many database backups!
Backup With phpMyAdmin Web Interface:
The first option is to use the default settings with phpMyAdmin and export the database via the web interface to a SQL file.
- Open phpMyAdmin Interface: First open the phpMyAdmin web interface in your favorite browser.
- Open Correct Database: Now choose the database you want to backup from the drop down list of databases in the upper left corner of the phpMyAdmin web interface underneath the text Database as shown in the below image where alextest is the example database.
- Open Export Tab: After the correct database has been selected click on the Export tab in the top center navigation menu in between the Query tab and Import tab.
- Select Export Settings: The default settings will work but first make sure they mirror the settings in the image below and also make sure to select the “Save as file” check box.
You can compress the data however in this scenario since we want to verify the Blob fields are not corrupted you want to make sure that the *None radio button is selected in the compression settings.
- Initiate Backup: Once you have verified all of the settings for the database export are correct you will click the Go button which will initiate the backup process. After clicking this button this will pop open a window that will prompt you to save the file as shown below.
Click the Save button and then browse to the location where you want to save the file. Click the Save button in the new window to start the backup and download process.
- Verify MySQL File: Now open the database backup in your favorite text editor such as vi or Notepad++ to verify it is not corrupted.
Backup With An Extra mysqldump Switch:
The second option is to add an extra switch to the mysqldump syntax as shown below. The “–hex-blob” switch will dump binary columns using hexidecimal notation which will keep the WordPress Translator Pro Plugin Blob columns from becoming corrupted.
- mysqldump --hex-blob -u root -p database_name > db_backup.sql
Once the backup is completed open the backup in your favorite text editor which if you are using a Linux shell will probably be vi to verify the backup is not corrupted.
Again make sure to backup the database numerous times before proceeding with your WordPress upgrade since once the database has been modified it could become difficult to recover any data that is converted in the upgrade process.
