Solved

Create New Database From SQL Dump File

Posted on 2008-06-23
6
782 Views
Last Modified: 2010-04-21
Experts, I need to create a MySQL database from a .sql file created by using the sqldump command. I'm new to MySql and am not exactly sure the command to create the new database from this file. Also, do I need to manually setup the user and permissions?

Thank you for your help!

~ C
0
Comment
Question by:clickclickbang
  • 2
  • 2
  • 2
6 Comments
 
LVL 20

Expert Comment

by:virmaior
ID: 21847166
the command to create a table is
...

CREATE TABLE


you can actually just append this to  the front of a SELECT statement and it will make a table...  The field design might prove to be very sub-optimal, but you can change that later.

I would highly recommend using phpmyadmin to see what is going on as you do this.
0
 
LVL 8

Expert Comment

by:CoyotesIT
ID: 21847339
To create a database you simply issue the statement


CREATE DATABASE <database_name>;

Use the following with caution and only if you are sure you want to drop and recreate the database if it already exists:

DROP DATABASE IF EXISTS <database_name>;

Then you would run your CREATE statements.

You can always take a look at the output from using the mysqldump command to see how MySQL builds the sql script for that database which will aid in getting your solution built.

Hope that helps!

0
 
LVL 1

Author Comment

by:clickclickbang
ID: 21856087
Thank you for your posts. What I am trying to figure out is how to execute the SQL from the dump of SQL created via the dump command.

1) What is the syntax for targeting the .sql file created via the dump to re-create the database?

2) Is there anything I need to do before I re-create the database? (set user permissions, etc.)

Thank you for your help!

~ C
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 20

Accepted Solution

by:
virmaior earned 500 total points
ID: 21856161
(2) if you want to use the command line to do it, then you'll probably login as the root user anyway (which means  you won't initially need to set any permissions to create the tables).

(1) http://articles.techrepublic.com.com/5100-10878_11-5259660.html
mysql -u root -psecret -D stocks2 < stocksdb.sql

where
root is the username,
secret is the password,
stocksdb.sql is the file you are loading from and
stocks2 is the database you want

your values should of course be different for these items.




0
 
LVL 8

Expert Comment

by:CoyotesIT
ID: 21856688
Oh I see, sorry yes Virmaior's post is how you do it.

Good luck!
0
 
LVL 1

Author Closing Comment

by:clickclickbang
ID: 31469791
Thanks!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now