Skip to content

Instantly share code, notes, and snippets.

@sebastian13

sebastian13/docker-zabbix-mysql-dump.sh

Last active August 28, 2023 21:09
Show Gist options
  • Save sebastian13/2bd018d47f5e5c4bca1f58055b9f47c9 to your computer and use it in GitHub Desktop.
Save sebastian13/2bd018d47f5e5c4bca1f58055b9f47c9 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
readme.md

Download

curl -JO https://gist.github.com/sebastian13/2bd018d47f5e5c4bca1f58055b9f47c9/raw/docker-zabbix-mysql-dump.sh
curl -JO https://gist.github.com/sebastian13/2bd018d47f5e5c4bca1f58055b9f47c9/raw/docker-zabbix-mysql-restore.sh
chmod +x docker-zabbix-mysql-*.sh

Example: .env

MYSQL_DATABASE=example
MYSQL_ROOT_PASSWORD=123456

Example: docker-compose.yml

services:
  mysql:
    image: mariadb
    ...
    env_file: .env
    
  other:
    ...
    environment:
      - WORDPRESS_DB_NAME=${MYSQL_DATABASE}
      - WORDPRESS_DB_PASSWORD=${MYSQL_ROOT_PASSWORD}

Acknowledgements

Raw
docker-zabbix-mysql-dump.sh
#!/bin/bash
# Change to the script's directory & create directory
cd $(dirname "$(readlink -f "$0")")
mkdir -p ./dbdumps
# Start mysql service
docker-compose --log-level ERROR up -d mysql
# Wait
i=20
while (( i >= 1 )); do
sleep 10
echo -ne
echo -ne "Wait for DB to initialize. Creating Dump in $(( i-- )) seconds ... "'\r'
done
# Load database name + root password
source .env
# Delete old Backups
find ./dbdumps/* -atime +7 -exec rm {} \;
# Dump Table Definitions
docker-compose exec -T mysql mysqldump -u root --password=$MYSQL_ROOT_PASSWORD --no-data $MYSQL_DATABASE > ./dbdumps/`date +\%Y\%m\%d-\%H\%M`-$MYSQL_DATABASE-defs.sql
## Dump Content
docker-compose exec -T mysql /usr/bin/mysqldump -u root \
--password=$MYSQL_ROOT_PASSWORD \
--no-create-info \
--ignore-table=zabbix.acknowledges \
--ignore-table=zabbix.alerts \
--ignore-table=zabbix.auditlog \
--ignore-table=zabbix.auditlog_details \
--ignore-table=zabbix.event_recovery \
--ignore-table=zabbix.event_suppress \
--ignore-table=zabbix.event_tag \
--ignore-table=zabbix.events \
--ignore-table=zabbix.history \
--ignore-table=zabbix.history_log \
--ignore-table=zabbix.history_str \
--ignore-table=zabbix.history_str_sync \
--ignore-table=zabbix.history_sync \
--ignore-table=zabbix.history_text \
--ignore-table=zabbix.history_uint \
--ignore-table=zabbix.history_uint_sync \
--ignore-table=zabbix.item_rtdata \
--ignore-table=zabbix.problem \
--ignore-table=zabbix.problem_tag \
--ignore-table=zabbix.task \
--ignore-table=zabbix.task_acknowledge \
--ignore-table=zabbix.task_check_now \
--ignore-table=zabbix.task_close_problem \
--ignore-table=zabbix.task_remote_command \
--ignore-table=zabbix.task_remote_command_result \
--ignore-table=zabbix.trends \
--ignore-table=zabbix.trends_uint \
$MYSQL_DATABASE \
| gzip --rsyncable > ./dbdumps/`date +\%Y\%m\%d-\%H\%M`-$MYSQL_DATABASE.sql.gz
Raw
docker-zabbix-mysql-restore.sh
#!/bin/bash
# Check package availability
command -v pv >/dev/null 2>&1 || { echo "[Error] Please install pv"; exit 1; }
# Start mysql service
docker-compose --log-level ERROR up -d mysql
# Load database name + root password
source .env
# Find latest definition
latestd1=$(ls -1t ./dbdumps/*-defs.sql | head -1)
latestd2=$(ls -1t ./dbdumps/*-defs.sql | head -n2 | tail -n1)
latestd3=$(ls -1t ./dbdumps/*-defs.sql | head -n3 | tail -n1)
# Select dump
echo "Which content dump should be used for restoring?"
select resultd in $latestd1 $latestd2 $latestd3
do
[ $resultd ] && break
done
# Find latest content
latest1=$(ls -1t ./dbdumps/*.sql.gz | head -1)
latest2=$(ls -1t ./dbdumps/*.sql.gz | head -n2 | tail -n1)
latest3=$(ls -1t ./dbdumps/*.sql.gz | head -n3 | tail -n1)
# Select dump
echo "Which content dump should be used for restoring?"
select result in $latest1 $latest2 $latest3
do
[ $result ] && break
done
# Get the size of the uncompressed sql file
size=$(gzip -l $result | awk 'FNR==2{print $2}')
# Wait
while ! (docker-compose exec mysql /usr/bin/mysqladmin -u root --password=${MYSQL_ROOT_PASSWORD} ping --silent)
do
sleep 3
echo "Wait for DB to initialize"
done
# Restore Defs
cat $resultd | docker-compose exec -T mysql \
/usr/bin/mysql -u root --password=${MYSQL_ROOT_PASSWORD} ${MYSQL_DATABASE}
# Restore
echo "Starting restore now."
gunzip --keep --stdout $result | pv --size $size | docker-compose exec -T mysql \
/usr/bin/mysql -u root --password=${MYSQL_ROOT_PASSWORD} ${MYSQL_DATABASE}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment