elepil
asked on
How to backup mysql databases
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thats the default install path for XAMPP
Leave the files in the D: path and just edit your mysql.ini file
Leave the files in the D: path and just edit your mysql.ini file
ASKER
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.
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.
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?
XAMPP will by default install the MySQL data folder to the C: drive
Are you sure you are looking at the right my.ini?
ASKER
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.
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.
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 .
ASKER
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.
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.
ASKER
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?