Moving a large mySQL database to a new server

birwin

I have a new dedicated server. I have a large mySQL database on my old site that is on a shared host's server. I need to move it to the new server.

I don't have root access to the shared host's mySQL database, nor do I have its root password, although I can telnet into it.

The databases - about 40 - total around 600 Mb

phpMyAdmin will download the file but will not upload it. It is too large and exceeds the upload limit. Some of my individual tables exceed 40 Mb and with about 20 tables per database, uploading and downloading 800 tables is not practical. My largest database is 139 Mb.

I would appreciate your help finding a way to migrate this database

Brian

Norman_Graham

Hi Brian

You sound like you know what you're doing so you've probably thought of this suggestion already, but could you take the downloaded files and incorporate them into a series of PHP files, say one for each DB? When you do a DB dump, you get the SQL syntax in a flat file, right? If you take that syntax and dump it into PHP queries - you'd have to do a bit of searching and replacing to set it up - could you then run each PHP file on your server and populate your MySQL DBs, thereafter deleting the files to make sure they don't run twice?

Perhaps this is a dumb suggestion, but I don't know of any other clever workarounds.

Norm

birwin

Thanks Norman:

I had considered writing some PHP to move it but I fear that the larger databases may exceed the execution limits of PHP.

Of course I was also hoping that I wouldn't have to deal with each of 40 databases individually.

I have full access to the new server's root, including the root password.

Thanks.

Brian

tom_s

[sorry, please ignore this, dont know how to delete it]

Tracer

I just did this!

But a bit more complex, I moved a Mysql 3.x DB to a new server running MySQL 4.x.

I couldnt get any oth the PHPMyAdmin Export/Imports to fxn for the entire databas e(which was huge). A friend recommended using the trial version of NAVICAT to SYN the two databases...

Worked QUICK and GREAT (Even with the trial version)

Check out
This Site

birwin

Every option I tried, both posted hints and third party commercial programs that claimed they would do it, timed out or failed.

The answer was too easy, but it took me three days to figure it out. Hopefully this posting will save someone that time.

phpmyadmin's newer versions have an EXPORT function on the main page. Click on that. On your old server, select the databases you wish to move by highlighting them (or select ALL). Under DATA choose COMPLETE INSERTS. Click SAVE AS FILE and click GO. You will then be asked to load the file to your hard disk. Choose an easy to find folder and download the .sql file.

If your file is very large I suggest making several files, each less than 250,000Kb. (phpmyadmin doesn't have download restrictions but it does have upload restrictions, plus it is limted to the transaction times set in PHP)

Use an FTP program, like CoffeeCup DirectFTP, to upload the resulting file to your new server.

Use an SSH program to get root access to your server. I used Putty. Sign in for root access. Then go to the mysql server with the command

mysql -h localhost

Then type:

mysql < and add the path to the file you uploaded. e.g.

mysql < /home/my_Servers_User_Name/public_html/the_name_of_my_file.sql

BANG! Within a couple of minutes you are up and running.

Good Luck!

Brian Irwin
www.pointsplan.com

rachel2004

Originally posted by birwin
I have a new dedicated server. I have a large mySQL database on my old site that is on a shared host's server. I need to move it to the new server.

I don't have root access to the shared host's mySQL database, nor do I have its root password, although I can telnet into it.

The databases - about 40 - total around 600 Mb

I know you've already solved your problem, but had you already tried SSH-ing or telneting to your server hosting your db and then just executing a:

mysqldump

http://dev.mysql.com/doc/mysql/en/mysqldump.html

or were you restricted from doing that?

birwin

I was previously on a shared server and upgraded to a dedicated server. I was restricted from doing a mysqldump by my original host.

mysqldump would have been my choice if I had access to it.

Brian

Tracer

I'm trelling you, I attempted to us PhpMyAdmin to move a medium database (10+ gig) from a MySQl 3.2 to a MySQl 4.1 server and PHPMyAdmin could not handle it.... As I stated above, Navicat (the demo version at that) not only created the new database on the 4.1 server but then copied the 3.2 to 4.1 making the necessary datatype changes. And it did it quickly...

birwin

Dear Tracer:

I tried Navicat but I couldn't get it to log into my shared server.

Brian