MySQL Backup Database: How to Backup MySQL Database in Linux and Windows

A simple step-by-step guide to the MySQL backup database process in Linux & Windows

People running around, someone screaming in disbelief, people gathering at one computer… if you see these scenes inside a company’s IT department, then you’re likely witnessing the reaction to an unexpected data loss from the server. As any website administrator will say “you don’t want to be a part of that!” That’s why taking regular MySQL backups is the simplest, yet the most effective way you can save yourself from being part of a similar scene of chaos. This article will guide you on how to take backups in Linux as well as in Windows aka the MySQL backup database process. Let’s get going!

Take MySQL Backups in Linux/Ubuntu Using “mysqldump” Command

The MySQL backup database process in Linux/Ubuntu is a relatively simple process. It can be divided into two significant steps: the first of them is applying the mysqldump command, and the second one is automating the backup process. Let’s see how you can apply both of these steps and take MySQL backups in a flash.

Step 1: Apply the mysqldump Command

Mysqldump command is a great utility provided by MySQL for backing up the databases. Thanks to it, you don’t need any 3^rd-party external tool or software. This is the mysqldump command/syntax that you’ll need to apply:

$ mysqldump -u username -p database_name > backup_file_name.sql

Note: Please don’t forget to change the username, database_name, and backup_file_name to correct details. You must also enter your database password if prompted to do so.

Step 2: Automate the Backup Process

We know how boring it can be to take manual MySQL database backups time after time. That’s why it’s a wise choice to automate this process in such a way that it happens regularly. For that, you’ll need to download a tool called “automysqlbackup.”

Here’s the command you need to enter to download the tool:

$ sudo apt-get install automysqlbackup

Once the tool has been downloaded, run the below command to run the automysqlbackup tool:

$ sudo automysqlbackup

Now enter the following command to specify the content of the scheduled MySQL backups and schedule them daily:

$ sudo ls -a /var/lib/automysqlbackup/daily

Voila! You have just scheduled automatic daily backups on MySQL. Now you can sit back, relax, and watch your favorite tv show without fearing data loss. Awesome, isn’t it?

How to Take MySQL Backups in a Windows Server Using MySQL Backup Script

If you’ve ever wondered how to take MySQL backups in Windows, then wonder no more! You’re about to find out that it’s a much simpler process than you ever thought. In just 9 straightforward steps, you’ll have backed up databases on your Windows server.

• First of all, open Notepad and save the following MySQL backup script.

@echo off
For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c-%%a-%%b)
For /f "tokens=1-2 delims=/:" %%a in ("%TIME%") do (set mytime=%%a%%b)

SET backupdir=C:\xampp\htdocs\backup
SET mysqluername=root
SET mysqlpassword=somepassword
SET database=dbname

C:\xampp\mysql\bin\mysqldump.exe -uroot -pPASSWORD %database% > %backupdir%\%database%_%mydate%_%mytime%_.sql

Note: Please specify the SET parameters and the mysqldump.exe location as per your configuration.

• Go to Start > All Programs > Accessories > Command Prompt to open Command Prompt.
• Type Taskschd.msc in the Command Prompt
• Opt for the Create Task… the option listed under Actions pane on the right-hand side of the screen.
• Now, choose the user that has all the admin rights.
• Mark ticks for two choices: Run whether the user is logged on or not and Run with highest privileges.
• Click on the Triggers tab you see at the top of the screen.
• At last, under the Actions tab, select the batch file that we created in the first step.
• Click OK.

Congratulations! You have just set up MySQL database backups on your Windows server.

Sectigo CodeGuard: One Stop Solution to All MySQL Database Backup Worries

Taking manual MySQL backups can be a real pain and securing the backed-up data can be even more of a pain. Why not go for an industry-leading backup solution that not only takes your backups on its own but protects it as well? We’re talking about CodeGuard, a backup solution offered by Sectigo (formerly known as ‘Comodo’).

CodeGuard helps you safeguard your data using one-click on-demand backups as well as scheduled incremental automatic backups. A big advantage that CodeGuard offers is the security it provides for backed up data. CodeGuard protects the data using 256-bit AES encryption and has an in-built malware scanner that keeps malware from infecting your data. Moreover, CodeGuard works with almost every type of database, content management system, and file system.