How to quickly back up and restore a database with phpMyAdmin


If a web-based GUI is better suited to your admin skills, Jack Wallen wants to show you how to back up and restore your MySQL/MariaDB databases with one of the easiest tools on the market.

database administrator working

Image: iStock/gorodenkoff

Many of your business processes depend on databases. Should one of those databases fail, your workflow could come to a standstill. That would cost you money, an outcome you certainly do not want.

To avoid that, you need to back up those databases. I’ve already walked you through the process of doing so from the command line. This is most definitely a technique you should know. But there might be times when you want to work with a bit more efficiency, such as by way of a web-based GUI like phpMyAdmin. That, my friends, is exactly what I’m going to show you today.

After you read how easy this is, you might never go back to the terminal for this task.

Let’s get to work.

SEE: 40+ open source and Linux terms you need to know (TechRepublic Premium)

What you’ll need

You’ll need three things to make this happen:

  • A running instance of phpMyAdmin
  • A user with full access to the databases (such as the phpmyadmin user)
  • A database to back up

With those things at the ready, let’s back up and restore that data.

How to back up a database with phpMyAdmin

The first thing we’ll do is back up the database. Log in to phpMyAdmin and select the database to be backed up from the left navigation (Figure A).

Figure A


I’m going to back up my nextcloud database.

Once you’ve selected the database, go to the main pane and select the Export tab (Figure B).

Figure B


The export tab is where the backup process is found.

In the next window (Figure C), make sure to select Quick, and SQL format. 

Figure C


You can ignore the export template window for basic backups.

Click Go and you’ll be prompted to save the .sql file from the export. Save the file and the backup is complete.

One thing to keep in mind is that phpMyAdmin can’t regularly back up your databases. Because of this, you must manually back them up. I’d suggest getting in the habit of doing this daily.

How to restore from a backup

Should something go wrong with your database (let’s hope it never does), you can restore it from your latest backup but it’s not quite as straightforward as is the backup.

First off, you have to delete the failed backup from phpMyAdmin. To do that, click on the SQL tab and, in the query pane (Figure D — the text area above the Submit Query button) type:

drop database NAME;

Where NAME is the name of the database to be deleted.

Once you’ve done that, you then need to create a new database with the same name. Go to the main phpMyAdmin window and go to the Databases tab. Type the name of the new database and click Create (Figure D).

Figure D


Recreating the deleted database.

Once the database has been created, select it from the left navigation and click the Import tab. In this new window (Figure E), click the Browse button and then locate the .sql backup file you downloaded from the export.

Figure E


Restoring a MySQL database using the phpMyAdmin import tool.

This action will take some time (depending on how large the database is). When the import completes, you should see all of the tables listed in the left navigation and you’re good to go. Although this process isn’t quite as simple as when working from the command line, it will get the job done and do so with the help of a handy GUI.

Also see

Source link

Leave a reply

Please enter your comment!
Please enter your name here