Backing Up MySQL Databases

MySQL is the most popular software in the world used for databases. Making sure that you have backups of your database is very important. This practice allows you to recover your database in the event of data loss. Data loss can occur with hardware failure, or software changes that break the database. You can restore your database anytime once you have a backup of it.

In this tutorial, we'll export a MySQL database using mysqldump.

Step 1: Exporting your database

The syntax for using the mysqldump command for exporting a database is:

mysqldump -u username -p database > name.sql

This says:

• We will access the database with the username user.
• Mysqldump can ask us for the password of username (-p).
• The name of the database is database.
• We are going to back up this database to an SQL file called name.sql.

You now have an SQL file with your whole database.

Step 2: Restoring your database

When something went wrong, we can restore our database easily. Simply log in to MySQL:

mysql -u username -p

Then, create a new database:

CREATE DATABASE newdb;

Exit MySQL:

exit

Then you can import the SQL file to the new database:

mysql -u username -p newdb < name.sql

This says:

• We will access the database with the username user.
• MySQL can ask us for the password of username (-p).
• The name of the database is newdb.
• We are going to import from an SQL file called name.sql.

Congratulations! You have now imported your SQL file to a new database!