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?

Aundreaaunson

Compress data dump: mysqldump --defaults-group-suffix=db your_db table_name | gzip > table_name-$(date +%Y%m%d-%H%M).sql.gz – Vendue

Answer 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

---

Evert

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 – Goodrow

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

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

Answer 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

Czarist

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

Esra

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

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 – Esra

Answer 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

Honora

Answer 5

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

Takeo

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

Answer 6

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 

Foliaceous

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

Answer 7

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.

Theatrician

Answer 8

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;

--

Photodrama

Answer 9

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

Durand

Answer 10

I've come across this and wanted to extend others' answers with our fully working example:

This will backup the schema in it's own file, then each database table in its own file.

The date format means you can run this as often as your hard drive space allows.

DATE=`date '+%Y-%m-%d-%H'`
BACKUP_DIR=backups/
DATABASE_NAME=database_name
mysqldump --column-statistics=0  --user=fake --password=secure --host=10.0.0.1  --routines --triggers --single-transaction --no-data --databases ${DATABASE_NAME} | gzip > ${BACKUP_DIR}${DATE}-${DATABASE_NAME}--schema.sql.gz
for table in $(mysql  --user=fake --password=secure --host=10.0.0.1 -AN -e "SHOW TABLES FROM ${DATABASE_NAME};");
    do
    echo ""
    echo ""
    echo "mysqldump --column-statistics=0  --user=fake --password=secure --host=10.0.0.1 --routines --triggers --single-transaction --databases ${DATABASE_NAME} --tables ${table} | gzip > ${BACKUP_DIR}${DATE}-${DATABASE_NAME}-${table}.sql.gz"
    mysqldump --column-statistics=0  --user=fake --password=secure --host=10.0.0.1 --routines --triggers --single-transaction --databases ${DATABASE_NAME} --tables ${table} | gzip > ${BACKUP_DIR}${DATE}-${DATABASE_NAME}-${table}.sql.gz
done

We run this as bash script on an hourly basis, and actually have HOUR checks and only backup some tables through the day, then all tables in the night.

to keep some space on the drives, the script also runs this to remove backups older than X days.

# HOW MANY DAYS SHOULD WE KEEP
DAYS_TO_KEEP=25
DAYSAGO=$(date --date="${DAYS_TO_KEEP} days ago" +"%Y-%m-%d-%H")
echo $DAYSAGO
rm -Rf ${BACKUP_DIR}${DAYSAGO}-*
echo "rm -Rf ${BACKUP_DIR}${DAYSAGO}-*"

Delois