Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have to migrate MySQL server to a different data center so I would like to create another MySQL slave server in new DC and then promote it to a master later on. I previously used LVM snapshots and Percona XtraBackup for this purpose. However, this time I have optimized the MySQL configuration file that prevents me from using these methods.

Old server (backup):

innodb_log_file_size = 256M
innodb_log_files_in_group = 3

New server (restore):

innodb_log_file_size = 512M
innodb_log_files_in_group = 2

The XtraBackup script and LVM snapshots copy the whole directory structure so the MySQL server won't start because there is a different size for InnoDB logs.

Is there any solution to avoid a downtime in this case? I can't use mysqldumps because there are around 8000 databases. That being the case, I would have to take the server down for a couple of hours.

I was also thinking to use the old settings with XtraBackup and then change it once the new server is promoted to a master - less downtime but I'm not sure if this will work?

Thank you

Regards

share|improve this question

3 Answers 3

up vote 2 down vote accepted

"around 8000 databases so I would have to take the server down for a couple of hours."

Is there already another slave server you can take out of production and use as the temporary master?

Is the problem with mysqldump the performance hit or the locking (in the latter case --single-transaction --master-data might be viable. BTW, never rely on --triggers or --routines being default!)

share|improve this answer
    
Why didnt I think of that: or build another temporary local slave that has the same settings and initialize it by whatever "faster" method. Then mysqldump (still i would recommend with --master-data, saves the error prone hassle of having to manually transfer replication parameters) that slave at leisure. –  rackandboneman May 31 '12 at 23:02
    
Yes, there is another slave server where a backup script is running (separate .sql file for each database) and it takes almost whole night so there is a performance hit. Do you think I can backup all DBs to one .sql file - /var/lib/mysql = 120G –  HTF Jun 1 '12 at 7:53
    
Perfectly viable, people have done it and will do it again - pipe it through gzip as you dump though. Just make sure there are no surprises with locking. –  rackandboneman Jun 1 '12 at 8:26

I think the latter suggestion sounds like the best one, what sort of total size are you talking about?

It would seem fairly simple to make a innobackupex FULL dump and transfer that to the new data-centre, and then bring the new server up with the xtrabackup dump but with the old innodb settings.

Once that is running in read-only mode, you can then mysqldump it, to provide a SQL file which you can import anywhere.

Then you can reload the dump file into a fresh mysql instance with the new innodb settings.

share|improve this answer
    
The problem is that mysqldumps running very slow. Total size is 120G. –  HTF Jun 1 '12 at 7:54
    
Yes, but you need the logical dump. Any xfs, LVS or xtrabackup strategy is going to also backup innodb metadata that you don't want. –  Tom H Jun 1 '12 at 9:00

I'd start with the LVM snapshot and original settings. Bring up the new server. Shut it down. Check that it exited cleanly. Move the log file out of the way, make config changes, and then restart. Mysql should create new ones and you're all set.

More in depth explanation here.

You might also read How to Calculate a Good Innodb Log File Size/

share|improve this answer
    
Thanks, I'll test it today. –  HTF Jun 1 '12 at 7:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.