Ottomata has submitted this change and it was merged. (
https://gerrit.wikimedia.org/r/345646 )
Change subject: Improvements to eventlogging_sync.sh script
......................................................................
Improvements to eventlogging_sync.sh script
- Default to replicating by id if the table has an auto increment id field,
otherwise timestamp will be used.
- Sane CLI options
- -D <cutoff-days> option to not even compare tables where there is
no recent data in master
- -n options for dry run - allows for testing of script without actually
modifying anything
- -d for master database name
- -s for slave database name
TODO: bring back purging? Not sure.
Bug: T124307
Change-Id: Ie96e872ebb763ce8398adcedc52a93ff9f16702a
---
M modules/role/files/mariadb/eventlogging_sync.sh
M modules/role/manifests/mariadb.pp
R modules/role/templates/mariadb/eventlogging_sync.init.erb
3 files changed, 157 insertions(+), 48 deletions(-)
Approvals:
Ottomata: Verified; Looks good to me, approved
diff --git a/modules/role/files/mariadb/eventlogging_sync.sh
b/modules/role/files/mariadb/eventlogging_sync.sh
index 339e026..6dda100 100755
--- a/modules/role/files/mariadb/eventlogging_sync.sh
+++ b/modules/role/files/mariadb/eventlogging_sync.sh
@@ -5,71 +5,168 @@
# does not currently take advantage of bulk insert batching which often leads
to
# replication lag. Adding DELETE into the mix makes it extra painful.
#
-# This script sychronizes a slave using bulk inserts with mysqldump where id >
N, and
-# purges old data with throttled bulk deletes where id < N order by id limit M.
-# Useful for creating a new slave, syncing a broken one, or replacing
replication
+# This script sychronizes a slave using bulk inserts with mysqldump where id
(or timestamp, if no
+# id field exists) > N, and purges old data with throttled bulk deletes where
id < N order by id
+# limit M. Useful for creating a new slave, syncing a broken one, or
replacing replication
# with a cron job.
-while true; do
-## execute endlessly in an infinite loop
+script_name=$(basename $0)
-db='log'
-ls="regexp '^[A-Z0-9].*[0-9]+$'"
-mhost='m4-master.eqiad.wmnet'
-shost="localhost"
+function usage {
+ echo "
+${script_name} [-n] [-b <batch-size>] [-D <cutoff-days>] [-d <database>] [-s
<slave-database>] <master-host> [<slave-host>]
-slave="mysql -h $shost --compress --skip-column-names --skip-ssl"
-master="mysql -h $mhost --compress --skip-column-names --skip-ssl"
-dump="mysqldump --skip-ssl -h $mhost --skip-opt --single-transaction --quick
--skip-triggers"
-dumpdata="$dump --no-create-info --insert-ignore --extended-insert --compress
--hex-blob"
-querytables="select table_name from information_schema.tables where
table_schema = '$db' and table_name"
+OPTIONS:
+ -h Print this usage message
+ -n Dry run. Only print the dump command that will be run, don't actually
dump from master into slave.
+ -b Batch replicate this many rows at a time. Default: 1000
+ -d Database name. Default: log
+ -s Slave database name. Defaults to -d if not given.
+ -D Don't replicate tables if they don't have events more recent than than
-D days ago.
+
+DESCRIPTION:
+ Checks all eventlogging tables on <master-host>, and looks for those tables
in <slave-host>
+ If <master-host> has any records with a larger auto increment \`id\` (or
\`timestamp\`) than
+ <slave-host>, then those records are mysqldumped into <slave-host>
<batch-size> records at a time.
+
+ <slave-host> defaults to localhost.
+"
+
+exit 0
+}
+
# select this many rows per table at a time
batch_size=1000
+database=log
+slave_host=localhost
+dry_run=0
+cutoff_days=0
-script=$(basename ${BASH_SOURCE})
-# Multi-execution is controlled by init.d
-#if [ $(ps ax | grep $script | grep -v grep | wc -l) -gt 2 ]; then
-# echo "duplicate process" 1>&2
-# exit 1
-#fi
+while getopts "hnvb:d:s:D:" opt; do
+ case "$opt" in
+ h)
+ usage
+ ;;
+ b) batch_size=$OPTARG
+ ;;
+ d)
+ database=$OPTARG
+ ;;
+ s)
+ slave_database=$OPTARG
+ ;;
+ D)
+ cutoff_days=$OPTARG
+ ;;
+ n)
+ dry_run=1
+ ;;
+ esac
+done
+
+# If slave_database was not given, default to using database.
+slave_database=${slave_database:-database}
+
+shift $((OPTIND-1))
+
+
+master_host="${1}"
+[ -z "${master_host}" ] && echo "ERROR: Must specify <master-host>" && usage
+shift
+
+# Last arg will be slave_host if given, otherwise use localhost.
+[ -n "${1}" ] && slave_host="${1}"
+
+
+
+table_regex="regexp '^[A-Z0-9].*[0-9]+$'"
+
+slave="mysql -h $slave_host --compress --skip-column-names --skip-ssl"
+master="mysql -h $master_host --compress --skip-column-names --skip-ssl"
+
+dump_opts="--skip-ssl --skip-opt --single-transaction --quick --skip-triggers"
+dump_schema="mysqldump -h $master_host $dump_opts --no-data"
+dump_data="mysqldump -h $master_host $dump_opts --no-create-info
--insert-ignore --extended-insert --compress --hex-blob"
+
+master_tables_query="select table_name from information_schema.tables where
table_schema = '$database'"
+slave_tables_query="select table_name from information_schema.tables where
table_schema = '$slave_database'"
+
+# Add and table_name = $table at the end of this query to use it
+has_id_column_query="select count(*) from information_schema.columns where
table_schema = '$database' and column_name = 'id'"
set -e
-for table in $($master $db -e "$querytables $ls"); do
+echo "Syncing EventLogging records in $master_host $database -> $slave_host
$slave_database..."
+# Execute endlessly in an infinite loop.
+while true; do
- echo -n "`date` $shost $table"
+ for table in $($master -e "$master_tables_query and table_name
$table_regex order by rand()"); do
- if [ $($slave $db -e "$querytables = '$table'" | wc -l) -eq 0 ]; then
- echo -n ", create"
- $dump --no-data $db $table | $slave $db
- fi
+ echo -n "$(date +"%Y-%m-%dT%H:%M:%S") $slave_host $slave_database
$table"
- #id=$($master $db -e "select min(id) from \`$table\`")
+ # If no new events for this table since cutoff_days ago, don't attempt
to replicate.
+ if [ $cutoff_days -ne 0 ]; then
+ cutoff_timestamp=$(date --date="$cutoff_days days ago"
+'%Y%m%d%H%M%S')
- #if [ ! $id = "NULL" ]; then
- #echo -n ", purge < $id"
- #$slave $db -e "delete from \`$table\` where id < $id order by id
limit 100000"
- #fi
+ max_master_timestamp=$($master $database -e "select max(timestamp)
from \`$table\` where timestamp >= '$cutoff_timestamp'")
+ if [ "${max_master_timestamp}" = "NULL" ]; then
+ echo " (no new data on master in last $cutoff_days days,
skipping)"
+ continue
+ fi
+ fi
- ts=$($slave $db -e "select ifnull(max(timestamp),0) from \`$table\`")
+ # If the table does not exist on the slave,
+ # then dump the schema from the master to create it.
+ if [ $($slave -e "$slave_tables_query and table_name = '$table'" | wc
-l) -eq 0 ]; then
+ echo -n ", create"
+ if [ $dry_run -eq 1 ]; then
+ echo -n " (dry-run) $dump_schema $database $table | $slave
$slave_database"
+ else
+ $dump_schema $database $table | $slave $slave_database
+ fi
+ fi
- echo -n " >= $ts"
- # mysqldump has overhead with information_schema queries, so do a quick
check for a noop
- if [ ! $($master $db -e "select ifnull(max(timestamp),0) from \`$table\`")
= $ts ]; then
- echo -n " (rows!)"
- $dumpdata --insert-ignore --where="timestamp >= '$ts' ORDER BY
timestamp LIMIT $batch_size" $db "$table" | $slave $db
- #$dumpdata --insert-ignore --where="timestamp >= '$ts'" $db "$table"
>tmp/$table.sql
- else
- echo -n " (nothing)"
- fi
+ # # Get the minimum autoincrement id from master table
+ # TODO: should we bring this back???
+ # id=$($master $database -e "select min(id) from \`$table\`")
+ #
+ # if [ ! $id = "NULL" ]; then
+ # echo -n ", purge < $id"
+ # $slave $db -e "delete from \`$table\` where id < $id order by id
limit 100000"
+ # fi
- echo " ok"
+ # replicate by timestamp or id. id is preferred.
+ column='id'
+ # If this table does not have an auto-increment id field, then use
timestamp instead.
+ if [ $($master -e "$has_id_column_query and table_name = '$table'")
-eq 0 ]; then
+ column='timestamp'
+ fi
-done
+ # Select records from the master where $column > max($column) on the
slave,
+ # and dump them into the slave.
+ max_master=$($master $database -e "select ifnull(max($column),0) from
\`$table\`")
+ max_slave=$($slave $slave_database -e "select ifnull(max($column),0)
from \`$table\`")
-#echo "Sleeping for 10 seconds before the next batch..."
-#sleep 10
-## infinite loop
+ # If no new data on the master, do nothing.
+ if [ $max_slave = $max_master ]; then
+ echo -n " (nothing)"
+ # Else dump $batch_size records from master into the slave.
+ else
+ echo -n " (rows!)"
+ if [ $dry_run -eq 1 ]; then
+ echo -n " (dry-run) $dump_data --where=\"$column >= $max_slave
ORDER BY $column LIMIT $batch_size\" $database \"$table\" | $slave
$slave_database"
+ else
+ $dump_data --where="id >= $max_slave ORDER BY id LIMIT
$batch_size" $database "$table" | $slave $slave_database
+ fi
+ fi
+
+ echo " ok"
+ done
+
+ echo "Sleeping for 5 seconds before the next batch..."
+ sleep 5
+
+# End infinite loop.
done
diff --git a/modules/role/manifests/mariadb.pp
b/modules/role/manifests/mariadb.pp
index f16fb9b..7e3870c 100644
--- a/modules/role/manifests/mariadb.pp
+++ b/modules/role/manifests/mariadb.pp
@@ -196,6 +196,18 @@
}
class role::mariadb::analytics::custom_repl_slave {
+ # Sync eventlogging tables from m4-master.eqiad.wmnet to localhost
+ # using a custom bash 'replication' script, that looks for new records on
'master',
+ # and inserts into 'slave'.
+ $master_host = 'm4-master.eqiad.wmnet'
+ $slave_host = 'localhost'
+ $database = 'log'
+
+ # Don't try to 'replicate' tables with no events more recent than this
many days ago.
+ $cutoff_days = 90
+
+ # Only 'replicate' this many rows at a time.
+ $batch_size = 1000
file { '/usr/local/bin/eventlogging_sync.sh':
ensure => present,
@@ -208,7 +220,7 @@
owner => 'root',
group => 'root',
mode => '0755',
- source => 'puppet:///modules/role/mariadb/eventlogging_sync.init',
+ content => template('role/mariadb/eventlogging_sync.init.erb'),
require => File['/usr/local/bin/eventlogging_sync.sh'],
notify => Service['eventlogging_sync'],
}
diff --git a/modules/role/files/mariadb/eventlogging_sync.init
b/modules/role/templates/mariadb/eventlogging_sync.init.erb
old mode 100644
new mode 100755
similarity index 92%
rename from modules/role/files/mariadb/eventlogging_sync.init
rename to modules/role/templates/mariadb/eventlogging_sync.init.erb
index 437a0a4..cbecba4
--- a/modules/role/files/mariadb/eventlogging_sync.init
+++ b/modules/role/templates/mariadb/eventlogging_sync.init.erb
@@ -10,7 +10,7 @@
### END INIT INFO
dir="/usr/local"
-cmd="/usr/local/bin/eventlogging_sync.sh"
+cmd="/usr/local/bin/eventlogging_sync.sh -D <%= @cutoff_days %> -b <%=
@batch_size %> -d <%= @database %> <%= @master_host %> <%= @slave_host %>"
user="root"
name=`basename $0`
--
To view, visit https://gerrit.wikimedia.org/r/345646
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: merged
Gerrit-Change-Id: Ie96e872ebb763ce8398adcedc52a93ff9f16702a
Gerrit-PatchSet: 4
Gerrit-Project: operations/puppet
Gerrit-Branch: production
Gerrit-Owner: Ottomata <[email protected]>
Gerrit-Reviewer: Giuseppe Lavagetto <[email protected]>
Gerrit-Reviewer: Jcrespo <[email protected]>
Gerrit-Reviewer: Marostegui <[email protected]>
Gerrit-Reviewer: Mforns <[email protected]>
Gerrit-Reviewer: Nuria <[email protected]>
Gerrit-Reviewer: Ottomata <[email protected]>
Gerrit-Reviewer: jenkins-bot <>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
