本文翻译自:How to take backup of a single table in a MySQL database?
By default, mysqldump
takes the backup of an entire database. 默认情况下, mysqldump
会备份整个数据库。 I need to backup a single table in MySQL. 我需要在MySQL中备份一个表。 Is it possible? 可能吗? How do I restore it? 我该如何恢复它?
#1楼
参考:https://stackoom.com/question/S2Wy/如何在MySQL数据库中备份单个表
#2楼
您可以使用MYSQLWorkbench tool
轻松地转储选定的表,在一个转储中单独或一组表,然后按如下方式导入:如果您在本地运行它,您可以通过添加-h IP.ADDRESS.NUMBER添加主机信息 -u用户名
mysql -u root -p databasename < dumpfileFOurTableInOneDump.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强效名称表;)
#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 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. sugarcrm.sql将包含sugarcrm数据库中所有表的drop table,create table和insert命令。 Following is a partial output of sugarcrm.sql, showing the dump information of accounts_contacts table: 以下是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;
--
#6楼
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. 希望它会有所帮助。