• 0 Posts
  • 3 Comments
Joined 1 year ago
cake
Cake day: October 18th, 2023

help-circle


  • I just had a failed upgrade from MariaDB 10.6 to 11.1 and it was only my backup that saved me. I have a service that runs mariadb-dump every day and tars the resulting .sql file with a dated name (keeping only the previous 10 backups), so I was able to just start fresh with an empty 11.1 db and restore from yesterday’s dump.

    Here’s my backup setup:

    # /etc/systemd/system/backupdb.service
    [Unit]
    Description=auto backup mariadb daily
    
    [Service]
    Type=simple
    ExecStart=/usr/bin/docker exec db /config/backupdb
    
    # /etc/systemd/system/backupdb.timer
    # enable with 'systemctl enable --now backupdb.timer'
    [Unit]
    Description=Periodically runs the backupdb service
    
    [Timer]
    OnCalendar=Daily
    Persistent=true
    
    [Install]
    WantedBy=timers.target
    
    #!/bin/bash
    
    # /pool/config/db/backupdb
    # runs within the context of the container
    # /pool/config/db/ is bind mounted into /config in the container 
    # backs up to /config/backup within container
    # requires files /config/db_user and /config/db_pw to exist and be populated
    
    mkdir -p /config/backup
    cd /config/backup
    BASENAME=db.backup.$(date "+%Y.%m.%d-%H.%M.%S")
    /usr/bin/mariadb-dump -u $(cat /config/db_user) -p$(cat /config/db_pw) -x -A > $BASENAME.sql
    /usr/bin/tar -czvf $BASENAME.tar.gz $BASENAME.sql
    rm $BASENAME.sql
    while (($(ls | wc -l) > 10))
    do
    rm $(ls -t | tail -1)
    done
    

    And this was how I restored with that setup.

    cd /pool/config/mariadb/backup
    tar xvf db.backup.2023.09.22-00.00.02.tar.gz
    docker exec -it db bash
    root@db:/# mariadb -u root -p < /config/backup/db.backup.2023.09.22-00.00.02.sql