[Hotfix] Rebuild LMX replication db01 > db02

24 May 2022

      Hi,

replication of LMX xRes main has been off track due to high load and needed to be rebuild.

Regards

Gunnar

Details to whom it may concern

Transfer der DB
Prepare both source and target <https://wiki.app.infra.gs.xtrav.de/doku.php?id=root:traso:entwicklung:mysql:replikation#prepare_both_source_and_target>
Install packages

if ! which netcat >/dev/null || nc -N 2>&1 | grep -q 'invalid option'  2>&1
then
    if which apt-get >/dev/null 2>&1; then
        apt-get update
        apt-get install -y zstd netcat-openbsd pv #  mariadb-backup
        ln -s /bin/nc.openbsd /usr/local/bin/netcat
    elif which yum >/dev/null 2>&1; then
        yum install -y netcat pv zstd \
        https://archive.mariadb.org/mariadb-10.3.17/yum/rhel/7.4/x86_64/rpms/MariaDB...
    else
        echo 'ERROR!'
    fi
fi
Bearbeiten
Target (slave) <https://wiki.app.infra.gs.xtrav.de/doku.php?id=root:traso:entwicklung:mysql:replikation#target_slave>
Start listener on slave, first. Progress shown is (compressed) network traffic incoming from source/master.

NETCAT_PORT=3306
IMPORT_DIR=/srv/mysql-import
MYSQL_DIR="$(mysql -Bse "SELECT @@datadir" 2>/dev/null )" || MYSQL_DIR=/srv/mysql
which firewall-cmd 2> /dev/null && firewall-cmd --add-port=$NETCAT_PORT/tcp
systemctl stop mariadb
mkdir -pv "$IMPORT_DIR" $MYSQL_DIR
[ -z "${MYSQL_DIR}" ] || rm -rvf "$MYSQL_DIR"/*
[ -z "${IMPORT_DIR}" ] || rm -rvf "$IMPORT_DIR"/*
netcat -l $NETCAT_PORT \
  | pv \
  | zstd -dcf \
  | mbstream --directory="$IMPORT_DIR" -x --parallel=$(nproc) \
&& time mariabackup --prepare --target-dir="$IMPORT_DIR" \
&& mariabackup --move-back --target-dir="$IMPORT_DIR" \
&& mv "$IMPORT_DIR/xtrabackup_binlog_info" "$MYSQL_DIR" \
&& rm -rf "$IMPORT_DIR" \
&& install -o mysql -g mysql -d "$MYSQL_DIR"/{,tmp,binlog} /var/{log,lib}/mysql \
&& chown -R mysql:mysql "$MYSQL_DIR" /var/{log,lib}/mysql \
&& systemctl start mariadb
Bearbeiten
Source (master) <https://wiki.app.infra.gs.xtrav.de/doku.php?id=root:traso:entwicklung:mysql:replikation#source_master>
Progress shown is uncompressed db size. –parallel=$(nproc) does not work out well on systems with a lot of cpu cores - too many connections. Parameter –kill-long-queries-timeout prevents long READ LOCK (RO-mode of whole databases) on db01, killing queries that have been running when establishing the READ LOCK.

Ankündigung: 5 Sekunden Read Lock, siehe oben
in tmux, open mytop -d mysql session before starting transfer so in case of „too many connections“ there is still an open root session
start transfer:
CU=lmx
SLAVE=db02.$CU.xres.rz1.xtrav.de
NETCAT_PORT=3306
MYSQL_DIR=$(mysql -Bse 'SELECT @@datadir')
db_size=$(LANG=C du -csm $MYSQL_DIR/*/*.{ibd,frm,MYD,MYI} $MYSQL_DIR/ibdata* | awk '/total$/{print $1}')
mariabackup --backup --stream=xbstream --parallel=2 --kill-long-queries-timeout=5 2> mariabackup.log \
  | pv -s ${db_size}m\
  | zstd -1 \
  | netcat -N $SLAVE $NETCAT_PORT \
&& tail mariabackup.log
Bearbeiten
Optional: Set up new replication <https://wiki.app.infra.gs.xtrav.de/doku.php?id=root:traso:entwicklung:mysql:replikation#optionalset_up_new_replication>
Bearbeiten
Master <https://wiki.app.infra.gs.xtrav.de/doku.php?id=root:traso:entwicklung:mysql:replikation#master>
Replication user name is derived from slave host name

CU=lmx
MASTER=db01.$CU.xres.rz1.xtrav.de
SLAVE=db02.$CU.xres.rz1.xtrav.de
replication_id=${SLAVE%%\.*} \
&& replication_user=replication_$replication_id \
&& replication_pass="$(tr -dc '[:alnum:]' < /dev/urandom | head -c 32 && echo)" \
&& mysql -Bse "
  DELETE FROM mysql.user WHERE user=\"$replication_user\";
  GRANT REPLICATION SLAVE ON *.* TO $replication_user@\"%\"
  IDENTIFIED BY \"$replication_pass\";
  FLUSH PRIVILEGES;
"
echo "RUN ON SLAVE:"
echo
cat << SLAVE_END
MYSQL_DIR="\$(mysql -Bse "SELECT @@datadir" 2>/dev/null )"
read LOG_FILE LOG_POS GTID < "\$MYSQL_DIR/xtrabackup_binlog_info"
if [ -z "\$LOG_POS" ] || [ -z "\$MYSQL_DIR" ]
then
    echo "ERROR - could not read from '\$MYSQL_DIR/xtrabackup_binlog_info'"
else
    mysql -e "
        STOP SLAVE;
        CHANGE MASTER TO
            MASTER_HOST='$MASTER',
            MASTER_PORT=3306,
            MASTER_USER='$replication_user',
            MASTER_PASSWORD='$replication_pass',
            MASTER_LOG_FILE='\$LOG_FILE',
            MASTER_LOG_POS=\$LOG_POS;
        START SLAVE;
    "
fi
sleep 2
mysql -Bse "SHOW SLAVE STATUS\G"
SLAVE_END
Bearbeiten
Slave <https://wiki.app.infra.gs.xtrav.de/doku.php?id=root:traso:entwicklung:mysql:replikation#slave>
Paste RUN ON SLAVE: output above and check replication state:

mysql -Bse "SHOW SLAVE STATUS\G"

Berechtigungen für lmx-tech (SLAVE)

mysql -e "
DROP USER IF EXISTS 'lmx-tech'@'10.21.4.%';
CREATE USER 'lmx-tech'@'10.21.4.%' IDENTIFIED BY 'xxxxxx';
GRANT USAGE ON *.* TO 'lmx-tech'@'10.21.4.%';
GRANT SELECT ON xres_lmx_bookings.* TO 'lmx-tech'@'10.21.4.%';
GRANT SELECT ON xres_lmx_flights.nurflug_rt TO 'lmx-tech'@'10.21.4.%';
GRANT SELECT ON xres_lmx_logs.log_b TO 'lmx-tech'@'10.21.4.%';
GRANT SELECT ON xres_lmx_hotels.calculation_fallback TO 'lmx-tech'@'10.21.4.%';
GRANT SELECT ON xres_lmx_hotels.roomtypes_matching TO 'lmx-tech'@'10.21.4.%';
GRANT SELECT ON xres_lmx_agencies.agentur TO 'lmx-tech'@'10.21.4.%';
GRANT SELECT ON xres_lmx_logs.flight_pricechange_handling TO 'lmx-tech'@'10.21.4.%';
GRANT SELECT ON xres_lmx_agencies.rsc TO 'lmx-tech'@'10.21.4.%';
GRANT SELECT ON xres_lmx_hotels.price_control TO 'lmx-tech'@'10.21.4.%';
GRANT SELECT ON xres_lmx_hotels.pauschal_calculation TO 'lmx-tech'@'10.21.4.%';
GRANT SELECT ON xres_lmx_logs.log_misc TO 'lmx-tech'@'10.21.4.%';
GRANT SELECT ON xres_lmx_logs.log_ba TO 'lmx-tech'@'10.21.4.%';
GRANT SELECT ON xres_lmx_hotels.boardtypes_matching TO 'lmx-tech'@'10.21.4.%';
GRANT SELECT ON xres_lmx_hotels.export_seasons TO 'lmx-tech'@'10.21.4.%';
GRANT SELECT ON xres_lmx_hotels.hotel_matching TO 'lmx-tech'@'10.21.4.%';
GRANT SELECT ON xres_lmx_flights.oneway_pool TO 'lmx-tech'@'10.21.4.%';
GRANT SELECT ON xres_lmx_logs.pricechange_handling TO 'lmx-tech'@'10.21.4.%';
FLUSH PRIVILEGES;
"

Test der Berechtigungen auf proxy.vpn.xres.rz1.xtrav.de <http://xtrav.de/>

mysql -h proxy.vpn.xres.rz1.xtrav.de  -u lmx-tech -p$(cat ~/pw) -e "show variables like 'server_id'"

--

Mit freundlichen Grüßen

Gunnar Mann

- Systemadministration -
________________________________________________________

TraSo GmbH

Nonnenstraße 42
D-04229 Leipzig

Tel.: +49 341 355 740 76
Fax: +49 341 355 740 21
E-Mail: g.mann@traso.de <mailto:g.mann@traso.de>

 <https://www.traso.de/> <https://www.facebook.com/TraSoGmbH> <https://www.xing.com/companies/trasogmbh>
________________________________________________________
Geschäftsführer: Haiko Gerdes
Handelsregister: Amtsgericht Leipzig, HRB 21850 <https://www.kununu.com/de/traso1>
________________________________________________________
Geschäftsführer: Haiko Gerdes
Handelsregister: Amtsgericht Leipzig, HRB 21850
 <https://www.kununu.com/de/traso1>