MySQL database transfer to another server - how?

[Barry]

Hi all

I am trying to transfer my site to another server because my old server keeps crashing for some reason (maybe because its 4 years old!!)

I can export my database but its too big to import even if done a table at a time.

Is there a tool that anyone uses that you could recommend to transfer my database to my new server or a way of importing without timing out?

Ta

[Paulzie]

If you have full access to the server you can pick up the underlying data files. The exact location varies. Try searching on mysql data file location in goolge.

I have often doen this so I can test a web site off-line. We're talking about a databse of over 1gb.

On sevrver 2 I created a new account for the domain and set up a blank database. This creates the directory structure etc. Then copy the raw files from the other server in to the directory on server 2.

[Barry]

Excellent, glad its possible this way. I know where the files are on my windows server - now off to google see where they are under linux and how to get the files there?!?

god its daunting all these new tasks.

Ta

[Harriers9]

Download this: BigDump: The Staggered MySQL Dump Importer

You may need to find a mirror site if the download doesn't work. Google for one.

[Donk]

If both servers are windows and you have root access you can just copy the folder that stores the database.

On my server there is a folder C:\Program Files\MySQL\MySQL Server 5.0\data that contains a folder for each data base.

On upgrading from mysql 4 to 5 I just found the equivalent the mysql 4 equivalent and simply copied the folders.

I would try creating a small database, find the relevant folder and copy it, delete the database and copy the folder back. If that works go for the big one.

If your servers are both linux the same principles should apply.

[tbp]

Whilst you can copy the raw data files over, it's not really recommended, and you would only be able to do it on a dedicated server you have full access to.

The best way to do it is to use MySQL dump, or PHPMyAdmin to dump the whole database to a file, which contains all the SQL statements needed to rebuild the database on the new server.

On the new server, you run the following command from the command line:

mysql -u USER -p DBNAME < dump.sql

Where DBNAME is the name of the new database to insert into, and dump.sql is the name of the dump file. This will execute the instructions in the file and reconstruct your database on the new server.

If you get stuck, feel free to PM me, and i`ll help you out.

[Brett]

Hi Barry,

Are both of the servers running CPanel?

There's a back up option in CPanel that you could use to back it up from the old server (downloads as a zip file) and then 'restore' it on the new server?

Never done it before but sounds right

Brett

[Paulzie]

Agree'd if there both CPanel installs it is a fairly simple process to create a full backups, copy the file, and restore it.

Have done this with a couple large websites also.

Doing the MYsql dump will probably need command line access. If importing a large databaase it it likely to time out using PHPmyadmin

[tbp]

If importing a large databaase it it likely to time out using PHPmyadmin

Yes, one of the problems with phpMyAdmin is theres a very low limit as to the amount of data you can upload through the upload function, usually round about 2Mb.

As a result, you end up having to copy chunks from the backup file into the form in phpMyAdmin by hand, as doing it in one go usually gives a timeout error as you rightly say.

The best way to do it is through the command line, although if you can't access this yourself your host will usually do it for you if you ask nicely.

[Reddy]

Hi,

I use a program called SQLyog (there is a freeware version available - webyog.com) which I use to access/maintain Mysql databases. There are also functions in there to import/export databases/tables or copy them to different hosts/servers.

regards
David

[golles]

mysql administrator - MySQL :: MySQL Administrator

part of the mysql tools suite - MySQL :: MySQL Tools

Free - and we move databases regularly using it.

the backup / restore works in a simialr way to phpmyadmin but you are not resticted on size as it is a client application