How to Use mysqldump Command to Backup MySQL Databases (with examples)

The mysqldump command is a widely used tool for creating backups of MySQL databases. It allows you to easily create a snapshot of the database structure and data, which can be used for restoration or for transferring the database to another server.

In this article, we will explore eight different use cases of the mysqldump command, along with code examples and explanations for each.

1. Create a backup (user will be prompted for a password)

mysqldump --user user --password database_name --result-file=path/to/file.sql

• Motivation: This use case allows you to create a backup of a specific MySQL database.
• Explanation:
  • --user: Specifies the MySQL username used for authentication.
  • --password: Prompts the user to enter the password for the specified MySQL user.
  • database_name: Specifies the name of the database to backup.
  • --result-file=path/to/file.sql: Specifies the file path where the backup will be saved.
• Example Output: The specified MySQL database will be backed up and saved to the specified file.

2. Backup a specific table redirecting the output to a file (user will be prompted for a password)

mysqldump --user user --password database_name table_name > path/to/file.sql

• Motivation: This use case is helpful when you only need to backup a specific table rather than the entire database.
• Explanation:
  • --user: Specifies the MySQL username used for authentication.
  • --password: Prompts the user to enter the password for the specified MySQL user.
  • database_name: Specifies the name of the database to backup.
  • table_name: Specifies the name of the table to backup.
  • > path/to/file.sql: Redirects the output of the command to the specified file.
• Example Output: The specified table within the MySQL database will be backed up and saved to the specified file.

3. Backup all databases redirecting the output to a file (user will be prompted for a password)

mysqldump --user user --password --all-databases > path/to/file.sql

• Motivation: This use case is useful when you want to backup all the databases present in the MySQL server.
• Explanation:
  • --user: Specifies the MySQL username used for authentication.
  • --password: Prompts the user to enter the password for the specified MySQL user.
  • --all-databases: Instructs mysqldump to backup all databases.
  • > path/to/file.sql: Redirects the output of the command to the specified file.
• Example Output: All databases present in the MySQL server will be backed up and saved to the specified file.

4. Backup all databases from a remote host, redirecting the output to a file (user will be prompted for a password)

mysqldump --host=ip_or_hostname --user user --password --all-databases > path/to/file.sql

• Motivation: This use case is helpful when you need to backup all databases on a remote MySQL server.
• Explanation:
  • --host=ip_or_hostname: Specifies the IP address or hostname of the remote MySQL server.
  • --user: Specifies the MySQL username used for authentication.
  • --password: Prompts the user to enter the password for the specified MySQL user.
  • --all-databases: Instructs mysqldump to backup all databases.
  • > path/to/file.sql: Redirects the output of the command to the specified file.
• Example Output: All databases present in the remote MySQL server will be backed up and saved to the specified file.

These four use cases cover different scenarios of using the mysqldump command to create backups of MySQL databases. By applying these methods, you can ensure the safety and availability of your database data.