#!/usr/bin/perl
#
# Propagate changed settings in mysql .cnf files into a running mysqld instance where possible
#
# Usage: see --usage or man reconf-mysql
#
# TODO
#  - Also log to the mysqld log (ie in parallel)
#  - Consider NOT honouring --silent for the log output, only STDERR
#  - Teach changed() about more mysql global variable special cases
#  - Review the dynamic variable list for any variables requiring special
#    treatment
#

use strict;
use warnings;
use feature 'state';
use Tie::Syslog;
use Getopt::Long qw(:config gnu_getopt);
use Pod::Usage;
use Socket;
use Errno qw(:POSIX);
use Text::Wrap qw(wrap);
use IPC::Run qw(run);
use Data::Dumper;

$Text::Wrap::columns = 120;
$Text::Wrap::break = qr/[\s]/;

# Command line options
my $OPTS = {
	'dryrun'      => 0,
	'verbose'     => 0,
	'apply'       => 0,
	'usage'       => 0,
	'silent'      => 0,
	'nocheckhost' => 0,
	'nodefaults'  => 0
};

my $DEFAULTS = {}; # Config values compiled into mysqld (from mysqld --no-defaults --verbose --help)
my $CONF     = {}; # Config optionss parsed from files
my $LIVE     = {}; # Live server configuration

# The following config key patterns can't be used/trusted from the system defaults and thus should be ignored
# each entry is treated as an anchored regex $<pattern>$ against the normalised key name (lower case, - => _ no --)
# metacharcters in the patterns are NOT escaped by the code.  see sub skip() for implementation details
# NOTE: At present the *value* matches are NOT considered ONLY the key matches
my $IGNORE_DEFAULTS = {
	'back_log'                   => '.*',                    # Dyanmic dependency on max_connections
	'host_cache_size'            => '.*',                    # Dyanmic dependency on max_connections
	'innodb_data_file_path'      => '',                      # Empty string => auto
	'innodb_io_capacity_max'     => '18446744073709551615',  # INT_MAX => Auto
	'innodb_log_group_home_dir'  => '',                      # Empty string => auto
	'innodb_mirrored_log_groups' => '0',
	'innodb_open_files'          => 0,
	'log_bin_index'              => '',
	'open_files_limit'           => '.*',
	'relay_log_index'            => '',
	'report_port'                => '0',
	'thread_cache_size'          => '.*',
	'optimizer_trace'            => '',
	'performance_schema_.+'      => '-1'
};

# Config keys that should be ignored from config files (via my_print_defaults), see the description of
# $IGNORE_DEFAULTS for details
my $IGNORE_CONFIG = {
};

# Config keys that should be ignored regardless of source, see the description of $IGNORE_DEFAULTS for details
my $IGNORE_ANY = {
	'log_error'        => '.*',
	'pid_file'         => '.*',
	'log_bin'          => '.*',
	'open_files_limit' => '.*' # Not dynamic
};


GetOptions(
	'dry-run|dryrun|test|n' => \$OPTS->{'dryrun'},
	'verbose|v:+'           => \$OPTS->{'verbose'},
	'silent|s|q!'           => \$OPTS->{'silent'},
	'apply'                 => \$OPTS->{'apply'},
	'usage|help|h|?'        => \$OPTS->{'usage'},
	'no-check-host|c!'      => \$OPTS->{'nocheckhost'},
	'log-only|l!'           => \$OPTS->{'logonly'},
	'no-defaults|d!'        => \$OPTS->{'nodefaults'}

) or pod2usage(-exitval=>1);
pod2usage(-exitval=>0) if $OPTS->{'usage'};

# Silent overrides verbose
$OPTS->{'verbose'} = 0 if $OPTS->{'silent'};

# --dry-run overrides --apply
$OPTS->{'apply'} = 0 if $OPTS->{'dryrun'};

pod2usage(
	-exitval => 1,
	-msg     => "\nERROR: You must specific at least one of --dry-run or --apply!\n"
) if (!$OPTS->{'dryrun'} && !$OPTS->{'apply'});

# Set up redirection to ensure everything always gets logged
$Tie::Syslog::ident  = "mysqld(reconf)";
if ($OPTS->{'logonly'}) {
	# Don't (also) output to stderr (eg for systemd)
	$Tie::Syslog::logopt = "pid,ndelay";
} else {
	$Tie::Syslog::logopt = "pid,ndelay,perror";
}
tie *STDERR, 'Tie::Syslog', {
	facility => 'LOG_DAEMON',
	priority => 'LOG_INFO'
};
tie *STDOUT, 'Tie::Syslog', {
	facility => 'LOG_DAEMON',
	priority => 'LOG_INFO'
};


# Check that this is a *local* mysqld instance! (that is the host maps to a local address)
# TODO: For output purposes should failing this check really be considered an 'error' ?
my $dbhost = (map { s/^--host=//gr; } grep {m/^--host=/} split(/\n/, qx(my_print_defaults client mysql)))[-1];
$dbhost = 'localhost' unless defined $dbhost && length($dbhost =~ s/\s//gr);
print "Checking whether configured mysql host ($dbhost) is local\n" if $OPTS->{'verbose'} >= 1;
if ($OPTS->{'nocheckhost'}) {
	print "  * Skipping local host check due to --no-check-host option\n" if $OPTS->{'verbose'} >= 1;
} elsif ($dbhost =~ m/^(localhost(\.localdomain)?|127.0.0.1|::1)$/i) {
	print "  * Procceding with host from my.cnf ($dbhost) as it is a well known name for the local host\n" if $OPTS->{'verbose'} >= 1;
} else {
	# Check the address is local by trying to bind to it (on any port)
	my $dbaddr = scalar gethostbyname($dbhost);
	my $sock;
	if (!socket($sock, PF_INET, SOCK_DGRAM, (getprotobyname('udp'))[2])) {
		print "  - Skipping host from my.cnf ($dbhost) because it can't be confirmed as this host ($!)\n" unless $OPTS->{'silent'};
		exit(1);
	}
	if (bind($sock, sockaddr_in(0, $dbaddr))) {
		my ($port, $addr) = sockaddr_in(getsockname($sock));
		if ($addr != $dbaddr) {
			print "  - Skipping host from my.cnf ($dbhost) as it doesn't appear to be this host (" . inet_ntoa($dbaddr) .  " != " . inet_ntoa($addr) . ")\n" unless $OPTS->{'silent'};
			exit(1);
		} elsif ($OPTS->{'verbose'} >= 1) {
			print "  * Procceding with host from my.cnf ($dbhost) as it appears to be this host (" . inet_ntoa($addr) . ")\n";
		}
	} else {
		if ($! == &Errno::EADDRNOTAVAIL) {
			# Failed due to un-listenable address
			print "  - Skipping host from my.cnf ($dbhost) as it doesn't appear to be this host ($!)\n" unless $OPTS->{'silent'};
			exit(1);
		} else {
			# Failed for some other reason
			print "  - Skipping host from my.cnf ($dbhost) because it can't be confirmed as this host ($!)\n" unless $OPTS->{'silent'};
			exit(1);
		}
	}
}

# Check that mysqld is actually reachable / running
qx(mysqladmin ping >/dev/null 2>/dev/null);
my $in = "";
my $out;
my $err;
print "Checking whether mysqld is running and reachable\n" if $OPTS->{'verbose'} >= 1;
run(['mysqladmin', 'ping'], \$in, \$out, \$err);
if ($?) {
	print "  ! Failed to connect to mysqld (not running?), aborting update process\n" unless $OPTS->{'silent'};
	exit(1);
}

# Parse the mysqld default values?
if (! $OPTS->{'nodefaults'}) {
	print "Parsing defaults from mysqld --no-defaults --verbose --help\n" if ($OPTS->{'verbose'} >= 3);
	my $defaults = qx(mysqld --no-defaults --verbose --help 2>/dev/null);
	$defaults =~ s/^.*\n-----------------------------------+\s+-------+\n//msg;
	$defaults =~ s/\n\s*\n.*$//msg;
	for my $line (split(/\n/, $defaults)) {
		my ($k,$v) = split(/\s+/, $line, 2);
		next if skip($k, $v, 'defaults');
		($k,$v) = normalise($k,$v,'mysqld defauts');
		$DEFAULTS->{lc($k)} = $v;
	}
}

# Parse the system .cnf files using my_print_defaults
print "Parsing options from config file(s) using my_print_defaults\n" if ($OPTS->{'verbose'} >= 3);
for my $line (grep { m/^--[A-Za-z][-A-Za-z_0-9]+=/ } (split(/\n/, qx(my_print_defaults mysqld)))) {
	my ($k,$v) = split(/=/, $line, 2);
	# These are from the mysqld_safe section and/or can't be set at runtime and should be skipped
	next if skip($k, $v, 'config');
	($k,$v) = normalise($k,$v,'config files');
	$CONF->{lc($k)} = $v;
}

# Extract the current settings from mysql for logging purposes
print "Fetching option values from mysqld server performance_schema\n" if ($OPTS->{'verbose'} >= 3);

my @keys = keys %$DEFAULTS;
push @keys, keys %$CONF;
@keys = sort @keys;

my $SQL = "SELECT VARIABLE_NAME, VARIABLE_VALUE\n" .
          "  FROM performance_schema.global_variables\n" .
          " WHERE VARIABLE_NAME IN (\n" .
          "       " . Text::Wrap::wrap('    ', '               ', join(', ', map { "'$_'" } @keys)) . "\n" .
          "       );\n";
if ($OPTS->{'verbose'} >= 4) {
	print "  + Using SQL statment:\n" if ($OPTS->{'verbose'} >= 4);
	print $SQL =~ s/^/    /gmr;
}

$out = '';
$err = '';
run(['mysql', '-NB'], \$SQL, \$out, \$err);
if ($?) {
	# Call to mysql failed
	chomp $err;
	print "  ! Failed to read current settings from live mysql server: $err\n" unless $OPTS->{'silent'};
	exit(1);
}
for my $line (split(/\n/, qx(mysql -NB -e "$SQL"))) {
	my ($k, $v) = split(/\t/, $line, 2);
	$LIVE->{lc($k)} = $v;
	print "  * Read variable $k value $v from performance_schema\n" if $OPTS->{'verbose'} >= 3;
}


# Check whether a value from a config source should be skipped
sub skip {
	my ($k, $v, $source) = @_;

	# Normalise the key
	$k = lc($k);
	$k =~ s/^--//g;
	$k =~ s/-/_/g;

	# Compile the ignored key regex on the first use
	state $ignore_any_key_pattern = undef;
	if ( ! defined $ignore_any_key_pattern ) {
		$ignore_any_key_pattern = '^(' . join('|', keys(%$IGNORE_ANY)) . ')$';
		$ignore_any_key_pattern = qr/$ignore_any_key_pattern/i;
	}

	if ( $k =~ m/$ignore_any_key_pattern/ ) {
		if ($OPTS->{'verbose'} >= 3) {
			print "  * Ignored variable " . uc($k) . " value $v from $source because: hardcoded global exclusion\n";
		}
		return 1;
	}

	if ($source eq 'defaults') {
		# Compile the ignored key regex on the first use
		state $ignore_defaults_key_pattern = undef;
		if ( ! defined $ignore_defaults_key_pattern) {
			$ignore_defaults_key_pattern = '^(' . join('|', keys(%$IGNORE_DEFAULTS)) . ')$';
			$ignore_defaults_key_pattern = qr/$ignore_defaults_key_pattern/i;
		}


		if ($k =~ m/$ignore_defaults_key_pattern/) {
			if ($OPTS->{'verbose'} >= 3) {
				print "  * Ignored variable " . uc($k) . " value $v from $source because: hardcoded defaults exclusion\n";
			}
			return 1;
		}
	} elsif ($source eq 'config') {
		# Compile the ignored key regex on the first use
		state $ignore_config_key_pattern = undef;
		if ( ! defined $ignore_config_key_pattern) {
			$ignore_config_key_pattern = '^(' . join('|', keys(%$IGNORE_CONFIG)) . ')$';
			$ignore_config_key_pattern = qr/$ignore_config_key_pattern/i;
		}


		if ($k =~ m/$ignore_config_key_pattern/) {
			if ($OPTS->{'verbose'} >= 3) {
				print "  * Ignored variable " . uc($k) . " value $v from $source because: hardcoded config exclusion\n";
			}
			return 1;
		}
	}

	return 0;
}


#
# Normalise a key => value pair into standard form acceptable to SET GLOBAL ....
#
sub normalise {
	my $k      = shift;
	my $rawv   = shift;
	my $source = shift // 'unknown';

	my $v = $rawv;

	$k =~ s/^--//;
	$k =~ s/-/_/g;
	$k = lc($k);

	# Convert no default value to the empty string
	$v =~ s/^\(No default value\)$//;

	# Need to transform SI suffixes to raw numbers (k|M|G|T) etc
	# Since internally these aren't accepted, only from initial config parse
	if ($v =~ m/^(\d+)([KMGT])$/i) {
		$v = $1;
		my $pow = (index('KMGT', uc($2))+1)*10;
		$v = $v * 2**$pow;
		if ($OPTS->{'verbose'} >= 3) {
			print "  * Parsed variable " . uc($k) . " value $v from $source (raw value $rawv)\n";
		}
	} elsif ($OPTS->{'verbose'} >= 3) {
		print "  * Parsed variable " . uc($k) . " value $v from $source\n";
	}

	return ($k,$v);
}


#
# Compare an old and new variable using some hueristics around booleans etc
# to determine whether the value has actually changed, this is mostly just about
# reducing noise as generally (re)setting a variable to the same value shouldn't
# have much impact
#
sub changed {
	my ($name, $old, $new) = @_;
	return 0 if ($old eq $new);                                                                         # Exact match
	return 0 if ($old =~ m/^ON$/i    && ($new eq '1' || uc($new) eq 'TRUE'  || uc($new) eq uc($old)) ); # Boolean
	return 0 if ($old =~ m/^OFF$/i   && ($new eq '0' || uc($new) eq 'FALSE' || uc($new) eq uc($old)) ); # Boolean
	return 0 if ($old =~ m/^TRUE$/i  && ($new eq '1' || uc($new) eq 'TRUE'  || uc($new) eq uc($old)) ); # Boolean
	return 0 if ($old =~ m/^FALSE$/i && ($new eq '0' || uc($new) eq 'FALSE' || uc($new) eq uc($old)) ); # Boolean
	return 0 if ($old eq 'OFF'       && $new eq ''                                                   ); # Boolean(ish)
	return 0 if ($old =~ m/\d(\.\d+)?/ && $new =~ m/\d(\.\d+)?/ && $old == $new);                       # Numeric match
	return 0 if ($old eq '' && $new eq '(built-in)' || $new eq '' && $old eq '(built-in)');             # Allow '(built-in)' to equal ''
	return 0 if ($name =~ m/(^secure_file_priv|dir)$/i && $old eq "$new/");                             # Directory path

	if ($name =~ m/sql_mode/i) {
		# Order of comma separated list doesn't matter for sql_mode
		# TODO: Some mode shortcut names map to multuple values
		return 0 if (join(',', sort(split(/,/,$old))) eq join(',', sort(split(/,/,$new))));
	}

	# Note that this could still be enhanced greatly, many string enum settings
	# (eg binlog_checksum) are case-insensitive and various set-based options
	# are order-independant (eg sql_mode) currently we will detect trivial case
	# or ordering changes for these variables as changes requiring application.
	# There are also paths that will be normalised (eg trailing slash for
	# directories
	# Unfortunantly MySQL provides no access to these details through the SQL interface

	return 1;
}

# Since we are using the mysql client binary we don't have the client library functions
# available, so instead we check for known safe patterns and otherwise use hex encoding
sub mysql_safe_escape {
	my $val = shift;

	# Undefined values map to 'NULL'
	return 'NULL' if !defined $val;

	# Integer or float
	return $val if $val =~ m/^\d+(\.\d+)?$/;

	# Boolean
	return $val if $val =~ m/^(ON|OFF|TRUE|FALSE)$/;

	# Simple string
	return "'$val'" if $val =~ m|^[-_./,+A-Za-z0-9]*$|;

	# Complex string, just hex encode it
	return "UNHEX('" . unpack('H*', $val) . "')";
}

# Return a human readable string string for the value $v
sub readable {
	my $v = shift;
	$v =~ s/^(\s*)$/'$1'/;
	return $v;
}

print "Setting option values in mysqld server\n" if ($OPTS->{'verbose'} >= 1);
my $pass = 0;
my $fail = 0;
my $skip = 0;
foreach my $var (@keys) {
	# Defaults brings in some non SQL level variables, so skip them
	next unless defined $LIVE->{$var};
	my $source = exists $CONF->{$var} ? 'config' : 'defaults';
	my $value = exists $CONF->{$var} ? $CONF->{$var} : $DEFAULTS->{$var};
	if ( ! defined $LIVE->{$var} || changed($var, $LIVE->{$var}, $value) ) {
		# Has a new value so change it
		my $SQL = "SET \@\@global.\`$var\` = " . mysql_safe_escape($value) . "; -- Was previously '$LIVE->{$var}'";
		my $out=undef;
		my $err=undef;
		if ($OPTS->{'dryrun'}) {
			print "  * DRY-RUN: Would try to set variable " . uc($var) . " to " . readable($value) . " from $source (currently " . $LIVE->{$var} . ")\n" if ($OPTS->{'verbose'} >= 1);
			$pass++;
		} else {
			run(['mysql', '-NB'], \$SQL, \$out, \$err);
		        my $rc = $?;
		        $pass++ if $rc == 0;
		        $fail++ if $rc != 0;
		        if ($rc == 0) {
					print "  * Set global variable " . uc($var) . " to " . mysql_safe_escape($value) . " from $source (was " . mysql_safe_escape($LIVE->{$var}) . ")\n" if $OPTS->{'verbose'} >= 1;
		        } elsif (not $OPTS->{'silent'}) {
					# Failed for some reason
					chomp $err;
		                print "  ! Failed to set global variable " . uc($var) . " to " . mysql_safe_escape($value) . " from $source (currently " . mysql_safe_escape($LIVE->{$var}) . "): $err\n";
			}
		}
		print '    Using SQL: ' . $SQL ."\n" if ($OPTS->{'verbose'} >= 4);
	} else {
		print "  - Variable " . uc($var) . " parsed as $value from config and $LIVE->{$var} from performance_schema - no update required\n" if ($OPTS->{'verbose'} >= 2);
		$skip++;
	}
}
if ($OPTS->{'verbose'} >= 1) {
	if ($OPTS->{'dryrun'}) {
		print "Processed " . ($skip+$pass+$fail) . " option values, found " . ($pass+$fail) . " changed variables for which a change would have been attempted without --dry-run.  $skip variables were deemed unchanged.\n";
	} else {
		print "Processed " . ($skip+$pass+$fail) . " option values, found " . ($pass+$fail) . " changed variables of which $pass were applied successfully and $fail failed.  $skip varibles were deemed unchanged.\n";
	}
}



__END__

=head1 NAME

reconf-mysql - apply my.cnf settings to a running mysqld server

=head1 SYNOPSIS

reconf-mysql [options] (--dry-run|--apply)

    Options:

	--usage   | --help | -?    Show usage message
	--verbose | -v             Be verbose about actions and information, may be specified multiple
	                           times to increase the verbosity (max level 3)
	--silent  | -s     | -q    Don't print anything, even when errors are encountered, overrides
                                   --verbose
	--no-check-host    | -c    Don't verify that the affected mysqld server instance is local to this
	                           machine before proceeding
	--log-only | -l            Usually output is sent to both STDERR and the system log, when --log-only
	                           is specified all output is sent *only* to the log
	--no-defaults | -d         Consider the default values reported by mysqld --no-defaults --verbose --help
							   as well as config file values from my_print_defaults, this allows settings
							   removed from the config file to be reverted to it's default value

    Arguments:

	--dry-run                  Describe the changes that would be applied, but do not actually
	                           apply them to the mysqld server
	--apply                    Apply the changes to the running mysqld server

=head1 ARGUMENTS

=over 4

=item B<--dry-run>

Do everything except for actually applying any change(s) to the running mysqld server

=item B<--apply>

Apply any changes to the running mysqld server

=back

=head1 OPTIONS

=over 4

=item B<--usage | --help | -?>

Show help and usage information for reconf-mysql

=item B<--verbose | -v>

        Generate detailed output describing the information and actions being taken by reconf-mysql
        may be specified up to 2 times (eg -vv) to increse the verbosity level

        0: Show errors only
        1: Also show all variable changes
        2: Also show comparison results for unchanged variables
        3: Also show parsed and retrieved values for variables from config files and performance_schema
           as well as details of skipped / ignored variables
        4: Also show all raw SQL to be executed

=item B<--silent | -s>

        Suppress all output, including error messages.  This implies --verbose=0 and overrides any
        --verbose / -v flags specified on the command line

=item B<--no-check-host | -c>

        By default reconf-mysql will check that my.cnf is pointing to a local mysqld service (ie hosted
        on the machine reconf-mysql is running on, --no-check-host can be used to skip this check if
        it fails for some reason or you genuinely do want to apply changes to a remote host

=item B<--log-only | -l>

        By default all output from reconf-mysql will be sent to both STDERR and the local syslog/journal
        by specifying the --log-only flag the output will only be sent to the syslog.  This exists primarily
        for the systemd ExecReload case to prevent duplicate logging


=item B<--no-defaults | -d>

        By default reconf-mysql tries to mimic the impact that a restart of mysqld would have on the configuration
        including restoring any values not specified in the config files to the mysqld default values (as
        parsed from mysql --no-defaults --verbose --help), --no-defaults can be used to prevent the system defaults
        from being considered such that only variables specified in the configuration files will be considered

=back

=head1 DESCRIPTION

B<reconf-mysql> reads the mysql server compiled-in defaults and configuration file(s) - my.cnf and compares all
values found in the defaults and the the [mysqld] config section(s) against a running mysqld server instance,
optionally attempting to apply the changes to the running server.  It is intended to provide a 'reload' style
feature for mysqld where setting changes can be applied automatically without a mysqld restart.

B<reconf-mysql> is generally not aware of which mysqld global variables support dyanmic settings, nor the relevant
types or values that are supported by individual variables.  Basic hueristics are applied in order to format and
match stings, integers, floats and directories (paths), but these heruistics are almost certainly incomplete

=head1 SEE ALSO

my_print_defaults(3), mysqld --no-defaults- --verbose --help, /etc/systemd/system/mysqld.service.d/reload.conf

=cut
