Tools for managing lots of database servers

January 15, 2015 1.9k views
FindMyBus
By:
FindMyBus

I have a growing number of MySQL database servers and am starting to struggle managing them all.

If for example I have updated a schematic (where the previous schematic exists and is in use on all the existing servers) are there any tools that would allow me to bulk update/insert the new database to all my database servers at once?

I’m sure I’m missing something here. I’ve heard of the likes of Ansible, Chef, Puppet for managing lots of servers but is there anything out there for databases like my example?

1 comment
  • sierracircle January 15, 2015

    Honestly, you could probably just write a bash script that would check for an updated database , do a dump of the schema and then update your other servers or databases. You could run that script on a cronjob.

    Writing your own script has the advantage of being light, fast and modified to exactly what you want.

    it might take you some time, but in the end it will be worth it, and you will learn some really great skills.

    A few years ago I took a week to learn how to write my own custom backup scripts (after being frustrated with all of the “Backup Software”)

    I have been using my custom script ever since. It has never failed and does exactly what I want.
    The one thing that struck me was how many tools are already built right into the OS, and can easily accessed with just a few lines of code.

Log In to Comment
1 Answer
gndo January 15, 2015

I’m not sure if this applies to your use case, but there is support for db replication and sharding with MySQL/MariaDB. Those capabilities have an upfront cost (you need time to properly setup), but have a long term savings on the time you would spend synching your databases manually.

If you want a station-by-station manual upgrade because your databases/tables are being treated like a warehouse (it’s write by you and read by everybody else), then you could use phpMyAdmin to export your database or tables to a file and distribute to each station to import them. You could also do the export/import using a script (like @sierracircle) using the mysql command line tools.

Reply
Have another answer? Share your knowledge.