Configuring MySQL + InnoDB using Puppet

JeevesBond posted this at 19:36 — 29th September 2012.

Joined: Jun 2002

I've just been struggling with problems getting InnoDB and MySQL configured using Puppet. There seems to be very little help with this out there, so thought I'd post this for anyone searching for some.

If your Puppet manifest is failing to apply and there are errors like either of these appearing in your MySQL log (possibly located in /var/log/mysql/error.log):

InnoDB: Error: auto-extending data file ./ibdata1 is of a different size
InnoDB: 1152 pages (rounded down to MB) than specified in the .cnf file:
InnoDB: initial 64000 pages, max 0 (relevant if non-zero) pages!
120929 18:59:23 InnoDB: Could not open or create data files.
120929 18:59:23 InnoDB: If you tried to add new data files, and it failed here,
120929 18:59:23 InnoDB: you should now edit innodb_data_file_path in my.cnf back
120929 18:59:23 InnoDB: to what it was, and remove the new ibdata files InnoDB created
120929 18:59:23 InnoDB: in this failed attempt. InnoDB only wrote those files full of
120929 18:59:23 InnoDB: zeros, but did not yet use them in any way. But be careful: do not
120929 18:59:23 InnoDB: remove old data files which contain your precious data!

InnoDB: Error: log file ./ib_logfile0 is of different size 0 41943040 bytes
InnoDB: than specified in the .cnf file 0 52428800 bytes!

read on!

The problem is: InnoDB does not handle changing database or log file sizes gracefully; Puppet installs MySQL, which automatically starts and creates the InnoDB database and log files according the default configuration, Puppet applies your InnoDB settings, then tries to restart MySQL, which refuses to start because the database and log files already exist and are the 'wrong' size.

ibdata1 is of a different size problem

To workaround the ibdata1 file being the wrong size it seemed safer to just create a new InnoDB data file, since this manifest may be used on different servers. So the relevant part of the Puppet manifest now looks like:

mysql::server::config { 'innodb':
  settings => {
    'mysqld' => {
      'innodb_data_file_path' => 'ibdata2:1000M:autoextend',
      [... other stuff ...]
    }
  }
}

ib_logfile0 and ib_logfile1 is of different size problem

The ib_logfile{0,1} problem is a little harder to work around. Here's the relevant part of the manifest:

$innodb_log_file_size = 40
$innodb_log_file_byte_size = $innodb_log_file_size * 1024 * 1024

# InnoDB does not handle changing log file sizes gracefully.
# This works around that by stopping MySQL and checking if the log file size
# matches the configured size, if not the logs are deleted.

exec { 'fix_logfiles':
  command   => "/etc/init.d/mysql stop; rm /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile1",
  path      => "/usr/bin:/usr/sbin:/bin",
  subscribe => Mysql::Server::Config['innodb'],
  onlyif    => "test -e /var/lib/mysql/ib_logfile0 -a \$(du -b /var/lib/mysql/ib_logfile0 | awk '{ print \$1 }') -ne ${innodb_log_file_byte_size}",
}

Mysql::Server::Config['innodb'] -> Exec['fix_logfiles'] -> Exec['mysqld-restart']

mysql::server::config { 'innodb':
  settings => {
    'mysqld' => {
      [... other stuff ...]
      'innodb_log_file_size' => "${innodb_log_file_size}M",
      [... other stuff ...]
    }
  }
}

$innodb_log_file_size should be changed to the desired log file size (in MB).

This checks if the ib_logfile0 log file exists and if its size is different from the desired log file size, if not MySQL is shutdown and both ib_logfile0 and ib_logfile1 are deleted.

Note this is a bit hackish, particularly since it just checks against ib_logfile0 and assumes: if it's present and the wrong size, ib_logfile1 will be too. But then, if InnoDB would handle things like data and log files changing size more gracefully, this pain wouldn't be necessary.

EDIT: Also, I'm pretty new to Puppet, so exercise caution when doing this. I offer no warranties including merchantability or fitness for a particular purpose.

a Padded Cell our articles site!

giffy posted this at 05:36 — 14th December 2012.

They have: 15 posts

Joined: Dec 2012

thanks for the help !!

Shoe123 posted this at 06:33 — 19th November 2014.

They have: 1 posts

Joined: Nov 2014

particularly since it just checks against ib_logfile0 and assumes: if it's present and the wrong size, ib_logfile1 will be too. But then, if InnoDB would handle things like data and log files changing size more gracefully, this pain wouldn't be necessary.

Get