I'm moving everything to a different ISP in the next couple days.
What is the easiest way to duplicate the production database on the new
server? How careful do I need to be about major/minor version numbers
on the mysql server software on both ends? The destination VPS is a new
Centos5 install, so is quite probably a little newer across
the board than the almost a year old source VPS. Sorry I didn't have
the actual version numbers handy while I wrote this.
The data being moved is just from a social networking type site, not
accounting or something where the world would come tumbling down if
there are a few minor differences after the transition.
Other than the database, getting all the necessary gems installed, and
getting the new Capistrano recipe setup right, is there anything else I
haven't thought of that is lurking there to bite me?
Thanks in advance for any words of wisdom before I tackle this thing.
Posted via http://www.ruby-forum.com/.
We use CentOS 5.2 as our default install on our virtual servers, so
we're pretty familiar with the installation and process. To really
answer your question, you'd want to look at the two specific versions
you're moving to / from. There can be some specific glitches that crop
up depending on version differences, so without knowing the specifics,
it's hard to say.
The easiest way to do the actual duplication would be to stop your
existing MySQL server, tar up the binary MySQL files and move that over
to your new instance. Again, depending on versions, you should just be
able to place the binaries in the right place and start MySQL and away
> What is the easiest way to duplicate the production database on the new
> server? How careful do I need to be about major/minor version numbers
> on the mysql server software on both ends?
It could be significant (4.x -> 5.x, for instance). You would probably
be well-advised to get the version numbers, read the change notes,
*and* ask on a MySQL-specific mailing list.
Regardless, I would *never* move binary files between systems; the
mysqldump program works just fine, and insulates you from platform
differences. And gives you a backup file at the same time, just in case.
The biggest gotcha I've personally run into moving from one instance
to another is encoding. Run
mysql> show variables like '%char%';
mysql> show variables like '%collation%';
on each instance and set appropriately. You probably also want to
compare the defaults for `old_passwords` and `storage_engine`, at
least. Compare the config file(s) as well.
HTH, and good luck,
Hassan Schroeder ------------------------ hassan.s...@gmail.com
I agree with Hassan, we do this move for people all the time when
they move to engine yard and we *never* move the mysql binary files,
it hardly ever works unless you are running the exact same mysql
version on the exact same OS. You are far better off with mysqldump.
However, if your database is of more modest size, MySQL dump can provide
a nice method to transfer data. You can even chain the dump and import
process together to move the bottle neck from disk to the network and
cause the transfer to complete faster. Here's an untested example:
[user@oldhost ~]$ mysqldump -u USER -pPASSWORD DATABASE_NAME | \
mysql -h newserver.com -u USER -pPASSWORD DATABASE_NAME
Please note, this will dump your SQL data over the unencrypted MySQL
protocol, so you may want to look at creating a SSH tunnel between your
two hosts for added security.
The point to copy the appropriate sections from your my.cnf is also a
If you have more information on the specifics of your old and new
environment, this group can probably be even more helpful.
Alex Malinovich wrote:
> I definitely agree with Hassan. Moving the MySQL binaries over is
> definitely easy to do, but if the versions of MySQL don't match
> precisely you could be asking for trouble. The safe option is to just
> use mysqldump to create a backup and then import it on the new db.
Blue Box Group, LLC
p. 800-613-4305 x 801