This is a quick guide mentioning how you can backup and restore a MySQL database on Windows and Linux using Adminer.

Why backup and restore

You may need to know how to backup a restore a database for a number of reasons..

e.g

  • Send the database to someone to debug or give feedback while learning.
  • Move the database from a local machine to the cloud
  • Move the database from cloud vendor A to cloud vendor B
  • etc.

Having a backup of the VM is good but having a backup of the database too is better. I use UpCloud for hosting my VM’s and setting backups is easy. But I cannot download those backups.

UpCloud Backup Screen

Murphy’s Law

“If anything can go wrong, it will”

The most important reason for taking a backup and knowing how to restore it is for disaster recovery reasons.

Backup (the easiest way) with Adminer

Adminer is a free PHP based IDE for MySQL and other databases. Simply install Adminer and save the file on your local computer or remote web server directory.

FYI: The Adminer author Jakub Vrana has a patron page, I am a patron of this awesome software.

Snip from Adminers website. “Adminer (formerly phpMinAdmin) is a full-featured database management tool written in PHP. Conversely to phpMyAdmin, it consist of a single file ready to deploy to the target server. Adminer is available for MySQL, MariaDB, PostgreSQL, SQLite, MS SQL, Oracle, Firebird, SimpleDB, Elasticsearch andMongoDB.”

adminer.php file icon screenshot

TIP: The file would be publicly accessible to anyone so don’t save it to a common area, obfuscate the file, protect it of delete the file when you are done using it.

Once Adminer is installed load it in a web browser, login with your MySQL credentials. Once you login you will see all databases and an Import and Export menu.

Adminer main screen, all databases and import and export menu.

tbtest is a simple database with one table and 4 fields (ID, Key, Value and Modified)

.Click Export to open the export screen.

Export screen showing a list of databases and export options

Click Export, a SQL file will be generated (this is the export of the database).

Here is a save of the file:
https://fearby.com/wp-content/uploads/export.txt

Exported view of https://dev.mysql.com/doc/workbench/en/wb-admin-export-import-management.html

Its that simple.

If I add a binary blob file to the table and upload a PNG file lets see how the export looks.

Screenshot o the new table with a blog field in Adminer UI

Let export the database again in Adminer and check out the output. I used Sublime Text editor to view the export file.

New Export shows the binary file in the Backup SQL file

Restore (the easiest way) with Adminer

OK lets delete the tbtest database and then restore it with Adminer. I used Adminer to delete (DROP) the database.

Database dropped with Adminer

Database “dbtest” deleted.

Now lets create a blank database to restore to (same name).

Create database screen.

Database created.

dbtest created.

Now lets import the database backup using Adminer.

Click Import, select the backup file and un-tick Stop on errors.

Import screenshot, dxtest selectded, Restore file selected, stop on errors disabled

TIP: The 2MB next the the choose file button is defined by your web server and PHP configuration. If you are trying to import a larger database (e.g 80MB) first increase the limits in your web server and PHP (via php.ini).

The Import (restore should take seconds)

Import Success

The database was imported from a backup, all tables and records imported just fine.

The database was imported from a backup

Bonus methods.

On Ubuntu use this guide to backup from the command line. If you use the Oracle MySQL Workbench read this.

I hope this helps someone.

The post How to backup and restore a MySQL database on Windows and Linux appeared first on Code, InfoSec and Server Stuff.