Why backup and restore
This is a quick guide demonstrating how you can backup and restore a MySQL database on Windows and Linux using Adminer.
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.
Advertisement:
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.
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.”
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.
Advertisement:
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.
tbtest is a simple database with one table and 4 fields (ID, Key, Value and Modified)
.Click Export to open the export screen.
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
Its that simple.
If I add a binary blob file to the table and upload a PNG file lets see how the export looks.
Advertisement:
Let export the database again in Adminer and check out the output. I used Sublime Text editor to view the export 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 “dbtest” deleted.
Now lets create a blank database to restore to (same name).
Database created.
Advertisement:
Now lets import the database backup using Adminer.
Click Import, select the backup file and un-tick Stop on errors.
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)
The database was imported from a backup, all tables and records imported just fine.
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.
Advertisement:
