-1

I'm working on a project that has a MySQL database schema that's used on three different servers (MySQL databases) into 20 copies of the schema for each server (which are all identical in form but contain different data).... so 60 copies of the same schema spread across three machines.

Those machines report back to a host machine with their results. The problem I'm running into is managing any schema changes while everything is deployed. Every time I want to add or remove a column, I have to replicate the query 20 times and then copy it over onto three machines. Is there a piece of software I can use to manage all of these identical (in schema, not in data contents obviously) database instances?? I want to find a piece of software that will look over all the instances of the database and tell me any schema changes between them (as well as the table sizes of the individual instances).

Are there any pieces of software out there that can help me manage everything from a single source GUI or command line program?

Dave M
  • 4,494
  • 21
  • 30
  • 30
user1290173
  • 3
  • 1
  • 2
    If you have any Linux in your environment, have you considered [Ansible](https://www.ansible.com/use-cases/configuration-management) for automated configuration management? – rst-2cv Jan 31 '20 at 05:41
  • Or puppet or chef or an SSH script. – davidgo Jan 31 '20 at 09:12
  • Do you need the "sharding"? Is there any advantage in having 20 'identical' tables on each machine; why not one? Why change the schema so often; once a year is rather often. – Rick James Jan 31 '20 at 19:43
  • Each of the databases use a separate storage drive for maximum read/write operations. – user1290173 Feb 02 '20 at 01:09

1 Answers1

0

There are a couple of options that can help you with that, some are:

  • Ansible
  • Jenkins
  • Rundeck

You can integrate Jenkins with Github, Bitbucket or Gitlab webhooks, so each time a new commit is made, a DevOps pipeline is launched.

If you're using some framework like Rails, you can also run migrations and seeders from it using these softwares, which is also an option to keep your database schema version controlled.

Cya!

Stefano Martins
  • 1,131
  • 7
  • 10