Transfer DBs from mysql 4 to 5
Moderator: General Moderators
Transfer DBs from mysql 4 to 5
Hey guys!
I've been having issues with mysql and I've working on it but still not working.
Ok I had Mysql 4.1 installed and decided to update to 5 and update my phpbb forum. I'm running mysql on IIS because I tried to install Apache but it's too complicated.
anyway what I wanna know is how do I transfer my DBs I had in Mysql 4.1 to 5?
I tried simply copying the data folder and pasting it in mysql 5 folder but I can't access the DBs.
I've been having issues with mysql and I've working on it but still not working.
Ok I had Mysql 4.1 installed and decided to update to 5 and update my phpbb forum. I'm running mysql on IIS because I tried to install Apache but it's too complicated.
anyway what I wanna know is how do I transfer my DBs I had in Mysql 4.1 to 5?
I tried simply copying the data folder and pasting it in mysql 5 folder but I can't access the DBs.
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
Grrrr.... please don't give out this adviceraghavan20 wrote:go to install dir of the mysql 4 and look at this directory, install-dir-4/data, you will see your database there in a folder, just copy it and paste it in the install-dir-5/data of mysql 5.
That's NOT the correct way to move a database... variations in the database engines and versions of MySQL can affect this. SQL is SQL... dump it and import it correctly
raghavan20 that's what I tried to do. I got a program Navicat which is an interface for Mysql and when I pasted them in mysql 5 folder I could see them in that program now. But when I clicked on them it popped an error saying it didn't exist.
feyd, I have already removed mysql 4 but made a backup of the data folder. Should I restore my comp to before I removed and try to use mysqldump?
feyd, I have already removed mysql 4 but made a backup of the data folder. Should I restore my comp to before I removed and try to use mysqldump?
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
I directly interact with mysql client. I had a problem with mysql 4 earlier and I copied the data directory and pasted in mysql 5 and I am now able to interact with older dbs using mysql 5 client. But a d11wtq said, it is not the best way to do it. I do not know whether indexes are restored if you move data folder, I do not know it since I did not have any index earlier in mysql 4
Actually, in some cases that's exactly what you end up having to do. Read some of the MySQL docs on setting up and managing replication and you will see this method mentioned. And not as an afterthought or "Hail Mary" either, but instead as the primary method.d11wtq wrote:Grrrr.... please don't give out this adviceraghavan20 wrote:go to install dir of the mysql 4 and look at this directory, install-dir-4/data, you will see your database there in a folder, just copy it and paste it in the install-dir-5/data of mysql 5.
That's NOT the correct way to move a database... variations in the database engines and versions of MySQL can affect this. SQL is SQL... dump it and import it correctly
I can't comment on doing this on Windows, but the reason this normally doesn't work on *nix boxes is permissions.MySQL Manual: 6.4. How to Set Up Replication wrote: The easiest way to create a snapshot is to use an archiving program to make a binary backup of the databases in your master's data directory. For example, use tar on Unix, or PowerArchiver, WinRAR, WinZip, or any similar software on Windows.
Have you tried monitoring the mysql error file? Inside the data directory, there should be a *.err file. The exact name of it usually depends on you host or box name. Normally MySQL will dump a lot of information into that file that can be helpfull in trying to find problems.
Cheers,
BDKR
when copying data folder into mysql 5 I had to stop the mysql service. I pasted and then tried to restart the service but it didn't work. What I had copied was the complete data folder. Should I only copy paste the DB folders?
I did that earlier also and was not able to access the DBs.
This is only a dev server.
no BDKR haven't tried monitoring the mysql error file.
I'm just confused about mysql now...
I did that earlier also and was not able to access the DBs.
This is only a dev server.
no BDKR haven't tried monitoring the mysql error file.
I'm just confused about mysql now...
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
I have just copied one folder of a mysql 4.1 db to mysql 5 folder. On the command line for mysql I then connect to the DB successfully. When I try to "select * from contactlist;" it says:
ERROR 1146 (42S02): Table 'crl.contactlist' doesn't exist
Then for testing, I connect to the mysql DB which is the one that is created by mysql upon installation. I am able to query it.
Something is wrong here. I need help!
ERROR 1146 (42S02): Table 'crl.contactlist' doesn't exist
Then for testing, I connect to the mysql DB which is the one that is created by mysql upon installation. I am able to query it.
Something is wrong here. I need help!
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
d11wtq, I do have a ibdata1 file. I copied the file in my mysql 5 folder. To do that I had to stop the mysql service. Then when I tried to restart the service I got the following error:
Could not start the MySQL service on Local Computer.
Error 1067: The process terminated unexpectedly.
Then if I recopy the original ibdata1 file I can restart the service.
Could not start the MySQL service on Local Computer.
Error 1067: The process terminated unexpectedly.
Then if I recopy the original ibdata1 file I can restart the service.
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact: