Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a centos server and /var/lib/mysql/ is 125GB (disk has 1GB free space).

Ordinarily I would use mysqldump to backup the databases, but I don't normally work with such large databases, so I need to know the safest way of copying the databases over to a new server.

All advice appreciated!

share|improve this question

You can use following steps

On old Server

1.Stop mysql server

2.Copy contents of datadir to another location on disk as ... mysqlbackup

3.Start mysql server again

4.Compress the data (tar -czvf mysqlbackup.tar.gz mysqlbackup)

5.Copy the compressed file to new server

On New Server

1.Install MySQL [MySQL version should be same as of old server](don't start MySQL server)

2.Unzip compressed file (tar -xzvf mysqlbackup.tar.gz)

3.Move contents of mysqlbackup to the datadir.

4.Make sure that permissions of datadir are correct

5.Make sure your innodb_log_file_size is same on new server, or if it's not, don't copy the old log files (MySQL will generate these)

6.Start MySQL.

You can also look at How can I move a database from one server to another?

share|improve this answer

From new server, assuming you have a public/private key pair set up

ssh root@oldserver mysqldump database | mysql database

should work fine, I don't think any disk space will be used at all

share|improve this answer
Don't forget to use ssh -C for on-the-fly compression. – Twinkles Apr 17 '15 at 17:01

Usually you run mysqldump to create a database copy and backups as follows:

$ mysqldump -u user -p db-name > db-name.out

Copy db-name.out file using sftp/ssh to remote MySQL server:

$ scp db-name.out

Restore database at remote server (login over ssh):

$ mysql -u user -p db-name < db-name.out


$ mysql -u user -p 'password' db-name < db-name.out
share|improve this answer

While a lot of answers are on the right track, none of them seem to validate that your data is actually consistent, here is what I do for databases (small or large):

  1. Ensure that the database is not being used; disable/turn off the application. This is to ensure that the dump is consistent.
  2. Dump the database using mysqldump -u <user> -p <database> out.sql.
  3. Generate a hash to verify the integrity of the database using: md5sum out.sql.
  4. Compress the database to reduce the time it takes to move to a new host: tar -czf out.tgz out.sql
  5. Use scp or rsync to copy the database dump to a new host: scp out.tgz blah@
  6. Decompress the database: tar -xzf out.tgz
  7. Verify integrity with md5sum out.sql.
  8. Import the database: mysql -u <who> -p <database> < out.sql.
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.