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

Question

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 1

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

---

Solution 2

mysqldump can take a tbl_name parameter, so that it only backups the given tables.

mysqldump -u -p yourdb yourtable > c:\backups\backup.sql

Solution 3

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 ;)

Solution 4

We can take a mysql dump of any particular table with any given condition like below

mysqldump -uusername -p -hhost databasename tablename --skip-lock-tables

If we want to add a specific where condition on table then we can use the following command

mysqldump -uusername -p -hhost databasename tablename --where="date=20140501" --skip-lock-tables

Solution 5

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 

View more solutions

Comments

[xiankai]

This is handy for dumping a database into separate table queries - may I know what exactly the options do?

[John McGrath]

SQL usually compresses well--you can pipe the command above through gzip and the resulting file will be much smaller: mysqldump db_name table_name | gzip > table_name.sql.gz to restore: gunzip < table_name.sql.gz | mysql -u username -p db_name

[Robin Gomez]

Hello, -AN(--no-auto-rehash, -A | --skip-column-names, -N Do not write column names in results.) -e(--execute=statement, -e statement | Execute the statement and quit. The default output format is like that produced with --batch.) fuente: dev.mysql.com/doc/refman/5.6/en/mysql-command-options.html

[barell]

Unfortunately Mysql Workbench has some escaping issues which may lead to exporting invalid data which is useless...

[Agung Sagita]

just a small info, omit space between -p and password --> -ppassword, but its insecure

[Freedo]

What if you want to include the password on the command line? So you are already using -pPASSWORD

[Eugen Konkov]

mysqldump --where='where_condition', -w 'where_condition' Dump only rows selected by the given WHERE condition.