?yb?
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?

转载于:https://stackoverflow.com/questions/6682916/how-to-take-backup-of-a-single-table-in-a-mysql-database

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

8条回答 默认 最新

  • YaoRaoLov 2016-01-04 09:35
    已采纳

    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
    

    点赞 打赏 评论
  • 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

    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 ;)
    点赞 打赏 评论
  • 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`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    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

    点赞 打赏 评论

相关推荐