Backup mysql database. my way =)

The task was this - often backing up the base + to save space on hard.
After reading one article on the creation of an incremental backup for dumps through diff, I realized that this was all kind of crazy stuff.
A thought flashed through my mind - why not dump the base into git? After all, it will be possible to roll back to the desired commit, and clone the repository for experiments, and taste all the delights of this system. =)
Immediately decide - I did everything under FreeBSD. Therefore, under Linux you will need to file a file.
Well, let's go! First we put git (if not in the system)!
After updating the ports, we write cd / usr / ports / devel / git && make install clean
Git has been installed. Select a place for the folder with backup of the base and create the folder mkdir / backup / bases / test
Next, cd / backup / bases / tes t and initialize the repository there with the git init command
I wrote a script that dumps the database casually, adds files to the repository and creates a commit with the date of the backup

#!/usr/local/bin/bash
backupurl=”/backup/bases/test”
for i in `/usr/local/bin/mysql -e “use test;show tables;” -uUSER -pPASS|tr -d “|”|grep -v “Tables_in_”`;do /usr/local/bin/mysqldump –quick –skip-extended-insert –dump-date=false –compact=true -uUSER -pPASS test $i >$backupurl/$i.sql;done
/usr/local/bin/mysqldump -uUSER -pPASS –no-data test > $backupurl/structure.sql #
cd $backupurl
/usr/local/bin/git add . #
sleep 2
/usr/local/bin/git commit -am “backup `date +%d.%m.%y.%H.%M`” #

We seal this script in a file, make it executable and flop in cron.
Then, after several backups, go to the folder with the backup database and write git log - you will see the history of our backups.
Finally:

• Read the gita documentation
• Put the cd / backup / bases / test && git gc command in crowns (depending on the size of the database ... I personally have it once a day) - this seriously compresses the repository
• For a rollback, it is desirable to tilt the repository and make a rollback via git reset HEAD ^^
• The –skip-extended-insert option is MANDATORY . she forbids extended inserts that nullify my whole idea with git