Last active
August 28, 2023 21:09
-
-
Save sebastian13/2bd018d47f5e5c4bca1f58055b9f47c9 to your computer and use it in GitHub Desktop.
Revisions
-
sebastian13 revised this gist
Aug 28, 2023 . 2 changed files with 3 additions and 20 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -27,32 +27,14 @@ docker compose exec -T mysql mysqldump -u root --password=$MYSQL_ROOT_PASSWORD - ## Dump Content docker compose exec -T mysql /usr/bin/mysqldump -u root \ --password=$MYSQL_ROOT_PASSWORD \ --single-transaction \ --no-create-info \ --ignore-table=zabbix.events \ --ignore-table=zabbix.history \ --ignore-table=zabbix.history_log \ --ignore-table=zabbix.history_str \ --ignore-table=zabbix.history_text \ --ignore-table=zabbix.history_uint \ --ignore-table=zabbix.trends \ --ignore-table=zabbix.trends_uint \ $MYSQL_DATABASE \ This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -28,4 +28,5 @@ services: ### Acknowledgements * This omits tables listed in https://blog.zabbix.com/backups-to-the-rescue/23442/ * Another nice list of tables to exclude from backup: https://github.com/maxhq/zabbix-backup/blob/4a093d348a3ca07402c6482990a3a8e29a9b6a86/zabbix-dump#L607 -
Jul 28, 2023 . No changes.There are no files selected for viewing
-
Jul 28, 2023 . 2 changed files with 2 additions and 2 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -5,7 +5,7 @@ cd $(dirname "$(readlink -f "$0")") mkdir -p ./dbdumps # Start mysql service docker --log-level=error compose up -d mysql # Wait i=20 This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -4,7 +4,7 @@ command -v pv >/dev/null 2>&1 || { echo "[Error] Please install pv"; exit 1; } # Start mysql service docker --log-level=error compose up -d mysql # Load database name + root password source .env -
Jul 24, 2023 . 2 changed files with 7 additions and 7 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -5,7 +5,7 @@ cd $(dirname "$(readlink -f "$0")") mkdir -p ./dbdumps # Start mysql service docker compose --log-level ERROR up -d mysql # Wait i=20 @@ -22,10 +22,10 @@ source .env 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 \ This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -4,7 +4,7 @@ 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 @@ -37,17 +37,17 @@ done 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 15 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} -
Aug 25, 2020 . 1 changed file with 1 addition and 1 deletion.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -39,7 +39,7 @@ 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 15 echo "Wait for DB to initialize" done -
Aug 25, 2020 . 2 changed files with 17 additions and 2 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -10,7 +10,7 @@ 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 @@ -32,7 +32,9 @@ docker-compose exec -T mysql /usr/bin/mysqldump -u root \ --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 \ @@ -42,6 +44,15 @@ docker-compose exec -T mysql /usr/bin/mysqldump -u root \ --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 \ This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -25,3 +25,7 @@ services: - WORDPRESS_DB_NAME=${MYSQL_DATABASE} - WORDPRESS_DB_PASSWORD=${MYSQL_ROOT_PASSWORD} ``` ### Acknowledgements * A nice list of tables to exclude from backup: https://github.com/maxhq/zabbix-backup/blob/4a093d348a3ca07402c6482990a3a8e29a9b6a86/zabbix-dump#L607 -
Aug 20, 2020 . 1 changed file with 1 addition and 4 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -22,10 +22,7 @@ source .env 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 \ -
Aug 20, 2020 . 1 changed file with 1 addition and 1 deletion.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -2,7 +2,7 @@ ``` 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 -
Aug 20, 2020 . 1 changed file with 27 additions and 0 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,27 @@ ### 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-(dump|restore).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} ``` -
Aug 20, 2020 .There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,51 @@ #!/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 1 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 /usr/bin/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.escalations \ --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.trends \ --ignore-table=zabbix.trends_uint \ $MYSQL_DATABASE \ | gzip --rsyncable > ./dbdumps/`date +\%Y\%m\%d-\%H\%M`-$MYSQL_DATABASE.sql.gz This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,53 @@ #!/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}