Transfer DBs from mysql 4 to 5

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

vchris
Forum Contributor
Posts: 204
Joined: Tue Aug 30, 2005 7:53 pm
Location: Canada, Quebec

Transfer DBs from mysql 4 to 5

Post by vchris »

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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

use mysqldump, or create your own dump file while in 4. Jump into 5 and run the dump'd sql.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

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.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

raghavan20 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.
Grrrr.... please don't give out this advice :P

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 ;)
vchris
Forum Contributor
Posts: 204
Joined: Tue Aug 30, 2005 7:53 pm
Location: Canada, Quebec

Post by vchris »

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?
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

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
User avatar
BDKR
DevNet Resident
Posts: 1207
Joined: Sat Jun 08, 2002 1:24 pm
Location: Florida
Contact:

Post by BDKR »

d11wtq wrote:
raghavan20 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.
Grrrr.... please don't give out this advice :P

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 ;)
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.
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.
I can't comment on doing this on Windows, but the reason this normally doesn't work on *nix boxes is permissions.

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
vchris
Forum Contributor
Posts: 204
Joined: Tue Aug 30, 2005 7:53 pm
Location: Canada, Quebec

Post by vchris »

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...
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

Do not copy the entire data folder into mysql 5 install-dir, instead copy all the dbs you need into install-dir/data because you might overwrite some of the mysql 5 specific files.
vchris
Forum Contributor
Posts: 204
Joined: Tue Aug 30, 2005 7:53 pm
Location: Canada, Quebec

Post by vchris »

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!
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

What database engine do these tables use? Certain types require that you copy other files than the actual database. Have a look and see if you have an ibdata file you can copy too for example ;)
vchris
Forum Contributor
Posts: 204
Joined: Tue Aug 30, 2005 7:53 pm
Location: Canada, Quebec

Post by vchris »

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.
vchris
Forum Contributor
Posts: 204
Joined: Tue Aug 30, 2005 7:53 pm
Location: Canada, Quebec

Post by vchris »

Does anyone know how to do this or any other alternative or should I just stay with MySQL 4.1?
vchris
Forum Contributor
Posts: 204
Joined: Tue Aug 30, 2005 7:53 pm
Location: Canada, Quebec

Post by vchris »

I have reinstalled MySQL 4.1 and now I got the same problem I can see the DBs and connect to them but not query them.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

don't you have the backup as SQL commands instead of folder in data directory? What is the error notice now? Post error numbers if available..
Post Reply