2016/12/05

Let us play with some puppet Modules - Part III: Puppetizing Mysql Multi-Master Replication, Installation and Configuration with puppetlabs/mysql on Mysql 5.7 Community Edition

In the series of "Let us play with some puppet Modules" posts, I'm willing to share my experience related to the usage of some Forge Puppet Modules. To make it more understandable, I'm making use of a set of well-known Puppet's best practice, with a great emphasis on the following :
  • Roles and Profiles Pattern
  • Hiera for Configuration Data
The posts in this series are all following the below structure:

  1. Module Installation and others prerequisites
  2. Profile Module creation
  3. Hiera Configuration
  4. Roles Configuration
  5. Puppet Agent Single run

This post is the third within that "Let us play with some puppet Modules" series and it aims to describe Mysql multi-master Installation and configuration using Puppetlabs/MySQL (3.10.0) Module. This has been used to complete Installation of MySQL Community edition 5.7 (and 5.6) on Oracle Enterprise Linux.

I won't emphasize on what is MySQL Multi-Master replication as there are already many great blogs (and official documentations)  on that topic (you can check in the reference section below for some of them). 

   I. Module Installation and others prerequisites:

As usual, the module can either be installed on Command line or through PuppetFile

[root@pe-master ~] # puppet module install camptocamp-bind

or simply by adding the following to Puppetfile,


mod 'puppetlabs-mysql'

   II. Profile Module Creation:

Let us create a specific Profile for Mysql Servers , note that I'm using a parameterized class for that profile, with some default values for each parameter. These default values are set as an example to install a standalone Mysql Server (the version that is on the official distribution repository). A simple include profiles::mysqlserver will do just that.
The same parameters can obviously be modified to suit our needs. In this case, they will be adjusted using Hiera (Check Hiera section) for MySQL multi-master replication Installation and Configuration.
I did my best to add  comments to most of the parameters and block of codes below, but I'm leveraging on Puppet DSL readability to make this easy to understand.


class profiles::mysqlserver (
  $root_password          = "Myr00t", # set password as Myr00t
  $override_options       = {},    # define server options in this hash
  $datadir                = "/var/lib/mysql", # can also be defined under override option
  $port                   = "3306", # can also be defined under override option
  $pid_file               = "/var/run/mysqld/mysqld.pid", # can also be defined under override option
  $socket                 = "/var/lib/mysql/mysql.sock", # can also be defined under override option
  $bind_address           = "127.0.0.1",  # can also be defined under override option
  $dbs                    = {},    # Hash of Array for mysql::db
  $community_release      = false, # Set to true to use community edition
  $community_major_v      = '5',   # If using community what is the major version
  $community_minor_v      = '7',   # If using community what is the minor version
  $is_master              = false, # True if the node is master
  $replica_user           = "replication", # For master, what is the replication account
  $replica_password       = "replication", # Replication User password
  $replica_password_hash  = '*D36660B5249B066D7AC5A1A14CECB71D36944CBC', # the same replication account password hashed
  $slaves_ips             = [],     # What are the potentials slave for this master
  $is_slave               = false,  # True if the node is slave
  $master_ip              = "",     # The IP Address of the master in case this is a slave
  $master_port            = "3306", # The port where the master is listening to
)
{

  ### Check Additional defined option and merge to have full defined options
  $additional_override_options = {
    'mysqld' => {
       'datadir'      => "${datadir}",
       'port'         => "${port}",
       'pid-file'     => "${pid_file}",
       'socket'       => "${socket}",
       'bind-address' => "${bind_address}",
    }
  }
  $full_override_options = deep_merge($additional_override_options, $override_options)

  ### Install the requested Mysql Release
  if $community_release == false {
    class { '::mysql::server':
      root_password           => $root_password,
      override_options        => $full_override_options,
      restart                 => true, #service should be restarted when things change
    }
  }
  else {
    $url_repo = "http://dev.mysql.com/get/mysql${community_major_v}${community_minor_v}-community-release-el${::facts['os']['release']['major']}-${community_minor_v}.noarch.rpm"
    package { 'mysql-community-repo':
      name      => "mysql${community_major_v}${community_minor_v}-community-release",
      ensure    => installed,
      provider  => rpm,
      source    => $url_repo,
    }
    # Note: 
    # Installing mysql-community-server will install all the needed packages
    # for MySQL server mysql-community-server, mysql-community-client, mysql-community-common and mysql-community-libs
    class { '::mysql::server':
      root_password           => $root_password,
      override_options        => $full_override_options,
      package_name            => 'mysql-community-server',
      package_ensure          => 'present',
      restart                 => true, #service should be restarted when things change
      require                 => Package['mysql-community-repo'],
    }
  }

  ### With Create Resource Converts a hash into a set of resources and create dbs
  create_resources(mysql::db, $dbs)

  ### Check if this system was marked as master and set appropriate params and users
  if $is_master {
  
    if $full_override_options['mysqld']['bind-address'] == "127.0.0.1" {
        fail("This cant be a master and listening only to localhost, you must change the bind_address variable to a suitable one")
    }
    
    $slaves_ips.each |$slave_ip| {
  
      mysql_user { "${replica_user}@${slave_ip}":
        ensure                   => 'present',
        max_connections_per_hour => '0',
        max_queries_per_hour     => '0',
        max_updates_per_hour     => '0',
        max_user_connections     => '0',
        password_hash            => $replica_password_hash,
      }
    
      mysql_grant { "${replica_user}@${slave_ip}/*.*":
        ensure                 => 'present',
        options                => ['GRANT'],
        privileges             => ['REPLICATION SLAVE'],
        table                  => '*.*',
        user                   => "${replica_user}@${slave_ip}",
        require                => Mysql_user["${replica_user}@${slave_ip}"],
      }
        
    }
  }
  
  ### Check if this system was marked as slave and set appropriate params and commands
  if $is_slave {
    validate_ip_address($master_ip)  # IP Address must be set to identify the master
    $cmd_change_master = join(["CHANGE MASTER TO MASTER_HOST","\'${master_ip}\',MASTER_PORT","${master_port},MASTER_USER","\'${replica_user}\',MASTER_PASSWORD","\'${replica_password}\';"], "=") 
    $cmd_start_slave   = "START SLAVE;"
    exec { 'set_master_params':
      command => "mysql --defaults-extra-file=/root/.my.cnf --execute=\"${cmd_change_master}${cmd_start_slave}\"",
      require => Class['::mysql::server'],
      path    => ["/usr/local/sbin","/usr/local/bin","/sbin","/bin","/usr/sbin","/usr/bin","/opt/puppetlabs/bin","/root/bin"],
      unless  => "grep ${master_ip} $datadir/master.info && grep ${master_port} $datadir/master.info && grep -w ${replica_user} $datadir/master.info && grep -w ${replica_password} $datadir/master.info",
    }
  }
  
  
}

   III. Hiera Configuration:

As said during the brief introduction above, I'm exclusively using Hiera to store Nodes' configuration data. As this is about Muti-Master replication, I'm showing two separates nodes configuration here, each one related to the node we're willing to replicate. The IP Addresses and Names of these nodes are: 192.168.10.11(node1.yaml) & 192.168.10.12(node2.yaml)

node1.yaml

---
profiles::mysqlserver::root_password    : 'Myr00t'
profiles::mysqlserver::community_release: true
profiles::mysqlserver::is_master        : true
profiles::mysqlserver::bind_address     : '0.0.0.0'
profiles::mysqlserver::slaves_ips       :
  - "192.168.10.12"
profiles::mysqlserver::is_slave         : true
profiles::mysqlserver::master_ip        : "192.168.10.12"
profiles::mysqlserver::override_options :
  'mysqld'                              :
    'server-id'                         : '1'
    'log-bin'                           : "mysql-bin"
    'binlog-ignore-db'                  :
      - 'test'
      - 'information_schema'
    'replicate-ignore-db'               :
      - 'test'
      - 'information_schema'
    'relay-log'                         : "mysql-relay-log"
    #By setting the auto_increment_increment and auto_increment_offset values independent servers
    #will create unique auto_increment values allowing for replication without fear of collision!
    #Note that auto_increment_increment is set to the total number of nodes in this multi-master
    'auto-increment-increment'          : '2'
    'auto-increment-offset'             : '1'
profiles::mysqlserver::dbs:
  'stiv-db1':
    'user'                      : 'stiv'
    'password'                  : 'stiv'
    'host'                      : 'localhost'
    'grant'                     :
      - 'SELECT'
      - 'UPDATE'

node2.yaml


---
profiles::mysqlserver::root_password    : 'Myr00t'
profiles::mysqlserver::community_release: true
profiles::mysqlserver::is_master        : true
profiles::mysqlserver::bind_address     : '0.0.0.0'
profiles::mysqlserver::slaves_ips       :
  - "192.168.10.11"
profiles::mysqlserver::is_slave         : true
profiles::mysqlserver::master_ip        : "192.168.10.11"
profiles::mysqlserver::override_options :
  'mysqld'                              :
    'server-id'                         : '2'
    'log-bin'                           : "mysql-bin"
    'binlog-ignore-db'                  :
      - 'test'
      - 'information_schema'
    'replicate-ignore-db'               :
      - 'test'
      - 'information_schema'
    'relay-log'                         : "mysql-relay-log"
    #By setting the auto_increment_increment and auto_increment_offset values independent servers
    #will create unique auto_increment values allowing for replication without fear of collision!
    #Note that auto_increment_increment is set to the total number of nodes in this multi-master
    'auto-increment-increment'          : '2'
    'auto-increment-offset'             : '2'


   IV. Roles Configuration:

Using the mysqlserver profile described in Section II, with the right Hiera Data, we can move forward with the roles modules configuration,


class roles::mydb  {
 
    # Install Oracle Server
    include profiles::mysqlserver

}

   IV. Puppet Agent Single run:

And finally, let us trigger a puppet convergence and enjoy the result,


[root@node1 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 82
Server version: 5.7.16-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.12
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 2468
               Relay_Log_File: mysql-relay-log.000003
                Relay_Log_Pos: 2681
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: test,information_schema
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2468
              Relay_Log_Space: 3101
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: 4527f43c-ba02-11e6-a49e-b4b52f6d2a48
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)


Done, Mysql multi-master Installation and Configuration Puppetized!

References:
https://forge.puppet.com/puppetlabs/mysql
https://www.digitalocean.com/community/tutorials/how-to-set-up-mysql-master-master-replication
http://codetalk.code-kobold.de/mysql-55-server-as-replication-slave/
http://linusramos.blogspot.com/2012/09/mysql-replication-for-v55-up-unknown.html
https://tapasmishra.wordpress.com/2012/06/11/how-to-configure-mysql-5-5-server-as-replication-slave/
https://support.rackspace.com/how-to/mysql-master-master-replication/
https://www.howtoforge.com/mysql_master_master_replication



No comments:

Post a Comment