mysql backup and restore

Posted on 2015-02-15
Last Modified: 2015-03-04

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.
Question by:srinivas2324
  • 2
  • 2
LVL 35

Expert Comment

ID: 40611948
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).
LVL 11

Expert Comment

by:N R
ID: 40612264
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.
LVL 11

Expert Comment

by:N R
ID: 40612268
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.

Author Comment

ID: 40628562

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

Accepted Solution

Kimputer earned 500 total points
ID: 40630555
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.

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
If more than 10% of results are over X in mysql 2 49
MySQL Error 3 39
No row return after calling the fillschema method 4 40
Create a Select Query and Populate a Table 3 44
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 …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: (…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

910 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

21 Experts available now in Live!

Get 1:1 Help Now