Hi there,
Currently using mysql innodb cluster
every night , the cronjob for restic backup for DB will run as below script :
backupDb() {
tag="db-pn-schema-$sysdate"
echo "Backing up $PNDB schema" >> $RESTIC_LOG
mysqldump -u$MYSQL_USER -p$MYSQL_PASSWORD --set-gtid-purged=OFF --no-data --routines --triggers --single-transaction --databases $PNDB > $DB_BASE_DIR/$PNDB-schema.sql
echo "SFTP..." >> $RESTIC_LOG
$RESTIC_PATH -r $RESTIC_DB_DATA_REPOSITORY backup --tag $tag --verbose $DB_BASE_DIR/$PNDB-schema.sql >> $RESTIC_LOG
tag="db-shp-schema-$sysdate"
echo "Backing up $SHPDB schema" >> $RESTIC\_LOG
mysqldump -u$MYSQL\_USER -p$MYSQL\_PASSWORD --set-gtid-purged=OFF --no-data --routines --triggers --single-transaction --databases $SHPDB > $DB\_BASE\_DIR/$SHPDB-schema.sql
echo "SFTP..." >> $RESTIC\_LOG
$RESTIC\_PATH -r $RESTIC\_DB\_DATA\_REPOSITORY backup --tag $tag --verbose $DB\_BASE\_DIR/$SHPDB-schema.sql >> $RESTIC\_LOG
tag="db-pn-$sysdate"
echo "Backup $PNDB" >> $RESTIC\_LOG
mysql -u$MYSQL\_USER -p$MYSQL\_PASSWORD $PNDB\<\<EOF >> $RESTIC\_LOG
USE $PNDB;
FLUSH TABLES $PN_TABLELIST FOR EXPORT;
system $RESTIC_PATH -r $RESTIC_DB_DATA_REPOSITORY backup --tag $tag --verbose $DB_PATH_PN
UNLOCK TABLES;
exit
EOF
tag="db-shp-$sysdate"
echo "Backup $SHPDB" >> $RESTIC\_LOG
mysql -u$MYSQL\_USER -p$MYSQL\_PASSWORD $SHPDB\<\<EOF >> $RESTIC\_LOG
FLUSH TABLES $SHP_TABLELIST FOR EXPORT;
system $RESTIC_PATH -r $RESTIC_DB_DATA_REPOSITORY backup --tag $tag --verbose $DB_PATH_SHP
UNLOCK TABLES;
exit
EOF
if [ $? -eq 0 ]; then
echo "BACKUP Successful - $sysdate" >> $RESTIC_LOG
else
echo "BACKUP Failed! - $sysdate" >> $RESTIC_LOG
fi
}
this metadata lock not happening everyday, but only happening twice a week which will cause a replication lagging issue when metadata lock still in the processlist more than 5 hours
in order to recover from the replication lagging issue, i have to kill the process.
flush tables for export makes the system lock hanging. i did tried issue the unlock tables; command manually but still no luck
please help to overcome this system lock hanging from occurring again