From f61507920beeffa925c1dd13d76939e0352e2ae7 Mon Sep 17 00:00:00 2001 From: Steve Kemp Date: Wed, 21 Sep 2016 14:26:09 +0300 Subject: Import byteback-mysql scripts/files from jlawrie. --- byteback-mysql/post-backup.d/00-mysql | 27 +++++++ byteback-mysql/pre-backup.d/00-mysql | 31 ++++++++ byteback-mysql/scripts.d/Bytemyback/ini.pm | 91 ++++++++++++++++++++++ byteback-mysql/scripts.d/Bytemyback/lvm | 80 +++++++++++++++++++ byteback-mysql/scripts.d/Bytemyback/mysqldump-full | 8 ++ .../scripts.d/Bytemyback/mysqldump-split-db | 44 +++++++++++ 6 files changed, 281 insertions(+) create mode 100755 byteback-mysql/post-backup.d/00-mysql create mode 100755 byteback-mysql/pre-backup.d/00-mysql create mode 100755 byteback-mysql/scripts.d/Bytemyback/ini.pm create mode 100755 byteback-mysql/scripts.d/Bytemyback/lvm create mode 100755 byteback-mysql/scripts.d/Bytemyback/mysqldump-full create mode 100755 byteback-mysql/scripts.d/Bytemyback/mysqldump-split-db diff --git a/byteback-mysql/post-backup.d/00-mysql b/byteback-mysql/post-backup.d/00-mysql new file mode 100755 index 0000000..72369bf --- /dev/null +++ b/byteback-mysql/post-backup.d/00-mysql @@ -0,0 +1,27 @@ +#!/usr/bin/perl + +use warnings; +use strict; + +use lib '/etc/byteback/scripts.d'; +use Bytemyback::ini qw(readconf writeconf generateconf); + +use Sys::Hostname; + +# Shim script to determine which MySQL backup(s) to run if any + +my %config = Bytemyback::ini::readconf(); + +my $backup_method = $config{'backup_method'}; + +foreach my $method (keys %$backup_method) { + next unless $backup_method->{$method}; + print "Running /etc/byteback/scripts.d/Bytemyback/${method} post\n"; + `/etc/byteback/scripts.d/Bytemyback/${method} post` if (-x "/etc/byteback/scripts.d/Bytemyback/${method}"); + print "Finished /etc/byteback/scripts.d/Bytemyback/${method} post\n"; + + # Each script should have a heartbeat alert. They may also have specific failure alerts on both client and + # server side. + my $host = hostname; + system("mauvesend -i $method-hb-${host}-low -c now -r +26h -s 'MySQL backup method $method on ${host} has not run for over a day' --detail='Please try running manually to see what the problem was, and check the mailing list'"); +} diff --git a/byteback-mysql/pre-backup.d/00-mysql b/byteback-mysql/pre-backup.d/00-mysql new file mode 100755 index 0000000..edfcd56 --- /dev/null +++ b/byteback-mysql/pre-backup.d/00-mysql @@ -0,0 +1,31 @@ +#!/usr/bin/perl + +use warnings; +use strict; + +use Data::Dumper; + +use lib '/etc/byteback/scripts.d'; +use Bytemyback::ini qw(readconf writeconf generateconf); + +# Shim script to determine which MySQL backup(s) to run if any + +my %config = Bytemyback::ini::readconf(); +if (!%config) { + %config = Bytemyback::ini::generateconf(); +} + +# Backup directory shouldn't be world-readable +system("mkdir -p /var/backups/byteback"); +system("chmod 700 /var/backups/byteback"); + +# List of each script to run (not mutually exclusive). Generated automatically if it doesn't exist + +my $backup_method = $config{'backup_method'}; + +foreach my $method (keys %$backup_method) { + next unless $backup_method->{$method}; + print "Running /etc/byteback/scripts.d/Bytemyback/${method} pre\n"; + `/etc/byteback/scripts.d/Bytemyback/${method} pre` if (-x "/etc/byteback/scripts.d/Bytemyback/${method}"); + print "Finished /etc/byteback/scripts.d/Bytemyback/${method} pre\n"; +} diff --git a/byteback-mysql/scripts.d/Bytemyback/ini.pm b/byteback-mysql/scripts.d/Bytemyback/ini.pm new file mode 100755 index 0000000..74b1fc7 --- /dev/null +++ b/byteback-mysql/scripts.d/Bytemyback/ini.pm @@ -0,0 +1,91 @@ +#!/usr/bin/perl + +package Bytemyback::ini; + +use warnings; +use strict; + +use Data::Dumper; + +use Exporter; +our @EXPORT_OK = qw(readconf writeconf generateconf); + +# Didn't want to have to install any libraries on all servers. +# Couldn't find anything default and lightweight to read ini files +# So wrote this. Sorry. + +sub readconf { + my $file = shift || '/etc/byteback/mysql.ini'; + my %config; + my $subsection = "default"; + return if !$file; + open my $fh, "<", $file or return; + foreach (<$fh>) { + if (/^\s*\[([^\]]+)\]/) { + $subsection = $1; + } + elsif (/^\s*(\S[^\s=]*)\s*=\s*(\S[^\s=]*)\s*$/) { + $config{$subsection}->{$1}=$2; + } + } + close $fh; + return %config; +} + +sub writeconf { + # Hate writing code like this. @_ is the list of arguments passed to + # this subroutine. It's either a file and a hash, or just a hash. A + # hash is passed as a list of pairs of arguments (and Perl puts them + # back into a hash). When used in scalar context, @_ returns the number + # of items in the array. @_ % 2 is the remainder when this is divided by + # 2 (modulo). So if it's either 1 if there are an odd number of elements + # or 0. Perl treats 1 as true, 0 as false. So this one line with 10 lines + # of explanation say if there are an odd number of elements, the first is + # the ini file and should be shifted (removed from the front), otherwise + # use a default. + my $config_file = @_ % 2 ? shift : '/etc/byteback/mysql.ini'; + my %config = @_; + open my $fh, ">", $config_file; + foreach my $subsection (keys %config) { + print $fh "[$subsection]\n"; + my $href = $config{$subsection}; + foreach my $key (keys %$href) { + print $fh $key, " = ", $href->{$key}, "\n"; + } + } + close $fh; +} + +sub generateconf { + # Wipes current config, tries to figure out a set of defaults. + # set LVM to 1 only if /etc/lvmbackup.conf exists + # If it does, also populate LV size and whether or not to lock from there + my %backup_method = ( "lvm", 0, "mysqldump-split-db", 0, "mysqldump-full", 1 ); + # LVM specific variables + my %lvm; + # By default, lock tables. May be overridden below. + $lvm{"lock"} = 1; + my $lvmconfig = '/etc/mylvmbackup.conf'; + if (-e $lvmconfig) { + my $failed = 0; + open my $conffh, "<", "/etc/mylvmbackup.conf" or $failed++; + if ($conffh) { + foreach (<$conffh>) { + if (/^\s*lvsize=(\S+)\s*$/) { + $lvm{"lvsize"} = $1; + } + if (/^\s*skip_flush_tables=1/) { + $lvm{"lock"} = 0; + } + } + close $conffh; + } + $backup_method{"lvm"} = 1; + } + else { + $backup_method{"mysqldump-full"} = 1; + } + my %config = ( "backup_method" => \%backup_method, "lvm" => \%lvm ); + writeconf(%config); + return(%config); +} diff --git a/byteback-mysql/scripts.d/Bytemyback/lvm b/byteback-mysql/scripts.d/Bytemyback/lvm new file mode 100755 index 0000000..c3ff4fb --- /dev/null +++ b/byteback-mysql/scripts.d/Bytemyback/lvm @@ -0,0 +1,80 @@ +#!/usr/bin/perl + +use DBI; +use Data::Dumper; + +use warnings; +use strict; + +my $mountpoint = "/var/backups/bytemyback/mnt/"; +my $snapshot_name = "byteback_data_snap"; +my $snapshot_size = "20G"; +my $dbh; + +die "Must specify either pre or post\n" unless @ARGV; + +if ($ARGV[0] eq 'pre') { + # Check snapshot doesn't already exist + die "Snapshot still exists\n" if `lvs 2>&1` =~ /$snapshot_name/; + my $lock_tables = 1; # This will be configurable. Whether or not it runs FLUSH TABLES WITH READ LOCK before taking the snapshot. + my $defaults_file = '/etc/mysql/debian.cnf'; + my $dsn = "DBI:mysql:;mysql_read_default_file=$defaults_file"; + $dbh = DBI->connect( + $dsn, + undef, + undef, + {RaiseError => 1} + ) or die "DBI::errstr: $DBI::errstr"; + + my $data_dir = ask_mysql_for_var('datadir'); + my $lvm_dir = get_LVM_dir(); + die "MySQL doesn't seem to be running from LVM\n" if !$lvm_dir; + $dbh->do("FLUSH TABLES WITH READ LOCK") if $lock_tables; + # Create snapshot + my $mounted_lvm = `df $lvm_dir | tail -n1 | cut -f1 -d" "`; + if (!`lvcreate -n $snapshot_name -L $snapshot_size --snapshot $mounted_lvm`) { + my $error = $!; + $dbh->do("UNLOCK TABLES") if $lock_tables; + die("Unable to create snapshot: $!\n"); + } + else { + $dbh->do("UNLOCK TABLES") if $lock_tables; + chomp (my $mapped_snapshot = "/dev/mapper/" . `dmsetup ls | grep $snapshot_name | grep -v cow | cut -f1 -d " "`); + `mkdir -p $mountpoint`; + `mount $mapped_snapshot $mountpoint`; + `touch ${mountpoint}.bytebacklvm`; + } +} +elsif ($ARGV[0] eq 'post') { + `umount $mountpoint`; + chomp (my $mapped_snapshot = "/dev/mapper/" . `dmsetup ls | grep $snapshot_name | grep -v cow | cut -f1 -d " "`); + `lvremove -f $mapped_snapshot`; +} + +sub ask_mysql_for_var { + my $var = '@@' . shift; + my $query = $dbh->prepare("SELECT ${var}"); + $query->execute; + return $query->fetchrow_hashref()->{$var}; +} + +sub get_LVM_dir { + # This is a bit hacky, it checks if lvs returns, + # then checks if the mountpoint is /dev/mapper/$firstbit-$secondbit + # Returns true only if above matches and lvs output has $secondbit $firstbit somewhere + my $datadir = ask_mysql_for_var('datadir'); + my $lvs = `lvs 2>&1`; + if (($?) || ($lvs =~ /No volume groups found/)) { + return 0; + } + my $output = `df $datadir | tail -n1`; + $output =~ s/--/:/g; + if ($output =~ m#/dev/mapper/([\w:]+)-([\w:]+)\s#) { + my ($vg, $lv) = ($1, $2); + $vg =~ s/:/-/; $lv =~ s/:/-/; + if ($lvs =~ /\s+$lv\s+$vg\s/) { + return $datadir; + } + } + return 0; +} diff --git a/byteback-mysql/scripts.d/Bytemyback/mysqldump-full b/byteback-mysql/scripts.d/Bytemyback/mysqldump-full new file mode 100755 index 0000000..b4bb01e --- /dev/null +++ b/byteback-mysql/scripts.d/Bytemyback/mysqldump-full @@ -0,0 +1,8 @@ +#!/bin/bash + +if [ "$1" == "pre" ]; then + HOST=`hostname` + echo "Dumping all databases into one backup file" + mkdir -p /var/backups/byteback/mysqldump-full + mysqldump --defaults-file=/etc/mysql/debian.cnf -A --events | gzip > /var/backups/byteback/mysqldump-full/dump.sql.gz && mauvesend -i mysqldump-split-${HOST}-low -c now -r +30h -s "mysqldump on ${HOST} has not run for over a day" --detail='Please try running manually to see what the problem was, and check the mailing list' +fi diff --git a/byteback-mysql/scripts.d/Bytemyback/mysqldump-split-db b/byteback-mysql/scripts.d/Bytemyback/mysqldump-split-db new file mode 100755 index 0000000..b484ef6 --- /dev/null +++ b/byteback-mysql/scripts.d/Bytemyback/mysqldump-split-db @@ -0,0 +1,44 @@ +#!/usr/bin/perl + +use warnings; +use strict; +use Sys::Hostname; + +if ((!@ARGV) or ($ARGV[0] ne 'pre')) { + exit; +} + +# Quick mysqldump wrapper to dump individual databases and alert if it fails at all +my $failed = 0; # only set to 0 once +my $failed_databases = ''; +my $backup_directory = '/var/backups/byteback/mysqldump-split-db/'; +`mkdir -p $backup_directory`; + +my @databases = `echo "SHOW DATABASES" | mysql --defaults-file=/etc/mysql/debian.cnf`; +shift @databases; # Get rid of 'Databases' title from the top +foreach my $database (@databases) { + chomp($database); + next if $database eq "lost+found"; + next if $database =~ /^#mysql..#lost\+found$/; + next if $database =~ /^information_schema$/; + next if $database =~ /^performance_schema$/; + next if $database =~ /^events$/; + next if $database =~ /^cond_instances$/; + print "Dumping $database\n"; + # Need to get rid of this -f once we've fixed errors with views (access denied etc.) + my $gzip = -x '/usr/bin/pigz' ? '/usr/bin/pigz' : 'gzip'; + my $error_code = system("mysqldump --defaults-file=/etc/mysql/debian.cnf --events -f $database | $gzip > ${backup_directory}/${database}.sql.gz\n"); + if ($error_code) { + $failed++; + $failed_databases .= " $database"; + } +} + +my $host = hostname; +if ($failed) { + $host = hostname; + system("mauvesend -i mysqldump-${host}-low -r now -s 'mysqldump on ${host} failed for $failed_databases' --detail='Please try running them manually to see what the problem was, and check the mailing list'"); +} +else { + system("mauvesend -i mysqldump-${host}-low -c now -s 'mysqldump on ${host} failed for $failed_databases' --detail='Please try running them manually to see what the problem was, and check the mailing list'"); +} -- cgit v1.2.1