I have a deployed rails app that has been running for several months on
one ISP.
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.
cheers,
jp
JP -
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
you go.
Thanks
On Wed, Nov 12, 2008 at 8:36 AM, Jeff P.
[email protected] wrote:
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 S. ------------------------ [email protected]
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?
You’ll have to tell MySQL to allow connections for remote hosts, give
new permissions, and edit your database.yml file accordingly to the IP.
–
French training by VoD: http://www.digiprof.fr
Jesse P. wrote:
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
you go.
I recommend to use SQL dumps instead of binary dumps, especially since
you’re not sure if the MySQL versions will be identical. SQL dumps will
be a little slower but provide better interoperability between versions.
Having a good test suite is the best way to ensure compatibility. In
that case you can run “rake test” or “rake spec” on your soon-to-be
production server.
–
Roderick van Domburg
http://www.nedforce.com
Roderick van Domburg wrote:
Jesse P. wrote:
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
you go.
I recommend to use SQL dumps instead of binary dumps, especially since
you’re not sure if the MySQL versions will be identical. SQL dumps will
be a little slower but provide better interoperability between versions.
Having a good test suite is the best way to ensure compatibility. In
that case you can run “rake test” or “rake spec” on your soon-to-be
production server.
–
Roderick van Domburg
http://www.nedforce.com
I’m the original poster. Just wanted to let everyone know that this
went off without a hitch awhile back. I just used SQL dump. No
problems at all.
thanks,
jp