Copy MYSQL to another server.

Portal Home > Knowledgebase > Articles Database > Copy MYSQL to another server.

Posted by Jimerson, 09-04-2007, 09:38 AM
I would like to copy all MYSQL databases, users, etc.. to another server. One server is RHEL 4 and the other is RHEL5. What do I need to copy to insure everything is moved over, Thanks, Jim
Posted by weber_, 09-04-2007, 10:13 AM
You have to install the same mysql version you have (recommended) on the new server and just copy whole mysql folder /var/lib/mysql
Posted by Jimerson, 09-04-2007, 10:15 AM
Does it have to be the same version? If not what specific files should I copy. Thanks, Jim
Posted by weber_, 09-04-2007, 10:17 AM
It is recommended to install the same version but is not required. I told that it needs to copy whole mysql folder /var/lib/mysql
Posted by tanfwc, 09-04-2007, 10:17 AM
you might want to use mysqlhotcopy to copy all the database under /var/lib/mysql as one of the database contains all the permission rights.
Posted by derek.bodner, 09-04-2007, 12:11 PM
unless there are innodb tables.
Posted by weber_, 09-04-2007, 12:34 PM
Just stop mysql and copy everything. No 3rd party tools is needed.
Posted by david510, 09-04-2007, 02:54 PM
You can write a small script to dump all the databases and copy those dump files to the new server and restore all dbs there. If you are moving from mysql 4.1 to 4.0, just copying the /var/lib/mysql may cause issue. Here you need to take the 4.0 compatible dump and restore.
Posted by jdfcomp, 09-04-2007, 04:52 PM
I would start with both servers on same version then stop mysql on both servers use rsync (search and find the commands needed) to copy the /var/lib/mysql to the new server then upgrade the new server to the higher version start off on the same level much fewer issues
Posted by whmcsguru, 09-04-2007, 08:31 PM
The safest and easiest way to do this is to do a dump of the database, NOT copy entire directories over. Simply do a dump of each and every database you want to copy over. Copying the entire mysql directory will screw something up inevitably. Either you'll get something in the middle of a write, or you'll get something locke, or something, anything. Just do a dump. It's the safest and most effective way to move sql from host1 to host2.
Posted by david510, 09-04-2007, 11:13 PM
I agree 100% with linux-tech.
Posted by weber_, 09-05-2007, 04:40 AM
I do not agree. I agreed that this is the most correct way. What if you have to deal with foreign charsets and collations? In 10% cases database dump goes fine, but when you reload db it fails due to unrecognized characters etc. Therefore, IMHO the easiest, fastest and safest way is indeed to copy whole mysql folder.
Posted by whmcsguru, 09-05-2007, 04:59 AM
Not an option. RHEL4 uses MySQL4 RHEL5 uses MySQL5 While you CAN upgrade, if you're not using something like CPanel to address things, you're going to run into issues with updates, dependencies, and, let's just say it'll be very very messy. You can NOT copy binaries like this, as they will screw everything up. You DO want your server (mysql) running, right? You can NOT copy binary data like this, period. The best, and safest method to do this is through mysqldump, or if you're lucky to be able to use it mysqlhotcopy. Even BETTER would be using something to automatically dump it into the new server. If there were a better way to do things, by all means, let it be done, but copying entire mysql directories is the WRONG approach for this and WILL, in fact lead to more issues!
Posted by david510, 09-05-2007, 05:15 AM
In this case, we need to tweak the mysql configuration. If the mysql version is newer it will not be of much problem.
Posted by weber_, 09-05-2007, 06:01 AM
If you read my first posts in this thread I wrote that it needs (recommended) to have the same versions of MySQL of both servers. I think we talk about copying of MySQL to another server rather than copy with upgrade. In the upgrade case of course copying binaries will screw up everything but not exactly.
Posted by whmcsguru, 09-05-2007, 06:11 AM
Not possible Unless you have customized your server (by which you wouldn't be asking here how to move a database), it's not going to happen. RHEL4 comes with mySQL4 installed RHEL5 comes with MySQL5 installed Changing ANY of these, removing ANY of these without knowing what you're doing can cause CRITICAL ISSUES! You can NOT move a "mysql4" directory to a "mysql5" directory and expect it to work. You can NOT hotcopy a mysql4 server to a mysql5 server and expect it to work. It won't work.

Add to Favourites Print this Article