How to Automate Backup for PHP & MySQL Project on Google Drive.

Taking backups is a way of ensuring that if your computer crashes you won’t lose hours of valuable work.

I have a habit of building rapid prototypes and project of hundreds of ideas and I usually do not get time to take their backups on a regular bases.

Considering the current situation when the Corona Virus has hit the world, I was just imagining what would happen if my computer crashes. I would have to start from zero on some of the things. And also it’s difficult to get data recovered or computer repaired when we are under lockdown.

Well to avoid this I have created a 1 click backup system that will help me upload my project folder + a mysql dump to the online server.

I thought of writing this post to help others out who might also be finding a solution to automate their backup process.

Please not I have Xampp setup on windows and these steps apply to windows. If you are on a different platform this article might only be useful for you to get and Idea and build a simple solution.

Objectives

Make the task of taking backup as much easier as possible.
All the backup will be saved according to date and planning to upload a daily backup.
Avoid talking backup of “Vendor” folder which is created by Composer. To avoid bloating the project size and since these can be downloaded later.
Avoid taking backup of “node_modules” (I also use VueCLI) to build parts of my project. I want to avoid node_modules folder because it takes up a lot of space, and these dependencies can easily be downloaded later.
Upload the Zip Folder to Google drive.

Tools Used.

I have used 7zip command line which I downloaded from their website.
https://www.7-zip.org/download.html. I had to use 7zip because I need a command so that I can exclude a folder such as “node_modules” while creating a zip file.

Technique

The idea was to build this solution fastest as possible so the rough idea was to create a bat file which will take the sql dump using mysql utilities and create a file inside the folder. Then use a 7zip command to exclude the “vendor” and “node_modules” folder.

Once the zip is created this is moved to a folder called “online_backup”. “online_backup” folder is synced with the server using the “Backup and sync” app by Google.

Backup and Sync app simply uploads anything which inside the folder which you have added too the online server.

How to setup your project folder for backup.

Download 7zip

Download the command line version or standalone console version.
https://www.7-zip.org/download.html

Unzip and copy the 7za.exe to your project folder. It is not necessary to place it there you can also add it to your environment variable.

Create a bat file

Once you have this you need to create a backup.bat file. you can keep the bat file inside your project folder. (But I assume you know how to code and you can modify the code and keep it where you like.)

echo off
del backup_projectname_%date%.zip
C:\xampp\mysql\bin\mysqldump -h localhost -u root dbname > dbname.sql
7za.exe  a ./backup_projectname_%date%.zip   ./ -r   -xr!vendor -xr!node_modules
move backup_projectname_%date%.zip D:\online_backup\
del dbname.sql
echo "Backup Taken and is moved to D:\onlinebackups\. Press any key to exit;"
set /p id="Press any key to exit."

Explanation of what is happening in the above file.

C:\xampp\mysql\bin\mysqldump -h localhost -u root dbname > dbname.sql

This line simply creates a dump of a database called “dbname”. You need to replace this with your database name. The dump is placed inside your project folder.

7za.exe  a ./backup_projectname_%date%.zip   ./ -r   -xr!vendor -xr!node_modules

This line uses 7zip exe to create a file called “backup_projectname_%date%.zip” inside your project folder.
Not “%date%” automatically gets replaced with the current date.

-xr!vendor -xr!node_modules these tell 7zip to skip the folders which are named “vendor” and “node_module”. You can add your own folders you wish to skip.

move backup_projectname_%date%.zip D:\online_backup\

This line simply moves the newly created zip file to “D:\online_backup” this is the folder which I have synced with “Backup & Sync” app from google.

Use Backup & Sync Google App to upload the folder automatically to Google Drive.

You can download the backup and Sync here.
https://www.google.com/drive/download/backup-and-sync/

I will not explain the steps here as it is pretty straight forward once you download the app.

Done

Once all the above steps are down you just have to click the bat file and the backup will be created with your MySQL database and placed in the Google Drive folder which will upload it online.