diff options
| -rw-r--r-- | manifests/init.pp | 311 | ||||
| -rw-r--r-- | templates/gather_master_data.bash.erb | 10 | ||||
| -rw-r--r-- | templates/my.cnf.erb | 24 | ||||
| -rw-r--r-- | templates/verify_slave_configuration.bash.erb | 70 |
4 files changed, 266 insertions, 149 deletions
diff --git a/manifests/init.pp b/manifests/init.pp index f70e6bd..57258df 100644 --- a/manifests/init.pp +++ b/manifests/init.pp @@ -6,186 +6,215 @@ import "variables.pp" import "passwords" class mysql::server { - include passwords - include variables - - package { "mysql-server": - name => "MySQL-server-community", - ensure => installed, - } - - package { "mysql-client": - name => "MySQL-client-community", - ensure => installed, - } - - service { "mysql": - ensure => running, - hasstatus => true, - require => Package["mysql-server"], - } - - # It is more convenient to set the root password with - # and exec than using the plugin - - $mysql_root_user = $variables::mysql_root_user - $mysql_root_database = $variables::mysql_root_database - $mysql_root_password = $passwords::mysql_root_password - $mysql_root_local_host = $variables::mysql_root_local_host - $mysql_global_host = $variables::mysql_global_host - - $mysql_replication_user = $variables::mysql_replication_user - $mysql_replication_password = $passwords::mysql_replication_password - - - $mysql_cmd_root_without_pwd = "/usr/bin/mysql --user=$mysql_root_user --database=$mysql_root_database --host=$mysql_root_local_host" - $mysql_cmd_root_with_pwd = "/usr/bin/mysql --user=$mysql_root_user --database=$mysql_root_database --host=$mysql_root_local_host --password=$mysql_root_password" - $mysql_cmd_repl_with_pwd = "/usr/bin/mysql --user=$mysql_replication_user --database=$mysql_root_database --host=$mysql_root_local_host --password=$mysql_replication_password" - - exec { "mysql root password init": - command => "$mysql_cmd_root_without_pwd --execute=\"Update user set password=password('$mysql_root_password') where user='$mysql_root_user';\"", - unless => "$mysql_cmd_root_with_pwd --execute '\s'", - require => Service["mysql"], - } - - exec { "mysql root flush password": - command => "$mysql_cmd_root_without_pwd --execute=\"flush privileges;\"", - unless => "$mysql_cmd_root_with_pwd --execute '\s'", - require => Exec["mysql root password init"], - } + include passwords + include variables - exec {"mysql create replication user": - command => "$mysql_cmd_root_with_pwd --execute=\"Create user '$mysql_replication_user'@'$mysql_global_host' identified by '$mysql_replication_password';\"", - unless => "$mysql_cmd_repl_with_pwd --execute '\s'", - require => Exec["mysql root flush password"], - } + package { "mysql-client": + name => "MySQL-client-community", + ensure => installed, + } - exec { "grants all to replication user": - command => "$mysql_cmd_root_with_pwd --execute=\"GRANT All PRIVILEGES ON *.* TO '$mysql_replication_user'@'$mysql_global_host' IDENTIFIED BY '$mysql_replication_password';\"", - unless => "$mysql_cmd_repl_with_pwd --execute '\s'", - require => Exec["mysql create replication user"], - } + package { "mysql-server": + name => "MySQL-server-community", + ensure => installed, + require => Package["mysql-client"], + } - exec { "mysql root flush grants": - command => "$mysql_cmd_root_without_pwd --execute=\"flush privileges;\"", - unless => "$mysql_cmd_root_with_pwd --execute '\s'", - require => Exec["grants all to replication user"], - } + service { "mysql": + ensure => running, + hasstatus => true, + require => Package["mysql-server"], + } + + # It is more convenient to set the root password with + # and exec than using the plugin + + $mysql_root_user = $variables::mysql_root_user + $mysql_root_database = $variables::mysql_root_database + $mysql_root_password = $passwords::mysql_root_password + $mysql_root_local_host = $variables::mysql_root_local_host + $mysql_global_host = $variables::mysql_global_host + + $mysql_replication_user = $variables::mysql_replication_user + $mysql_replication_password = $passwords::mysql_replication_password + + + $mysql_cmd_root_without_pwd = "/usr/bin/mysql --user=$mysql_root_user --database=$mysql_root_database --host=$mysql_root_local_host" + $mysql_cmd_root_with_pwd = "/usr/bin/mysql --user=$mysql_root_user --database=$mysql_root_database --host=$mysql_root_local_host --password=$mysql_root_password" + $mysql_cmd_repl_with_pwd = "/usr/bin/mysql --user=$mysql_replication_user --database=$mysql_root_database --host=$mysql_root_local_host --password=$mysql_replication_password" + $mysql_cmd_repl_slave ="/usr/bin/mysql --user=$mysql_replication_user --database=$mysql_root_database --host=$mysql_master_ip_address --password=$mysql_replication_password" + + exec { "mysql root password init": + command => "$mysql_cmd_root_without_pwd --execute=\"Update user set password=password('$mysql_root_password') where user='$mysql_root_user';\"", + unless => "$mysql_cmd_root_with_pwd --execute '\s'", + require => Service["mysql"], + } + + exec { "mysql root flush password": + command => "$mysql_cmd_root_without_pwd --execute=\"flush privileges;\"", + unless => "$mysql_cmd_root_with_pwd --execute '\s'", + require => Exec["mysql root password init"], + } + + exec {"mysql create replication user": + command => "$mysql_cmd_root_with_pwd --execute=\"Create user '$mysql_replication_user'@'$mysql_global_host' identified by '$mysql_replication_password';\"", + unless => "$mysql_cmd_repl_with_pwd --execute '\s'", + require => Exec["mysql root flush password"], + } + + exec { "grants all to replication user": + command => "$mysql_cmd_root_with_pwd --execute=\"GRANT All PRIVILEGES ON *.* TO '$mysql_replication_user'@'$mysql_global_host' IDENTIFIED BY '$mysql_replication_password';\"", + unless => "$mysql_cmd_repl_with_pwd --execute '\s'", + require => Exec["mysql create replication user"], + } } class mysql::standalone inherits mysql::server { mysql::mysql_config {"set binary logging": - binary_logging => false, + binary_logging => false, } } class mysql::m2s inherits mysql::server { mysql::mysql_config {"set binary logging": - binary_logging => true, + binary_logging => true, } - - exec { "restart mysql server": - command => "service mysql restart", - unless => "$mysql_cmd_repl_with_pwd --execute=\"show master status;\" | grep mysqllog", - require => Service ["mysql"], - } - } class mysql::slave inherits mysql::server { mysql::mysql_config {"set binary logging": - binary_logging => false, + binary_logging => false, } - mysql::mysql_replication {"Configure Slave Server for Replication":} + mysql::mysql_replication {"Configure Slave Server for Replication": + } } class mysql::m2m inherits mysql::server { mysql::mysql_config {"set binary logging": - binary_logging => true, + binary_logging => true, } - mysql::mysql_replication {"Configure Master Server for Master to Master Replication": } + mysql::mysql_replication {"Configure Master Server for Master to Master Replication": + } } -define mysql::mysql_config($binary_logging){ - +define mysql::mysql_config ($binary_logging){ file { "/etc/my.cnf": - ensure => present, - owner => "mysql", - group => "mysql", - mode => 0644, - content => template("mysql/my.cnf.erb"), - before => Service["mysql"], + ensure => present, + owner => "mysql", + group => "mysql", + mode => 0644, + content => template("mysql/my.cnf.erb"), + require => [Service["mysql"],Exec["grants all to replication user"]] + } + exec { "restart mysql server": + command => "service mysql restart", + unless => "$mysql_cmd_repl_with_pwd --execute=\"show master status;\" | grep mysqllog", + require => File["/etc/my.cnf"], } } -define mysql::mysql_replication{ - +define mysql::mysql_replication { file { "/var/lib/mysql/gather_master_data.bash": - owner => "mysql", - group => "mysql", - ensure => present, - mode => 0755, - content => template("mysql/gather_master_data.bash.erb"), - require => Exec["grants all to replication user"], + owner => "mysql", + group => "mysql", + ensure => present, + mode => 0755, + content => template("mysql/gather_master_data.bash.erb"), + require => Exec["restart mysql server"], } - exec { "remove set master datafile": - command => "rm /var/lib/mysql/set_master_repl_data.sql", - unless => "$mysql_cmd_repl_with_pwd --execute=\"show slave status;\" | grep Wait", - require => File["/var/lib/mysql/gather_master_data.bash"], + exec { "get master data for slave": + command => "/var/lib/mysql/gather_master_data.bash", + creates => "/var/lib/mysql/set_master_repl_data.sql", + onlyif => "$mysql_cmd_repl_slave --execute=\"show master status;\" | grep mysqllog", + require => File["/var/lib/mysql/gather_master_data.bash"], } - exec { "set master data for slave": - command => "/var/lib/mysql/gather_master_data.bash", - creates => "/var/lib/mysql/set_master_repl_data.sql", - unless => "$mysql_cmd_repl_with_pwd --execute=\"show slave status;\" | grep Wait", - require => File["/var/lib/mysql/gather_master_data.bash"], - } - - exec { "restart mysql server": - command => "service mysql restart", - unless => "$mysql_cmd_repl_with_pwd --execute=\"show slave status;\" | grep Wait", - require => Exec["set master data for slave"], - } - - exec { "start slave server": - command => "$mysql_cmd_repl_with_pwd --execute=\"start slave;\"", - unless => "$mysql_cmd_repl_with_pwd --execute=\"show slave status;\" | grep Wait", - require => Exec["set master data for slave"], + # Restart local slave server + exec { "restart slave server": + command => "$mysql_cmd_repl_with_pwd --execute=\"slave stop; slave start\"", + unless => "$mysql_cmd_repl_with_pwd --execute=\"show slave status;\" | grep Wait", + require => Exec["get master data for slave"], } } -define mysql::datasource($rootpw, $ds_name, $ds_owner, $ds_owner_pwd, $ds_user, $ds_user_pwd, $ds_schema) { - # TODO: This will soon be replaced. - # The execs will selectively run based on the value of $mysql_type - include mysql::standalone - - $mysql_root_cmd = "/usr/bin/mysql -u root -p$rootpw" - exec { "create datasource $ds_name": - command => "/usr/bin/mysqladmin -u root -p$rootpw create $ds_name", - unless => "$mysql_root_cmd $ds_name -e '\s'", - require => [Service["mysql"], Exec["mysql root password init"]], - } - - exec { "create grants $ds_name": - command => "$mysql_root_cmd -e \"GRANT ALL PRIVILEGES ON *.* TO '$ds_owner'@'%' IDENTIFIED BY '$ds_owner_pwd' WITH GRANT OPTION;\"", - unless => "/usr/bin/mysql --host=$ipaddress --user=$ds_owner --password=$ds_owner_pwd -e '\s'", - require => [Service["mysql"], Exec["mysql root password init"]], - } - - exec { "create grants $ds_user": - command => "$mysql_root_cmd -e \"GRANT SELECT,INSERT,UPDATE,DELETE ON $ds_name.* TO '$ds_user'@'%' IDENTIFIED BY '$ds_user_pwd';\"", - unless => "/usr/bin/mysql --host=$ipaddress --user=$ds_user --password=$ds_user_pwd -e '\s'", - require => [Service["mysql"], Exec["mysql root password init"]], - } - # Only create the schema is a template directory was specified - if $ds_schema { - exec { "create db $ds_name": - command => "$mysql_root_cmd $ds_name < $ds_schema > /var/lib/mysql/${ds_name}-create-db.log", - creates => "/var/lib/mysql/${ds_name}-create-db.log", - require => [Service["mysql"], Exec["create datasource $ds_name"]], +define mysql::datasource($rootpw, $ds_name, $ds_owner, $ds_owner_pwd, $ds_user, $ds_user_pwd, $ds_schema, mysql_replication_user, mysql_replication_password, mysql_root_database, mysql_root_local_host) { + case $mysql_type { + standalone: { + $mysql_root_cmd = "/usr/bin/mysql --user=root --password=$rootpw " + + exec { "create datasource $ds_name": + command => "/usr/bin/mysqladmin -u root -p$rootpw create $ds_name", + unless => "$mysql_root_cmd $ds_name --execute='\s'", + require => [Service["mysql"], Exec["restart mysql server"]], + } + + exec { "create grants $ds_name": + command => "$mysql_root_cmd --database=$mysql_root_database --execute=\"GRANT ALL PRIVILEGES ON *.* TO '$ds_owner'@'%' IDENTIFIED BY '$ds_owner_pwd' WITH GRANT OPTION;\"", + unless => "/usr/bin/mysql --host=$ipaddress --user=$ds_owner --password=$ds_owner_pwd --database=$mysql_root_database --execute='\s'", + require => Exec["create datasource $ds_name"], + } + + exec { "create grants $ds_user": + command => "$mysql_root_cmd --database=$mysql_root_database --execute=\"GRANT SELECT,INSERT,UPDATE,DELETE ON $ds_name.* TO '$ds_user'@'%' IDENTIFIED BY '$ds_user_pwd';\"", + unless => "/usr/bin/mysql --host=$ipaddress --user=$ds_user --password=$ds_user_pwd --database=$mysql_root_database --execute='\s'", + require => Exec["create grants $ds_name"], + } + + # Only create the schema is a template directory was specified + if $ds_schema { + exec { "create db $ds_name": + command => "$mysql_root_cmd $ds_name < $ds_schema > /var/lib/mysql/${ds_name}-create-db.log", + creates => "/var/lib/mysql/${ds_name}-create-db.log", + onlyif => "$mysql_root_cmd --database=$mysql_root_database --execute='\s'", + require => Exec["create grants $ds_user"], + } + } + } + primary-master: { + $mysql_root_cmd ="/usr/bin/mysql --user=root --password=$rootpw " + $mysql_cmd_repl_slave ="/usr/bin/mysql --user=$mysql_replication_user --database=$mysql_root_database --host=$mysql_master_ip_address --password=$mysql_replication_password" + + file { "/var/lib/mysql/${ds_name}_verify_slave_configuration.bash": + ensure => present, + owner => "mysql", + group => "mysql", + mode => 0755, + content => template("mysql/verify_slave_configuration.bash.erb"), + require => [Service["mysql"], Exec["restart slave server"]], + } + + exec { "create datasource $ds_name": + command => "/usr/bin/mysqladmin -u root -p$rootpw create $ds_name", + onlyif => "/var/lib/mysql/${ds_name}_verify_slave_configuration.bash", + require => File["/var/lib/mysql/${ds_name}_verify_slave_configuration.bash"], + } + + exec { "create all grants $ds_name": + command => "$mysql_root_cmd --database=$mysql_root_database --execute=\"GRANT ALL PRIVILEGES ON *.* TO '$ds_owner'@'%' IDENTIFIED BY '$ds_owner_pwd' WITH GRANT OPTION;\"", + creates => "/var/lib/mysql/'$ds_name'-all-grants-created.out", + unless => "$mysql_cmd_repl_slave --execute=\"select user from user;\" | grep '$ds_owner'", + require => Exec["create datasource $ds_name"], + } + + exec { "create select grants $ds_user": + command => "$mysql_root_cmd --database=$mysql_root_database --execute=\"GRANT SELECT,INSERT,UPDATE,DELETE ON $ds_name.* TO '$ds_user'@'%' IDENTIFIED BY '$ds_user_pwd';\"", + creates => "/var/lib/mysql/'$ds_name'-select-grants-created.out", + unless => "$mysql_cmd_repl_slave --execute=\"select user from user;\" | grep '$ds_user'", + require => Exec["create all grants $ds_name"], + } + + # Only create the schema is a template directory was specified + if $ds_schema { + exec { "create db schema $ds_name": + command => "$mysql_root_cmd --database=$ds_name < $ds_schema > /var/lib/mysql/${ds_name}-create-db.log", + creates => "/var/lib/mysql/${ds_name}-create-db.log", + onlyif => "$mysql_root_cmd --database=$mysql_root_database --execute='\s'", + require => Exec["create select grants $ds_user"], + } + } } } } diff --git a/templates/gather_master_data.bash.erb b/templates/gather_master_data.bash.erb index 53cf2cf..093b4a8 100644 --- a/templates/gather_master_data.bash.erb +++ b/templates/gather_master_data.bash.erb @@ -2,9 +2,9 @@ PATH=$PATH -logname=$(mysql -u replication --host=<%= mysql_master_ip_address %> --password=password --execute="show master status" | grep mysqllog | awk '{ print $1 }') +logname=$(mysql --user=<%= mysql_replication_user %> --host=<%= mysql_master_ip_address %> --password=<%= mysql_replication_password %> --execute="show master status" | grep mysqllog | awk '{ print $1 }') export logname -position=$(mysql -u replication --host=<%= mysql_master_ip_address %> --password=password --execute="show master status" | grep mysqllog | awk '{ print $2 }') +position=$(mysql --user=<%= mysql_replication_user %> --host=<%= mysql_master_ip_address %> --password=<%= mysql_replication_password %> --execute="show master status" | grep mysqllog | awk '{ print $2 }') export position rm /var/lib/mysql/set_master_repl_data.sql @@ -16,11 +16,11 @@ export mysql_master_ip_address=<%= mysql_master_ip_address %> echo CHANGE MASTER TO >> /var/lib/mysql/set_master_repl_data.sql echo MASTER_HOST=\'$mysql_master_ip_address\', >> /var/lib/mysql/set_master_repl_data.sql -echo MASTER_USER=\'replication\', >> /var/lib/mysql/set_master_repl_data.sql -echo MASTER_PASSWORD=\'password\', >> /var/lib/mysql/set_master_repl_data.sql +echo MASTER_USER=\'<%= mysql_replication_user %>\', >> /var/lib/mysql/set_master_repl_data.sql +echo MASTER_PASSWORD=\'<%= mysql_replication_password %>\', >> /var/lib/mysql/set_master_repl_data.sql echo MASTER_LOG_FILE=\'$logname\', >> /var/lib/mysql/set_master_repl_data.sql echo MASTER_LOG_POS=$position, >> /var/lib/mysql/set_master_repl_data.sql echo MASTER_CONNECT_RETRY=10\; >> /var/lib/mysql/set_master_repl_data.sql +/usr/bin/mysql --user=<%= mysql_replication_user %> --password=<%= mysql_replication_password %> --database=<%= mysql_root_database %> --host=<%= mysql_root_local_host %> --execute="source /var/lib/mysql/set_master_repl_data.sql" -/usr/bin/mysql --user='replication' --password='password' --database='mysql' --host='localhost' --execute="source /var/lib/mysql/set_master_repl_data.sql" diff --git a/templates/my.cnf.erb b/templates/my.cnf.erb index 1c009fa..db1a4ef 100644 --- a/templates/my.cnf.erb +++ b/templates/my.cnf.erb @@ -54,7 +54,7 @@ default-character-set=utf8 # <% if binary_logging %> log-output =FILE -log =/var/lib/mysql/mysqllog_general.log +#log =/var/lib/mysql/mysqllog_general.log log-slow-queries=/var/lib/mysql/mysqllog_slow_queries.log log-error =/var/lib/mysql/mysqlderror.log log-bin =/var/lib/mysql/mysqllog_bin.log @@ -74,6 +74,10 @@ init-connect='SET NAMES utf8' innodb_file_per_table +# Auto Increment Parameters +auto_increment_increment = 10 +auto_increment_offset = <%= mysql_server_id %> + # # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set @@ -147,6 +151,19 @@ server-id = <%= mysql_server_id %> # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high #innodb_buffer_pool_size = 16M + +innodb_buffer_pool_size=<%= +mem = memorysize[0..-4].to_f +unit = memorysize[-2..-2] +if unit == "G" +mem = mem*1024 +unit = "M" +end +(mem / 2.0).round +%><%= +unit +%> + #innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 5M @@ -154,6 +171,7 @@ server-id = <%= mysql_server_id %> #innodb_flush_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 + [mysqldump] quick max_allowed_packet = 16M @@ -181,12 +199,12 @@ interactive-timeout <% if binary_logging %> [mysqld_safe] log-output =FILE -log =/var/lib/mysql/mysqllog_general.log +#log =/var/lib/mysql/mysqllog_general.log log-slow-queries=/var/lib/mysql/mysqllog_slow_queries.log log-error =/var/lib/mysql/mysqlderror.log log-bin =/var/lib/mysql/mysqllog_bin.log log-queries-not-using-indexes -long-query-time=2 +long-query-time=5 innodb_flush_log_at_trx_commit=1 sync_binlog=1 diff --git a/templates/verify_slave_configuration.bash.erb b/templates/verify_slave_configuration.bash.erb new file mode 100644 index 0000000..91abffc --- /dev/null +++ b/templates/verify_slave_configuration.bash.erb @@ -0,0 +1,70 @@ +#!/bin/bash -x + +PATH=$PATH + +# Chech to see if the messaging and datasource databases are configured. + +ds_name_response=$(mysql --user=<%= mysql_replication_user %> --host=<%= mysql_master_ip_address %> --password=<%= mysql_replication_password %> --execute="show databases;" | grep <%= ds_name %>) +export ds_name_response +echo $ds_name_response + +if [ $ds_name_response = '<%= ds_name %>' ]; then + export response=True + exit 1 +else + export response=False + echo $response + echo $ds_name_response +fi + +rm /var/lib/mysql/<%= ds_name %>_verify_slave_status.out + +response=TRUE +export $response + +mysql --user=<%= mysql_replication_user %> --host=<%= mysql_master_ip_address %> --password=<%= mysql_replication_password %> --execute="show slave status\G" > /var/lib/mysql/<%= ds_name %>_verify_slave_status.out + +Slave_IO_Running=$(grep 'Slave_IO_Running:' /var/lib/mysql/<%= ds_name %>_verify_slave_status.out | grep Yes | awk '{ print $2 }') +echo $Slave_IO_Running + +if [ $Slave_IO_Running = "Yes" ]; then + export response=True +else + export response=False + echo $response + exit 1 +fi + +Slave_SQL_Running=$(grep 'Slave_SQL_Running:' /var/lib/mysql/<%= ds_name %>_verify_slave_status.out | grep Yes | awk '{ print $2 }') + echo $Slave_SQL_Running + +if [ $Slave_SQL_Running = "Yes" ]; then + export response=True +else + export response=False + echo $response + exit 1 +fi + +Slave_IO_Stat=$(grep 'Slave_IO_State:' /var/lib/mysql/<%= ds_name %>_verify_slave_status.out | grep 'Waiting for master to send event' | awk '{ print $6 }') + echo $Slave_IO_Stat + +if [ $Slave_IO_Stat = "send" ]; then + export response=True +else + export response=False + echo $response + exit 1 +fi + +Seconds_Behind_Master=$(grep 'Seconds_Behind_Master:' /var/lib/mysql/<%= ds_name %>_verify_slave_status.out | grep 0 | awk '{ print $2 }') + echo $Seconds_Behind_Master + +if [ $Seconds_Behind_Master = "0" ]; then + export response=True +else + export response=False + echo $response + exit 1 +fi + |