MySQL Installation/Configuration Automation

5.00 avg. rating (91% score) - 1 vote

In today’s fast paced technology world, where numbers are growing be it data size, no. of requests per second, no of databases etc., to manage these big volumes, standard operation procedures play a key role. It helps not only in ensuring smooth operations of web based products but also plays a major role for administrators to manage it in a controlled fashion. Standards are important, but enforcing them if one have multiple human resources to work towards achieving them is difficult. Configuring the setups manually is error prone and not manageable as your no. of installations grow up.

Here at InfoEdge we have multiple database instances serving huge traffic of various types like End Users, Analytics, Sales, Operations etc. Managing these growing numbers was becoming tedious as well as monotonous and error prone so to eliminate all these we started working on an automated solution for doing these installations and configurations.

We chose to work on writing a bash shell script with proper validation controls so that chances of errors and misses remain very less. Below is the list of enhancements we could bring in existing manual process after doing installations via automated script:-

* Multiple MySQL versions can be installed via this (depending on the required binary package)

* Instance can be created from copied data of another instance ( complete data directory has to be copied from source to destination)

* Instances will be configured with agreed configuration which will help in maintaining standard across all installations

* Script is smart enough to identify available ports, version , user/group which are required for installation

* One installation is taking approximately 5 minutes against 1 hour (or more depending upon the user who is actually doing it) when done via earlier manual installation

* User can define the role of instance to be created like Master, Slave, Repeater etc. based on this input script will do the configurations

* Few inputs are taken from user to make it compatible for copied data from other instance

How to use this script 

There are few prerequisites which should be followed before running this script.

  • Script should be copied to server wherein we want to do the installations
  • MySQL compiled binary for the required version should be placed in /opt/

Now we are  ready to execute the script.

Shell > ./my_mysql_install.sh -p <PORT> -a <APP-NAME> -v <VERSION> -i <LOCAL-HOST-IP> -f <FLAG> -r <ROLE>

Below is the detail of switches mandatory to execute this

     OPTION NAME                 USAGE            POSSIBLE VALUES
     PORT  (-p) Port of MySQL instance 3306/3307/…
     APP-NAME (-a) To create data directory and naming convention naukri
     VERSION (-v ) MySQL version 5.5.25/5.6.22/…
     LOCAL-HOST-IP (-i) To define some configurations like report-host, server-id etc. xx.xx.111.yyy
     FLAG (-f) What type of instance it is New or created from copied data FRESH|EXIST
     ROLE(-r) Purpose on instance whether it will be used as master, slave basis this input replication related parameters will be configured MASTER|SLAVE| CLRMASTER| REPEATER| BACKUPSLAVE|STANDALONE
How it works ? 

This script is well suited for MySQL version of 5.5.x and 5.6.x although few modifications can make it suitable for any of the available versions. Broadly complete functionality of this script can be categorised as

1.)    Configuring Environment

This section is creating environment as per predefined standards to create necessary directories like data directory  , start file, connect file , slow logs directory, my.cnf and extracting  tar binary at /usr/local/mysql<5/6> , changes ownerships of directories and files to make it ready to start

 2.)    Doing necessary changes as per role mentioned

Whatever role has been provided as input the script will modify configuration parameters accordingly like if it’s a master it will configure log_bin, server_id, report_host etc. as per standards.Once configuration file is ready , it will move forward to create instance on the basis of flags provided during execution which can be FRESH (For fresh installation ) or EXIST (To create instance with existing data copied from different server). Script has the intelligence to take care of variables according to version.

 3.)    Grants Handling

It is intelligent enough to take care of DB users created and their respective grants. If we do a fresh installation it will be creating DB admin related grants,  in case of EXIST installation it will provide option to keep old grants as it is or can create new grants as per standard defined

Efficiency Statistics  (Difference in numbers before and after)

This script came to us a savior in times we needed it most ,we had to migrate our data center and to test the complete setup along with production DB instances we could configure up to 300 DB instances in less than a month’s time which includes transferring data (approx. 8 TB of data) from existing DC to new DC which was connecting over the internet. If we would have done this manually it would have lots of configuration issues as well as no standards followed during the configuration phase and last but not the least time taken to complete the job.

You can contact me to get this script  and enjoy installing MySQL by simply typing

Shell > ./my_mysql_install.sh -p <PORT> -a <APP-NAME> -v <VERSION> -i <LOCAL-HOST-IP> -f <FLAG> -r <ROLE>

just press Enter  and have a sip of coffee and after few minutes MySQL instance is up and running.

Posted in Automation, Database

One thought on “MySQL Installation/Configuration Automation

  1. Nice Work Kasid. I am sure this is good platform to see great success stories and lot more things since the industry is moving towards automation.

Comments are closed.