HOWTO: Moving MySQL

HOWTO: Moving MySQL

Postby admin » Thu May 01, 2003 5:04 am
This is how I do it:

Old server:

cd /var/lib/mysql
tar -zcvf filename.tgz databasename

New server:

cd /var/lib/mysql
scp serverx.serverdomain.com:/var/lib/mysql/filename.tgz ./

[ enter password ]

tar -zxvf filename.tgz

 

you should stop your mysql server before doing this and generally you need to change the user permissions (chown) if mysql is run under different users on OLD and NEW servers

 

If you use this method, some of your MyISAM tables may need repairing on the target server afterwards if they were open at the time you copied them.

Use:

mysql> repair table myDB.mytable;

Code (markup):

Shutting MySQL down first will prevent this but will mean that your site will be down while you do it. If you don't shut down MySQL before copying then you may also lose data in the transfer. Anything that is written to your server after you copy the files will be lost.

This technique will only work for MyISAM tables. InnoDB tables will not work in this way. If you have any InnoDB tables, don't even attempt this. Heap and Memory tables will also not work because they are never written to disk. If you have Heap or Memory tables, the following method will work.

For InnoDB, Heap and Memory tables, the easiest way is to use mysqldump:

[root@host]# mysqldump -uroot -p myDB myTable > myTable.sql

Code (markup):

Copy the .sql file to the new server and:

[root@host]# mysql -uroot -p myDB < myTable.sql

Code (markup):

This may involve quite a bit of downtime if you have large tables or low bandwidth between the servers. You can improve the speed significantly by making the whole process into one streamlined command:

[root@host]# mysqldump -holdhost -uroot -p myDB myTable | mysql -hnewhost -uroot -p myDB

Code (markup):

If you want to do a MySQL migration with very little downtime, there are two relatively simple strategies:

1. You can set up MySQL replication from the old host to the new host and once it is up to date, switch the web server to point at the new one. This will involve a second or two of downtime to make sure that the slave has caught up to the master before switching.

2. You could set up MySQL cluster. With MySQL cluster, the MySQL server doesn't actually store any data which means you can use as many MySQL servers as you want simultaneously. The data is stored on data nodes and every piece of data is stored on at least two nodes. This strategy will have no downtime at all (once it is set up) but has the downside of requiring at least 4 physical servers: 1 old host, 1 new host and two data nodes.