Backup Database in MySQL | MySql Backup FTP

As it 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 database in MySQL? Of course, from one hand you can backup database in MySQL using Command line (mysqldump) and from another hand, the best and the easiest way to backup database in MySQL is to use MySQLBackupFTP tool. Now let’s learn how to backup database in MySQL using all three different methods.

Backup Database in MySQL Using Command Line (mysqdump)

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 it 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 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 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

Use the –database option if you need to backup more than one MySQL database. Please note, it is necessary to separate by space each MySQL database’s name.

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

Use the –all-databases option is you need to backup all the databases on the server at one time.

$ 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]

If you prefer to extract the .gz file, use the next command:

$ gunzip [backupfile.sql.gz]

Backup Database in MySQL Using MySQLBackupFTP

Follow this simple steps and create MySQLBackupFTP job which will backup database in MySQL according to your schedule and send backups to selected destination place.

Download, install and connect MySQLBackupFTP to your MySQL Server. To do it press “Connect to MySQL Server” 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 MySQL Connections” to check the connections.
Tick off all MySQL database which you are going to backup. You can find them in the list on the left side.
Select a destination place. You can do it by clicking “Add backup destination” link. In the opened window select a destination where you want to backup database in MySQL.
Set up MySQL database backup schedule. To do it tick off “Schedule this job” and then press “Advanced backup schedule”. In the “Advanced Backup Schedule” window create a backup schedule click “Save & Close”.
That’s it. Your MySQLBackupFTP backup job is ready. All you need is to save it. Press “Save” and the backup job will run according to the selected backup schedule. If you need to run it immediately, click “Run Now”.