如何备份 MySQL 数据库中的单个表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6682916/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to take backup of a single table in a MySQL database?
提问by Mandar Pande
By default, mysqldumptakes 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 中备份单个表。是否可以？我该如何恢复？
回答by Ozair Kafray
Dump and restore a single table from .sql
从 .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 username -p db_name < /path/to/table_name.sql
Dump and restore a single table from a compressed (.sql.gz) format
从压缩 (.sql.gz) 格式转储和恢复单个表
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
回答by Jacob
回答by Robin Gomez
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 内容名称表 ;)
回答by minhas23
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
回答by Daniel Adenew
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 informationif u are running it in your local by adding -hIP.ADDRESS.NUMBER after-u username
您可以轻松地使用MYSQLWorkbench tool单个或一组表在一个转储中转储选定的表，然后按如下方式导入：如果您在本地运行它，您也可以添加主机信息，方法是在您之后添加-hIP.ADDRESS.NUMBER用户名
mysql -u root -p databasename < dumpfileFOurTableInOneDump.sql
回答by Linda Martin
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
-- 表的表结构 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;
--
回答by Phoenix
You can use the below code:
您可以使用以下代码：
- For Single Table Structure alone Backup
- 对于单表结构单独备份
-
——
mysqldump -d <database name> <tablename> > <filename.sql>
- For Single Table Structure with data
- 对于带有数据的单表结构
-
——
mysqldump <database name> <tablename> > <filename.sql>
Hope it will help.
希望它会有所帮助。
回答by SAM AB
You can either use mysqldumpfrom the command line:
您可以mysqldump从命令行使用：
mysqldump -u username -p password dbname tablename > "path where you want to dump"
mysqldump -u username -p password dbname tablename > "path where you want to dump"
You can also use MySQL Workbench:
您还可以使用 MySQL 工作台：
Go to left > Data Export > Select Schema > Select tables and click on Export
转到左侧 > 数据导出 > 选择架构 > 选择表，然后单击导出
