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

482
votes

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?

9
mysqlbackuprestore

9 Answers

912
votes

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
25
votes

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

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

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

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
8
votes

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
6
votes

You can use this code:

This example takes a backup of sugarcrm database and dumps the output to sugarcrm.sql

# mysqldump -u root -ptmppassword sugarcrm > sugarcrm.sql

# mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

The sugarcrm.sql will contain drop table, create table and insert command for all the tables in the sugarcrm database. Following is a partial output of sugarcrm.sql, showing the dump information of accounts_contacts table:

--

-- Table structure for table accounts_contacts

DROP TABLE IF EXISTS `accounts_contacts`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `accounts_contacts` (
`id` varchar(36) NOT NULL,
`contact_id` varchar(36) default NULL,
`account_id` varchar(36) default NULL,
`date_modified` datetime default NULL,
`deleted` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `idx_account_contact` (`account_id`,`contact_id`),
KEY `idx_contid_del_accid` (`contact_id`,`deleted`,`account_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

--
5
votes

You can use the below code:

  1. For Single Table Structure alone Backup

-

mysqldump -d <database name> <tablename> > <filename.sql>
  1. For Single Table Structure with data

-

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

Hope it will help.

5
votes

You can either use mysqldump from the command line:

mysqldump -u username -p password dbname tablename > "path where you want to dump"

You can also use MySQL Workbench:

Go to left > Data Export > Select Schema > Select tables and click on Export

0
votes

just use mysqldump -u root database table or if using with password mysqldump -u root -p pass database table