Move MySQL database/app to different isp?
Jeff Pritchard
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
--
Posted via http://www.ruby-forum.com/.
Jesse Proudman
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
- Jesse
Hassan Schroeder
<ruby-foru...@andreas-s.net> 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 Schroeder ------------------------ hassan.s...@gmail.com
Alex Malinovich
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.
On Nov 12, 10:00 am, "Hassan Schroeder" <hassan.schroe...@gmail.com>
wrote:
>
Ezra Zygmuntowicz
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.
Cheers-
Ezra Zygmuntowicz
e...@engineyard.com
Jesse Proudman
original database was. Running MySQL dump and re-import on a 25GB
database can take a significant amount of time that may prove
unacceptable based on your traffic levels. In our environments where
the MySQL versions are the same or within a few minor revisions and the
physical platforms are the same, moving the binary file provides a more
rapid transition option.
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
good one.
If you have more information on the specifics of your old and new
environment, this group can probably be even more helpful.
- Jesse
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.
--
Jesse Proudman
Blue Box Group, LLC
p. 800-613-4305 x 801