http://virtualmin.com/node/21919#comment-126477
when migrating LAMP-based websites to another server, it is usually recommended to use master/slave mySQL replication in order to help reduce (or even avoid) unnecessary downtime when migrating the DB - once replication is complete, the DB credentials on the master (old) website would be updated to use the (new) slave server via mySQL over an SSH tunnel.
Typically, DBAs recommend having a test suite to verify/validate a replicated DB prior to making it active: http://virtualmin.com/node/21919#comment-126527
Implementing this could turn out to be a real time-saver for virtualmin users - and personally, I'd even consider getting "Virtualmin Pro" if that could be supported, because it's obviously a feature for professional users.
It is worth noting that some users mentioned (here and on the forum) that migrating virtualmin VHOSTs seems to currently have the implicit assumption that the DB is also part of virtualmin, so maybe this should become more decoupled to also explicitly support scenarios where mySQL may be running separately? https://virtualmin.com/node/12679
Comments
Submitted by JamieCameron on Thu, 01/02/2014 - 20:34 Comment #1
That would indeed be a nice feature, but technically it would be extremely complex to implement. Normally setting up replication requires that the DB be first backed up and restored on the new system, then replication run to catch up ... and the current domain migration process doesn't support this kind of two-stage move.
Also, does replication in MySQL allow only a single DB to be replication, rather than the whole server?
Regarding your point about using a remote MySQL server, that is already fully supported by Virtualmin.
Submitted by wocul on Thu, 01/02/2014 - 22:38 Comment #2
Hi,
thanks for responding!
Yes, I just replicated just a single DB by adding the "binlog_do_db" directive to /etc/my.cnf - not sure if that can be done without editing the config file though, but it sure would be handy!
As I mentioned on the forum, I followed this tutorial (which uses xtrabackup to pipe the dump to the new server, which is specifically for "new-style" InnoDB DBs), and it was pretty straightforward actually: http://techblog.procurios.nl/k/n618/news/view/56429/14863/how-to-migrate...
Regarding xtrabackup, see: http://www.percona.com/doc/percona-xtrabackup/2.1/howtos/setting_up_repl...
Personally, I would not mind requiring separate tools (like xtrabackup) for such a useful feature to work.
However, I am obviously not aware of restrictions on the Webmin-side of things.
Without being familiar with the Webmin/Virtualmin source code, I would imagine that one could come up with a workaround to emulate a multi-stage migration, i.e. using cron-jobs to initiate various steps of the migration, that set up new cron jobs in turn.
For example, we would basically have a "server migration" dashboard that lists pending migrations and their status - each migration would be decoupled into distinct steps, that would be triggered sequentially by using cron jobs that are set up only after each step is successful - otherwise, the migration would be considered a failure and marked/shown accordingly.
Webmin and virtualmin can already manage cron jobs, so this could be a feasible workaround to implement support for multi-stage migrations, such as transferring lots of data (files, databases) between hosts, doing server-side DB validation, enabling replication etc.
In fact, even the conventional backup/restore route could be automated that way, i.e. using rsync.
Here are some more pointers on mysql master/slave replication without downtime, and without dumping first:
http://dba.stackexchange.com/questions/26961/replicate-master-slave-with... http://plusbryan.com/mysql-replication-without-downtime http://serverfault.com/questions/392691/how-can-i-rebuilt-mysql-replicat... http://stackoverflow.com/questions/7083823/setting-up-mysql-master-slave...
Submitted by JamieCameron on Fri, 01/03/2014 - 16:35 Comment #3
I'l look into this some more - migration using replication is pretty complex to automate, so I can't make any guarantees that we will implement it.
Submitted by wocul on Fri, 01/03/2014 - 17:40 Comment #4
sure,I fully understand - a first step could involve supporting incremental mysql backups via msql bin logs (using mysqlbinlog), this could later on be extended to also do remote master/slave replication:
http://www.techflirt.com/mysql-incremental-backup-restore/
http://mossiso.com/2012/07/24/backing-up-mysql-with-replication-and-incr...
Like I said, xtrabackup will help automate this a great deal, but only helps with InnoDB-style DBs: http://www.percona.com/software/percona-xtrabackup
Submitted by wocul on Tue, 01/07/2014 - 21:40 Comment #5
even if replication isn't used, a "live" migration would typically involve:
that's typically how it's done to ensure that even despite DNS propagation delays, any users using still the old server, end up using the new DB.