Solved

Create New Database From SQL Dump File

Posted on 2008-06-23
6
788 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…

738 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