Jcrespo has submitted this change and it was merged.
Change subject: Save binary log coordinates from the master and the slave on
backup
......................................................................
Save binary log coordinates from the master and the slave on backup
Right now, backups are performed, but no binary log coordinates
are saved, which makes them useless except for "best effort"
backups.
I have changed predump backups from the backups system and custom
misc backups to include "--master-data=2 --dump-slave=2
--include-master-host-port". This allows to store as a comment:
the binary log position of the slave used. The binary log position
of its master. And the master host and port. This will allow
real point in time recovery to an arbitrary point in time, which
just one text line of overhead per file. I do not consider at this
point backuping the binary logs, as we have one copy per server
on 2 datacenters.
For both scripts, I have setup separate command lines for the
master and slave. In most cases, except in s5, the server is a
master and a slave at the same time. Be careful to use mariadb
clients for servers with mariadb extension "multisource
replication", or the slave log coordinates will not be stored.
We may want, however, to perform xtrabackup dumps in the future
if we want to reduce the MTTR. Current recovery time could be as
long as 2-3 days.
Change-Id: Ia87879f8d888efd10bd2cea771e75dd6d7b50e35
---
M manifests/role/mariadb.pp
M modules/backup/manifests/mysqlset.pp
M templates/backups/mysql-predump.erb
M templates/mariadb/dumps-misc.sh.erb
4 files changed, 24 insertions(+), 6 deletions(-)
Approvals:
Jcrespo: Looks good to me, approved
jenkins-bot: Verified
diff --git a/manifests/role/mariadb.pp b/manifests/role/mariadb.pp
index e930ea2..dfcbfb7 100644
--- a/manifests/role/mariadb.pp
+++ b/manifests/role/mariadb.pp
@@ -370,6 +370,8 @@
xtrabackup => false,
per_db => true,
innodb_only => true,
+ binlog => false,
+ slave => true,
local_dump_dir => '/srv/backups',
password_file => '/etc/mysql/conf.d/dumps.cnf',
method => 'predump',
diff --git a/modules/backup/manifests/mysqlset.pp
b/modules/backup/manifests/mysqlset.pp
index 2b8f02a..d9ca539 100644
--- a/modules/backup/manifests/mysqlset.pp
+++ b/modules/backup/manifests/mysqlset.pp
@@ -3,6 +3,8 @@
$xtrabackup = true,
$per_db = false,
$innodb_only = false,
+ $binlog = true,
+ $slave = true,
$local_dump_dir = undef,
$password_file = undef,
$mysql_binary = undef,
diff --git a/templates/backups/mysql-predump.erb
b/templates/backups/mysql-predump.erb
index b3d1532..63d63ec 100644
--- a/templates/backups/mysql-predump.erb
+++ b/templates/backups/mysql-predump.erb
@@ -31,6 +31,18 @@
# Provided we only have innodb tables --single-transcation works wonders
MYSQLDUMP="$MYSQLDUMP --single-transaction "
<% end -%>
+# register
+<% if @binlog -%>
+# if it is a master (any server that produces binary logs), register its
+# position. This will fail if the server does not produce binary logs and
+# its is not a slave either (so it can get the coordinated from the master)
+MYSQLDUMP="$MYSQLDUMP --master-data=2 "
+<% end -%>
+<% if @slave -%>
+# if the server is a slave, register the master binlog coordinates, too
+# this only works on multisource replication hosts with mariadb clients
+MYSQLDUMP="$MYSQLDUMP --dump-slave=2 --include-master-host-port "
+<% end -%>
find <%= @local_dump_dir %> -name "*sql.gz" -mtime +15 -exec rm {} \;
for database in `$MYSQL -B -N -e "select schema_name from
information_schema.schemata where schema_name not like '%\_schema'"`
diff --git a/templates/mariadb/dumps-misc.sh.erb
b/templates/mariadb/dumps-misc.sh.erb
index 9658803..ad725d0 100644
--- a/templates/mariadb/dumps-misc.sh.erb
+++ b/templates/mariadb/dumps-misc.sh.erb
@@ -4,27 +4,29 @@
my="/usr/local/bin/mysql --skip-column-names -e"
dump="/usr/local/bin/mysqldump --single-transaction --quick"
+dump_master="$dump --master-data=2"
+dump_slave="$dump --dump-slave=2 --include-master-host-port"
-$dump -h m1-slave --databases bacula etherpadlite librenms puppet racktables
rt | \
+$dump_slave -h m1-slave --databases bacula etherpadlite librenms puppet
racktables rt | \
pigz > /srv/backups/m1-$(date +%Y%m%d%H%M%S).sql.gz &
-$dump -h m2-slave --databases iegreview reviewdb scholarships | \
+$dump_slave -h m2-slave --databases iegreview reviewdb scholarships | \
pigz > /srv/backups/m2-$(date +%Y%m%d%H%M%S).sql.gz &
-$dump -h m2-slave --max_allowed_packet=64M --databases otrs | \
+$dump_slave -h m2-slave --max_allowed_packet=64M --databases otrs | \
pigz > /srv/backups/m2-otrs-$(date +%Y%m%d%H%M%S).sql.gz &
sql="select schema_name from information_schema.schemata where schema_name
regexp '^(phab|phlegal)'"
-$dump -h m3-slave --ignore-table=phabricator_search.search_documentfield \
+$dump_slave -h m3-slave --ignore-table=phabricator_search.search_documentfield
\
--max_allowed_packet=32M \
--databases $($my "$sql" | tr '\n' ' ') | \
pigz > /srv/backups/m3-phabricator-phlegal-$(date +%Y%m%d%H%M%S).sql.gz
-$dump -h m5-slave --databases ceilometer glance keystone neutron nova pdns
designate | \
+$dump_master -h m5-slave --databases ceilometer glance keystone neutron nova
pdns designate | \
pigz > /srv/backups/m5-$(date +%Y%m%d%H%M%S).sql.gz &
sql="select schema_name from information_schema.schemata where schema_name
regexp '^(wik|flowdb)'"
-$dump -h x1-slave --databases $($my "$sql" | tr '\n' ' ') | \
+$dump_slave -h x1-slave --databases $($my "$sql" | tr '\n' ' ') | \
pigz > /srv/backups/x1-$(date +%Y%m%d%H%M%S).sql.gz
--
To view, visit https://gerrit.wikimedia.org/r/234503
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: merged
Gerrit-Change-Id: Ia87879f8d888efd10bd2cea771e75dd6d7b50e35
Gerrit-PatchSet: 9
Gerrit-Project: operations/puppet
Gerrit-Branch: production
Gerrit-Owner: Jcrespo <[email protected]>
Gerrit-Reviewer: Alexandros Kosiaris <[email protected]>
Gerrit-Reviewer: Jcrespo <[email protected]>
Gerrit-Reviewer: jenkins-bot <>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
