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
- Module Installation and others prerequisites
- Profile Module creation
- Hiera Configuration
- Roles Configuration
- 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 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:
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
node2.yaml
IV. Roles Configuration:
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
This comment has been removed by the author.
ReplyDelete