Last active
November 13, 2022 18:54
-
-
Save michoelchaikin/fc4db078c0f64f6f5d35 to your computer and use it in GitHub Desktop.
Backup a mySQL database to Google Drive
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#! /usr/bin/perl | |
# Backups and compresses a SQL database, and uploads to Google Drive | |
# Make sure ~/.my.cnf is set with mySQL login credentials | |
# Follow instructions at https://developers.google.com/drive/web/auth/web-server to generate a client id and secret | |
# Use bash script at https://github.com/soulseekah/bash-utils/blob/master/google-oauth2/google-oauth2.sh to create refresh token | |
# Configuration Options | |
my $HOST = ''; # mySQL server host | |
my $DB_NAME = ''; # mySQL database to backup | |
my $PATH = ''; # Path to directory the backup files will be stored in | |
my $CLIENT_ID = ''; # Get this from Google Developer Console | |
my $CLIENT_SECRET = ''; # Get this from Google Developer Console | |
my $REFRESH_TOKEN = ''; # Use google-oauth2.sh to generate a refresh token | |
my $FOLDER_ID = ''; # ID of folder on Google Drive to upload backups to, get it from end of URL | |
use strict; | |
use warnings; | |
use LWP; | |
use HTTP::Request::Common; | |
use JSON; | |
use File::Slurp; | |
sub backup_database { | |
my @now = localtime(); | |
my $timestamp = sprintf("%04d%02d%02d_%02d%02d%02d", $now[5]+1900, $now[4]+1, $now[3], $now[2], $now[1], $now[0]); | |
`mysqldump --host $HOST $DB_NAME > $PATH/$timestamp.sql`; | |
`bzip2 -9 $PATH$timestamp.sql`; | |
return "$timestamp.sql.bz2"; | |
} | |
sub get_access_token { | |
my $ua = shift; | |
my $req = POST 'https://www.googleapis.com/oauth2/v3/token', | |
[ | |
'client_id' => $CLIENT_ID, | |
'client_secret' => $CLIENT_SECRET, | |
'refresh_token' => $REFRESH_TOKEN, | |
'grant_type' => 'refresh_token' | |
]; | |
my $res = $ua->request($req); | |
$res->is_success || die $res->status_line; | |
my $response = decode_json $res->content; | |
return ${$response}{'access_token'}; | |
} | |
sub get_upload_location { | |
my ($ua, $access_token, $file_name) = @_; | |
my $req = POST 'https://www.googleapis.com/upload/drive/v2/files?uploadType=resumable', | |
'Content_Type' => 'application/json', | |
'X-Upload-Content-Type' => 'application/x-bzip2', | |
'Authorization' => "Bearer $access_token", | |
'Content' => encode_json { 'title' => $file_name, 'parents' => [{'kind' => 'drive#fileLink', 'id' => $FOLDER_ID }] }; | |
my $res = $ua->request($req); | |
$res->is_success || die $res->status_line; | |
return $res->header('Location'); | |
} | |
sub upload_file { | |
my ($ua, $access_token, $location, $file_name) = @_; | |
my $file = read_file("$PATH/$file_name", {binmode => ':raw'}); | |
my $req = PUT $location, | |
'Content_Type' => 'application/x-bzip2', | |
'Authorization' => "Bearer $access_token", | |
'Content' => $file; | |
my $res = $ua->request($req); | |
$res->is_success || die $res->status_line; | |
} | |
my $file_name = backup_database(); | |
my $ua = LWP::UserAgent->new; | |
my $access_token = get_access_token($ua); | |
my $location = get_upload_location($ua, $access_token, $file_name); | |
upload_file($ua, $access_token, $location, $file_name); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This is great, you could also RM the database dump once upload.