[RESOLVED] Migrating large db from mysql3 to mysql5 on new machine

[sneakyimp]

I am trying to export several databases from my old server to my new server. The old server has MySQL 3.23.58. The new one is 5.0.22.

I tried once so far with a simple mysqldump of all databases:

mysqldump -uroot -p --all-databases > db.sql

I used scp to copy the sql file over to the new server

scp -Cp db.sql [e-mail]:/var/lib/mysql/db.sql

or something like that anyway.

Then I tried to import the file with a simple mysql command:

[root@server2 html]# mysql -uroot -p < db.sql
Enter password:
ERROR 1064 (42000) at line 1733492: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int(4) unsigned NOT NULL auto_increment,
  prim_state char(2) NOT NULL default '' at line 2

As you can see I got what appears to be a version error 1064. Can anyone tell me what the error is? Is there some flag I should have used when I dumped or imported?

My first thought is that perhaps this is just too large a leap. But I'm not using any foreign key constraints or anything like that. Just pretty simply MyISAM tables. On the other hand, there could be charset weirdness or whatever.

Any advice is much appreciated.

[sneakyimp]

I've been trying a slightly different approach and I'm having some luck so far. I can't move all the databases at once but I can move one at a time. Here's the basic process so far.

1) Login to the old machine with your data and dump the database with a few extra flags...these will iron out some of the incompatibilities between sql versions:

mysqldump -uroot -p --quote-names --allow-keywords db_name > db_name.sql

2) While still logged ino to old machine, use scp to securely copy the file to the new machine. Assuming the new machine's ip is 11.11.11.11 type this:

scp -Cp db_name.sql root@11.11.11.11:/var/lib/mysql/

NOTE: the directory /var/lib/mysql must exist on your new machine. if it doesn't, you can choose some other directory that does exist. the trailing backslash is really important.

3) Log in to the new machine and cd to the dir where you copied the file in the last step:

cd /var/lib/mysql

4) On the new machine, login to mysql, create the database using the same name as your original database name in step 1, and then quit

mysql -uroot -p
create database db_name
quit

5) import the database to myslq on the new machine:

mysql -uroot -p db_name < db_name.sql

That appears to be working for me now. If you don't have the root password this will probably be trickier.

[troybtj]

You will want to import all but the mysql table as well. This means recreating users and permissions. An import may work, but some glitches will remain.

When I move data, it is usually simply database -> database or table -> table, unless using a "Translator SQL Program" to port between the request streams, it usually doesn't work due to different keywords (not much of an issue in a 2 level mysql, but when moving from M$$QL to MySQL it is).

Baby Steps is the best advice i can give.

[sneakyimp]

Yeah i switched from porting all databases to one at a time so I could get a better idea of where exactly the problem was. For what it's worth, the extra flags on the dump seemed to have fixed all the complaints:

--quote-names --allow-keywords

I've also seen this page in the mysql 5 manual which says:

 If you want to copy a database from a remote machine over a slow network, you can use these commands:

  shell> mysqladmin create db_name
  shell> mysqldump -h 'other_hostname' --opt --compress db_name | mysql db_name

I couldn't help but wonder how the mysql client version might figure in this equation. If I run that command from the new machine with mysql 5, does that mean that the new v5 client is constructing the sql statements in the dump?? Or does the client on the local (new) machine merely hand off a request to the old machine's mysql client which is 3.23?

[troybtj]

sneakyimp wrote:

 If you want to copy a database from a remote machine over a slow network, you can use these commands:

  shell> mysqladmin create db_name
  shell> mysqldump -h 'other_hostname' --opt --compress db_name | mysql db_name

I couldn't help but wonder how the mysql client version might figure in this equation. If I run that command from the new machine with mysql 5, does that mean that the new v5 client is constructing the sql statements in the dump?? Or does the client on the local (new) machine merely hand off a request to the old machine's mysql client which is 3.23?

Due to the pipe ('|'), that would run the local mysql command, mysqldump is connecting to the remote host as a client on port 3306, then dumping that information into your local mysql.

[sneakyimp]

so you are saying that the command after the pipe (mysql db_name) would run locally? What about the mysqldump command before the pipe? If that also runs locally this could be very helpful. mysqldump is the command that creates all the sql in a dump and if my local mysqldump runs, it would be version 5, therefore hopefully creating an sql dump that is compatible with mysql 5 rather than version 3.23 which is the mysql version on the remote server.

right?

[troybtj]

Yes, both the mysqldump and mysql commands would run on the machine you entered them on.

You can do --version to make sure the v5 is in your path.

[sneakyimp]

Thanks!