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?

Answer 1

You can simply copy the whole /data folder.

Have a look at High Performance MySQL - transferring large files

Answer 2

I have no experience with doing this with mysql, but to me it seems the bottleneck is transferring the actual data?

4oo MB isnt that much. But if dump -> SCP is slow, i dont think connecting to the db server from the remove box would be any faster?

I'd suggest dumping, compressing, then copying over network or burning to disk and manually transfering the data. Compressing such a dump will most likely give you quite good compression rate since, most likely , theres a lot of repeptetive data.

Answer 3

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

Answer 4

try importing the dump on the new server using mysql console, not an auxiliar software

Answer 5

If your only copying all the databases of the server, copy the entier /data dir. if your just copying one or more databases and adding them to an existing mysql server.

-> create the empty database in the new server, set up the permissions for users etc.

-> copy the folder for the database in /data/databasename to the new server /data/databasename

Answer 6

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).

Answer 7

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.

Answer 8

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.