I'm sure that would work but it's a lot of work and time and doesn't help me the next time I ( or someone else ) has to do this.
I'm stuck at step1 as well. I'd have to shutdown this box to configure bin logging ( it's a hybrid master/slave ). It takes 20 to 30 min to quiesce this thing, product team rolls eyes any time I have to do this. Even a somewhat parallel backup you described ( which was awesome ) would be too long. I could start mysql in read lock mode, mark the master log pos, unlock, backup, restore, point slave to the master log pos which is in the past and hope for the best ( this has both worked and failed for me in the past ). What I like about drdb is the option to go from master to slave, promote slave to master, setup new slave lather rinse repeat. So am I wrong in assuming I need to keep the DB down while doing the parallel backup? Has anyone done a rsync of innodb tables with mysql running, shut down mysql and run rsync again? Are the subsequent rsyncs deltas or are the ibd files vastly different and no time is saved from having done it the first time? Thanks... On Tue, Sep 6, 2011 at 10:39 PM, Gary Mort <[email protected]> wrote: > 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 > _______________________________________________ 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
