How Can I Efficiently Export MySQL Data Using the Command Line?

Exporting MySQL Data from the Command Line

If you need to move data out of your MySQL database, the mysqldump command-line function is a powerful tool. With this utility, you can export entire databases, specific tables, or even all databases in a single command.

Exporting an Entire Database

To export an entire database named 'db_name' to a file named 'db_backup.sql', run the following command:

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

Exporting All Databases

If you want to export all databases, use the '--all-databases' flag:

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

Exporting Specific Tables

To export specific tables, list them after the database name:

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

Compressing the Output

For large databases, you can compress the output using gzip:

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

Remote Export

If the MySQL server is remote, specify the IP address and port:

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

Note:

For security reasons, it's recommended to avoid including the password in the command line. Instead, use the '-p' option without the password and enter it when prompted.

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