Dani,

My customer's MySql db's are tiny--nothing like your 1GB monster. I simply use the mysqldump command. As you most surely know, this command simply creates a text file that includes all the SQL statements to create your tables and indexes and insert all the data. For you, these files would be enormous. You would want to dump one table at a time. You'll probably want to use the --opt option which locks tables and disables buffering. (Otherwise, mysqldump tries to buffer the entire table to memory before writing to file--bad idea with your big tables.)

You can use the mysqlimport program to import the mysqldump files.

I've not used mysqlhotcopy, but it's supposed to be faster than mysqldump. It is only for MyISAM and ISAM tables--which you probably have. That's the default table type, and all my tables in my MySQL databases are MyISAM. I guess 5.0 supports the InnoDB type. With InnoDB databases, you can simply copy the raw files from one server to another (as long as both computers use the same floating point logic, etc.)

Hmmm...reading more about mysqlhotcopy....this may be a good solution for you. (At your linux server prompt, read the doc using `perldoc mysqlhotcopy`--worked for me anyway.) From the manpage:
mysqlhotcopy supports scp copying--meaning you could copy directly to the new server, but this would keep the live server locked longer. Assuming you have enough disk space, the docs recommend copying to local disk, then scp to the new server "at your leisure". Then again, you really want the db locked until the new system is online. Like you said, you don't ever want any more updates to the old db once you start the copy process.

I just haven't had experience with any relatively large MySql databases. Please follow up and let us know your experience.

Thanks for the reply, Troy. I actually use mysqldump nightly to do a large .sql dump of DaniWeb for backup reasons. Backing up to a massive .sql file is not a problem - it's not like I have to ever open the file in a text editor or something

The source statement that I referred to actually takes what is in the .sql file and imports it back into the database. THAT is what takes so long, because it has to do one INSERT query at a time. As you can imagine, there are hundreds of thousands of INSERT queries. Doing one INSERT query at a time takes much longer than simply writing line after line to a single text file.

I'm using MySQL 4 and am not sure yet whether to go 4 or 5 with the new database. I will have to research I assume. However, I will look into your suggestions. Thanks!

HI cscgal ,

why not use the rsync program over ssh to move your database to new server I presume this would be on linux server.

the only think is you would need to shutdown both mysql servers to use rsync as you would get database errors.

commands are very simple:
login as root with putty secure shell to server.

1. shutdown mysql databases
run command service mysql stop on both servers.

2. run rsync to move database

login to new mysql server as root and type command:

rsync -av -e 'ssh -p 22' root@old-servers-ip-address:/var/lib/mysql/yourdatabase/ /var/lib/mysql/yourdatabase/

when completed restart mysql on both servers with service mysql start

your databse should now be on the new mysql server ready for use.

As for moving to MySql 5. I'd surely recommend it. We moved from 4 to 5 a while back, and noticed almost immediately an improvement in performance. Also 5 has transactions and stored procedures, the latter may help you with you admin tasks.

At the Php Conference here I learned that MySql 6 is going to have events, which will make your admin life a while lot easier. So maybe just wait for now and go for 6 when it is stable.

Jst my $0.02...

This thread is old. Methinks she would have sorted it out by now

You're right, didn't even notice the date... mea culpa.