Transfering a large MySql database

E-Oreo · 06-03-2004, 05:37 PM

I was wondering if anyone has any tips on moving a large MySql database. I switched servers and I need to transfer a 10 megabyte table from one server to the other. PhpMyAdmin stalls when you ask it to run a 10Mb file, what other ways are there to transfer a database from one server to another?

ricstirato · 06-03-2004, 06:58 PM

Right, phpMyAdmin is very slow and has also a (configurable iirc) limit for the SQL file's size.

You simply can use the command line client. It's much faster and does not care about the file's size.

Assuming you already created the database on the destination system, do:

mysql -u username -p database_name < sql_dump_file

You will be prompted for the password and on it goes.
If you don't use a password for the user, omit the "-p"

E-Oreo · 06-03-2004, 07:06 PM

Ok, I just have a few questions.

Where do I run this, in the CLI of my local computer? If so where do I tell it what host to use.

I only need to dump one table, is there any way to do that?

After I have the dump downloaded to my local computer how do I upload it back onto the other server?

Thanks for the help,
Tim

Hko · 06-04-2004, 07:15 AM

Quote:

Where do I run this, in the CLI of my local computer?

This is your choice, but if you have shell access to the host running the database, you get better performance doing it there.

Quote:

If so where do I tell it what host to use.

mysql -u username@hostname -p database_name < sql_dump_file

Quote:

I only need to dump one table, is there any way to do that?

Read "man mysqldump".

Quote:

After I have the dump downloaded to my local computer how do I upload it back onto the other server?

That was what ricstirato's commandline does. To make the dump use "mysqldump", see its man page.