Skip to content

Instantly share code, notes, and snippets.

@sebastian13

sebastian13/docker-mysql-dump.sh

Last active July 28, 2023 20:42
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save sebastian13/cc58304f7b177ac1d16d7671dc67efb9 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save sebastian13/cc58304f7b177ac1d16d7671dc67efb9 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. sebastian13 revised this gist Jul 28, 2023. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion docker-mysql-restore.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -14,7 +14,7 @@ command -v pv >/dev/null 2>&1 || { echo "[Error] Please install pv"; exit 1; }
    command -v awk >/dev/null 2>&1 || { echo "[Error] Please install awk"; exit 1; }

    # Start mysql service
    docker compose --log-level ERROR up -d mysql
    docker --log-level=error compose up -d mysql

    # Find latest dumps
    latest1=$(ls -1t ./dbdumps/*.sql.gz | head -1)
  2. sebastian13 revised this gist Jul 24, 2023. 3 changed files with 9 additions and 9 deletions.
    6 changes: 3 additions & 3 deletions docker-mysql-dump.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -12,15 +12,15 @@ if [ -z ${MYSQL_DATABASE+x} ]; then echo \$MYSQL_DATABASE was not provided; exit
    if [ -z ${MYSQL_ROOT_PASSWORD+x} ]; then echo \$MYSQL_ROOT_PASSWORD was not provided; exit 1; fi

    # Check package availability
    command -v docker-compose >/dev/null 2>&1 || { echo "[Error] Please install docker-compose"; exit 1; }
    command -v docker compose >/dev/null 2>&1 || { echo "[Error] Please install the Compose plugin"; exit 1; }
    command -v gzip >/dev/null 2>&1 || { echo "[Error] Please install gzip"; exit 1; }

    # Check if mysql is running
    docker-compose ps | grep -q 'mysql.*Up' || { echo "[Error] mysql service not running. Please start manually."; exit 1; }
    docker compose ps | grep -q 'mysql.*Up' || { echo "[Error] mysql service not running. Please start manually."; exit 1; }

    # Delete old Backups
    find ./dbdumps/* -atime +7 -exec rm {} \;

    docker-compose exec -T mysql /usr/bin/mysqldump -u root \
    docker compose exec -T mysql /usr/bin/mysqldump -u root \
    --password=$MYSQL_ROOT_PASSWORD $MYSQL_DATABASE \
    | gzip --rsyncable > ./dbdumps/`date +\%Y\%m\%d-\%H\%M`-$MYSQL_DATABASE.sql.gz
    8 changes: 4 additions & 4 deletions docker-mysql-restore.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -8,13 +8,13 @@ if [ -z ${MYSQL_DATABASE+x} ]; then echo \$MYSQL_DATABASE was not provided; exit
    if [ -z ${MYSQL_ROOT_PASSWORD+x} ]; then echo \$MYSQL_ROOT_PASSWORD was not provided; exit 1; fi

    # Check package availability
    command -v docker-compose >/dev/null 2>&1 || { echo "[Error] Please install docker-compose"; exit 1; }
    command -v docker compose >/dev/null 2>&1 || { echo "[Error] Please install the Compose plugin"; exit 1; }
    command -v gzip >/dev/null 2>&1 || { echo "[Error] Please install gzip"; exit 1; }
    command -v pv >/dev/null 2>&1 || { echo "[Error] Please install pv"; exit 1; }
    command -v awk >/dev/null 2>&1 || { echo "[Error] Please install awk"; exit 1; }

    # Start mysql service
    docker-compose --log-level ERROR up -d mysql
    docker compose --log-level ERROR up -d mysql

    # Find latest dumps
    latest1=$(ls -1t ./dbdumps/*.sql.gz | head -1)
    @@ -32,13 +32,13 @@ 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)
    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
    echo "Starting restore now."
    gunzip --keep --stdout $result | pv --size $size | docker-compose exec -T mysql \
    gunzip --keep --stdout $result | pv --size $size | docker compose exec -T mysql \
    /usr/bin/mysql -u root --password=${MYSQL_ROOT_PASSWORD} ${MYSQL_DATABASE}
    4 changes: 2 additions & 2 deletions restore-help.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,3 @@
    # ERROR 1118 (42000) Row size too large
    docker-compose exec mysql /usr/bin/mysql -u root --password=${MYSQL_ROOT_PASSWORD} -e "SET GLOBAL innodb_strict_mode = 0"
    docker-compose exec mysql /usr/bin/mysql -u root --password=${MYSQL_ROOT_PASSWORD} -e "show variables like '%innodb_strict_mode%' "
    docker compose exec mysql /usr/bin/mysql -u root --password=${MYSQL_ROOT_PASSWORD} -e "SET GLOBAL innodb_strict_mode = 0"
    docker compose exec mysql /usr/bin/mysql -u root --password=${MYSQL_ROOT_PASSWORD} -e "show variables like '%innodb_strict_mode%' "
  3. sebastian13 revised this gist Nov 29, 2021. 1 changed file with 4 additions and 4 deletions.
    8 changes: 4 additions & 4 deletions docker-mysql-dump.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,9 @@
    #!/bin/bash

    # Change to the script's directory & create directory
    cd $(dirname "$(readlink -f "$0")")
    mkdir -p ./dbdumps

    # Load database name + root password
    source .env

    @@ -11,10 +15,6 @@ if [ -z ${MYSQL_ROOT_PASSWORD+x} ]; then echo \$MYSQL_ROOT_PASSWORD was not prov
    command -v docker-compose >/dev/null 2>&1 || { echo "[Error] Please install docker-compose"; exit 1; }
    command -v gzip >/dev/null 2>&1 || { echo "[Error] Please install gzip"; exit 1; }

    # Change to the script's directory & create directory
    cd $(dirname "$(readlink -f "$0")")
    mkdir -p ./dbdumps

    # Check if mysql is running
    docker-compose ps | grep -q 'mysql.*Up' || { echo "[Error] mysql service not running. Please start manually."; exit 1; }

  4. sebastian13 revised this gist Nov 28, 2021. 2 changed files with 6 additions and 6 deletions.
    6 changes: 3 additions & 3 deletions docker-mysql-dump.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,8 @@
    #!/bin/bash

    # Load database name + root password
    source .env

    # Check if variables were provided
    if [ -z ${MYSQL_DATABASE+x} ]; then echo \$MYSQL_DATABASE was not provided; exit 1; fi
    if [ -z ${MYSQL_ROOT_PASSWORD+x} ]; then echo \$MYSQL_ROOT_PASSWORD was not provided; exit 1; fi
    @@ -15,9 +18,6 @@ mkdir -p ./dbdumps
    # Check if mysql is running
    docker-compose ps | grep -q 'mysql.*Up' || { echo "[Error] mysql service not running. Please start manually."; exit 1; }

    # Load database name + root password
    source .env

    # Delete old Backups
    find ./dbdumps/* -atime +7 -exec rm {} \;

    6 changes: 3 additions & 3 deletions docker-mysql-restore.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,8 @@
    #!/bin/bash

    # Load database name + root password
    source .env

    # Check if variables were provided
    if [ -z ${MYSQL_DATABASE+x} ]; then echo \$MYSQL_DATABASE was not provided; exit 1; fi
    if [ -z ${MYSQL_ROOT_PASSWORD+x} ]; then echo \$MYSQL_ROOT_PASSWORD was not provided; exit 1; fi
    @@ -13,9 +16,6 @@ command -v awk >/dev/null 2>&1 || { echo "[Error] Please install awk"; exit 1; }
    # Start mysql service
    docker-compose --log-level ERROR up -d mysql

    # Load database name + root password
    source .env

    # Find latest dumps
    latest1=$(ls -1t ./dbdumps/*.sql.gz | head -1)
    latest2=$(ls -1t ./dbdumps/*.sql.gz | head -n2 | tail -n1)
  5. sebastian13 revised this gist Nov 28, 2021. 2 changed files with 10 additions and 2 deletions.
    6 changes: 5 additions & 1 deletion docker-mysql-dump.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,10 @@
    #!/bin/bash

    # Check package availability
    # Check if variables were provided
    if [ -z ${MYSQL_DATABASE+x} ]; then echo \$MYSQL_DATABASE was not provided; exit 1; fi
    if [ -z ${MYSQL_ROOT_PASSWORD+x} ]; then echo \$MYSQL_ROOT_PASSWORD was not provided; exit 1; fi

    # Check package availability
    command -v docker-compose >/dev/null 2>&1 || { echo "[Error] Please install docker-compose"; exit 1; }
    command -v gzip >/dev/null 2>&1 || { echo "[Error] Please install gzip"; exit 1; }

    6 changes: 5 additions & 1 deletion docker-mysql-restore.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,10 @@
    #!/bin/bash

    # Check package availability
    # Check if variables were provided
    if [ -z ${MYSQL_DATABASE+x} ]; then echo \$MYSQL_DATABASE was not provided; exit 1; fi
    if [ -z ${MYSQL_ROOT_PASSWORD+x} ]; then echo \$MYSQL_ROOT_PASSWORD was not provided; exit 1; fi

    # Check package availability
    command -v docker-compose >/dev/null 2>&1 || { echo "[Error] Please install docker-compose"; exit 1; }
    command -v gzip >/dev/null 2>&1 || { echo "[Error] Please install gzip"; exit 1; }
    command -v pv >/dev/null 2>&1 || { echo "[Error] Please install pv"; exit 1; }
  6. sebastian13 revised this gist Oct 19, 2021. 1 changed file with 2 additions and 9 deletions.
    11 changes: 2 additions & 9 deletions docker-mysql-dump.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -8,15 +8,8 @@ command -v gzip >/dev/null 2>&1 || { echo "[Error] Please install gzip"; exit 1;
    cd $(dirname "$(readlink -f "$0")")
    mkdir -p ./dbdumps

    # Start mysql service
    docker-compose --log-level ERROR up -d mysql

    # 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
    # Check if mysql is running
    docker-compose ps | grep -q 'mysql.*Up' || { echo "[Error] mysql service not running. Please start manually."; exit 1; }

    # Load database name + root password
    source .env
  7. sebastian13 revised this gist Dec 23, 2020. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion docker-mysql-restore.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -4,7 +4,7 @@
    command -v docker-compose >/dev/null 2>&1 || { echo "[Error] Please install docker-compose"; exit 1; }
    command -v gzip >/dev/null 2>&1 || { echo "[Error] Please install gzip"; exit 1; }
    command -v pv >/dev/null 2>&1 || { echo "[Error] Please install pv"; exit 1; }
    command -v awl >/dev/null 2>&1 || { echo "[Error] Please install awk"; exit 1; }
    command -v awk >/dev/null 2>&1 || { echo "[Error] Please install awk"; exit 1; }

    # Start mysql service
    docker-compose --log-level ERROR up -d mysql
  8. sebastian13 revised this gist Dec 23, 2020. 2 changed files with 14 additions and 5 deletions.
    13 changes: 8 additions & 5 deletions docker-mysql-dump.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,9 @@
    #!/bin/bash

    # Check package availability
    command -v docker-compose >/dev/null 2>&1 || { echo "[Error] Please install docker-compose"; exit 1; }
    command -v gzip >/dev/null 2>&1 || { echo "[Error] Please install gzip"; exit 1; }

    # Change to the script's directory & create directory
    cd $(dirname "$(readlink -f "$0")")
    mkdir -p ./dbdumps
    @@ -8,11 +12,10 @@ mkdir -p ./dbdumps
    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'
    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

    # Load database name + root password
    6 changes: 6 additions & 0 deletions docker-mysql-restore.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,11 @@
    #!/bin/bash

    # Check package availability
    command -v docker-compose >/dev/null 2>&1 || { echo "[Error] Please install docker-compose"; exit 1; }
    command -v gzip >/dev/null 2>&1 || { echo "[Error] Please install gzip"; exit 1; }
    command -v pv >/dev/null 2>&1 || { echo "[Error] Please install pv"; exit 1; }
    command -v awl >/dev/null 2>&1 || { echo "[Error] Please install awk"; exit 1; }

    # Start mysql service
    docker-compose --log-level ERROR up -d mysql

  9. sebastian13 revised this gist Aug 20, 2020. 2 changed files with 0 additions and 47 deletions.
    43 changes: 0 additions & 43 deletions docker-zabbix-mysql-dump.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,43 +0,0 @@
    #!/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 {} \;

    docker-compose exec -T mysql /usr/bin/mysqldump -u root \
    --password=$MYSQL_ROOT_PASSWORD \
    --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`-$MYSQL_DATABASE.sql.gz
    4 changes: 0 additions & 4 deletions readme.md
    View file
    Original file line number Diff line number Diff line change
    @@ -3,10 +3,6 @@
    curl -JO https://gist.githubusercontent.com/sebastian13/cc58304f7b177ac1d16d7671dc67efb9/raw/docker-mysql-dump.sh
    curl -JO https://gist.githubusercontent.com/sebastian13/cc58304f7b177ac1d16d7671dc67efb9/raw/docker-mysql-restore.sh
    chmod +x docker-mysql-(dump|restore).sh
    # for zabbix database dump run
    curl -JO https://gist.githubusercontent.com/sebastian13/cc58304f7b177ac1d16d7671dc67efb9/raw/docker-zabbix-mysql-dump.sh
    chmod +x docker-zabbix-mysql-dump.sh
    ```

    ### Example: .env
  10. sebastian13 revised this gist Jun 9, 2020. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion docker-mysql-dump.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -23,4 +23,4 @@ find ./dbdumps/* -atime +7 -exec rm {} \;

    docker-compose exec -T mysql /usr/bin/mysqldump -u root \
    --password=$MYSQL_ROOT_PASSWORD $MYSQL_DATABASE \
    | gzip --rsyncable > ./dbdumps/`date +\%Y\%m\%d`-$MYSQL_DATABASE.sql.gz
    | gzip --rsyncable > ./dbdumps/`date +\%Y\%m\%d-\%H\%M`-$MYSQL_DATABASE.sql.gz
  11. sebastian13 revised this gist Oct 10, 2019. 2 changed files with 11 additions and 6 deletions.
    14 changes: 8 additions & 6 deletions docker-mysql-restore.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -18,15 +18,17 @@ do
    [ $result ] && break
    done

    # Get the size of the uncompressed sql file
    size=$(gzip -l $result | awk 'FNR==2{print $2}')

    # Wait
    i=15
    while (( i >= 1 )); do
    sleep 1
    echo -ne
    echo -ne "Wait for DB to initialize. Starting Restore of $result in $(( i-- )) seconds ... "'\r'
    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
    echo "Starting restore now."
    gunzip --keep --stdout $result | docker-compose exec -T mysql \
    gunzip --keep --stdout $result | pv --size $size | docker-compose exec -T mysql \
    /usr/bin/mysql -u root --password=${MYSQL_ROOT_PASSWORD} ${MYSQL_DATABASE}
    3 changes: 3 additions & 0 deletions restore-help.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,3 @@
    # ERROR 1118 (42000) Row size too large
    docker-compose exec mysql /usr/bin/mysql -u root --password=${MYSQL_ROOT_PASSWORD} -e "SET GLOBAL innodb_strict_mode = 0"
    docker-compose exec mysql /usr/bin/mysql -u root --password=${MYSQL_ROOT_PASSWORD} -e "show variables like '%innodb_strict_mode%' "
  12. sebastian13 revised this gist Oct 10, 2019. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions docker-mysql-restore.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -27,5 +27,6 @@ while (( i >= 1 )); do
    done

    # Restore
    echo "Starting restore now."
    gunzip --keep --stdout $result | docker-compose exec -T mysql \
    /usr/bin/mysql -u root --password=${MYSQL_ROOT_PASSWORD} ${MYSQL_DATABASE}
  13. sebastian13 revised this gist Sep 23, 2019. 4 changed files with 4 additions and 4 deletions.
    2 changes: 1 addition & 1 deletion docker-mysql-dump.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -23,4 +23,4 @@ find ./dbdumps/* -atime +7 -exec rm {} \;

    docker-compose exec -T mysql /usr/bin/mysqldump -u root \
    --password=$MYSQL_ROOT_PASSWORD $MYSQL_DATABASE \
    | gzip --rsyncable > ./dbdumps/`date +\%Y\%m\%d`-$MYSQL_DATABASE.sql.gz
    | gzip --rsyncable > ./dbdumps/`date +\%Y\%m\%d`-$MYSQL_DATABASE.sql.gz
    2 changes: 1 addition & 1 deletion docker-mysql-restore.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -28,4 +28,4 @@ done

    # Restore
    gunzip --keep --stdout $result | docker-compose exec -T mysql \
    /usr/bin/mysql -u root --password=${MYSQL_ROOT_PASSWORD} ${MYSQL_DATABASE}
    /usr/bin/mysql -u root --password=${MYSQL_ROOT_PASSWORD} ${MYSQL_DATABASE}
    2 changes: 1 addition & 1 deletion docker-zabbix-mysql-dump.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -40,4 +40,4 @@ docker-compose exec -T mysql /usr/bin/mysqldump -u root \
    --ignore-table=zabbix.trends \
    --ignore-table=zabbix.trends_uint \
    $MYSQL_DATABASE \
    | gzip --rsyncable > ./dbdumps/`date +\%Y\%m\%d`-$MYSQL_DATABASE.sql.gz
    | gzip --rsyncable > ./dbdumps/`date +\%Y\%m\%d`-$MYSQL_DATABASE.sql.gz
    2 changes: 1 addition & 1 deletion readme.md
    View file
    Original file line number Diff line number Diff line change
    @@ -28,4 +28,4 @@ services:
    environment:
    - WORDPRESS_DB_NAME=${MYSQL_DATABASE}
    - WORDPRESS_DB_PASSWORD=${MYSQL_ROOT_PASSWORD}
    ```
    ```
  14. sebastian13 revised this gist Sep 23, 2019. 1 changed file with 4 additions and 0 deletions.
    4 changes: 4 additions & 0 deletions readme.md
    View file
    Original file line number Diff line number Diff line change
    @@ -3,6 +3,10 @@
    curl -JO https://gist.githubusercontent.com/sebastian13/cc58304f7b177ac1d16d7671dc67efb9/raw/docker-mysql-dump.sh
    curl -JO https://gist.githubusercontent.com/sebastian13/cc58304f7b177ac1d16d7671dc67efb9/raw/docker-mysql-restore.sh
    chmod +x docker-mysql-(dump|restore).sh
    # for zabbix database dump run
    curl -JO https://gist.githubusercontent.com/sebastian13/cc58304f7b177ac1d16d7671dc67efb9/raw/docker-zabbix-mysql-dump.sh
    chmod +x docker-zabbix-mysql-dump.sh
    ```

    ### Example: .env
  15. sebastian13 revised this gist Sep 23, 2019. 1 changed file with 43 additions and 0 deletions.
    43 changes: 43 additions & 0 deletions docker-zabbix-mysql-dump.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,43 @@
    #!/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 {} \;

    docker-compose exec -T mysql /usr/bin/mysqldump -u root \
    --password=$MYSQL_ROOT_PASSWORD \
    --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`-$MYSQL_DATABASE.sql.gz
  16. sebastian13 revised this gist Sep 16, 2019. 2 changed files with 19 additions and 10 deletions.
    2 changes: 1 addition & 1 deletion docker-mysql-dump.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -12,7 +12,7 @@ i=20
    while (( i >= 1 )); do
    sleep 1
    echo -ne
    echo -ne "Wait for DB to initialize. Creating Dump in $(( i-- )) Seconds ... "'\r'
    echo -ne "Wait for DB to initialize. Creating Dump in $(( i-- )) seconds ... "'\r'
    done

    # Load database name + root password
    27 changes: 18 additions & 9 deletions docker-mysql-restore.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -3,20 +3,29 @@
    # Start mysql service
    docker-compose --log-level ERROR up -d mysql

    # Load database name + root password
    source .env

    # Find latest dumps
    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 dump should be used for restoring?"
    select result in $latest1 $latest2 $latest3
    do
    [ $result ] && break
    done

    # Wait
    i=20
    i=15
    while (( i >= 1 )); do
    sleep 1
    echo -ne
    echo -ne "Wait for DB to initialize. Starting Restore in $(( i-- )) Seconds ... "'\r'
    echo -ne "Wait for DB to initialize. Starting Restore of $result in $(( i-- )) seconds ... "'\r'
    done

    # Load database name + root password
    source .env

    # Find latest dump
    latest=$(ls -1t ./dbdumps/*.sql.gz | head -1)

    # Restore
    gunzip --keep --stdout $latest | docker-compose exec -T mysql \
    gunzip --keep --stdout $result | docker-compose exec -T mysql \
    /usr/bin/mysql -u root --password=${MYSQL_ROOT_PASSWORD} ${MYSQL_DATABASE}
  17. sebastian13 revised this gist Sep 16, 2019. 2 changed files with 16 additions and 0 deletions.
    8 changes: 8 additions & 0 deletions docker-mysql-dump.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -7,6 +7,14 @@ 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

    8 changes: 8 additions & 0 deletions docker-mysql-restore.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -3,6 +3,14 @@
    # 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. Starting Restore in $(( i-- )) Seconds ... "'\r'
    done

    # Load database name + root password
    source .env

  18. sebastian13 revised this gist Mar 30, 2019. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions readme.md
    View file
    Original file line number Diff line number Diff line change
    @@ -22,6 +22,6 @@ services:
    other:
    ...
    environment:
    - MYSQL_DATABASE=${MYSQL_DATABASE}
    - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
    - WORDPRESS_DB_NAME=${MYSQL_DATABASE}
    - WORDPRESS_DB_PASSWORD=${MYSQL_ROOT_PASSWORD}
    ```
  19. sebastian13 revised this gist Mar 30, 2019. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion readme.md
    View file
    Original file line number Diff line number Diff line change
    @@ -22,6 +22,6 @@ services:
    other:
    ...
    environment:
    - MYSQL_DATABSE=${MYSQL_DATABSE}
    - MYSQL_DATABASE=${MYSQL_DATABASE}
    - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
    ```
  20. sebastian13 revised this gist Mar 18, 2019. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion docker-mysql-restore.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -10,5 +10,5 @@ source .env
    latest=$(ls -1t ./dbdumps/*.sql.gz | head -1)

    # Restore
    gunzip --keep $latest | docker-compose exec -T mysql \
    gunzip --keep --stdout $latest | docker-compose exec -T mysql \
    /usr/bin/mysql -u root --password=${MYSQL_ROOT_PASSWORD} ${MYSQL_DATABASE}
  21. sebastian13 revised this gist Mar 18, 2019. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion docker-mysql-restore.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -10,5 +10,5 @@ source .env
    latest=$(ls -1t ./dbdumps/*.sql.gz | head -1)

    # Restore
    gunzip $latest | docker-compose exec -T mysql \
    gunzip --keep $latest | docker-compose exec -T mysql \
    /usr/bin/mysql -u root --password=${MYSQL_ROOT_PASSWORD} ${MYSQL_DATABASE}
  22. sebastian13 revised this gist Dec 21, 2018. 2 changed files with 2 additions and 2 deletions.
    2 changes: 1 addition & 1 deletion docker-mysql-dump.sh
    View file
    Original 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 up -d mysql
    docker-compose --log-level ERROR up -d mysql

    # Load database name + root password
    source .env
    2 changes: 1 addition & 1 deletion docker-mysql-restore.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,7 +1,7 @@
    #!/bin/bash

    # Start mysql service
    docker-compose up -d mysql
    docker-compose --log-level ERROR up -d mysql

    # Load database name + root password
    source .env
  23. sebastian13 revised this gist Dec 17, 2018. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions readme.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,7 +1,7 @@
    ### Download
    ```
    curl -O https://gist.githubusercontent.com/sebastian13/cc58304f7b177ac1d16d7671dc67efb9/raw/docker-mysql-dump.sh
    curl -O https://gist.githubusercontent.com/sebastian13/cc58304f7b177ac1d16d7671dc67efb9/raw/docker-mysql-restore.sh
    curl -JO https://gist.githubusercontent.com/sebastian13/cc58304f7b177ac1d16d7671dc67efb9/raw/docker-mysql-dump.sh
    curl -JO https://gist.githubusercontent.com/sebastian13/cc58304f7b177ac1d16d7671dc67efb9/raw/docker-mysql-restore.sh
    chmod +x docker-mysql-(dump|restore).sh
    ```

  24. sebastian13 revised this gist Dec 17, 2018. 2 changed files with 6 additions and 2 deletions.
    4 changes: 3 additions & 1 deletion docker-mysql-dump.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,8 +1,10 @@
    #!/bin/bash

    # Change to the script's directory & create directory
    cd $(dirname "$(readlink -f "$0")")
    mkdir -p ./dbdumps

    # Start mysql service of your compose file
    # Start mysql service
    docker-compose up -d mysql

    # Load database name + root password
    4 changes: 3 additions & 1 deletion docker-mysql-restore.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,6 @@
    # Start mysql service of your compose file
    #!/bin/bash

    # Start mysql service
    docker-compose up -d mysql

    # Load database name + root password
  25. sebastian13 revised this gist Dec 17, 2018. 1 changed file with 4 additions and 2 deletions.
    6 changes: 4 additions & 2 deletions readme.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,15 +1,17 @@
    ### Download
    ```
    curl -O https://gist.githubusercontent.com/sebastian13/cc58304f7b177ac1d16d7671dc67efb9/raw/docker-mysql-dump.sh
    curl -O https://gist.githubusercontent.com/sebastian13/cc58304f7b177ac1d16d7671dc67efb9/raw/docker-mysql-restore.sh
    chmod +x docker-mysql-(dump|restore).sh
    ```

    ### .env
    ### Example: .env
    ```
    MYSQL_DATABASE=example
    MYSQL_ROOT_PASSWORD=123456
    ```

    ### docker-compose.yml
    ### Example: docker-compose.yml
    ```
    services:
    mysql:
  26. sebastian13 revised this gist Dec 17, 2018. 1 changed file with 3 additions and 2 deletions.
    5 changes: 3 additions & 2 deletions readme.md
    View file
    Original file line number Diff line number Diff line change
    @@ -12,11 +12,12 @@ MYSQL_ROOT_PASSWORD=123456
    ### docker-compose.yml
    ```
    services:
    example-1:
    mysql:
    image: mariadb
    ...
    env_file: .env
    example-2:
    other:
    ...
    environment:
    - MYSQL_DATABSE=${MYSQL_DATABSE}
  27. sebastian13 revised this gist Dec 17, 2018. 1 changed file with 14 additions and 0 deletions.
    14 changes: 14 additions & 0 deletions readme.md
    View file
    Original file line number Diff line number Diff line change
    @@ -7,4 +7,18 @@ curl -O https://gist.githubusercontent.com/sebastian13/cc58304f7b177ac1d16d7671d
    ```
    MYSQL_DATABASE=example
    MYSQL_ROOT_PASSWORD=123456
    ```

    ### docker-compose.yml
    ```
    services:
    example-1:
    ...
    env_file: .env
    example-2:
    ...
    environment:
    - MYSQL_DATABSE=${MYSQL_DATABSE}
    - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
    ```
  28. sebastian13 revised this gist Dec 17, 2018. 3 changed files with 12 additions and 2 deletions.
    2 changes: 1 addition & 1 deletion docker-mysql-dump.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -11,6 +11,6 @@ source .env
    # Delete old Backups
    find ./dbdumps/* -atime +7 -exec rm {} \;

    docker-compose exec mysql /usr/bin/mysqldump -u root \
    docker-compose exec -T mysql /usr/bin/mysqldump -u root \
    --password=$MYSQL_ROOT_PASSWORD $MYSQL_DATABASE \
    | gzip --rsyncable > ./dbdumps/`date +\%Y\%m\%d`-$MYSQL_DATABASE.sql.gz
    2 changes: 1 addition & 1 deletion docker-mysql-restore.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -8,5 +8,5 @@ source .env
    latest=$(ls -1t ./dbdumps/*.sql.gz | head -1)

    # Restore
    gunzip $latest | docker-compose exec mysql \
    gunzip $latest | docker-compose exec -T mysql \
    /usr/bin/mysql -u root --password=${MYSQL_ROOT_PASSWORD} ${MYSQL_DATABASE}
    10 changes: 10 additions & 0 deletions readme.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,10 @@
    ```
    curl -O https://gist.githubusercontent.com/sebastian13/cc58304f7b177ac1d16d7671dc67efb9/raw/docker-mysql-dump.sh
    curl -O https://gist.githubusercontent.com/sebastian13/cc58304f7b177ac1d16d7671dc67efb9/raw/docker-mysql-restore.sh
    ```

    ### .env
    ```
    MYSQL_DATABASE=example
    MYSQL_ROOT_PASSWORD=123456
    ```
  29. sebastian13 revised this gist Dec 14, 2018. 2 changed files with 4 additions and 4 deletions.
    4 changes: 2 additions & 2 deletions docker-mysql-dump.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -3,14 +3,14 @@ cd $(dirname "$(readlink -f "$0")")
    mkdir -p ./dbdumps

    # Start mysql service of your compose file
    docker-compose up -d mariadb
    docker-compose up -d mysql

    # Load database name + root password
    source .env

    # Delete old Backups
    find ./dbdumps/* -atime +7 -exec rm {} \;

    docker-compose exec mariadb /usr/bin/mysqldump -u root \
    docker-compose exec mysql /usr/bin/mysqldump -u root \
    --password=$MYSQL_ROOT_PASSWORD $MYSQL_DATABASE \
    | gzip --rsyncable > ./dbdumps/`date +\%Y\%m\%d`-$MYSQL_DATABASE.sql.gz
    4 changes: 2 additions & 2 deletions docker-mysql-restore.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,5 @@
    # Start mysql service of your compose file
    docker-compose up -d mariadb
    docker-compose up -d mysql

    # Load database name + root password
    source .env
    @@ -8,5 +8,5 @@ source .env
    latest=$(ls -1t ./dbdumps/*.sql.gz | head -1)

    # Restore
    gunzip $latest | docker-compose exec mariadb \
    gunzip $latest | docker-compose exec mysql \
    /usr/bin/mysql -u root --password=${MYSQL_ROOT_PASSWORD} ${MYSQL_DATABASE}
  30. sebastian13 revised this gist Dec 14, 2018. 1 changed file with 3 additions and 4 deletions.
    7 changes: 3 additions & 4 deletions docker-mysql-restore.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -4,10 +4,9 @@ docker-compose up -d mariadb
    # Load database name + root password
    source .env

    # Extract Backup if needed
    gunzip backup.sql.gz
    bunzip2 backup.sql.bz2
    # Find latest dump
    latest=$(ls -1t ./dbdumps/*.sql.gz | head -1)

    # Restore
    cat backup.sql | docker-compose exec mariadb \
    gunzip $latest | docker-compose exec mariadb \
    /usr/bin/mysql -u root --password=${MYSQL_ROOT_PASSWORD} ${MYSQL_DATABASE}