2011-07-13 17:17
采纳率: 100%
浏览 944

如何备份 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?


  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

8条回答 默认 最新

  • YaoRaoLov 2016-01-04 09:35

    Dump and restore a single table from .sql


    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:



    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


    mysqldump db_name table_name | gzip > table_name.sql.gz


    gunzip < table_name.sql.gz | mysql -u username -p db_name

    点赞 打赏 评论
  • YaoRaoLov 2011-07-13 17:22

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

    mysqldump -u -p yourdb yourtable > c:\backups\backup.sql
    点赞 打赏 评论
  • from.. 2013-05-31 07:01

    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 
    点赞 打赏 评论
  • 斗士狗 2013-09-16 19:48


    for line in $(mysql -u... -p... -AN -e "show tables from NameDataBase");
    mysqldump -u... -p.... NameDataBase $line > $line.sql ; 
    • $line cotent names tables ;)
    点赞 打赏 评论
  • Memor.の 2014-06-09 07:19

    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
    点赞 打赏 评论
  • python小菜 2015-08-03 12:05

    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`)
    SET character_set_client = @saved_cs_client;
    点赞 打赏 评论
  • csdnceshi62 2016-11-05 11:40

    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.

    点赞 打赏 评论
  • 10.24 2017-06-19 12:28

    You can either use mysqldump from the command line:

    mysqldump -u username -p password dbname tablelname > "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

    点赞 打赏 评论