Is it reliable to compress database backups with git?

Question

I'm working as an intern at a fund. I spent the last month building a website for internal use, and now I think it's a good time to set up a backup scheme for the MySQL database at its backend. Funny enough, my mentor is reluctant to get me another server because we are not allowed to use external IaaS like AWS and DigitalOcean for security reasons, and it takes weeks to get a usable server from our IT department. Thus, I'm planning to make the backup on the same server running my website. Yeah, I understand the data would be gone if the disk fails or the server brows up, but at least it would be a lifesaver in case of an accidental DROP DATABASE production;. By the way, all "servers" assigned by IT appear to be VPS running on the same physical machine, so I guess backing up on another virtual server can't protect the data against a disk/server/power failure after all?

Anyway, here is my local backup plan: I will run mysqldump and commit it to a local git repository every minute. More concretely, I have set up a cronjob to run the following script with */1 * * * *. Essentially I'm using git as an incremental compression tool, rather than a VCS. I have also included some tags for easy navigation.

#!/usr/bin/env bash
export BACKUP_DIR=/home/foo/Backups/bar
mysqldump \
    --defaults-extra-file=/home/foo/Developer/MYSQL_ROOT.cnf \
    --single-transaction \
    --extended-insert=FALSE \
    production | sed '$d' > $BACKUP_DIR/production.sql
git -C $BACKUP_DIR commit --all -m 'Auto backup via cron' > /dev/null
git -C $BACKUP_DIR tag -f `date +%F`
git -C $BACKUP_DIR tag -f `date +%F@%H`
git -C $BACKUP_DIR tag -f `date +%F@%H-%M`

Currently, the backup script takes about a second to complete, and the resulting production.sql is around 3MiB in size. I estimate it would stay under 10MiB for years. The website in question has ~20 users, and I won't expect more than 1000 requests per day. I'm using MySQL Community Server 8.0.21 on RHEL 7, without any Enterprise subscription.

Can I make a reliable local backup this way? Is one lightweight tag per minute too much? Is there a better alternative?

Answer 1

In short

No, this is not a real backup, and it might bring lots of trouble. There are better alternatives to protect you against accidental errors that worry you most.

More details

A real backup must be somewhere else, because the real question is not „if the server will break“ but „when it will“. Having the backup on the same server will not protect you against a hardware crash, nor even ransomeware. As Kain said: go talk to the sysadmin team about backup solutions. They surely can help!

Going for GIT is risky. For example:

• Kain already talked about volumes issue, since the data is dumped in text form and accumulates over time. Done every minute you‘ll soon run out of time.
• Privacy issues: not only do you make data more easily accessible to those who do not need to know (something that you can easily address in the db), but you keep forever old data which could have been corrected. This might be illegal for example under GDPR if you handle anything about a person.
• loss of data: now suppose you make that fatal sql command. If you restore the db, you‘ll have lost transactions that were finished by the end of the db dump, but that was not db-committed at the start. And maybe nobody will remember what this data was. Furthermore, you may not notice the issue immediately (more data lost due to recovery) and it may take time to restore the db (unavailable service = unhappy users). It is never a good idea to experiment manually sql on a productive system. dot.

Alternative: For protecting against your own data manipulation errors, For the kind of operations you intend to do, another alternative could be to replicate the database, write your command in a script. test. if it works, run the script in production.

If you do this often, let your app write in two databases constantly, one allowing test access. Or better, start to automate the techy cases ;-)

Answer 2

Git is not a backup solution.

There are countless programs available which are specialized for creating regular incremental backups of important files, both paid and free software. There are even some which are specifically designed for MySQL databases. You will likely get far better results by using a tool actually built for that purpose.

That way you won't end up with a Git repository with 1440 commits every day and a half million commits every year.

Also, remember that local backups should only be a temporary solution. Yes, processes in enterprise environments can be slow and bureaucratic, but it is common best practice in every professionally managed datacenter that every physical server and every productive application has at least one backup which is physically separated, managed by the datacenter administrators and can be restored if needed. If you forgot to order the backup for your server when you started your project, then you should better do that now.

Answer 3

Yes/No.

What is the growth on your Git repository over a day? over a week? a month? Use that to get a clear picture of how much history you can keep.

Similarly this backup, and all of the prior backups blow up if the repository experiences data corruption. What archival process are you instigating?

Can you liaise with the rest of your IT department, see what backup/archival mechanisms are in place. Many run weekly full backups, nightly diffs, and daily transaction logs. Are these sufficient for you? If not how does your mechanism segway into theirs so that you have sufficient redundancy, while also not over-engineering your own solution.

As for protecting against a drop xyz; might I recommend curating the login permissions being given? That statement cannot hurt you, if it cannot be run.

Answer 4

Achieving an incremental database backup by exporting dumps and using git on those dumps is very inefficient, as others pointed out.

• Databases offer binary logging (which is a transaction log of changes). This surely is the most efficient way to go.

• And doing this with a cronjob (Has something even changed? No I don't care dump the whole thing anyhow) instead of event based (only dump when certain (significant changes) occur), again is inefficient.

• But for small databases and lack of a professional database/setup with that incremental backup features, a mysqldump on demand (! not periodically, that's insane) + git version management may be an acceptable compromise, which I also went for, until knowing/having something better.

Change the following arguments in your mysqldump command line for better git efficiency:

1. --single-transaction=FALSE to that you get separate insert statements, which means in the SQL dump one insert statement is on one line.

   • That way your git diffs become much more granular!
   • Only that way you benefit from git's delta storing mechanism
   • git considers differences only on a line basis.
   • If you have huge long lines, then on each commit maybe up to 40-80% of your database dump differs, instead of only 0.01% which really changed. With separate transaction statements you come much closes to that number.

2. --skip-comments otherwise you have a diff also if nothing in your database changed.

   • Because the end of file dump always features the timestamp of the dump: -- Dump completed on 2022-12-19 18:23:37
   • And the start of the file always has some info about database name, server name and environment and version number. That stuff may changed, but you content may haven't. Would be a "false positive" in regards to change.

Answer 5

If you'd like to enhance your database backup process, you can consider the following code improvements:

#!/usr/bin/env bash

# Define your backup directory
export BACKUP_DIR=/home/foo/Backups/bar

# Set your MySQL configuration file
export MYSQL_CONFIG_FILE=/home/foo/Developer/MYSQL_ROOT.cnf

# Define your database name
export DB_NAME=production

# Set the current timestamp
TIMESTAMP=$(date +"%F@%H-%M")

# Create a backup filename with a timestamp
BACKUP_FILE="$BACKUP_DIR/$DB_NAME-$TIMESTAMP.sql"

# Perform the database backup
mysqldump --defaults-extra-file=$MYSQL_CONFIG_FILE \
           --single-transaction \
           --extended-insert=FALSE \
           $DB_NAME > $BACKUP_FILE

# Check if the backup was successful
if [ $? -eq 0 ]; then
    echo "Database backup completed successfully."
    
    # Initialize or update your Git repository
    if [ -d $BACKUP_DIR/.git ]; then
        git -C $BACKUP_DIR add $BACKUP_FILE
        git -C $BACKUP_DIR commit -m "Auto backup via cron - $TIMESTAMP" > /dev/null
    else
        git -C $BACKUP_DIR init
        git -C $BACKUP_DIR add $BACKUP_FILE
        git -C $BACKUP_DIR commit -m "Initial backup - $TIMESTAMP" > /dev/null
    fi
else
    echo "Database backup failed."
fi

Your approach to creating a reliable local backup for your MySQL database using Git is practical and efficient. Git is well-suited for this task, as it provides a version control system while also serving as a powerful incremental backup tool. Since your database is relatively small, around 3-10MiB, and the backup process takes only about a second, this method should work well for your needs.

Regarding the lightweight tagging frequency, creating one tag per minute is unlikely to cause issues, especially with a small database. Git tags are lightweight references and have a minimal impact on storage and performance. This tagging frequency allows for easy navigation and point-in-time recovery should you need to restore your database to a specific moment. It's a robust strategy for maintaining a version history of your database changes.

However, as your database grows, you might want to monitor the backup size and the impact on server resources. If it ever becomes a concern, consider optimizing the frequency or exploring alternative methods like database replication for real-time backups or cloud-based backup services for added reliability.

In summary, your current local backup plan is a suitable choice for your scenario. It's efficient and reliable, and you've accounted for the size of your database. Regular testing of your backups will help ensure they perform as expected when needed, providing peace of mind for accidental data loss or production issues.