Backing up database is essential to eliminate the risk of losing data whenever there is a hardware failure or software issues. In this post, I’ll be showing the possible ways to create backups for MySQL databases.
In fact, There are several ways to Backup MySQL databases, you need to evaluate each one and select the best fit into your needs. I’ll list the possible ways, but I’ll be focusing on the data dump approach.
Types of MySQL backups:
- Logical: By using MySqldump utility, you will be able to create a data dump of your database. The Expected output is a .sql file that has all insert statement needed to re-create the database again.
- Physical: by coping the actual database files into another location. – This approach usually is recommended for huge databases as it is faster to do the backup and faster to restore in case of any failure. The limitation of this approach, is restoring the database has to be on the similar hardware as the original one.
- Partial: Partial backup is meant to have a backup of single table. This is usually useful when you wat to do a bulk operation on that table and you want to create a restore point in case of any failure happened.
For the purpose of this article, I’ll be focusing on the first option; Logical Backup. It is the easiest way as well as it is using a built in tool called mysqldump. This utility tool is being shipped with MySQL engine, Thus it will be installed by default on your MySQL Server. – So no third party tool needed.
You can create a .bat file to do the backup and get benefits of the built-in Windows Task Scheduler to have the backup done on daily basis. Let’s dive into it.
- Creating backups for all databases on my MySQL engine:
mysqldump --user=root --password=******* --result-file="C:\MySQLdump.sql" --all-databasesOn this command line, you are telling mysqldump utility to have a backup of all databases and store it on the C:\MySqlDump.sql file. If you open the result file in any texts editor, you will find a normal sql statements to insert the data.
- Creating Backup for a single database:
mysqldump --user=root --password=******* --result-file="C:\MySQLdump.sql" --[DbName]it is the same command as all databases, but you need to replace the all-databases option with the name of the database that you are looking for backing it up.
Advantages of using data dump approach over other options:
- You are not limited to specific MySQL version. The output of this dump, is normal SQL CRUD operations which means all what you need to do is to connect to the new MySQL engine and execute the datadump file.
- You can edit the datadump file in case you are looking to do few tweaks before executing it again.
- No 3rd party tools are needed. The MysqlDump.exe tool is built in utility and will be installed within MySQL installation.
For more information about mysqldump, please see the MySQL related documentation here