How Can I Export MySQL Databases from the Command Line?

Exporting MySQL Databases via the Command Line

When transitioning away from services that may require advanced system administration skills, it becomes necessary to have strategies in place for exporting data from crucial platforms. In this specific instance, we will explore how to export the contents of a MySQL database from the command line.

Solution: Employing mysqldump

To successfully extract the contents of a MySQL database, utilize the mysqldump command-line utility. By leveraging mysqldump, you can create a dump file in SQL format, encompassing either the entire database, specific databases, or particular tables within a database.

Executing mysqldump

The following examples demonstrate how to use mysqldump depending on your specific requirements:

• Exporting an Entire Database: Simply execute the following command, replacing [uname] with your database username and db_name with the target database name:

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

• Exporting All Databases: To dump all databases within the MySQL instance, run this command:

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

• Exporting Specific Tables: You can also dump specific tables using this command, replacing db_name with the database name and table1 and table2 with your desired table names:

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

• Auto-Compressing Output with Gzip: For large databases, consider compressing the output dump file using gzip:

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

• Remotely Exporting Databases: If the MySQL server resides on a different machine accessible via SSH, use this command, replacing [ip_address] with the remote server's IP, [uname] with your username, and db_name with the target database:

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

The exported .sql file will be generated in the directory where you execute the commands.

Security Enhancement

For enhanced security, avoid embedding your password directly in the command string. Instead, use the -p option followed by an empty string to prompt the system for your password without recording it.

The above is the detailed content of How Can I Export MySQL Databases from the Command Line?. For more information, please follow other related articles on the PHP Chinese website!