How to Backup MySQL Database or Table using Command Line
Regularly backing up your MySQL or MariaDB databases is crucial for data protection and disaster recovery. In this comprehensive blog post, we will guide you through various backup scenarios using the command line interface. By following these steps, you can ensure the safety and integrity of your valuable data.
Steps to Backup MySQL Database or Table using Command Line
- Backup a Single MySQL Database
- Backup Multiple MySQL Databases
- Backup All MySQL Databases
- Backup MySQL Database Structure Only
- Backup MySQL Database Data Only
- Backup a Single Table of a Database
- Backup Multiple Tables of a Database
- Backup Remote MySQL Database
1. Backup a Single MySQL Database
To back up a single database, use the mysqldump command with the database name specified. Execute the following command:
1 | mysqldump -u username -p database_name > backup.sql |
Replace username with your MySQL/MariaDB username and database_name with the name of the database you want to back up. Press Enter after typing the command.
2. Backup Multiple MySQL Databases
To back up multiple databases, list the database names separated by spaces in the mysqldump command. Execute the following command:
1 | mysqldump -u username -p database1 database2 > backup.sql |
Replace username with your MySQL/MariaDB username and database1 and database2 with the names of the databases you want to back up. Press Enter after typing the command.
3. Backup All MySQL Databases
To back up all databases on the MySQL/MariaDB server, use the --all-databases option with the mysqldump command. Execute the following command:
1 | mysqldump -u username -p --all-databases > backup.sql |
Replace username with your MySQL/MariaDB username. Press Enter after typing the command.
4. Backup MySQL Database Structure Only
To back up only the structure of a MySQL database without data, add the --no-data option to the mysqldump command. Execute the following command:
1 | mysqldump -u username -p --no-data database_name > backup.sql |
Replace username with your MySQL/MariaDB username and database_name with the name of the database. Press Enter after typing the command.
5. Backup MySQL Database Data Only
To back up only the data of a MySQL database without the structure, add the --no-create-info option to the mysqldump command. Execute the following command:
1 | mysqldump -u username -p --no-create-info database_name > backup.sql |
Replace username with your MySQL/MariaDB username and database_name with the name of the database. Press Enter after typing the command.
6. Backup a Single Table of a Database
To back up a specific table within a database, use the mysqldump command with the --tables option. Execute the following command:
1 | mysqldump -u username -p --tables database_name table_name > backup.sql |
Replace username with your MySQL/MariaDB username, database_name with the name of the database, and table_name with the name of the table you want to back up. Press Enter after typing the command.
7. Backup Multiple Tables of a Database
To back up multiple tables within a database, list the table names separated by spaces in the mysqldump command. Execute the following command:
1 | mysqldump -u username -p --tables database_name table1 table2 > backup.sql |
Replace username with your MySQL/MariaDB username, database_name with the name of the database, table1 and table2 with the names of the tables you want to back up. Press Enter after typing the command.
8. Backup Remote MySQL Database
To back up a remote MySQL database, include the host and port information in the mysqldump command. Execute the following command:
1 | mysqldump -u username -p -h remote_host -P port database_name > backup.sql |
Replace username with your MySQL/MariaDB username, remote_host with the IP address or hostname of the remote server, port with the port number of the remote server (default is 3306), and database_name with the name of the database. Press Enter after typing the command.
Conclusion
In this comprehensive blog post, we have explained various backup scenarios for MySQL/MariaDB databases using the command line interface. By following the steps outlined above, you can confidently perform backups and ensure the safety of your valuable data. Regular backups are vital for data protection, disaster recovery, and maintaining the integrity of your databases. Remember to store the backup files securely in a separate location to prevent data loss.
