How to Backup MySQL Databases with mysqldump

Introduction

MySQL databases are the backbone of many web applications. They store critical data that your application relies on to function correctly. Regularly backing up your MySQL databases is essential for protecting your data from accidental deletion, hardware failure, or other unforeseen circumstances.

This guide will show you how to use the mysqldump utility to create backups of your MySQL databases for cPanel / WHM hosted on VPS, Cloud or Dedicated Servers. mysqldump is a command-line tool included with the MySQL server that allows you to dump the contents of a database or multiple databases into a SQL file. This SQL file can then be used to restore your database if necessary.

Backing Up All Databases into One Archive

The first method we’ll explore involves backing up all of your MySQL databases into a single archive file. This is a good option if you want to create a complete backup of all your data.

To back up all of your MySQL databases into one archive file, follow these steps:

1. Open a terminal window.

2. Log in to your MySQL server using the following command: (Not mandatory)

mysql -u username -p password

3. Once you are logged in, run the following command to back up all databases:

mysqldump --all-databases > all_databases.sql

NOTE: This command will use mysqldump to dump all databases on your server into a file named all_databases.sql. The --all-databases option tells mysqldump to back up all databases.

• To back up all databases into one large compressed archive via SSH (or WHM’s Terminal feature) as the root user: Use the below command (Single command for full task).

/usr/bin/mkdir -v /home/mysqlbackup/ ; /usr/bin/echo "Generating full MySQL backup..." ; /usr/bin/mysqldump --events --routines --triggers --all-databases | /usr/bin/gzip -9 > /home/mysqlbackup/"$(date +%F_%T)"_mysql_backup.sql.gz ; /usr/bin/echo "Complete."

• To individually back up each database into its own compressed archive:

/usr/bin/mkdir -v /home/mysqlbackup/ ; for DB in $(mysql -Be "show databases" | /usr/bin/grep -v 'row\|information_schema\|Database') ; do echo "Generating MySQL backup of $DB" ; /usr/bin/mysqldump --skip-lock-tables --events --routines --triggers ${DB} | /usr/bin/gzip -9 > /home/mysqlbackup/"$(date +%F_%T)"_${DB}.sql.gz ; done; /usr/bin/echo "Complete."

4. To compress the backup file, you can use the gzip command. Run the following command to compress the all_databases.sql file:

gzip all_databases.sql

Backing Up Each Database Individually

The second method we’ll explore involves backing up each of your MySQL databases individually. This is a good option if you only need to back up specific databases or if you want to have separate backups for each database.

To back up each of your MySQL databases individually, follow these steps:

1. Open a terminal window.

2. Log in to your MySQL server using the following command: (Not mandatory)

mysql -u username -p password

3. Once you are logged in, run the following command to back up a specific database:

mysqldump database_name > database_name.sql

Replace database_name with the name of the database you want to back up.
This command will use mysqldump to dump the specified database into a file named database_name.sql.

4. You can compress the backup file using the gzip command as described in the previous method.

Restoring a MySQL Database

If you need to restore a MySQL database, you can use the mysql command along with the SQL file you created from the mysqldump command. Here’s an example:

mysql -u username -p password < database_name.sql

Replace username with your MySQL username, password with your MySQL password, and database_name.sql with the name of the SQL file containing the database backup.

Conclusion

Backing up your MySQL databases is an essential part of any database management strategy. By following the steps outlined in this guide, you can easily create backups of your MySQL databases using the mysqldump utility. These backups can then be used to restore your databases in case of data loss.