I was looking for a way to backup my MySQL databases using Google Drive and FTP. So, I wrote this PHP script to do just that.This script is useful if you are using something like Little Software Stats, WordPress, or some other web application that utilizes a MySQL database.
Usually I like to keep everything open source, but this time I got a little lazy and bought a script from CodeCanyon to create the SQL files to backup. The script is $4 USD (or $20 USD for the extended license) so its not that expensive. Of course, you could write your own PHP code to generate SQL backups, but I’ll leave that up to you.
To use this script, you’re going to need a few things to have it work. Your going to need the MySQL information (hostname, username, password, and database name) that you want to backup, and you will also need a FTP login and/or Google Drive access information. The FTP login should be easy to figure out (you need the FTP hostname, port, username, and password). You will also need access to crontab and you can use something like the following to backup daily:
[highlight escape=”false”]@daily php /path/to/cron.php > /dev/null 2>&1[/highlight]
Since finding out the Google Drive access information is a little harder to figure out, I’ll try to explain how. First, you will need a Google account. Next, go to the Google Cloud Console and login if you need to. Click “Create Project” and either leave the Name and Project ID as they are or change them, then click “Create”. Select the project from the list on the screen.
The next step is to enable access to the Google Drive API. Select “APIs & auth” on the left side of the screen, and then it should open the “APIs” tab. Here, scroll down to where it says “Drive API” and click “OFF” so it becomes green and says “ON”. Next, click “Registered Apps” on the left hand side and then click “Register App”. Enter whatever you want for the Name but make sure “Native” is set as the platform. On the next screen, take note of the Client ID and the Client Secret.
Using your client ID and secret, you will now be able to get a refresh token. In the following URL, replace [ID] with the client ID (please note that it includes “.apps.googleusercontent.com” on the end) and open it in your browser:
[highlight escape=”false”]https://accounts.google.com/o/oauth2/auth?scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fdrive&redirect_uri=urn:ietf:wg:oauth:2.0:oob&response_type=code&client_id=[ID]&approval_prompt=force&access_type=offline[/highlight]
Click “Accept” and you will be given a code to copy. This is the refresh access token which is used to get a new access token. You can now configure the PHP script to use it with Google Drive. Open cron.php and find the following piece of code. Replace [ID] with the client ID, [SECRET] with the client secret, and [TOKEN] with the refresh token.
[highlight lang=”php”]// Google Drive info
define(‘GDRIVE’, true); // Set to false to disable Google Drive
define(‘CLIENT_ID’, ‘[ID]’);
define(‘CLIENT_SECRET’, ‘[SECRET]’);
define(‘CLIENT_REFRESH_ACCESS_TOKEN’, ‘[TOKEN]’);
[/highlight]
You can download the script below, but please note that the exportmysqlfunction.php script is not included and must be purchased from CodeCanyon for $4 USD. If your having any problems, please post them below or feel free to contact me.
UPDATE: It appears that the Google Drive API was updated and the old PHP library for it wasn’t working properly. I’ve released an update for the script which also includes a correction for the email subject (when an error occurs) and a fix which caused the script to upload an empty backup file to Google Drive.