How to backup and restore MySQL/MariaDB data for a website

In the event you’ve by no means backed up your MySQL or MariaDB databases, now’s the time. Jack Wallen reveals you tips on how to again up that knowledge and restore it, in case your web site has met an premature demise.

database administrator working

Picture: iStock/GaudiLab

Your organization or private web site is working like a champ. It serves your neighborhood and prospects, and your viewers and clientele develop each day. That web site serves a vital service to you. Not solely is it a gateway to your organization, it most likely permits your prospects to buy services.

SEE: 40+ open supply and Linux phrases you must know (TechRepublic Premium)

However someday …

You hate to even give it some thought, do not you?

In the future one thing’s going to go improper. When it does, you’d finest hope you have got a backup. That backup should not solely embrace the information and configurations on your web site however the knowledge housed within the database.

Which means you want to have the ability to backup and restore your database. However how? I’ll present you.

What you may want

To make this work, you may want a web site that’s powered by both the MySQL or MariaDB database server. You may additionally want a consumer with sudo privileges. I’ll show with MySQL. In the event you’re utilizing MariaDB, you may want to regulate the way you log into the console ever so barely.

Prepared? Let’s go!

How one can backup your database

That is so extremely straightforward, you will not imagine it. 

First, let’s be sure that we all know what the title of the database is we’re trying to backup. Log in to the MySQL server with the command:

sudo mysql -u root -p

As soon as within the console, listing your databases with:


The above command will listing each database you have got on the server. Make word of the database you wish to again up and exit from the console with:


To again up that database subject the command:

sudo mysqldump DATABASE > DATBASE-backup.sql

The place DATABASE is the title of the database to be backed up.

And there you go, you have backed up your database.

How one can arrange a each day backup

Let’s use cron to create a backup that may run at 1 AM each day. Open your crontab file for modifying with the command:

crontab -e

On the backside of that file, we’ll add the road:

00 01 * * * mysqldump -u root -p PASSWORD DATABASE > /residence/USER/DATBASE-backup.sql

The place:

  • PASSWORD is your MySQL root consumer password.
  • DATABASE is the database to be backed up.
  • USER is a username in your Linux system.

Save and shut the crontab file. Now, your MySQL database will probably be backed up each day at 1 a.m. into the /residence/USER listing.

How one can restore your database

OK, so as an example catastrophe has struck, and you must restore your web site. You’ve got returned all of the information and configurations to their rightful place (because of a backup you created) and now it is time to restore the database from the backup. For this, you may subject the command (from throughout the listing housing your .sql backup file):

sudo mysql DATABASE < DATABASE-backup.sql

The place DATABASE is the title of the database you backed up.

The restore command will take significantly longer than the backup command, so please be sure that to present it time.

And that is all there’s to backing up and restoring a database on your web site. It is an extremely straightforward however essential activity that you simply hope you may by no means have to fret about. However simply since you hope that eventuality won’t ever come, does not imply you should not all the time be ready.

Subscribe to TechRepublic’s How To Make Tech Work on YouTube for all the newest tech recommendation for enterprise professionals from Jack Wallen.

Additionally see

Recent Articles


Related Stories

Leave A Reply

Please enter your comment!
Please enter your name here

Stay on op - Ge the daily news in your inbox