如何在MySQL数据库中备份单个表?

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?

默认情况下，mysqldump将获取整个数据库的备份。我需要在MySQL中备份一个表。是可能的吗?如何恢复?

8 个解决方案

#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

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

mysql -u用户名-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

---

#2

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

mysqldump可以使用一个tbl_name参数，以便只备份给定的表。

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

#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 ;)
• $line cotent名称表;)

#4

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

我们可以使用任意给定条件下的任何特定表的mysql转储

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

如果我们想在表中添加一个特定的where条件，那么我们可以使用以下命令

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

#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

您可以使用MYSQLWorkbench工具轻松地将选定的表转储到一个转储文件中，或者在一个转储文件中单独或一组表，然后将其导入如下:如果您正在本地运行，您还可以通过添加-h IP.ADDRESS来添加主机信息。数量after-u用户名

mysql -u root -p databasename < dumpfileFOurTableInOneDump.sql 

#6

You can use this code:

您可以使用以下代码:

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

此示例备份sugarcrm数据库并将输出转储到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:

sugarcrm。sql将包含下拉表、创建表和为sugarcrm数据库中的所有表插入命令。下面是sugarcrm的部分输出。sql，显示accounts_contacts表的转储信息:

--

- - -

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

--

#7

You can use the below code:

您可以使用以下代码:

1. For Single Table Structure alone Backup
2. 对于单表结构单独备份

-

- - - - - -

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

2. For Single Table Structure with data
3. 对于具有数据的单表结构

-

- - - - - -

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

Hope it will help.

希望它会有所帮助。

#8

You can either use mysqldump from the command line:

您可以从命令行使用mysqldump:

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

mysqldump -u用户名-p密码dbname tablelname >“要转储的路径”

You can also use MySQL Workbench:

也可以使用MySQL Workbench:

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

转到左侧的>数据导出>选择模式>选择表并单击Export