MySQL Dump & Restore

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
APD TorontoAsked:
Who is Participating?
 
Andrej PirmanConnect With a Mentor Commented:
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
 
APD TorontoAuthor Commented:
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
 
APD TorontoAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.