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

Question

By default, mysqldump will be taking the backup of an entire database. I want to back up a single table in the MySQL. 

Will it be possible? If so how can I restore it?

Answer 1

Dumping and restoring 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

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

Dumping and restoring a single table from a compressed (.sql.gz) format:

Dump

mysqldump db_name table_name | gzip > table_name.sql.gz

Restore

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

Want to Learn SQL to get expertise in the concepts of SQL? Join the SQL Course and get certified.

Watch the below MySQL video tutorial to gain more knowledge on MySQL

Answer 2

In fact, you can use mysqldump to perform smart backups. If you want to backup a table, you can use mysqldump like this:

bash code 

mysqldump -u [username] -p [database_name] [table_name] > [ data_backup .txt] SQL ]

For example, if your database name is my_database and you need to backup a table named my_table, you can store the backup in a file named my_table_backup.sql like this:

bash code 

mysqldump -u root -p my_database my_table> my_table_backup.sql

Restore table

You can optionally use the mysql command to restore the table like this:

Option 1: Use the SOURCE command to edit the table data:

bash code

mysql -u root -p my_database

Log in and run:

SQL code 

SOURCE /path/to/my_table_backup.sql; >

Option 2: Restore the table directly with a command without entering the MySQL shell:

bash code 

mysql -u root -p my_database < my_table_backup.sql

This will read the contents of my_table_backup.sql and restore my_table to my_database.