Mangento REPLACE sample data database with blank data database via SSH

What SSH commands would I need to dump the blank database to .sql then replace the sample data database with the blank .sql file?
LVL 22
NerdsOfTechTechnology ScientistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ygouthamCommented:
need more info, but from what i understand you want to take MYSQLDUMP of a database structure without the data

mysqldump -d  DB_NAME -u DB_USER  -p  > some_file.sql

if you want to blank out your database then the best option would be to drop the database and recreate it with the blank sql file that you have from above

mysql> drop database DB_NAME;

#mysql -u DB_USER -p  DB_NAME < some_file.sql
0
NerdsOfTechTechnology ScientistAuthor Commented:
Excellent notion. For clarification purposes lets say the blank data "has" data and lets call it the "simple" database; let's call the sample data "example" database.

Steps:
1. You are saying I could drop the example database
2. Dump the simple database into a .sql file
3. Recreate the simple database to REPLACE the example database

Let me know if this is correct. Thanks!
0
ygouthamCommented:
Steps I would follow:

1.  Dump the example database with the "-d" switch to take only the structure and NO DATA
2. Drop the simple database
3. Recreate the simple database  (we only create the database and it has no tables as of now)
4. Restore the tables in simple database from the .sql file created
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

NerdsOfTechTechnology ScientistAuthor Commented:
Basically I want to erase the example database completely and replace it with the simple database.

Can I dump the example database and "rename" the simple database as the example database?

Is there such a command?
0
NerdsOfTechTechnology ScientistAuthor Commented:
correction:

Basically I want to erase the example database completely and replace it with the simple database.

Can I DELETE the example database and RENAME the simple database as the example database?

Is there such a command?
0
ygouthamCommented:
ok i understood it in the reverse.

you can rename databases

http://dev.mysql.com/doc/refman/5.1/en/rename-database.html

but ideally i would not recommend such a thing.  it is simpler that you dump an empty structure and move it into the database that you want to create (with or without data).  the grant privileges and other things do get hit.  the site (mysql) itself gives a warning that they are discontinuing the syntax. so if the gurus believe that it is a bad option, why get into trouble?
0
NerdsOfTechTechnology ScientistAuthor Commented:
What is the safe way to do this. Both databases have the same structure except the example database has stuff I DONT WANT. Both have same user, password, and privileges.

What is the best approach in your opinion in this case?
0
ygouthamCommented:
drop EXAMPLE, take a dump of SIMPLE, restore the same into EXAMPLE

that should give you the stuff you want and take away the unnecessary ones
0
ygouthamCommented:
more often than we require, we end up with a database that has more junk than useful data.  if you have a clean minimal data from a existing database that you want to use, then ideal would be to take a dump of the current UNWANTED data (store it for any unforeseen requirements) and drop database.

recreate the database with minimal required data and hope that you continue without a hassle.

renaming databases is currently discontinued in the latest versions of mysql. so good luck if you still have an older version which is willing to rename a database from CLI.  that is not a problem either, but as I said earlier, we do not know what problems that we might run into when we do this.  

better to be safe -> take a dump of both databases.  try renaming the SIMPLE -> EXAMPLE. if it works let the setup continue. else drop all databases and have only the one you want.

you would have the .sql files for both the databases in this way. and voila!!! can also create the database with unwanted data, if you so wish.... :-)
0
NerdsOfTechTechnology ScientistAuthor Commented:
Going the best practice route what is the procedure, step by step to:
{code please}

1. dump example to example.sql
2. drop example
3. dump simple to simple.sql
4. create "example" (name is same) using simple.sql
5. drop simple

Thanks!!!
0
ygouthamCommented:
1. dump example to example.sql

mysqldump -u DB_USERNAME -p  example > example.sql

2. drop example
mysql -u DB_USERNAME -p   -e "drop database example;"

3.  dump simple to simple.sql
mysqldump -u DB_USERNAME -p  simple > simple.sql

4. create "example" using simple.sql
mysql -u DB_USERNAME -p  -e "create database example;"
mysql -u DB_USERNAME -p  example  < simple.sql

5. drop simple
mysql -u DB_USERNAME -p   -e "drop database simple;"

remember that all the above commands are run from the command line and since you asked for a SSH command, am assuming that your server is linux based




0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NerdsOfTechTechnology ScientistAuthor Commented:
A+A+Amazing knowledge! Super Job! Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

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.