Solved

How to backup mysql databases

Posted on 2014-12-24
9
254 Views
Last Modified: 2014-12-24
My MySQL is currently installed in C:\xampp\mysql.

I tried backing up just C:\xampp\mysql\data, which is where all the database data is contained. But when I copied that directory to a new installation of MySQL on another computer, it couldn't see the databases at all. I had to create the database names, and then restore a mysqldump file for everything to be match the state of the original installation.

I am looking for a one-step restoration process that would make any MySQL installation match the state of the original installation. Can anyone help me do this?

Thanks.
0
Comment
Question by:elepil
  • 4
  • 4
9 Comments
 
LVL 58

Accepted Solution

by:
Gary earned 250 total points
ID: 40516886
At the same level as the data folder there is also ib_logfile and ibdata1 files - you need them as they store the data and other things

Otherwise do an sql dump of your databases which is the better way
0
 
LVL 34

Assisted Solution

by:Dan Craciun
Dan Craciun earned 250 total points
ID: 40516891
If you're migrating between the same versions of MySQL, copying the data folder should be enough.
Just be careful that both servers are NOT running when you do the copy/paste.

HTH,
Dan
0
 

Author Comment

by:elepil
ID: 40517104
You know, I just found out why my copying the data folder over didn't work.

I have MySQL installed at C:\xampp\mysql on the source computer, and the databases are stored in C:\xampp\mysql\data.

However, the second PC I was copying the data folder to had MySQL installed at D:\xampp\mysql, and I had copied the 'data' folder to D:\xampp\mysql. It didn't work.

I just found out that the second PC, even though MySQL is installed at D:\xampp\mysql, it had stored its database data at C:\Program Data\MySQL Server 5.1\data !??. I don't remember configuring it to save the database data in that directory, so I don't know why it did that. Anyway, when I copied my 'data' directory to that folder, I couldn't open the databases, I was getting an error message that it can't read the .frm file.

This frustrates me to find out MySQL had stored its data in the C: drive even though I installed it at the D: drive on the second PC. And secondly, it seems simply copying the data folder over doesn't work.

Any advice?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 58

Expert Comment

by:Gary
ID: 40517109
Thats the default install path for XAMPP
Leave the files in the D: path and just edit your mysql.ini file
0
 

Author Comment

by:elepil
ID: 40517114
Gary, I have no mysql.ini. I do have a my.ini, and there is an entry in there called:

datadir = "D:/xampp/mysql/data"

So it is already pointing to where I thought it should be. I still see no explanation why MySQL stored all the data in the C drive.
0
 
LVL 58

Expert Comment

by:Gary
ID: 40517119
Yep thats what I meant.
XAMPP will by default install the MySQL data folder to the C: drive

Are you sure you are looking at the right my.ini?
0
 

Author Comment

by:elepil
ID: 40517127
Well, I did a search in d:\xampp\mysql for *.ini, and the only other one that came up is a my-default.ini. But inside it, everything is commented with a #; besides, the datadir key didn't have a value anyway.

So my.ini is the only active .ini I could find, and it does say to store the database in the D drive, yet it stored it in the C drive.
0
 
LVL 58

Expert Comment

by:Gary
ID: 40517237
You should be searching in c:\xampp\mysql or rather using the my.ini in that directory unless you have some funky setup going on .
0
 

Author Closing Comment

by:elepil
ID: 40517296
I did a search in C:\xampp\mysql just like I said. I only saw the two .ini files I mentioned. The my-default.ini even explicitly stated in the comments not to modify it because it is used as a template.

I am left scratching my head as to why my MySQL stored its data in the C: drive, despite the datadir explicitly stating "D:/xampp/mysql/data". There is no other .ini file, that I am sure of unless it's located somewhere else outside of my D: drive.

This will have to go unresolved, but I thank you all for responding. I'm not wasting any more time on this.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

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.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
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.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

840 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