From 28544868b29c412418436e9924f301ac210c6236 Mon Sep 17 00:00:00 2001 From: Don Brewer Date: Thu, 27 Mar 2008 14:46:11 -0400 Subject: Adding support for standalone, master and slave mysql instances * Adding config file for UTF8 encoding * A fact called 'mysql_type' needs to be laid down on all mysql nodes. This should just be 'standalone' for now. * This module now requires the passwords module * Added ip variable to slave for master 2 * include passwords class and passwords.pp Brenton: * turned off resource collectiong since it broke us needlessly. * Added a definition for creating datasources. User's of this definition should not have to worry about what type of mysql (master,slave,etc) is running. --- files/gather_master_data.bash | 22 ++++ manifests/init.pp | 151 ++++++++++++++++++++++++-- manifests/variables.pp | 13 +++ templates/gather_master_data.bash.erb | 26 +++++ templates/my.cnf.erb | 194 ++++++++++++++++++++++++++++++++++ 5 files changed, 400 insertions(+), 6 deletions(-) create mode 100644 files/gather_master_data.bash create mode 100644 manifests/variables.pp create mode 100644 templates/gather_master_data.bash.erb create mode 100644 templates/my.cnf.erb diff --git a/files/gather_master_data.bash b/files/gather_master_data.bash new file mode 100644 index 0000000..ff7d244 --- /dev/null +++ b/files/gather_master_data.bash @@ -0,0 +1,22 @@ +#!/bin/bash + +PATH=$PATH + +logname=$(mysql -u replication --host=dmb1-jboss-mysql.usersys.redhat.com --password=password --execute="show master status" | grep mysqllog | awk '{ print $1 }') +export logname +position=$(mysql -u replication --host=dmb1-jboss-mysql.usersys.redhat.com --password=password --execute="show master status" | grep mysqllog | awk '{ print $2 }') +export position + +rm /var/lib/mysql/set_master_repl_data.sql + +#Build MySQL Master Data Script +echo CHANGE MASTER TO >> /var/lib/mysql/set_master_repl_data.sql +echo MASTER_HOST=\'10.11.227.42\', >> /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_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='replication' --password='password' --database='mysql' --host='localhost' --execute="source /var/lib/mysql/set_master_repl_data.sql" diff --git a/manifests/init.pp b/manifests/init.pp index 007d4c5..38ae339 100644 --- a/manifests/init.pp +++ b/manifests/init.pp @@ -2,27 +2,166 @@ # Copyright (C) 2007 David Schmitt # See LICENSE for the full license granted to you. +import "variables.pp" +import "passwords" + class mysql::server { + include passwords + include variables package { "mysql-server": name => "MySQL-server-community", ensure => installed, } - package { "mysql": + package { "mysql-client": name => "MySQL-client-community", ensure => installed, } - service { mysql: + service { "mysql": ensure => running, hasstatus => true, require => Package["mysql-server"], } - # Collect all databases and users - Mysql_database<<||>> - Mysql_user<<||>> - Mysql_grant<<||>> + # 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"], + } + + 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"], + } + + 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"], + } + +} + +class mysql::standalone inherits mysql::server { + $binary_logging = false + $server_id = "1" + file { "/etc/my.cnf": + ensure => present, + owner => "mysql", + group => "mysql", + mode => 0644, + content => template("mysql/my.cnf.erb"), + before => Service["mysql"], + } +} + +class mysql::master inherits mysql::server { + $binary_logging = true + $server_id = $variables::mysql_master_server_id + + file { "/etc/my.cnf": + ensure => present, + owner => "mysql", + group => "mysql", + mode => 0644, + content => template("mysql/my.cnf.erb"), + before => Service["mysql"], + } +} + +class mysql::slave inherits mysql::server { + $binary_logging = true + $server_id = $variables::mysql_slave_server_id + $master_host_ip ='10.11.227.42' + + file { "/etc/my.cnf": + ensure => present, + owner => "mysql", + group => "mysql", + mode => 0644, + content => template("mysql/my.cnf.erb"), + before => Service["mysql"], + } + + file { "/var/lib/mysql/gather_master_data.bash": + owner => "mysql", + group => "mysql", + ensure => present, + mode => 0755, + content => template("mysql/gather_master_data.bash.erb"), +# source => "puppet://$puppetserver/mysql/gather_master_data.bash", + require => Exec["grants all to replication user"], + } + + exec { "set master data for slave": + command => "/var/lib/mysql/gather_master_data.bash", + creates => "/var/lib/mysql/set_master_repl_data.sql", + require => File["/var/lib/mysql/gather_master_data.bash"], + } + + 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"], + } +} + +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_password -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"]], + } + } } diff --git a/manifests/variables.pp b/manifests/variables.pp new file mode 100644 index 0000000..5efc726 --- /dev/null +++ b/manifests/variables.pp @@ -0,0 +1,13 @@ +class variables { + $mysql_root_user = "root" + $mysql_root_database = "mysql" + $mysql_root_local_host = "localhost" + $mysql_global_host = "%" + + # Replication MySQL User + $mysql_replication_user = "replication" + + # Replication MySQL Server ID's + $mysql_master_server_id ="1" + $mysql_slave_server_id ="2" +} diff --git a/templates/gather_master_data.bash.erb b/templates/gather_master_data.bash.erb new file mode 100644 index 0000000..c9d3804 --- /dev/null +++ b/templates/gather_master_data.bash.erb @@ -0,0 +1,26 @@ +#!/bin/bash + +PATH=$PATH + +logname=$(mysql -u replication --host=<%= master_host_ip %> --password=password --execute="show master status" | grep mysqllog | awk '{ print $1 }') +export logname +position=$(mysql -u replication --host=<%= master_host_ip %> --password=password --execute="show master status" | grep mysqllog | awk '{ print $2 }') +export position + +rm /var/lib/mysql/set_master_repl_data.sql + +export master_host_ip=<%= master_host_ip %> + +#Build MySQL Master Data Script +# + +echo CHANGE MASTER TO >> /var/lib/mysql/set_master_repl_data.sql +echo MASTER_HOST=\'$master_host_ip\', >> /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_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='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 new file mode 100644 index 0000000..4013650 --- /dev/null +++ b/templates/my.cnf.erb @@ -0,0 +1,194 @@ +# Example MySQL config file for medium systems. +# +# This is for a system with little memory (32M - 64M) where MySQL plays +# an important part, or systems up to 128M where MySQL is used together with +# other programs (such as a web server) +# +# You can copy this file to +# /etc/my.cnf to set global options, +# mysql-data-dir/my.cnf to set server-specific options (in this +# installation this directory is /var/lib/mysql) or +# ~/.my.cnf to set user-specific options. +# +# In this file, you can use all long options that a program supports. +# If you want to know which options a program supports, run the program +# with the "--help" option. + +# The following options will be passed to all MySQL clients +[client] +#password = your_password +port = 3306 +socket = /var/lib/mysql/mysql.sock + +# Here follows entries for some specific programs + +# The MySQL server +[mysqld] +port = 3306 +socket = /var/lib/mysql/mysql.sock +skip-locking +key_buffer = 16M +max_allowed_packet = 1M +table_cache = 64 +sort_buffer_size = 512K +net_buffer_length = 8K +read_buffer_size = 256K +read_rnd_buffer_size = 512K +myisam_sort_buffer_size = 8M +# Added default character set +#default-character-set=utf8 +default-character-set=utf8 + +# Don't listen on a TCP/IP port at all. This can be a security enhancement, +# if all processes that need to connect to mysqld run on the same host. +# All interaction with mysqld must be made via Unix sockets or named pipes. +# Note that using this option without enabling named pipes on Windows +# (via the "enable-named-pipe" option) will render mysqld useless! +# +#skip-networking + +# Replication Master Server (default) +# binary logging is required for replication +#log-bin=mysql-bin +# +# +<% if binary_logging %> +log-output =FILE +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 + +innodb_flush_log_at_trx_commit=1 +sync_binlog=1 + +<% end %> + +#To change the connection charset permanently to UTF-8 +init-connect='SET NAMES utf8' + + +# Create tablespace per schema. +innodb_file_per_table + + +# +# required unique id between 1 and 2^32 - 1 +# defaults to 1 if master-host is not set +# but will not function as a master if omitted +server-id = <%= server_id %> + +# Replication Slave (comment out master section to use this) +# +# To configure this host as a replication slave, you can choose between +# two methods : +# +# 1) Use the CHANGE MASTER TO command (fully described in our manual) - +# the syntax is: +# +# CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=, +# MASTER_USER=, MASTER_PASSWORD= ; +# +# where you replace , , by quoted strings and +# by the master's port number (3306 by default). +# +# Example: +# +# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, +# MASTER_USER='joe', MASTER_PASSWORD='secret'; +# +# OR +# +# 2) Set the variables below. However, in case you choose this method, then +# start replication for the first time (even unsuccessfully, for example +# if you mistyped the password in master-password and the slave fails to +# connect), the slave will create a master.info file, and any later +# change in this file to the variables' values below will be ignored and +# overridden by the content of the master.info file, unless you shutdown +# the slave server, delete master.info and restart the slaver server. +# For that reason, you may want to leave the lines below untouched +# (commented) and instead use CHANGE MASTER TO (see above) +# +# required unique id between 2 and 2^32 - 1 +# (and different from the master) +# defaults to 2 if master-host is set +# but will not function as a slave if omitted +#server-id = 2 +# +# The replication master for this slave - required +#master-host = +# +# The username the slave will use for authentication when connecting +# to the master - required +#master-user = +# +# The password the slave will authenticate with when connecting to +# the master - required +#master-password = +# +# The port the master is listening on. +# optional - defaults to 3306 +#master-port = +# +# binary logging - not required for slaves, but recommended +#log-bin=mysql-bin + +# Point the following paths to different dedicated disks +#tmpdir = /tmp/ +#log-update = /path-to-dedicated-directory/hostname + +# Uncomment the following if you are using InnoDB tables +#innodb_data_home_dir = /var/lib/mysql/ +#innodb_data_file_path = ibdata1:10M:autoextend +#innodb_log_group_home_dir = /var/lib/mysql/ +#innodb_log_arch_dir = /var/lib/mysql/ +# 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_additional_mem_pool_size = 2M +# Set .._log_file_size to 25 % of buffer pool size +#innodb_log_file_size = 5M +#innodb_log_buffer_size = 8M +#innodb_flush_log_at_trx_commit = 1 +#innodb_lock_wait_timeout = 50 + +[mysqldump] +quick +max_allowed_packet = 16M + +[mysql] +no-auto-rehash +# Remove the next comment character if you are not familiar with SQL +#safe-updates + +[isamchk] +key_buffer = 20M +sort_buffer_size = 20M +read_buffer = 2M +write_buffer = 2M + +[myisamchk] +key_buffer = 20M +sort_buffer_size = 20M +read_buffer = 2M +write_buffer = 2M + +[mysqlhotcopy] +interactive-timeout + +<% if binary_logging %> +[mysqld_safe] +log-output =FILE +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 + +innodb_flush_log_at_trx_commit=1 +sync_binlog=1 + +<% end %> -- cgit