℡Wang Yan
2008-09-19 21:27
浏览 399

如何从 mysqldump 还原一个转储文件?

I was given a MySQL database file that I need to restore as a database on my Windows Server 2008 machine.

I tried using MySQL Administrator, but I got the following error:

The selected file was generated by mysqldump and cannot be restored by this application.

How do I get this working?

转载于:https://stackoverflow.com/questions/105776/how-do-i-restore-a-dump-file-from-mysqldump

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

15条回答 默认 最新

  • ℡Wang Yan 2008-09-19 21:29
    已采纳

    It should be as simple as running this:

    mysql -u <user> -p < db_backup.dump
    

    If the dump is of a single database you may have to add a line at the top of the file:

    USE <database-name-here>;
    

    If it was a dump of many databases, the use statements are already in there.

    To run these commands, open up a command prompt (in Windows) and cd to the directory where the mysql.exe executable is (you may have to look around a bit for it, it'll depend on how you installed mysql, i.e. standalone or as part of a package like WAMP). Once you're in that directory, you should be able to just type the command as I have it above.

    点赞 打赏 评论
  • ℙℕℤℝ 2008-09-19 21:34

    When we make a dump file with mysqldump, what it contains is a big SQL script for recreating the databse contents. So we restore it by using starting up MySQL’s command-line client:

    mysql -uroot -p 
    

    (where root is our admin user name for MySQL), and once connected to the database we need commands to create the database and read the file in to it:

    create database new_db;
    use new_db;
    \. dumpfile.sql
    

    Details will vary according to which options were used when creating the dump file.

    点赞 打赏 评论
  • 北城已荒凉 2008-09-19 21:45

    You simply need to run this:

    mysql -p -u[user] [database] < db_backup.dump

    If the dump contains multiple databases you should omit the database name:

    mysql -p -u[user] < db_backup.dump

    To run these commands, open up a command prompt (in Windows) and cd to the directory where the mysql.exe executable is (you may have to look around a bit for it, it'll depend on how you installed mysql, i.e. standalone or as part of a package like WAMP). Once you're in that directory, you should be able to just type the command.

    点赞 打赏 评论
  • 叼花硬汉 2008-10-01 13:52

    I got it to work following these steps…

    1. Open MySQL Administrator and connect to server

    2. Select "Catalogs" on the left

    3. Right click in the lower-left box and choose "Create New Schema"

      MySQL Administrator http://img204.imageshack.us/img204/7528/adminsx9.th.gif enlarge image

    4. Name the new schema (example: "dbn")

      MySQL New Schema http://img262.imageshack.us/img262/4374/newwa4.th.gif enlarge image

    5. Open Windows Command Prompt (cmd)

      Windows Command Prompt http://img206.imageshack.us/img206/941/startef7.th.gif enlarge image

    6. Change directory to MySQL installation folder

    7. Execute command:

      mysql -u root -p dbn < C:\dbn_20080912.dump
      

      …where "root" is the name of the user, "dbn" is the database name, and "C:\dbn_20080912.dump" is the path/filename of the mysqldump .dump file

      MySQL dump restore command line http://img388.imageshack.us/img388/2489/cmdjx0.th.gif enlarge image

    8. Enjoy!

    点赞 打赏 评论
  • perhaps? 2008-11-09 03:53

    You can also use the restore menu in MySQL Administrator. You just have to open the back-up file, and then click the restore button.

    点赞 打赏 评论
  • 叼花硬汉 2009-06-09 18:58

    If the database you want to restore doesn't already exist, you need to create it first.

    On the command-line, if you're in the same directory that contains the dumped file, use these commands (with appropriate substitutions):

    C:\> mysql -u root -p
    
    mysql> create database mydb;
    mysql> use mydb;
    mysql> source db_backup.dump;
    
    点赞 打赏 评论
  • MAO-EYE 2009-06-15 21:11

    You cannot use the Restore menu in MySQL Admin if the backup / dump wasn't created from there. It's worth a shot though. If you choose to "ignore errors" with the checkbox for that, it will say it completed successfully, although it clearly exits with only a fraction of rows imported...this is with a dump, mind you.

    点赞 打赏 评论
  • MAO-EYE 2012-02-06 08:20

    If you want to view the progress of the dump try this:

    pv -i 1 -p -t -e /path/to/sql/dump | mysql -u USERNAME -p DATABASE_NAME

    You'll of course need 'pv' installed. This command works only on *nix.

    点赞 打赏 评论
  • 10.24 2012-08-21 10:19

    You can try SQLyog 'Execute SQL script' tool to import sql/dump files.

    enter image description here

    点赞 打赏 评论
  • ?yb? 2013-01-03 16:59

    Using a 200MB dump file created on Linux to restore on Windows w/ mysql 5.5 , I had more success with the

    source file.sql
    

    approach from the mysql prompt than with the

    mysql  < file.sql
    

    approach on the command line, that caused some Error 2006 "server has gone away" (on windows)

    Weirdly, the service created during (mysql) install refers to a my.ini file that did not exist. I copied the "large" example file to my.ini which I already had modified with the advised increases.

    My values are

    [mysqld]
    max_allowed_packet = 64M
    interactive_timeout = 250
    wait_timeout = 250
    
    点赞 打赏 评论
  • ℙℕℤℝ 2013-06-03 17:11
    mysql -u username -p -h localhost DATA-BASE-NAME < data.sql
    

    look here - step 3: this way you dont need the USE statement

    点赞 打赏 评论
  • 三生石@ 2016-03-30 11:37
    ./mysql -u <username> -p <password> -h <host-name like localhost> <database-name> < db_dump-file
    
    点赞 打赏 评论
  • 零零乙 2017-01-13 22:16

    As a specific example of a previous answer:

    I needed to restore a backup so I could import/migrate it into SQL Server. I installed MySql only, but did not register it as a service or add it to my path as I don't have the need to keep it running.

    I used windows explorer to put my dump file in C:\code\dump.sql. Then opened MySql from the start menu item. Created the DB, then ran the source command with the full path like so:

    mysql> create database temp
    mysql> use temp
    mysql> source c:\code\dump.sql
    
    点赞 打赏 评论
  • lrony* 2017-12-27 16:10

    One-liner command to restore the generated SQL from mysqldump

    mysql -u <username> -p<password> -e "source <path to sql file>;"
    
    点赞 打赏 评论
  • bug^君 2018-03-30 06:00

    Run the command to enter into the DB

     # mysql -u root -p 
    

    Enter the password for the user Then Create a New DB

    mysql> create database MynewDB;
    mysql> exit
    

    And make exit.Afetr that.Run this Command

    # mysql -u root -p  MynewDB < MynewDB.sql
    

    Then enter into the db and type

    mysql> show databases;
    mysql> use MynewDB;
    mysql> show tables;
    mysql> exit
    

    Thats it ........ Your dump will be restored from one DB to another DB

    Or else there is an Alternate way for dump restore

    # mysql -u root -p 
    

    Then enter into the db and type

    mysql> create database MynewDB;
    mysql> show databases;
    mysql> use MynewDB;
    mysql> source MynewDB.sql;
    mysql> show tables;
    mysql> exit
    
    点赞 打赏 评论

相关推荐