Solved

mysql backup and restore

Posted on 2015-02-15
5
99 Views
Last Modified: 2015-03-04
Hello,

I am newbie to mysql and want to clarify below on mysql backup and restore

What are the things needed to backup mysql and restore it like database name, username, password etc.

1)I have a database which need to be restored on different server so I used mysql dump command to backup the database by using username root and now during restoration on different server what is the username and password  I need to use.

2)If I have created a new empty database on new server so can I restore old database on this new database.
0
Comment
Question by:srinivas2324
  • 2
  • 2
5 Comments
 
LVL 35

Expert Comment

by:Kimputer
Comment Utility
As you described it, the real data inside the database will now be transfered.
You now miss only one thing, the permission / user rights to the database (on the new server). This is best done manually on the new database server (create user, passwords, then assign it to the database you just restored).
0
 
LVL 11

Expert Comment

by:N R
Comment Utility
It's easy to import and export, or move database around like this with phpmyadmin.  It's an easy upload of files to install and use.  

But if you're set on using the shell or the file is to big to do any different then you can restore the database like this:
You can either change directories to wherever the backup is located and type in the following:
mysql -u dbusername -p databasename < backupname.sql

Open in new window


Or if you do not want to change directories and you know the path to where the backup is located, type in the following:
mysql -u dbusername -p databasename < /path/to/backupname.sql

Open in new window


You will be prompted for the database password.
0
 
LVL 11

Expert Comment

by:N R
Comment Utility
And here are the commands for backing it up.

Type in the following to create a backup in the current directory:
mysqldump --opt -Q -u dbusername -p databasename > backupname.sql

Open in new window


Or to create a backup in a separate directory (signified by /path/to/) type:
mysqldump --opt -Q -u dbusername -p databasename > /path/to/backupname.sql

Open in new window


You will be prompted for the database password.
0
 

Author Comment

by:srinivas2324
Comment Utility
Hi

When we backup database using php then during restore we need same username and database used for backup?
0
 
LVL 35

Accepted Solution

by:
Kimputer earned 500 total points
Comment Utility
Depends on the server where you restore it (did anyone install it and added the same users/passwords/rights?)! If not, just use something as long as it's an admin account.
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

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…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

763 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

6 Experts available now in Live!

Get 1:1 Help Now