We have a need to migrate a large number of 100GB databases onto a new infrastructure.

I would like to minimise downtime as much as possible, so am toying with the idea of migrating the clients to the new infrastructure on a clean,new, database and after the event load the existing data in behind the new data going in.

I fully understand that this is probably a "how long is a piece of string" question without seeing the schema (which is around 400 tables), but, does anyone know if this is a) possible and b) of any tools that might help.

These systems are all running on Ubuntu and are using mariadb.

I'm not sure what else I can provide to help extract a cogent answer, but I can but ask.

Thanks. Peter.

OK, so the stat of play is thus...

I have dumped the data from the tables I need to migrate and have added the --insert-ignore field to the dump to allow me to not get hung up on primary keys when I re-run things.

The data file produced is 23GB in total.

It all works well until I hit a table that has had the schema changed such that it has more columns than the source schema had at which point the inserts blow up because there is not enough data in the import line (the new fields do allow nulls, but I guess don't have a null default).

The error returned is Column count doesn't match value count on the table in question.

Any ideas on how to get around that?

  • AUTO_INCREMENT ids could make the task challenging. Ditto with FKs. And Normalization. And... – Rick James Sep 11 '19 at 5:43
  • How many is a large number? Could you set up your new machines as replicas and then cut over? Or does the new infrastructure consolidate your existing databases into one? – Vérace Sep 11 '19 at 5:46
  • Large number is about 30 at this point. I did think of replication. One thing I didn't mention was that in some cases, as schema upgrade would need to be done as well which does complicate things a bit. Keys I hadn't considered I must admin. There aren't many fk's but there are some. I guess I could work out the last entry in the auto_inc tables and make sure I stay away from them somehow. – Peter Nunn Sep 11 '19 at 6:34
  • OK, it would seem that the DB schema is not using any aut_inc keys, the PK's atleast seem to be pretty sane, non AI keys... so maybe this would work. – Peter Nunn Sep 11 '19 at 6:42
  • So you are consolidating? – Vérace Sep 11 '19 at 11:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.