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?

 Answers

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

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

---

---

try

for line in $(mysql -u... -p... -AN -e "show tables from NameDataBase");
do 
mysqldump -u... -p.... NameDataBase $line > $line.sql ; 
done

• $line cotent names tables ;)

---

You can use easily to dump selected tables using MYSQLWorkbench tool ,individually or group of tables at one dump then import it as follow: also u can add host information if u are running it in your local by adding -h IP.ADDRESS.NUMBER after-u username

mysql -u root -p databasename < dumpfileFOurTableInOneDump.sql 

---

You can use the below code:

1. For Single Table Structure alone Backup

-

mysqldump -d <database name> <tablename> > <filename.sql>

2. For Single Table Structure with data

-

mysqldump <database name> <tablename> > <filename.sql>

Hope it will help.