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
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
See the image below:
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!