If you’ve never backed up your MySQL or MariaDB databases, now’s the time. Jack Wallen shows you how to back up that data and restore it, in case your website has met an untimely demise.
Image: iStock/GaudiLab
Your company or personal website is running like a champ. It serves your community and customers, and your audience and clientele grow daily. That website serves a very important service to you. Not only is it a gateway to your company, it probably allows your customers to purchase products and services.
SEE: 40+ open source and Linux terms you need to know (TechRepublic Premium)
But one day …
You hate to even think about it, don’t you?
One day something’s going to go wrong. When it does, you’d best hope you have a backup. That backup must not only include the files and configurations for your site but the data housed in the database.
That means you need to be able to backup and restore your database. But how? I’m going to show you.
What you’ll need
To make this work, you’ll need a website that is powered by either the MySQL or MariaDB database server. You’ll also need a user with sudo privileges. I’m going to demonstrate with MySQL. If you’re using MariaDB, you’ll need to adjust how you log into the console ever so slightly.
Ready? Let’s go!
How to backup your database
This is so incredibly easy, you won’t believe it.
First, let’s make sure we know what the name of the database is we’re looking to backup. Log in 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 every database you have on the server. Make note of the database you want to back up and exit from the console with:
exit
To back up that database issue the command:
sudo mysqldump DATABASE > DATBASE-backup.sql
Where DATABASE is the name of the database to be backed up.
And 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 AM every day. Open your crontab file for editing with the command:
crontab -e
At the bottom of that file, we’ll add the line:
00 01 * * * mysqldump -u root -p PASSWORD DATABASE > /home/USER/DATBASE-backup.sql
Where:
- PASSWORD is your MySQL root user password.
- DATABASE is the database to be backed up.
- USER is a username on your Linux system.
Save and close the crontab file. Now, your MySQL database will be backed up every day at 1 a.m. into the /home/USER directory.
How to restore your database
OK, so let’s say disaster has struck, and you need to restore your website. You’ve returned all the files and configurations to their rightful place (thanks to a backup you created) and now it’s time to restore the database from the backup. For this, you’ll issue the command (from within the directory housing 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 considerably longer than the backup command, so please make sure to give it time.
And that’s all there is to backing up and restoring a database for your website. It’s an incredibly easy but crucial task that you hope you’ll never have to worry about. But just because you hope that eventuality will never come, doesn’t mean you shouldn’t always be prepared.
Subscribe to TechRepublic’s How To Make Tech Work on YouTube for all the latest tech advice for business pros from Jack Wallen.
Open Source Weekly Newsletter
You don’t want to miss our tips, tutorials, and commentary on the Linux OS and open source applications.
Delivered Tuesdays
Also see
For all the latest Technology News Click Here
For the latest news and updates, follow us on Google News.
