As it is well known that the best way to protect your data safe and sound is to make regular backups. But what is the best way to backup a database in MySQL? Of course, on one hand, you can backup a database in MySQL using Command Prompt (mysqldump) and from another hand, the best and the easiest way to backup a database in MySQL is to use the SQLBackupAndFTP tool. Now let’s learn how to backup a database in MySQL using all three different methods.

Backup Database in MySQL Using Command Line (mysqldump)

The following command connects to the MySQL Server to create an SQL dump file. This dump file contains all SQL statements which are crucial to re-create the database when needed. Find below the syntax:

$ mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]

Where

• [uname] is your MySQL database username
• [pass] is  your MySQL password for your database
• [dbname] is your MySQL database’s name
• [backupfile.sql] is the filename for your MySQL Server database backup
• [–opt]  is the mysqldump option

Assume, you are going to backup a database in MySQL with the name “Adventureworks”, the username is “Admin” without a password to a file db_backup.sql. Use the following syntax:

$ mysqldump -u Admin -p Adventureworks > db_backup.sql

Backup Tables in MySQL

Using the mysqldump command you also can backup database tables. Let’s backup only php_adventureworks and php_adventureworks tables from our database “Adventureworks”. Use the following command to backup MySQL tables:

$ mysqldump -u root -p Adventureworks php_adventureworks php_adventureworks > db_backup.sql

Multiple MySQL Database Backup

$ mysqldump -u Admin -p --databases Adventureworks Archives MyData > dbs_backups.sql

$ mysqldump -u Admin -p --all-databases > alldb_backup.sql

Backup Database in MySQL With Compression

If your MySQL database is big, you might want to compress the backup. To do it use the following syntax:

$ mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]

$ gunzip [backupfile.sql.gz]

Backup Database in MySQL Using SQLBackupAndFTP

Follow these simple steps and create SQLBackupAndFTP job which will backup the databases in MySQL according to your schedule and send backups to the selected destination places.

1. Download, install and connect SQLBackupAndFTP to your MySQL Server. To do it press on the “Gear” button at the “Connect to Database Server” section and select the way to connect to your MySQL Server using TCP / IP or phpMyAdmin. In the opened window specify your credentials and click “Test Connection” to check the connection.
   
2. Tick off all MySQL databases that you are going to backup. You can find the list by clicking the “Gear” button in the “Select databases” section.
3. Select a destination place. You can do it by clicking the “plus” at the “Store backups in the selected destinations” section and choosing destinations where you want to backup MySQL databases.
   
4. Set up MySQL database backup schedule. To do it turn on this option at the “Schedule backups” section and press the “Gear” to find the advanced backup schedule settings.
   
5. That’s it. Your SQLBackupAndFTP backup job is ready and backups will be run according to the schedule. If you need to run it immediately, click “Run Now”.