Search code examples
mysqlbackuprestore

How to take backup of a single table in a MySQL database?

By default, mysqldump takes the backup of an entire database. I need to backup a single table in MySQL. Is it possible? How do I restore it?

Solution

  • Dump and restore a single table from .sql

    Dump

    mysqldump db_name table_name > table_name.sql

    Dumping from a remote database

    mysqldump -u <db_username> -h <db_host> -p db_name table_name > table_name.sql

    For further reference:

    http://www.abbeyworkshop.com/howto/lamp/MySQL_Export_Backup/index.html

    Restore

    mysql -u <user_name> -p db_name
mysql> source <full_path>/table_name.sql

    or in one line

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

    Dump and restore a single table from a compressed (.sql.gz) format

    Credit: John McGrath

    Dump

    mysqldump db_name table_name | gzip > table_name.sql.gz

    Restore

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