Moving large databases

Question

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!

Answer 1

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?

Answer 2

Since the Source DB only has 1GB, you have no room to mysqldump anywhere.

What you need is the following

• Port 3306 open in the Source DB server's firewall
• Run mysqldump program from the Target DB server

For a Source DB server whose IP address is 10.20.30.40, login to Target DB and run this

PIPE mysqldump INTO MySQL Instance on Target DB Server

MYSQL_HOST=10.20.30.40
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQLDUMP_OPTIONS="--all-databases --add-drop-database --single-tranactions"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines --triggers"
mysqldump -h${MYSQL_HOST} ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS}| mysql ${MYSQL_CONN}

PIPE mysqldump into SQL File. Load MySQL from SQLFile

MYSQL_HOST=10.20.30.40
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQLDUMP_OPTIONS="--all-databases --add-drop-database --single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --hex-blob --routines --triggers"
mysqldump -h${MYSQL_HOST} ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > MySQLData.sql
mysql ${MYSQL_CONN} < MySQLData.sql

FINAL NOTES

Use Technique #1 is there is no diskspace to save a SQL File. Use Technique #2 if you want to dump the file and load it at a later time.

Answer 3

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 [email protected]:/backup

Restore database at remote server (login over ssh):

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

OR

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

Answer 4

This worked for me:

cd /var/lib/
rsync -rav mysql newmachine:/var/lib/

Answer 5

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

Answer 6

@user2029574, is your Hardware in cloud? I too once stumbled into the same problem that you were/are here. However, my installation of MySQL is in AWS and you know that means a lot of ease to attach yet another larger sized disk. Here I'm summing up what I did:

1. Attach a new larger disk to your current MySQL server instance.
2. Use Percona's innobackupex to prepare the full backup of the current MySQL data in the new disk that you've attached.
3. Prepare yet another instance with MySQL installed.
4. Detach the disk attached to instance 1.
5. Now attach the disk to the new instance, instance 2.
6. Stop the MySQL service in the instance 2.
7. Edit the MySQL config file so that the data dir points to the new location.
8. Restore the database in the new instance. Again, innobackupex, is there for your help.
9. Start the MySQL server in instance 2.

This way, you can have your applications that connect to this DB up all the time and you can also upgrade the profile of your cloud instance too, if that too is a requirement.