Solved

restore mysql server question

Posted on 2014-01-16
3
282 Views
Last Modified: 2014-01-16
Hello,
  I have a database already created. Now, I would like to restore a backup copy. Does the commands MySQL -<id> -p <database> < backup.sql delete the existing database and then create a new one or do I have to do the following

1: delete the existing DB
2: Create the database
2: restore the backup

Thanks for your help
0
Comment
Question by:SiemensSEN
3 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 200 total points
ID: 39786099
Whenever I have done this, the database (schema) remains because the first line of the backup SQL file usually is as follows.
CREATE DATABASE  IF NOT EXISTS `db_name`;
USE `db_name`;

Open in new window


If you want to create a new database, you can change the top two lines of the code.  If I remember correctly, you can remove them and in the Workbench the new schema option will work.  Otherwise, it ignores that because of the USE schema name statement.
0
 

Author Closing Comment

by:SiemensSEN
ID: 39786205
Thanks
0
 
LVL 8

Expert Comment

by:Surrano
ID: 39786693
It depends on your database dump file. E.g. using mysqldump, it is possible to request a "drop database" in the dump, using --add-drop-database
So have a look at the beginning of your backup.sql and if you can see something like "drop database ..." then you don't have to explicitly drop it.

As for creation, there's a similar option --no-create-db; unless invoked this way the "create database" will be present in the dump. Look for it.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

785 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