View Full Version : Moving database to new machine

01-18-2007, 10:27 PM
I'm trying to relocate my site to a new host and I'm having a lot of trouble moving the database.

I did the export for the old host/database with phpmyadmin. It saved to a txt/sql file with no problems.

When I try to import it into the new host/database I get all kinds of syntax errors.

Then I realized, after purchasing the new host that:

Old host/db = MySQL version 5.0.24a
New host/db = 4.0.27

Is there any way to convert a 5.0 db to 4.0?

01-19-2007, 12:47 AM
If you still have access to your old host, then you can make use of the following command using PhpMyAdmin

mysqldump --compatible=mysql40

This should dump out the database structure including the data in the database.


01-19-2007, 02:50 AM

SQL-query :

mysqldump * - compatible = mysql40

MySQL said:

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 'mysqldump * - compatible = mysql40' at line 1

Sorry I'm a bit of a n00b at this stuff. I can point and click, but don't know any of the code. I didn't put that star in there, I copy/pasted exactly what you wrote in your post.


01-19-2007, 03:15 AM
Here is a couple of websites that explain how might be able to dump out a single database, or all-database from a given MySql server instance.



Here is a good article that explains different ways to back MySql database

You can also have a go at using webyog. You can download the community edition from the following URL free of charge.

If you do download webyog, you can easily connect to a remote MySql database, and be able to export the database as a text file. You should note that you can disable MySql5 features when you are performing the export operation if you are to use webyog.

Note: I would advise you to install MySql4 on your local machine so that you can do all the testing etc. before dumping your database on the server.

Good luck.

01-19-2007, 03:38 AM
Don't most phpmyadmin versions have a dropdown selector on the export screen where you can pick the output file version?

Also, the mysql migration toolkit might be able to help you read your Version 5 file and import it into a version 4 server - http://mysql.com/products/tools/migration-toolkit

The mysql administrator program might also let you restore a version 5 file to a version 4 server (I have not tried this with the administrator program, but it would not hurt to try it.) The mysql administrator is part of the same package with the toolkit at the link already posted.

01-19-2007, 04:30 AM
That webyog program worked like a charm.

I still had to take out the "DEFAULT CHARSET=latin1", but it was quick/easy to do with notepad.

Thanks for the help! :thumbsup:

01-19-2007, 10:57 PM
Why would you move to a host with mysql 4.0.x?

it is out of date, two major code revisions ago. MySQL 4.1 was the production standard in October of 2004.

You are giving these people your money right?

I mean a car is a car, do you want to buy a DeSoto or Edsel or something more modern? Assuming you aren't a collector I mean?