Merge branch 'master' of git://git.puppet.immerda.ch/module-mysql

Conflicts:
	files/scripts/optimize_tables.rb
	manifests/server/base.pp

8 files changed, 270 insertions, 28 deletions

diff --git a/files/config/my.cnf.CentOS b/files/config/my.cnf.CentOS
index 1be434a..c15d2b8 100644
--- a/files/config/my.cnf.CentOS
+++ b/files/config/my.cnf.CentOS
@@ -3,13 +3,15 @@ datadir=/var/lib/mysql/data
 log-bin=/var/lib/mysql/mysql-bin
 expire_logs_days=5
 socket=/var/lib/mysql/mysql.sock
-# Default to using old password format for compatibility with mysql 3.x
-# clients (those using the mysqlclient10 compatibility package).
-old_passwords=0
+# Disabling symbolic-links is recommended to prevent assorted security risks
+symbolic-links=0
+# Settings user and group are ignored when systemd is used.
+# If you need to run mysqld under a different user or group,
+# customize your systemd unit file for mariadb according to the
+# instructions in http://fedoraproject.org/wiki/Systemd
 
 bind-address=127.0.0.1
-
-skip-bdb
+max_allowed_packet = 10M
 
 # Query cache is used to cache SELECT results and later return them
 # without actual executing the same query once again. Having the query
@@ -27,7 +29,7 @@ query_cache_size = 64M
 # indexes well, if log_long_format is enabled. It is normally good idea
 # to have this turned on if you frequently add new queries to the
 # system.
-log_slow_queries
+slow_query_log
 
 # How many threads we should keep in a cache for reuse. When a client
 # disconnects, the client's threads are put in the cache if there aren't
@@ -36,7 +38,7 @@ log_slow_queries
 # connections. (Normally this doesn't give a notable performance
 # improvement if you have a good thread implementation.)
 thread_cache_size = 4
-     
+
 # InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
 # row data. The bigger you set this the less disk I/O is needed to
 # access data in tables. On a dedicated database server you may set this
@@ -45,16 +47,20 @@ thread_cache_size = 4
 # cause paging in the operating system.  Note that on 32bit systems you
 # might be limited to 2-3.5G of user level memory per process, so do not
 # set it too high.
+innodb_data_home_dir = /var/lib/mysql/data
+innodb_data_file_path = ibdata1:10M:autoextend
+innodb_log_group_home_dir = /var/lib/mysql/
 innodb_buffer_pool_size = 50MB
 
 innodb_file_per_table
 
 query_cache_limit=5M
 
-[mysql.server]
-user=mysql
-basedir=/usr
-
 [mysqld_safe]
-log-error=/var/log/mysqld.log
-pid-file=/var/run/mysqld/mysqld.pid
+log-error=/var/log/mariadb/mariadb.log
+pid-file=/var/run/mariadb/mariadb.pid
+
+#
+# include all files from the config directory
+#
+!includedir /etc/my.cnf.d
diff --git a/files/config/my.cnf.CentOS.5 b/files/config/my.cnf.CentOS.5
new file mode 100644
index 0000000..1abf15c
--- /dev/null
+++ b/files/config/my.cnf.CentOS.5
@@ -0,0 +1,87 @@
+[mysqld]
+datadir=/var/lib/mysql/data
+log-bin=/var/lib/mysql/mysql-bin
+expire_logs_days=5
+socket=/var/lib/mysql/mysql.sock
+# Default to using old password format for compatibility with mysql 3.x
+# clients (those using the mysqlclient10 compatibility package).
+old_passwords=0
+
+character-set-server        = utf8
+skip-name-resolve
+
+bind-address=127.0.0.1
+max_allowed_packet = 10M
+
+# Disabling symbolic-links is recommended to prevent assorted security risks
+symbolic-links=0
+
+# Query cache is used to cache SELECT results and later return them
+# without actual executing the same query once again. Having the query
+# cache enabled may result in significant speed improvements, if your
+# have a lot of identical queries and rarely changing tables. See the
+# "Qcache_lowmem_prunes" status variable to check if the current value
+# is high enough for your load.
+# Note: In case your tables change very often or if your queries are
+# textually different every time, the query cache may result in a
+# slowdown instead of a performance improvement.
+query_cache_size = 64M
+
+# Log slow queries. Slow queries are queries which take more than the
+# amount of time defined in "long_query_time" or which do not use
+# indexes well, if log_long_format is enabled. It is normally good idea
+# to have this turned on if you frequently add new queries to the
+# system.
+slow_query_log
+
+# How many threads we should keep in a cache for reuse. When a client
+# disconnects, the client's threads are put in the cache if there aren't
+# more than thread_cache_size threads from before.  This greatly reduces
+# the amount of thread creations needed if you have a lot of new
+# connections. (Normally this doesn't give a notable performance
+# improvement if you have a good thread implementation.)
+thread_cache_size = 4
+
+# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
+# row data. The bigger you set this the less disk I/O is needed to
+# access data in tables. On a dedicated database server you may set this
+# parameter up to 80% of the machine physical memory size. Do not set it
+# too large, though, because competition of the physical memory may
+# cause paging in the operating system.  Note that on 32bit systems you
+# might be limited to 2-3.5G of user level memory per process, so do not
+# set it too high.
+innodb_data_home_dir = /var/lib/mysql/data
+innodb_data_file_path = ibdata1:10M:autoextend
+innodb_log_group_home_dir = /var/lib/mysql/
+innodb_buffer_pool_size = 50MB
+
+innodb_file_per_table
+
+query_cache_limit=5M
+
+[mysql]
+no-auto-rehash
+# Remove the next comment character if you are not familiar with SQL
+#safe-updates
+default-character-set=utf8
+
+[mysqlimport]
+default-character-set=utf8
+
+[mysqlshow]
+default-character-set=utf8
+
+[mysql.server]
+user=mysql
+basedir=/usr
+
+[mysqld_safe]
+log-error=/var/log/mysqld.log
+pid-file=/var/run/mysqld/mysqld.pid
+
+[mysqldump]
+quick
+max_allowed_packet = 16M
+default-character-set=utf8
+
+!includedir /etc/mysql/conf.d/
diff --git a/files/config/my.cnf.CentOS.6 b/files/config/my.cnf.CentOS.6
new file mode 100644
index 0000000..1abf15c
--- /dev/null
+++ b/files/config/my.cnf.CentOS.6
@@ -0,0 +1,87 @@
+[mysqld]
+datadir=/var/lib/mysql/data
+log-bin=/var/lib/mysql/mysql-bin
+expire_logs_days=5
+socket=/var/lib/mysql/mysql.sock
+# Default to using old password format for compatibility with mysql 3.x
+# clients (those using the mysqlclient10 compatibility package).
+old_passwords=0
+
+character-set-server        = utf8
+skip-name-resolve
+
+bind-address=127.0.0.1
+max_allowed_packet = 10M
+
+# Disabling symbolic-links is recommended to prevent assorted security risks
+symbolic-links=0
+
+# Query cache is used to cache SELECT results and later return them
+# without actual executing the same query once again. Having the query
+# cache enabled may result in significant speed improvements, if your
+# have a lot of identical queries and rarely changing tables. See the
+# "Qcache_lowmem_prunes" status variable to check if the current value
+# is high enough for your load.
+# Note: In case your tables change very often or if your queries are
+# textually different every time, the query cache may result in a
+# slowdown instead of a performance improvement.
+query_cache_size = 64M
+
+# Log slow queries. Slow queries are queries which take more than the
+# amount of time defined in "long_query_time" or which do not use
+# indexes well, if log_long_format is enabled. It is normally good idea
+# to have this turned on if you frequently add new queries to the
+# system.
+slow_query_log
+
+# How many threads we should keep in a cache for reuse. When a client
+# disconnects, the client's threads are put in the cache if there aren't
+# more than thread_cache_size threads from before.  This greatly reduces
+# the amount of thread creations needed if you have a lot of new
+# connections. (Normally this doesn't give a notable performance
+# improvement if you have a good thread implementation.)
+thread_cache_size = 4
+
+# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
+# row data. The bigger you set this the less disk I/O is needed to
+# access data in tables. On a dedicated database server you may set this
+# parameter up to 80% of the machine physical memory size. Do not set it
+# too large, though, because competition of the physical memory may
+# cause paging in the operating system.  Note that on 32bit systems you
+# might be limited to 2-3.5G of user level memory per process, so do not
+# set it too high.
+innodb_data_home_dir = /var/lib/mysql/data
+innodb_data_file_path = ibdata1:10M:autoextend
+innodb_log_group_home_dir = /var/lib/mysql/
+innodb_buffer_pool_size = 50MB
+
+innodb_file_per_table
+
+query_cache_limit=5M
+
+[mysql]
+no-auto-rehash
+# Remove the next comment character if you are not familiar with SQL
+#safe-updates
+default-character-set=utf8
+
+[mysqlimport]
+default-character-set=utf8
+
+[mysqlshow]
+default-character-set=utf8
+
+[mysql.server]
+user=mysql
+basedir=/usr
+
+[mysqld_safe]
+log-error=/var/log/mysqld.log
+pid-file=/var/run/mysqld/mysqld.pid
+
+[mysqldump]
+quick
+max_allowed_packet = 16M
+default-character-set=utf8
+
+!includedir /etc/mysql/conf.d/
diff --git a/files/scripts/CentOS/setmysqlpass.sh b/files/scripts/CentOS/setmysqlpass.sh
index b643edb..6876cb9 100644
--- a/files/scripts/CentOS/setmysqlpass.sh
+++ b/files/scripts/CentOS/setmysqlpass.sh
@@ -24,17 +24,22 @@ must_have chown
 
 rootpw=$(grep password /root/.my.cnf | sed -e 's/^[^=]*= *\(.*\) */\1/')
 
-/sbin/service mysqld stop
+/usr/bin/mysqladmin -uroot --password="${rootpw}" status > /dev/null && echo "Nothing to do as the password already works" && exit 0
 
-/usr/libexec/mysqld --skip-grant-tables --user=root --datadir=/var/lib/mysql/data --log-bin=/var/lib/mysql/mysql-bin &
+/usr/bin/systemctl stop mariadb
+
+/usr/libexec/mysqld --skip-grant-tables --user=root --datadir=/var/lib/mysql/data --log-bin=/var/lib/mysql/mysql-bin --pid-file=/var/run/mariadb/mariadb.pid &
 sleep 5
 mysql -u root mysql <<EOF
 UPDATE mysql.user SET Password=PASSWORD('$rootpw') WHERE User='root' AND Host='localhost';
+DELETE FROM mysql.user WHERE (User='root' AND Host!='localhost') OR USER='';
 FLUSH PRIVILEGES;
 EOF
-killall mysqld
+kill `cat /var/run/mariadb/mariadb.pid`
+sleep 15
 # chown to be on the safe side
-chown mysql.mysql /var/lib/mysql/mysql-bin.*
-
-/sbin/service mysqld start
+ls -al /var/lib/mysql/mysql-bin.* &> /dev/null
+[ $? == 0 ] && chown mysql.mysql /var/lib/mysql/mysql-bin.*
+chown -R mysql.mysql /var/lib/mysql/data/
 
+/usr/bin/systemctl start mariadb
diff --git a/files/scripts/CentOS/setmysqlpass.sh.5 b/files/scripts/CentOS/setmysqlpass.sh.5
new file mode 100644
index 0000000..abd0931
--- /dev/null
+++ b/files/scripts/CentOS/setmysqlpass.sh.5
@@ -0,0 +1,26 @@
+#!/bin/sh
+
+test -f /root/.my.cnf || exit 1
+
+rootpw=$(grep password /root/.my.cnf | sed -e 's/^[^=]*= *\(.*\) */\1/')
+
+/usr/bin/mysqladmin -uroot --password="${rootpw}" status > /dev/null && echo "Nothing to do as the password already works" && exit 0
+
+/sbin/service mysqld stop
+
+/usr/libexec/mysqld --skip-grant-tables --user=root --datadir=/var/lib/mysql/data --log-bin=/var/lib/mysql/mysql-bin &
+sleep 5
+mysql -u root mysql <<EOF
+UPDATE mysql.user SET Password=PASSWORD('$rootpw') WHERE User='root' AND Host='localhost';
+DELETE FROM mysql.user WHERE (User='root' AND Host!='localhost') OR USER='';
+FLUSH PRIVILEGES;
+EOF
+killall mysqld
+sleep 15
+# chown to be on the safe side
+ls -al /var/lib/mysql/mysql-bin.* &> /dev/null
+[ $? == 0 ] && chown mysql.mysql /var/lib/mysql/mysql-bin.*
+chown -R mysql.mysql /var/lib/mysql/data/
+
+/sbin/service mysqld start
+
diff --git a/files/scripts/CentOS/setmysqlpass.sh.6 b/files/scripts/CentOS/setmysqlpass.sh.6
new file mode 100644
index 0000000..abd0931
--- /dev/null
+++ b/files/scripts/CentOS/setmysqlpass.sh.6
@@ -0,0 +1,26 @@
+#!/bin/sh
+
+test -f /root/.my.cnf || exit 1
+
+rootpw=$(grep password /root/.my.cnf | sed -e 's/^[^=]*= *\(.*\) */\1/')
+
+/usr/bin/mysqladmin -uroot --password="${rootpw}" status > /dev/null && echo "Nothing to do as the password already works" && exit 0
+
+/sbin/service mysqld stop
+
+/usr/libexec/mysqld --skip-grant-tables --user=root --datadir=/var/lib/mysql/data --log-bin=/var/lib/mysql/mysql-bin &
+sleep 5
+mysql -u root mysql <<EOF
+UPDATE mysql.user SET Password=PASSWORD('$rootpw') WHERE User='root' AND Host='localhost';
+DELETE FROM mysql.user WHERE (User='root' AND Host!='localhost') OR USER='';
+FLUSH PRIVILEGES;
+EOF
+killall mysqld
+sleep 15
+# chown to be on the safe side
+ls -al /var/lib/mysql/mysql-bin.* &> /dev/null
+[ $? == 0 ] && chown mysql.mysql /var/lib/mysql/mysql-bin.*
+chown -R mysql.mysql /var/lib/mysql/data/
+
+/sbin/service mysqld start
+
diff --git a/files/scripts/Debian/setmysqlpass.sh b/files/scripts/Debian/setmysqlpass.sh
index f4ebee6..3de2781 100644
--- a/files/scripts/Debian/setmysqlpass.sh
+++ b/files/scripts/Debian/setmysqlpass.sh
@@ -25,12 +25,15 @@ must_have chown
 
 rootpw=$(grep password /root/.my.cnf | sed -e 's/^[^=]*= *\(.*\) */\1/')
 
+/usr/bin/mysqladmin -uroot --password="${rootpw}" status > /dev/null && echo "Nothing to do as the password already works" && exit 0
+
 /etc/init.d/mysql stop
 
 /usr/sbin/mysqld --skip-grant-tables --user=root --datadir=/var/lib/mysql --log-bin=/var/lib/mysql/mysql-bin &
 sleep 5
 mysql -u root mysql <<EOF
 UPDATE mysql.user SET Password=PASSWORD('$rootpw') WHERE User='root' AND Host='localhost';
+DELETE FROM mysql.user WHERE (User='root' AND Host!='localhost') OR USER='';
 FLUSH PRIVILEGES;
 EOF
 killall mysqld
@@ -38,6 +41,7 @@ sleep 15
 # chown to be on the safe side
 ls -al /var/lib/mysql/mysql-bin.* &> /dev/null
 [ $? == 0 ] && chown mysql.mysql /var/lib/mysql/mysql-bin.*
+chown -R mysql.mysql /var/lib/mysql/data/
 
 /etc/init.d/mysql start
 
diff --git a/files/scripts/optimize_tables.rb b/files/scripts/optimize_tables.rb
index 3eb7425..1b76704 100644
--- a/files/scripts/optimize_tables.rb
+++ b/files/scripts/optimize_tables.rb
@@ -2,12 +2,13 @@
 
 # set home as we runit as weekly cron, where HOME is /
 ENV['HOME'] = '/root'
-tables = %x(mysql -Bse "SELECT TABLE_SCHEMA,TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND Data_free > 0 AND ENGINE IN ('MyISAM','InnoDB','ARCHIVE')")
-tables = tables.split(/\n/)
-tables.each { |table|
-    tableitems = table.chomp.split(/\t/)
-    system "mysql #{tableitems[0]} -Bse \"OPTIMIZE TABLE \\`#{tableitems[0]}\\`.\\`#{tableitems[1]}\\`\" | grep -q OK"
-    if $?.to_i > 0 then
-        puts "error while optimizing #{tableitems[0]}.#{tableitems[1]}"
-    end
-}
+tables = %x{mysql -Bse "SELECT TABLE_SCHEMA,TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND Data_free > 0 AND ENGINE IN ('MyISAM','InnoDB','ARCHIVE')"}
+tables.split("\n").each do |table|
+  tableitems = table.chomp.split(/\t/)
+  output = %x{mysql #{tableitems[0]} -Bse "OPTIMIZE TABLE \\`#{tableitems[0]}\\`.\\`#{tableitems[1]}\\`"  2>&1}
+  unless output =~ /status\t+OK/
+    puts "Error while optimizing #{tableitems[0]}.#{tableitems[1]}:"
+    puts output
+    puts
+  end
+end