How to move whole tables fast - MySQL

[Rune]

I have two MySQL databases on different servers and computers. I often need
to move a number of tables from one database to the other database. So far I've
been using "mysqldump" to make a backup, and "mysql" to load the backup in
the other DB, but the amount of data is fairly large and it is very slow. Is
there a faster method? I though of simply copying the database files, but
the two database servers are not the same version, so I don't know how that
will work.

Thanks
/Rune

[Giuseppe Maxia]

Rune wrote:

> I have two MySQL databases on different servers and computers. I often need
> to move a number of tables from one database to the other database. So far I've
> been using "mysqldump" to make a backup, and "mysql" to load the backup in
> the other DB, but the amount of data is fairly large and it is very slow. Is
> there a faster method? I though of simply copying the database files, but
> the two database servers are not the same version, so I don't know how that
> will work.
>
> Thanks
> /Rune
>
>

To speed up a dump, you can use

mysqldump --opt -u USER -pPASSWORD database_name table_name > file.sql

That way, mysqldump will try all the known tricks to minimize the rebuild time.
[url]http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html[/url]

If you were already using that option and it's still too slow for your needs, you may
want to consider tion instead of dumping tables.
[url]http://dev.mysql.com/doc/refman/5.0/en/tion.html[/url]

Copying tables is a tricky business. To get a clean copy, you should stop the server,
copy the tables and restart the server. Or you could flush the tables, lock them all
and copy with the server still running. mysqlhotcopy will do the dirty work for you
[url]http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html[/url]
However, it works only for MyISAM tables, and only on Unix and Netware systems.

Table formats are backward compatible, i.e. a table from a 4.0 server will be read
and understood by a 4.1 server, but not the other way around.

ciao
gmax

--
_ _ _ _
(_|| | |(_|>< The Data Charmer
_|
[url]http://datacharmer.blogspot.com/[/url]

[Bill Karwin]

"Rune" <slamslam.slam> wrote in message
news:43d0d796$0$15791$14726298news.sunsite.dk...

>I have two MySQL databases on different servers and computers. I often need
>to move a number of tables from one database to the other database. So far
>I've been using "mysqldump" to make a backup, and "mysql" to load the
>backup in the other DB, but the amount of data is fairly large and it is
>very slow. Is there a faster method?

According to the docs, LOAD DATA INFILE is up to 20x faster than running a
lot of insert statements from a script. You could dump the table(s) with
SELECT...INTO OUTFILE, transport that data dump to the other server, and
load it with LOAD DATA INFILE.

Read about LOAD DATA INFILE here:
[url]http://dev.mysql.com/doc/refman/5.0/en/load-data.html[/url]

There are further tips on performance enhancements to loading data here:
[url]http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html[/url]
(e.g. use ALTER TABLE...DISABLE KEYS and SET FOREIGN_KEY_CHECKS=0 before the
data load, then re-enable them afterwards).

Regards,
Bill K.