Mirroring MySQL server with different configuration

Question

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

score 2 · Accepted Answer · answered May 31 '12 at 22:56

2

"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!)

answered May 31 '12 at 22:56

rackandboneman

2,577
11
8

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 01 '12 at 07: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 01 '12 at 08:26

score 1 · Answer 2 · answered May 31 '12 at 22:39

1

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.

answered May 31 '12 at 22:39

Tom

11,176
5
41
63

The problem is that mysqldumps running very slow. Total size is 120G. – HTF Jun 01 '12 at 07: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 Jun 01 '12 at 09:00

score 1 · Answer 3 · answered May 31 '12 at 22:44

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.

Mirroring MySQL server with different configuration

3 Answers3