How to Easily Backup MySQL Database

John D K -

In this short tutorial, we'll see how to easily backup MySQL Database. We will cover two ways which work on Linux and Windows.

To backup and restore MySQL database we can use the following commands:

(1) Create a backup in MySQL - all databases

mysqldump -u database_username -p --all-databases > full_backup.sql

(2) Create MySQL backup - single db

mysqldump -u db_name -p database_name > db_name_backup_file.sql

(3) Create MySQL backup - timestamp

mysqldump db_name -u  username -p > db_name_$(date +%Y%m%d).sql

(4) Restore MySQL backup

mysql -u  database_username  database_name -p < full_backup.sql

1. Overview

There are different types of backups. In this article we will do full logical backup - SQL script for creation of the same DataBase - like table structure and data.

2. Backup MySQL with mysqldump

MySQL has a backup utility called mysqldump which is installed by default. It creates a dump SQL file with content of some or all databases.

2.1 Backup all MySQL databases

To create MySQL backup with in one line use:

mysqldump --user root --password  --all-databases > full_backup.sql

2.2 Backup single MySQL database

To do backup of a single database use the following syntax:

mysql --user root --password db_name > database.sql

2.3 Recover MySQL database

To recover single or all MySQL databases we can use syntax like:

mysql --user root --password mysql < full_backup.sql

Since the dump file is a SQL script it can be used with different MySQL versions. Be careful that in some cases this might lead to inconsistencies.

3. Backup MySQL using HeidiSQL

HeidiSQL is a free and easy to use tool. It works both on Linux and Windows and can be installed from here: Download HeidiSQL 11.3. For Linux it can be installed with wine.

To backup MySQL database use these steps:

  • Right click the database
  • Export as SQL
  • Select database(s)
  • Select export options
    • Select Create tables
    • Data - select Insert if you like to backup your data
    • Output - select the output - clipboard, SQL script or database
  • Export

See the image below:

4. Conclusion

This article shows how to do a quick and full backup of your MySQL DataBase. We saw two different ways - by mysqldump tool or by using a free UI app.

P.S. Thank you for your feedback! Really appreciate it!