Moving a MySQL Instance to another Server
To move an innodb database or instance from one server to another can be done using several methods.
1. Backup and Recovery - Back up instance using Percona or zmanda (mysqldump), then restore to new instance.
The problems with this method is that it can be very slow and time consuming and result in a lot of downtime of the live system, especially if a dump is used and the database is large.
2. Using a file copy. This is the preferred option when moving an entire instance for a live system as it results in much less downtime. Guide as follows:
a) Create my.cnf on target which is the same as the source (however, there may be differences to file locations and buffer pool if there are more resources available)
b) Ensure firewall rules set up to allow an scp of a file from the source server to the target server.
c) Turn off source instance
d) scp the entire data directory to the new location on the target server.
e) ensure the redologs are also scp'd over to the target server.
f) Once files copied ensure the permissions on the copied files are mysql:mysql. If not then change.
g) Start instance on target server.
h) Check error log for errors. If you have copied to a newer version of mysql (I tested from 5.5 to 5.6) then you will see errors related to the structure.If so then run mysql_upgrade on new instance to upgrade the database.
1. Backup and Recovery - Back up instance using Percona or zmanda (mysqldump), then restore to new instance.
The problems with this method is that it can be very slow and time consuming and result in a lot of downtime of the live system, especially if a dump is used and the database is large.
2. Using a file copy. This is the preferred option when moving an entire instance for a live system as it results in much less downtime. Guide as follows:
a) Create my.cnf on target which is the same as the source (however, there may be differences to file locations and buffer pool if there are more resources available)
b) Ensure firewall rules set up to allow an scp of a file from the source server to the target server.
c) Turn off source instance
d) scp the entire data directory to the new location on the target server.
e) ensure the redologs are also scp'd over to the target server.
f) Once files copied ensure the permissions on the copied files are mysql:mysql. If not then change.
g) Start instance on target server.
h) Check error log for errors. If you have copied to a newer version of mysql (I tested from 5.5 to 5.6) then you will see errors related to the structure.If so then run mysql_upgrade on new instance to upgrade the database.
Comments