1

I have a large Mysql db (over 10gb). I'm moving from one hosting provider to another one (both in CentOS).

My idea was to do this:

Old server:

tar -cvf backup.tar backup.sql

New server:

wget oldserver.com/backup.tar
tar -xvf backup.tar

My question is, how can i generate the backup.sql file so that it can be easily restored on the new server via ssh?

3 Answers 3

3

Use mysqldump.

The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format.

1

If all your tables are MyISAM, you can just copy over the entire directory (through rsync or some other file transfer utility) and you don't need to dump and tar anything. You could use rsync's compression algorithm:

rsync -avz mysql_dir user@remotehost:~/

4
  • From a disk I/O perspective, I'm always wary to do this without checking the consistency of the data.
    – user3914
    Nov 22, 2010 at 22:43
  • The assumption is that he'd flush tables with read lock and then shut down mysqld before doing this. Personally, my database servers use LVM so I use mylvmbackup: lenzg.net/mylvmbackup
    – Vic
    Nov 22, 2010 at 23:04
  • nice approach, but mine are not MyISAM :(
    – Andres SK
    Nov 22, 2010 at 23:07
  • Then your BEST way to do it is to use a snapshot, or alternatively do the mysqldump method + rsync/scp/wget as suggested above.
    – Vic
    Nov 22, 2010 at 23:11
1

If having very small downtime is an issue, and you have a very active database, you may want to consider setting up the new server to slave off the old server. Follow the MySQL replication documentation, setting up the new server as a slave. Then, when you are ready to make the change, you just start having your applications access the new server instead of the old -- perhaps by bringing the IP address down on one and up on the other, reconfiguring your application to the the new server address, etc...

However, this is usually overkill for most sites. Usually you can take a few minutes of downtime to do the move, using a mechanism such as what follows...

Start with just priming the copy while MySQL is running on the source server (and down on the destination), with something like:

rsync -avzP --delete /var/lib/mysql/ root@destination:/var/lib/mysql.source/

I run it to a destination of "mysql.source" so that I can be sure that if I make a mistake I'm not, say, overwriting the active server with data from the new server. Depending on the speed of the network, this may take a long time (say, if you are moving from one provider to another over a 1mbps link).

Once this finishes, run it again to transfer the changed files. This should be dramatically faster.

After re-running it a few times, it will probably get down to something very small, maybe a few seconds.

At this point, you are ready for the final rsync. To do that:

ON OLD SERVER:
/etc/init.d/mysql stop
rsync -avzP --delete /var/lib/mysql/ root@destination:/var/lib/mysql.source/

ON NEW SERVER:
cd /var/lib
mv mysql mysql.orig
mv mysql.source mysql
/etc/init.d/mysql start

Of course, this assumes that you have already copied over your my.cnf and made appropriate adjustments if the system is larger or smaller than the old one (changing cache sizes and the like).

If having very small downtime is an issue, and you have a very active database, you may want to consider setting up the new server to slave off the old server. Follow the MySQL replication documentation, setting up the new server as a slave. Then, when you are ready to make the change, you just start having your applications access the new server instead of the old -- perhaps by bringing the IP address down on one and up on the other, reconfiguring your application to the the new server address, etc...

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .