Storing backups of the database is import for any possible service on the internet. Git can be the right\u00a0tool to backup databases.<\/p>\n
Like other version control systems, Git tracks the changes, and will only push the changes in files to the remote. So if one line in a 1\u00a0million database dump is changed, we don’t need to transfer the whole dump to our backup server. This economization is done by Gits delta compression mechanism.1<\/sup><\/p>\n Generating SSH keys:<\/p>\n If you want to execute the backup script automatically, leave the Now create a remote Git repository, and add the public key to this\u00a0Git service, e.g. Github, Gogs, ….<\/p>\n Init a new local repo with the SSH remote address, and commit\/push an initial commit.<\/p>\n The script will loop through all MySQL databases, and dump them to a After each commit, the changes are pushed to the remote repo. This avoids having very big\u00a0pushes to do, when working with large databases. If you want to push only once, just place the push at the end of the script.<\/p>\n Running this script manually isn’t the best solution. Making an automated backup service of this is straightforward, just make a cronjob that executes the script every day (or any timespan you want).<\/p>\n Type This particular example will run the backup script every day at 2h30, and append the output of the script to a Git works very well for small programming source files and small text files. Those database dumps, however, \u00a0aren’t always that small. On my VPS I have 200MB of database dumps, which each have to be compressed and packed for every commit.\u00a0This takes a lot of time on a machine with 512MB ram, and even crashes sometimes on the largest files. While pushing I’ve seen this error way too much:\u00a0 Some other Git users with larger files have reduced the limits regarding packing, which resulted in fewer problems packing those files:<\/p>\nConfiguring Git<\/h3>\n
$ ssh-keygen -t rsa -C \"your_email@example.com\"\r\nGenerating public\/private rsa key pair.\r\nEnter file in which to save the key (\/Users\/you\/.ssh\/id_rsa): [Press enter]\r\nEnter passphrase (empty for no passphrase): [Type a passphrase]\r\nEnter same passphrase again: [Type passphrase again]\r\nYour identification has been saved in \/Users\/you\/.ssh\/id_rsa.\r\nYour public key has been saved in \/Users\/you\/.ssh\/id_rsa.pub.\r\nThe key fingerprint is:\r\n01:0f:f4:3b:ca:85:d6:17:a1:7d:f0:68:9d:f0:a2:db your_email@example.com\r\n<\/code><\/pre>\npassphrase<\/code> blank. This way SSH won’t ask for it. Note that this might be insecure!<\/p>\nBackup script<\/h3>\n
#! \/bin\/sh\r\n\r\nTIMESTAMP=$(date +\"%F\")\r\nBACKUP_DIR=\"\/home\/mathias\/backup\/mysql_git\"\r\nMYSQL_USER=\"Mathias\"\r\nMYSQL=\/usr\/bin\/mysql\r\nMYSQL_PASSWORD=\"yourpassword\"\r\nMYSQLDUMP=\/usr\/bin\/mysqldump\r\n\r\ncd $BACKUP_DIR\r\n\r\necho \"Backupping databases\"\r\n\r\ndatabases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e \"SHOW DATABASES;\" | grep -Ev \"(Database|information_schema|performance_schema)\"`\r\n\r\nfor db in $databases; do\r\n\r\n echo \" > Dumping $db to disk\"\r\n $MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --skip-extended-insert --databases $db > \"$BACKUP_DIR\/$db.sql\"\r\n\r\n echo \" > Saving $db changes on Git server\"\r\n git add $BACKUP_DIR\/$db.sql\r\n git commit -m \"$db `date +\"%m-%d-%Y\"`\"\r\n git push\r\n\r\ndone\r\n\r\necho \"Done\"<\/code><\/pre>\n.sql<\/code> file (with their name). \u00a0After it is dumped, the file will be added to the local git repo and will be committed.<\/p>\nRunning the backup script<\/h3>\n
crontab -e<\/code> in the console, this will open your personal cron configuration in your favorite editor. Now add the cronjob to the crontab:<\/p>\n30 2 * * * \/home\/mathias\/backup\/mysql_git\/backup.sh >> \/home\/mathias\/backup_git_cron.log<\/code><\/pre>\nbackup_git_cron.log<\/code> file in my home directory. (Of course you are absolutely free to create any exotic cronjob that runs the backup script at your desired moment)<\/p>\nBig data and low-end hardware<\/h3>\n
error: pack-objects died of signal 9<\/code>.<\/p>\ngit config --global pack.windowMemory \"100m\"\r\ngit config --global pack.packSizeLimit \"100m\"\r\ngit config --global pack.threads \"1\"\r\n<\/code><\/pre>\n