How Can I Selectively Backup and Restore MySQL Tables?

Selective MySQL Table Backup and Restoration

Managing vast databases often necessitates selective backups of individual tables. MySQL offers a flexible solution for such scenarios, allowing users to isolate and retrieve specific table data.

Dumping a Single Table

To initiate a single-table backup, execute the mysqldump command with the following syntax:

mysqldump db_name table_name > table_name.sql

This command instructs mysqldump to create a SQL dump file containing only the specified table's data.

Restoring the Table

Restoration of the table can be achieved using the following methods:

• Direct Load:

mysql -u username -p db_name < table_name.sql

• One-Line Command:

mysql -u username -p db_name < /path/to/table_name.sql

Alternative Backup Option: Compressed Format

For space optimization, you can opt for compressed backup using the following syntax:

• Dump:

mysqldump db_name table_name | gzip > table_name.sql.gz

• Restore:

gunzip < table_name.sql.gz | mysql -u username -p db_name

These methods provide flexibility and efficiency when backing up and restoring individual tables in MySQL databases.

The above is the detailed content of How Can I Selectively Backup and Restore MySQL Tables?. For more information, please follow other related articles on the PHP Chinese website!