I would like to move my forum database to a new server. However, the MySQL database is approximately 700 megs, and takes a great deal of time to import. Doing a mysql> source backup.sql took well over 12 hours the last time I did it, and that was months ago. It will most likely take much longer than that.

What is the best way to go about doing this? My current plans would be to lock all new registrations and posting on the site for about two days while I copied over the database on the other machine. Then, set up the site to use the new database. I would be making the site read-only so that new data isn't being added to the old database which will be lost as soon as I set up the forum to use the new database. However, there has to be a more seamless way of doing it than making the site read only for two days.

If I were to leave the forum active, I would end up at the end of the day with two databases, one of them two days fresher than the other. Is there a way that I could then just import those two days worth of data to the new database? It's probably too good to be true, but is there some sort of database comparison utility that would work in this case?

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.