Solved

MySQL Dump & Restore

Posted on 2014-02-14
3
333 Views
Last Modified: 2014-02-15
Hi Experts,

I am trying to do a dump of all databases with:

C:\MySQL_DUMP>mysqldump --opt  --all-databases > c:\mysql_dump\all_dbs.sql -u root -p 123

Open in new window


However, my all_dbs.sql file simply contains the following contents, which I know is incorrect:

Usage: mysqldump [OPTIONS] database [tables]
 OR    mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
 OR    mysqldump [OPTIONS] --all-databases [OPTIONS]
 For more options, use mysqldump --help

Open in new window


I know I should expect over 100 lines with CREATE and INSERT statements.

As well, afterwards, how would I import?

The reason that I need to do this is because I need to reformat my system.

Thank you
0
Comment
Question by:APD_Toronto
  • 2
3 Comments
 
LVL 18

Accepted Solution

by:
Andrej Pirman earned 500 total points
ID: 39860208
This should do for getting a dump into a single file PER DATABASE:

c:\MySQL\bin\mysqldump -u root -pPASSWORD -r backup.sql -q -x -f -F -e -B --add-drop-table --add-drop-database dbName

Note:
There IS space after -u parameter, and there is NOT any space after -p parameter!
Replace paths, username root, PASSWORD and dbName with your real data.

And this should import it back, again single database:

mysql --port=3306 -u root -pPASSWORD dbName < backup.sql


**EDIT**
sorry, did not read your question well.
You have syntax error in your line; you directed the output into, literally, file named "c:\mysql_dump\all_dbs.sql -u root -p 123". Also after -p there should be no space:
C:\MySQL_DUMP>mysqldump --opt  --all-databases > c:\mysql_dump\all_dbs.sql -u root -p 123

Open in new window

Should be:
C:\MySQL_DUMP>mysqldump --opt  --all-databases -r c:\mysql_dump\all_dbs.sql -u root -p123

Open in new window

0
 

Author Comment

by:APD_Toronto
ID: 39860699
OK, your code worked:

C:\MySQL_DUMP>mysqldump --opt  --all-databases -r c:\mysql_dump\all_dbs.sql -u root -p123

Open in new window


now, how do I import all databases?
0
 

Author Closing Comment

by:APD_Toronto
ID: 39862055
Thanks!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Connect MySql database to wordpress site 3 79
MySQL ERROR 1045 (28000) 2 90
mysql update statement 3 30
Help needed with Powershell  XML to MySQL 5 59
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question