Migrating a MySQL server from one box to another

Question

The databases are prohibitively large (> 400MB), so dump > SCP > source is proving to be hours and hours work.

Is there an easier way? Can I connect to the DB directly and import from the new server?

Solution 1

You can simply copy the whole /data folder.

Have a look at High Performance MySQL - transferring large files

Solution 2

Use can use ssh to directly pipe your data over the Internet. First set up SSH keys for password-less login. Next, try something like this:

$ mysqldump -u db_user -p some_database | gzip | ssh someuser@newserver 'gzip -d | mysql -u db_user --password=db_pass some_database'

Notes:

• The basic idea is that you are just dumping standard output straight into a command on the other side, which SSH is perfect for.
• If you don't need encryption then you can use netcat but it's probably not worth it
• The SQL text data goes over the wire compressed!
• Obviously, change db_user to user user and some_database to your database. someuser is the (Linux) system user, not the MySQL user.
• You will also have to use --password the long way because having mysql prompt you will be a lot of headache.

Solution 3

You could setup a MySQL slave replication and let MySQL copy the data, and then make the slave the new master

Solution 4

400M is really not a large database; transferring it to another machine will only take a few minutes over a 100Mbit network. If you do not have 100M networks between your machines, you are in a big trouble!

If they are running the exact same version of MySQL and have identical (or similar ENOUGH) my.cnf and you just want a copy of the entire data, it is safe to copy the server's entire data directory across (while both instances are stopped, obviously). You'll need to delete the data directory of the target machine first of course, but you probably don't care about that.

Backup/restore is usually slowed down by the restoration having to rebuild the table structure, rather than the file copy. By copying the data files directly, you avoid this (subject to the limitations stated above).

Solution 5

If you are migrating a server:

The dump files can be very large so it is better to compress it before sending or use the -C flag of scp. Our methodology of transfering files is to create a full dump, in which the incremental logs are flushed (use --master-data=2 --flush logs, please check you don't mess any slave hosts if you have them). Then we copy the dump and play it. Afterwards we flush the logs again (mysqladmin flush-logs), take the recent incremental log (which shouldn't be very large) and play only it. Keep doing it until the last incremental log is very small so that you can stop the database on the original machine, copy the last incremental log and then play it - it should take only a few minutes.

If you just want to copy data from one server to another:

mysqldump -C --host=oldhost --user=xxx --database=yyy -p | mysql -C --host=newhost --user=aaa -p

You will need to set the db users correctly and provide access to external hosts.

View more solutions

Comments

[wdalhaj]

you don't need to use ssh. You could simply do: mysqldump -u db_user -p some_database | mysql -u db_user -p -h newserver some_database

[Brad Wright]

They're not in the same colo, sadly.

[Jason]

That link appears to be dead. However, here's what I have done. Jump into terminal on the source and type 'locate my.cnf'. open the file in your fav text editor (vi /etc/mysql/my.cnf) and fine the line "datadir=". Then all you need to do is repeat this on your target server. Now you should compress the contents of that folder on the source and ftp it to the target. Then simply unzip it in the data dir.

[Peter Lindqvist]

But it is common to have disabled remote access in mysql, at least where i'm from. Whereas SSH is readily available. It is possible in theory given the correct grants, but the ssh answer is to me the most realistic use case.

[Slashterix]

Instead of gziping on both sides of the ssh you can just use -C for ssh to compress its traffic

[Michał Leon]

400MB is not large, and SCP offers compression. In practice it is about 2× faster.