On 9/6/2011 8:23 PM, Anthony Acquanita wrote:
Hello everybody!
So I'm moving a rather large mysql DB from one server to another. It
sucks. I can't have any real long downtime so dump/restore, not an
option.
Keep in mind dump/restore is a very very poor process for moving large
amounts of data.
Basically, dump/restore using the classic mysqldump is a serial process,
it creates one table - with all it's indexes and keys, then imports each
record one by one, then moves on to the next table. For a modern
system, this sucks because your basically single threaded.
Instead you want to dump:
All your table structures, but not indexes or keys
Each table individually, and for really large tables, break them up into
multiple files
All your table indexes and keys
So your first step is to recreate all the tables, then run a bunch of
concurrent processes to import the individual files, thus going from
serial to parallel processing.
Finally at the end, apply your indexes and keys. By waiting till the
end to apply them, your speeding up the inserts since they don't need to
keep updating the indexes and checking for keys.
Because this is extremely database specific, I have not run across a
tool to do this.
It's innodb so I can't rsync and repair. What I'm using now is
percona xtrabackup which gives me a window of about three hours from
read lock to recovery. Not too bad for a 800+GB DB.
Never mind all that. The real question is, "What can I do now to
avoid this in the future?" I don't have a second box yet so a slave
won't help.
Why won't a slave help? You can run multiple instances of your mysql
server on the same system with different ports. You can even run them
with different table engines.
So you have your primary database:
Master MySQL server, Innodb tables
and on the same machine
Slave MySQL server, myisam tables
So a transfer process could be:
Shutdown the slave mysql server
Transfer all the myisam files to the new system
Load a new mysql slave server(secondary-slave] there, recover the myisam
tables, start replicating to pickup any missed records
Load a second slave mysql server, this one temporarily slave to the
secondary slave, and snarf all the records locally into innodb tables
Kill the innodb slave, now slave it to the original master server and
bring it back up...just in case anything was created between the time
you synced
Bring down the master innodb server, followed by bringing down the other
2 secondary system servers
Reconfigure the secondary innodb server as the master, reconfigure the
secondary myisam server to pull from the new master
Restart the secondary system servers....test test test to verify all is well
Decommission the primary server.
It all depends on where your costs are located. Really convenient would
be to store all your myisam tables on a physical hard drive that you can
move from one machine to another.
_______________________________________________
Mid-Hudson Valley Linux Users Group http://mhvlug.org
http://mhvlug.org/cgi-bin/mailman/listinfo/mhvlug
Upcoming Meetings (6pm - 8pm) MHVLS Auditorium
Sep 7 - DIY 3D Printing and the Makerbot Thing-o-Matic
Oct 5 - Distributed Authentication Systems
Nov 2 - Nov 2011
