Migrating old databases

Flyway Badge MariaDB Badge

Currently mostly busy with modernizing 15 years old infrastructure of some customer. Old and huge MySQL databses. No foreign keys. No timestamps. It’s all pretty convoluted. Many VM’s running directly on KVM without libvirt abstraction. Failing hard drives. Old PHP. And the overaly of many “big brain” sysadmins.

But at least, I gained full access to the most of the resources, restored passwords, established secure remote access, etc.

Now it’s all about writing migration scripts. Convert databases from MyISAM to InnoDB, clean up security issues, etc.

Ultimate goal is to write automation for fully containerized and clean environment.

While all this seems frustrating to “dig into old garbage” in some sense I like it. It’s like hunting. 😃

Learned about Flyway SQL migrations. Established IPMI and HP iLO access.

Decided add some update to this post about my general approach to this task.

  • Obtained physical access to the servers
  • Configured iLO (BMC) access for remote maintenance (was not used before)
  • Configured secure remote access from my home IP (concerned about IP spoofing)
  • Obtained OS root access
  • Examined different user Bash history to get an general idea of what was happening on these servers
  • Examined busy ports, mounts, drives, etc
  • Found Libvirt and vanilla KVM VM’s with host port forwardings
  • Obtained root access to all VM’s
  • Obtained access to SQL servers
  • Rsync’ed entire virtual drives and other critical files
  • In off-hours/weekend did full SQL dumps
  • Recreated empty SQL schemas from the backups (via Flyway)
  • Changed table engines, added additional timestamps (via Flyway)
  • Imported old data back
  • SQL repair and optimizations

At this stage basically, I have full control over the data and I can keep my investigation and improvements incrementally going. My next tasks will be to containerize and automate maintenance and backup processes. Also, recreating PHP environment might be a little bit challenging. Not yet sure about backup validation and monitoring workflow.