How do I restore a dump file from mysqldump

Question

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

User · Answer

Assuming you already have the blank database created  you can also restore a database from the command line like this  mysql databasename  lt  backup sql

User · Answer

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

User · Answer

You simply need to run this   mysql -p -u user   database   lt  db backup dump   If the dump contains multiple databases you should omit the database name   mysql -p -u user   lt  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

User · Answer

It should be as simple as running this    mysql -u  lt user gt  -p  lt  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  lt database-name-here gt     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

User · Answer

mysql -u username -p -h localhost DATA-BASE-NAME  lt  data sql   look here - step 3  this way you dont need the USE statement

User · Answer

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

User · Answer

I got it to work following these steps hellip    Open MySQL Administrator and connect to server Select  Catalogs  on the left 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 Name the new schema   example   dbn    MySQL New Schema http   img262 imageshack us img262 4374 newwa4 th gif enlarge image Open Windows Command Prompt  cmd   Windows Command Prompt http   img206 imageshack us img206 941 startef7 th gif enlarge image Change directory to MySQL installation folder Execute command   mysql -u root -p dbn  lt  C  dbn 20080912 dump    hellip 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 Enjoy

User · Answer

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

User · Answer

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   lt  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

User · Answer

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

User · Answer

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

User · Answer

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   gt  mysql -u root -p  mysql gt  create database mydb  mysql gt  use mydb  mysql gt  source db backup dump

User · Answer

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

User · Answer

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 gt  create database temp mysql gt  use temp mysql gt  source c  code dump sql

User · Answer

mysql -u  lt username gt  -p  lt password gt  -h  lt host-name like localhost gt   lt database-name gt   lt  db dump-file

User · Answer

One-liner command to restore the generated SQL from mysqldump  mysql -u  lt username gt  -p lt password gt  -e  source  lt path to sql file gt

User · Answer

Run the command to enter into the DB       mysql -u root -p    Enter the password for the user Then Create a New DB   mysql gt  create database MynewDB  mysql gt  exit   And make exit Afetr that Run this Command    mysql -u root -p  MynewDB  lt  MynewDB sql   Then enter into the db and type  mysql gt  show databases  mysql gt  use MynewDB  mysql gt  show tables  mysql gt  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 gt  create database MynewDB  mysql gt  show databases  mysql gt  use MynewDB  mysql gt  source MynewDB sql  mysql gt  show tables  mysql gt  exit

User · Answer

How to Restore MySQL Database with MySQLWorkbench  You can run the drop and create commands in a query tab   Drop the Schema if it Currently Exists  DROP DATABASE  your db name     Create a New Schema  CREATE SCHEMA  your db name     Open Your Dump File     Click the Open an SQL script in a new query tab icon and choose your db dump file  Then Click Run SQL Script    It will then let you preview the first lines of the SQL dump script  You will then choose the Default Schema Name Next choose the Default Character Set utf8 is normally a safe bet  but you may be able to discern it from looking at the preview lines for something like character set  Click Run Be patient for large DB restore scripts and watch as your drive space melts away

[mysql] How do I restore a dump file from mysqldump?

Examples related to mysql

Examples related to sql

Examples related to database