Export MySQL Database Contents Using Command Line

Background:

Exporting the contents of a MySQL database is a crucial task when migrating or backing up data. This article explores how to achieve this from the command line, providing flexible options for exporting specific databases, tables, or all databases at once.

Using mysqldump Command:

The mysqldump command is specifically designed for exporting MySQL database content. Here's how you can use it:

• Export an Entire Database:

  $ mysqldump -u [username] -p db_name > db_backup.sql

• Export All Databases:

  $ mysqldump -u [username] -p --all-databases > all_db_backup.sql

• Export Specific Tables within a Database:

  $ mysqldump -u [username] -p db_name table1 table2 > table_backup.sql

Auto-Compressing Output:

For large databases, auto-compressing the output using gzip can save space. Use the following command:

$ mysqldump -u [username] -p db_name | gzip > db_backup.sql.gz

Remote Export:

If the MySQL server is on a remote machine within your network, you can connect to it using the host IP address and port, as follows:

$ mysqldump -P 3306 -h [ip_address] -u [username] -p db_name > db_backup.sql

Security Recommendation:

To enhance security, avoid including passwords directly in CLI commands. Instead, use the -p option without the password. The command will prompt you for the password and not record it in the history.

By utilizing these techniques, you can efficiently export MySQL database content from the command line, ensuring that your data is secure and accessible for future use.