How to Backup and Restore MySQL / MariaDB Data for a Website

0


If you’ve never backed up your MySQL or MariaDB databases, now is the time. Jack Wallen shows you how to backup this data and restore it, in case your website goes down prematurely.

Image: iStock / Gaudi Laboratory

Your business or personal website works like a champ. It serves your community and your customers, and your audience and clientele are growing daily. This website is doing you a very important service. Not only is it a gateway to your business, but it probably allows your customers to purchase products and services.

SEE: Over 40 Open Source & Linux Terms You Need To Know (TechRepublic Premium)

But one day …

You hate to even think about it, don’t you?

Something will go wrong someday. When it does, you better hope you have a backup. This backup should not only include your site’s files and configurations, but the data hosted in the database.

This means that you should be able to back up and restore your database. But how? I will show you.

What you will need

For this to work you will need a website powered by the MySQL or MariaDB database server. You will also need a user with sudo privileges. I will do a demonstration with MySQL. If you are using MariaDB, you will need to adjust the way you log into the console very slightly.

Ready? Let’s go!

How to back up your database

It’s so incredibly easy, you won’t believe it.

First, let’s make sure we know the name of the database we’re looking to back up. Connect to the MySQL server with the command:

sudo mysql -u root -p

Once in the console, list your databases with:

SHOW DATABASES;

The above command will list all the databases you have on the server. Note the database you want to back up and exit the console with:

exit

To back up this database, run the command:

sudo mysqldump DATABASE > DATBASE-backup.sql

Where DATABASE is the name of the database to back up.

There you go, you’ve backed up your database.

How to set up a daily backup

Let’s use cron to create a backup that will run at 1 a.m. everyday. Open your crontab file to modify it with the command:

crontab -e

At the bottom of this file we will add the line:

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

Or:

  • PASSWORD is your MySQL root user password.
  • DATABASE is the database to back up.
  • USER is a username on your Linux system.

Save and close the crontab file. From now on, your MySQL database will be backed up every day at 1 a.m. in the / home / USER directory.

How to restore your database

Okay, let’s say a disaster has happened and you need to restore your website. You’ve put all the files and configurations back in their place (thanks to a backup you created) and now it’s time to restore the database from the backup. To do this, you will run the command (from the directory containing your .sql backup file):

sudo mysql DATABASE < DATABASE-backup.sql

Where DATABASE is the name of the database you backed up.

The restore command will take much longer than the backup command, so be sure to give it time.

And that’s all it takes to backup and restore a database for your website. It’s an incredibly easy but crucial task that you hope you never have to worry about. But just because you hope this eventuality never happens doesn’t mean you shouldn’t always be prepared.

Subscribe to TechRepublic How to make technology work on YouTube for all the latest technical advice for professionals at Jack Wallen’s business.

Also look


Share.

Comments are closed.