?
Solved

mysql backup and restore

Posted on 2015-02-15
5
Medium Priority
?
108 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
[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
5 Comments
 
LVL 36

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 12

Expert Comment

by:Nathan Riley
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 12

Expert Comment

by:Nathan Riley
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 36

Accepted Solution

by:
Kimputer earned 1000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article, we’ll look at how to deploy ProxySQL.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

800 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