OK ... I have a mysql db on one server (running mysql 3.23.40) that I've already made the dump for--mysqldump -uuser -ppassword dbname > dbname.sql.

FTP it to different server (running mysql 3.23.37) and tried to un-dump this db ... so I did mysql -uuser -ppassword dbname < dbname.sql ... Immediately I get this error:

photon:/var/lib/mysql/db_name# ERROR 1050 at line 11: Table 'some_table' already exists

Never had that one before ...

So, I went to the db directory, remove all the file (Deleting all the tables) ... and tried to redump mysql (Again with mysql -uuser -ppassword dbname < dbname.sql. Now I got this error ...

photon:/var/lib/mysql/db_name# ERROR 1062 at line 22: Duplicate entry '5-42' for key 1

So ... I drop and re-create the db (using drop/create database ) and tried to redump the db again ... Now it works, but ... it does not dump all of the tables .... It just stops after db_table name starting with "p" or something ... Out of the 160MB of total db I am dumping, I only get 110MB .... Also, using sqldump, I got 204 MB of file for dumping this 160 MB db. Is this normal that you gained about 30% in size while you are doing the dump?

So anyway, what is going on? Anybody knows? Does the version differences cause all the problems?

cheers,
:beer:

A mysqldump is simply a series of queries that will rebuild your database. Thus, it will always differ in size from the actual database (usually it will be larger, but could be smaller, depending on many factors).

'mysqldump' has a command-line option you might want to use: --add-drop-table. This option adds a "Drop table" before each create, ensuring that the table is wiped out before it tries to create it. This will solve the first problem.

For the second problem, I'm guessing it's simply timing out or running out of memory sometime during the process. Your best bet here is to split the dump up into individual tables. You can do this by making several dumps, one for each table.

This might take a while if you have a lot of tables, though. Plus you'd have to FTP your files all over again...

You could instead split up the file you have now -- remember, it's just a text file full of "insert into..." queries, one per line. You should be able to split it up at any arbitrary point, provided you split on a line boundary.

You can do this with a text editor, or if you're good with *nix commands you can probably whip up a nifty shell command to do it for you. Then run your above command, once for each of your (now much smaller) files.

Probably splitting it into two halves should suffice for your particular situation.

Hope this helps.

I ended up installing the a newer version of mysql (3.23.40) on my other server and it resolves the second problem ... It finishes restoring all the tables ... so I guess the different version may very well be the problem? Very strange ...

I was just told that to restore the db, I do need to drop and recreate the db ... Hm... I just knew that too ... I am 99% sure the last time I did that, I did not need to drop and recreate the db ...

Anyway ... seems to be working fine now. I am so glad it's all over :)

cheers,
:beer: