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

2 years ago 574

If you've ne'er backed up your MySQL oregon MariaDB databases, now's the time. Jack Wallen shows you however to backmost up that information and reconstruct it, successful lawsuit your website has met an untimely demise.

Your institution oregon idiosyncratic website is moving similar a champ. It serves your assemblage and customers, and your assemblage and clientele turn daily. That website serves a precise important work to you. Not lone is it a gateway to your company, it astir apt allows your customers to acquisition products and services.

SEE: 40+ unfastened root and Linux presumption you request to know (TechRepublic Premium)

But 1 time …

You hatred to adjacent deliberation astir it, don't you?

One time something's going to spell wrong. When it does, you'd champion anticipation you person a backup. That backup indispensable not lone see the files and configurations for your tract but the information housed successful the database.

That means you request to beryllium capable to backup and reconstruct your database. But how? I'm going to amusement you.

What you'll request

To marque this work, you'll request a website that is powered by either the MySQL oregon MariaDB database server. You'll besides request a idiosyncratic with sudo privileges. I'm going to show with MySQL. If you're utilizing MariaDB, you'll request to set however you log into the console ever truthful slightly.

Ready? Let's go!

How to backup your database

This is truthful incredibly easy, you won't judge it.

First, let's marque definite we cognize what the sanction of the database is we're looking to backup. Log successful to the MySQL server with the command:

sudo mysql -u basal -p

Once successful the console, database your databases with:

SHOW DATABASES;

The supra bid volition database each database you person connected the server. Make enactment of the database you privation to backmost up and exit from the console with:

exit

To backmost up that database contented the command:

sudo mysqldump DATABASE > DATBASE-backup.sql

Where DATABASE is the sanction of the database to beryllium backed up.

And determination you go, you've backed up your database.

How to acceptable up a regular backup

Let's usage cron to make a backup that volition tally astatine 1 AM each day. Open your crontab record for editing with the command:

crontab -e

At the bottommost of that file, we'll adhd the line:

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

Where:

PASSWORD is your MySQL basal idiosyncratic password.
DATABASE is the database to beryllium backed up.
USER is simply a username connected your Linux system.

Save and adjacent the crontab file. Now, your MySQL database volition beryllium backed up each time astatine 1 a.m. into the /home/USER directory.

How to reconstruct your database

OK, truthful let's accidental catastrophe has struck, and you request to reconstruct your website. You've returned each the files and configurations to their rightful spot (thanks to a backup you created) and present it's clip to reconstruct the database from the backup. For this, you'll contented the bid (from wrong the directory lodging your .sql backup file):

sudo mysql DATABASE < DATABASE-backup.sql

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

The reconstruct bid volition instrumentality considerably longer than the backup command, truthful delight marque definite to springiness it time.

And that's each determination is to backing up and restoring a database for your website. It's an incredibly casual but important task that you anticipation you'll ne'er person to interest about. But conscionable due to the fact that you anticipation that eventuality volition ne'er come, doesn't mean you shouldn't ever beryllium prepared.

Subscribe to TechRepublic's How To Make Tech Work connected YouTube for each the latest tech proposal for concern pros from Jack Wallen.