Page 1 of 2

Transfer DBs from mysql 4 to 5

Posted: Sun Jan 22, 2006 7:45 pm
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.

Posted: Sun Jan 22, 2006 9:15 pm
by feyd
use mysqldump, or create your own dump file while in 4. Jump into 5 and run the dump'd sql.

Posted: Mon Jan 23, 2006 5:40 am
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.

Posted: Mon Jan 23, 2006 6:09 am
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 ;)

Posted: Mon Jan 23, 2006 7:04 am
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?

Posted: Mon Jan 23, 2006 7:21 am
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

Posted: Mon Jan 23, 2006 7:24 am
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

Posted: Mon Jan 23, 2006 7:46 am
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...

Posted: Mon Jan 23, 2006 8:48 am
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.

Posted: Mon Jan 23, 2006 4:57 pm
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!

Posted: Mon Jan 23, 2006 5:06 pm
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 ;)

Posted: Mon Jan 23, 2006 6:48 pm
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.

Posted: Tue Jan 24, 2006 4:06 pm
by vchris
Does anyone know how to do this or any other alternative or should I just stay with MySQL 4.1?

Posted: Tue Jan 24, 2006 4:29 pm
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.

Posted: Tue Jan 24, 2006 4:51 pm
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..