Skip to content

aboese/mysql_provisioning

Repository files navigation

This is a provisioning database to keep MySQL instances in sync. Please note that the database should not be replicated between instances, unless you want slaves to carry the exact same permissions as master databases, which is not usually the case. This can be accomplished by excluding provisioning database from replication, or embedding statements between "SET sql_log_bin=0;" and "SET sql_log_bin=1;" respectively.

Provisioning Database

classes - definition of all user types db - this is temporary holding table for imports that are being moved to db_audit db_audit - this is a table showing the mysql.db table values for all the servers. this is run via mysql_audit_stuff.sh, which should be in a repo shortly db_delta - this is a temp table holding the changes that need to be corrected to bring the database users in line with the present configuration using the db_audit table to compare dbx - this is a table like mysql.db, excepting the user field has the names of the classes which need to occupy those permissions unmanaged_users - this is a view which shows which users from db_audit are not presently managed under the provisioning system. adding people in involves adding appropriate class,user_class, dbx, and userx values user - this is temporary holding table for imports that are being moved to user_audit user_audit - this is a table showing the mysql.user table values for all the servers. this is run via mysql_audit_stuff.sh, which should be in a repo shortly user_class - this is a user->class mapping table. presently it is only one to one. in the future, this might be changed. user_delta - this is a temp table holding the changes that need to be corrected to bring the database users in line with the present configuration using the user_audit table to compare userx - this is a table like mysql.db, excepting the user field has the names of the classes which need to occupy those permissions Provisioning Scripts There are three types of scripts that are part of this: prep, pull, audit scripts. Prep scripts establish the deltas that need to be pushed across, while pull scripts pull the changes onto each server.

Scripts

Prep

get_user_deltas.sql - This finds the delta for the mysql user tables. get_db_deltas.sql - This finds the delta for the mysql db tables. update_user_delta_defaults.sql - This applies defaults that are unique to specific db instances. update_user_delta_blank_password.sql - This prevents passwords from ever being set to blank.

Pull

get_provisioning_update_delta.sh - This script grabs the provisioning data across to the local db instance on local provisioning db. Global user access should be the same across database instances for all user types. This will not be so for every db in an instance. This simplifies greatly the user table update. update_user_deltas.sql - This updates accounts that exist in user table but have been changed. update_db_deltas.sql - This updates accounts that exist in db table but have been changed. insert_user_deltas.sql - This inserts accounts that do not exist in user table. insert_db_deltas.sql - This inserts accounts that do not exist in db table.

Audit

mysql_audit_stuff.sh - This gets an audit for all the servers so that deltas can be established.

About

This is a series of provisioning scripts for mysql

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published