Solved

mysql backup and restore

Posted on 2015-02-15
5
103 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
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).
0
 
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.
0
 
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.
0
 

Author Comment

by:srinivas2324
ID: 40628562
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
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.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
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 video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

828 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