Rate this article: (7 votes, average: 1.71)
If you witness scenes like this in your IT department, then two scenarios are highly likely: either there’s a security breach or data has vanished from the web server. Almost every website administrator has experienced both situations. The latter is sometimes more painful because the website admin could have most likely done something about it. But they didn’t, and that results in painful regret. You never want to be in that position, do you? Of course not, and that’s why you’re here.
So, let’s do what you’re here for: implementing a MySQL backup restore. First, let’s learn how to take MySQL backups.
CodeGuard comes with amazing features like daily Automatic Backups, One-Click Restore, Malware Scanning, and Advanced 256-bit AES encryption.
Shop CodeGuard Backup Solution
As far as backups in MySQL is concerned, many people have this misconception that you need an external backup tool to take backups. Well, you absolutely don’t. That’s because there’s an inbuilt utility provided by MySQL called “mysqldump.” This utility allows you to take backups in a blink of an eye (well, almost!).
Here’s the syntax to back up your MySQL database:
sudo mysqldump -u [user] -p [database_name] > [filename].sql
To backup your entire database:
mysqldump --all-databases --single-transaction --quick --lock-tables=false > full-backup-$(date +%F).sql -u root –p
To backup multiple databases:
sudo mysqldump -u [user] -p [database_1] [database_2] [database_etc] > [filename].sql
Note: You must replace the username, database_name and backup_file_name with the correct details. You’re also supposed to enter your database password whenever prompted to do so.
Why do one thing over and over again when you can automate the entire process? Well, you can also automate the MySQL backup process and stay stress-free. Cool, isn’t it? However, you’ll need to download a utility to automate the backup process.
Enter the below syntax to download the utility that will automate your backup process:
$ sudo apt-get install automysqlbackup
To take on-demand backups, run the command below:
$ sudo automysqlbackup
Run the following command to specify the content of the scheduled MySQL backups:
$ sudo ls -a /var/lib/automysqlbackup/daily
Just like the backup process, restoring backups in MySQL is smooth as good cake batter. Here again, the mysqldump utility will come to your rescue. Follow the two steps below to restore lost MySQL databases.
First, you must create a new empty database on the system that hosts the database. Please make sure to name it the same as the database you had lost. Mysqldump will restore your lost data in this database.
Enter the below command to implement a MySQL backup restore:
mysql -u [user] -p [database_name] < [filename].sql
Note: You must replace the username, database_name, and backup_file_name with the correct details. You’re also supposed to enter your database password wherever prompted to do so.
You just learned how you can backup and restore your MySQL database by yourself. But did you know you can automate the entire backup process and secure the backed-up data using encryption and malware scanner, all at the cost of less than a bagel? You heard that right, CodeGuard by Sectigo offers you the ability to automate your database, filesystem or website backups and protect it using 256-bit AES encryption and Malware scanner. If you’re a network/website administrator, then you definitely need this tool!