We previously demonstrated how to use PHP to backup MySQL databases to DropBox using the DropBox API (How To Use PHP And The Dropbox API To Automatically Back Up MySQL Databases To Dropbox). However, in June 2017 Dropbox deprecated the API. This tutorial is an update on how to use PHP to automatically backup MySQL databases to DropBox using the DropBox REST endpoints. This example requires more PHP code in the backup file, but it does not require the PHP SDK offered by DropBox and ought to be more stable than the SDK.
There are some differences this time from our previous tutorial. This time you will only need to:
- Create a DropBox App
- Create a PHP script
- Run the PHP script at regular intervals
1.) Create a DropBox app
As in our previous tutorial, you will first need to create a DropBox app.
Under your own Dropbox Account (assuming already that you have one) you need to create an App. This is easy to do in Dropbox, login to your account and go here: https://www.dropbox.com/developers/apps
You will see the app panel; click on ‘create a new app’ like below:
Next you need to select the kind of app as “Dropbox API App” and for security I would put “Yes” to limit the app as having access only to our backup folder. Then name your app and move on to the next step.
Finally, go to the actual App settings and get your API Key, API Secret and then also get a auth token generated for your account where you plan to upload the files to. Getting the auth token from your panel allows us to avoid running the authentication through the code and greatly simplifies the process. We are just hard coding an auth token into our app script for your user account.
2.) Create a PHP file for the backup
Unlike our previous tutorial you do not need to download the PHP SDK from DropBox. All you need to do is create a PHP file on your server named dropbox_sqlbackup.php. This PHP file will use the REST API provided by DropBox and completely avoid using any SDK.
First, add your access token to the top of the file
// Our access token from the Dropbox App Panel
$accessToken = 'YOUR_DROPBOX_ACCESS_TOKEN';
Second, add the code that will create a compressed backup file of our MySQL database before uploading it to the server. This code will work except for the function DropboxUploadLargeFile(), which will be defined later.
$tmpDir = "/home/php/temp_dir/"; // Location of your temp directory
$user = "DB_USER"; // Usernamefor database
$password = "DB_PASSWORD"; // Password for database
$unix_socket = "/opt/mariadb-data/mariadb.sock"; // Unix socket for MariaDB if using MariaDB
$dbName = "DB_NAME"; // Database name to backup
$dbHost = "DB_HOST"; // Hostname or IP address where database resides (localhost or xxx.xxx.xxx.xxx)
$prefix = "backup_db_"; // The compressed file will have this prefix
// Create the database backup file
$sqlFile = $tmpDir.$prefix.date('Y_m_d_H:i:s').".sql"; // Name of the .sql file we create for the database
$backupFilename = $prefix.date('Y_m_d_H:i:s').".tgz"; // Name of the .tgz backup file we upload to Dropbox
$backupFile = $tmpDir.$backupFilename; // Path to the backup file in the temp directory
// Shell commands for generating the .sql and .tgz files
//$createBackup = "mysqldump -h ".$dbHost." -u ".$user." --password='".$password."' ".$dbName." --> ".$sqlFile; // Shell command to create the .sql file for MySQl
$createBackup = "mysqldump -h ".$dbHost." -u ".$user." --password='".$password."' ".$dbName." --socket=".$unix_socket." --> ".$sqlFile; // Shell command to create the .sql file for MariaDB
$createZip = "tar -cvzf $backupFile $sqlFile"; // Shell command to create the backup file we upload to Dropbox
try {
exec($createBackup); // Run shell command to create the .sql file
exec($createZip); // Run shell command to create the .tgz file
} catch (Exception $e) {
echo "Failed to create CRM DB Backup or ZIP: " . $e->getMessage() . "\n";
}
// Upload the file to Dropbox
$file_path = $backupFile; // The path to the file on the local machine
$dbox_file_path = "/DROPBOX_DIRECTORY/"; // The path in DropBox where we will upload the file
DropboxUploadLargeFile($file_path, $dbox_file_path);
// Delete the temporary files
try {
unlink($sqlFile);
unlink($backupFile);
} catch (Exception $e) {
echo "Failed to unlink DB Backup temporary file: " . $e->getMessage() . "\n";
}
Third, add the functions that will actually upload the file. This is where the function DropboxUploadLargeFile() is defined.
// -----------------------------------------------------------------------------
// Functions for uploading files of any size
// -----------------------------------------------------------------------------
function DropboxUploadLargeFile($file_path, $dbox_file_path) { // Large is > 150MB
// https://www.dropbox.com/developers/documentation/http/documentation#files-upload
$session_ID = GetUploadSessionID(); // Get the session ID that we will use for uploading the file
$filesize = filesize($file_path); // Get the file size
$file_name = basename($file_path); // Get the name of the file (i.e. database.db, compressed.zip, excel.csv, etc.)
UploadFileInChunks($session_ID, $file_path); // Upload the file
FinishUploadingChunks($session_ID, $filesize, $file_name, $dbox_file_path); // Finish uploading the file
}
function GetUploadSessionID() {
// https://www.dropbox.com/developers/documentation/http/documentation#files-upload_session-start
global $accessToken; // Get the global access token value
$session_ID = ""; // Declare our session ID and initialize it to an empty string
$dropbox_endpoint_url = "https://content.dropboxapi.com/2/files/upload_session/start"; // Dropbox url for retrieving a session ID
$arguments = '
{
"close": false
}
';
// Encode the url parameters
$url = $dropbox_endpoint_url . "?authorization=" . urlencode("Bearer " . $accessToken) . "&arg=" . urlencode($arguments);
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_HTTPHEADER, array(
'Content-Type: application/octet-stream',
));
curl_setopt($ch, CURLOPT_POST, true); // Tell CURL to use HTTP POST
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); // Tell CURL that we want the data to be returned in a way that can be assigned to our $response variable
$response = curl_exec($ch); // Obtain response
curl_close($ch); // Close the CURL connection
$session_ID = json_decode($response, true)['session_id']; // Parse the JSON encoded array to get the session ID
return ($session_ID); // Return the session ID
}
function UploadFileInChunks($session_ID, $file_path) {
// Dropbox allows upload sizes of 150 MB, but we will define our chunk size slightly smaller just to be safe.
$chunk_size = 1024 * 1024 * 140; // 140 MB (1024B = 1KB, 1024KB = 1MB)
$file = fopen($file_path, "r"); // Open the file as read-only
$loop_count = 0;
while (!feof($file)) { // While not at the end of file, upload the current chunk
$buffer = fread($file, $chunk_size); // Read the current chunk of the file into a buffer
$offset = $loop_count * $chunk_size; // Define the offset of the chunk
UploadFileChunk($session_ID, $offset, $buffer); // Upload the current chunk
$loop_count++; // Increment the loop count
}
fclose($file); // Close the file
}
function UploadFileChunk($session_ID, $offset, $file_chunk) {
// https://www.dropbox.com/developers/documentation/http/documentation#files-upload_session-append_v2
global $accessToken; // Get the global access token value
$dropbox_endpoint_url = "https://content.dropboxapi.com/2/files/upload_session/append_v2"; // Dropbox upload endpoint
// The arguments require the session ID and the chunk offset
$arguments = '
{
"cursor": {
"session_id": "'.$session_ID.'",
"offset": '.$offset.'
},
"close": false
}
';
// Encode the url parameters
$url = $dropbox_endpoint_url . "?authorization=" . urlencode("Bearer " . $accessToken) . "&arg=" . urlencode($arguments);
$ch = curl_init();
curl_setopt ($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_HTTPHEADER, array(
'Content-Type: application/octet-stream',
));
curl_setopt($ch, CURLOPT_POST, true); // Tell curl to use HTTP POST
curl_setopt($ch, CURLOPT_POSTFIELDS, $file_chunk); // Write the file chunk into the post stream
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); // Tell CURL that we want the data to be returned in a way that can be assigned to our $response variable
$response = curl_exec($ch); // Obtain response
curl_close($ch); // Close the CURL connection
}
function FinishUploadingChunks($session_ID, $offset, $file_name, $dbox_file_path) {
// https://www.dropbox.com/developers/documentation/http/documentation#files-upload_session-finish
global $accessToken; // Get the global access token value
$dropbox_endpoint_url = "https://content.dropboxapi.com/2/files/upload_session/finish"; // Dropbox finish upload endpoint
// The arguments require the session ID and the chunk size
$arguments = '
{
"cursor": {
"session_id": "'.$session_ID.'",
"offset": '.$offset.'
},
"commit": {
"path": "'.$dbox_file_path.$file_name.'",
"mode": "add"
}
}
';
// Encode the url parameters
$url = $dropbox_endpoint_url . "?authorization=" . urlencode("Bearer " . $accessToken) . "&arg=" . urlencode($arguments);
$ch = curl_init();
curl_setopt ($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_HTTPHEADER, array(
'Content-Type: application/octet-stream',
));
curl_setopt($ch, CURLOPT_POST, true); // Tell curl to use HTTP POST
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); // Tell CURL that we want the data to be returned in a way that can be assigned to our $response variable
$response = curl_exec($ch); // Obtain response
curl_close($ch); // Close the CURL connection
}
// -----------------------------------------------------------------------------
// -----------------------------------------------------------------------------
// This could be used for small files (smaller than 150 MB) but I would rather deal with only
// one way of uploading files. This function is here for future reference only if needed.
function DropboxUploadFile($file_path, $dbox_file_path) { // < 150MB
global $accessToken;
// https://www.dropbox.com/developers/documentation/http/documentation#files-upload
$dropbox_endpoint_url = "https://content.dropboxapi.com/2/files/upload"; // Dropbox upload endpoint
$file_name = basename($file_path);
// Upload arguements
// path - the path we are uploading the file to and the filename we are naming it in Dropbox
// mode - determines if we add, overwrite, or update the file if it already exists
$arguments = '
{
"path": "'.$dbox_file_path.$file_name.'",
"mode": "add"
}
';
$url = $dropbox_endpoint_url . "?authorization=" . urlencode("Bearer " . $accessToken) . "&arg=" . urlencode($arguments);
$file_to_upload = fopen($file_path, 'rb'); // Open the file
$filesize = filesize($file_path); // Get the file size
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_HTTPHEADER, array(
'Content-Type: application/octet-stream',
));
curl_setopt($ch, CURLOPT_POST, true); // Tell curl to use HTTP POST
curl_setopt($ch, CURLOPT_POSTFIELDS, fread($file_to_upload, $filesize)); // Write the file into the post stream
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$response = curl_exec($ch); // Obtain response
curl_close($ch);
fclose($file_to_upload); // Close the file
}
// -----------------------------------------------------------------------------
All together
You should create backups at regular intervals. You can use the CRON daemon to run it weekly, daily, or hourly depending on your needs. If you encounter any problems then try checking /var/mail/your_user for any error messages that may have been generated by the CRON task. If you have any questions about this code, or could use our teams help in setting this up for your own database just let us know by contacting us today.
dropbox_sqlbackup.php
// Our access token from the Dropbox App Panel
$accessToken = 'YOUR_DROPBOX_ACCESS_TOKEN';
$tmpDir = "/home/php/temp_dir/"; // Location of your temp directory
$user = "DB_USER"; // Usernamefor database
$password = "DB_PASSWORD"; // Password for database
$unix_socket = "/opt/mariadb-data/mariadb.sock"; // Unix socket for MariaDB if using MariaDB
$dbName = "DB_NAME"; // Database name to backup
$dbHost = "DB_HOST"; // Hostname or IP address where database resides (localhost or xxx.xxx.xxx.xxx)
$prefix = "backup_db_"; // The compressed file will have this prefix
// Create the database backup file
$sqlFile = $tmpDir.$prefix.date('Y_m_d_H:i:s').".sql"; // Name of the .sql file we create for the database
$backupFilename = $prefix.date('Y_m_d_H:i:s').".tgz"; // Name of the .tgz backup file we upload to Dropbox
$backupFile = $tmpDir.$backupFilename; // Path to the backup file in the temp directory
// Shell commands for generating the .sql and .tgz files
//$createBackup = "mysqldump -h ".$dbHost." -u ".$user." --password='".$password."' ".$dbName." --> ".$sqlFile; // Shell command to create the .sql file for MySQl
$createBackup = "mysqldump -h ".$dbHost." -u ".$user." --password='".$password."' ".$dbName." --socket=".$unix_socket." --> ".$sqlFile; // Shell command to create the .sql file for MariaDB
$createZip = "tar -cvzf $backupFile $sqlFile"; // Shell command to create the backup file we upload to Dropbox
try {
exec($createBackup); // Run shell command to create the .sql file
exec($createZip); // Run shell command to create the .tgz file
} catch (Exception $e) {
echo "Failed to create CRM DB Backup or ZIP: " . $e->getMessage() . "\n";
}
// Upload the file to Dropbox
$file_path = $backupFile; // The path to the file on the local machine
$dbox_file_path = "/DROPBOX_DIRECTORY/"; // The path in DropBox where we will upload the file
DropboxUploadLargeFile($file_path, $dbox_file_path);
// Delete the temporary files
try {
unlink($sqlFile);
unlink($backupFile);
} catch (Exception $e) {
echo "Failed to unlink DB Backup temporary file: " . $e->getMessage() . "\n";
}
// -----------------------------------------------------------------------------
// Functions for uploading files of any size
// -----------------------------------------------------------------------------
function DropboxUploadLargeFile($file_path, $dbox_file_path) { // Large is > 150MB
// https://www.dropbox.com/developers/documentation/http/documentation#files-upload
$session_ID = GetUploadSessionID(); // Get the session ID that we will use for uploading the file
$filesize = filesize($file_path); // Get the file size
$file_name = basename($file_path); // Get the name of the file (i.e. database.db, compressed.zip, excel.csv, etc.)
UploadFileInChunks($session_ID, $file_path); // Upload the file
FinishUploadingChunks($session_ID, $filesize, $file_name, $dbox_file_path); // Finish uploading the file
}
function GetUploadSessionID() {
// https://www.dropbox.com/developers/documentation/http/documentation#files-upload_session-start
global $accessToken; // Get the global access token value
$session_ID = ""; // Declare our session ID and initialize it to an empty string
$dropbox_endpoint_url = "https://content.dropboxapi.com/2/files/upload_session/start"; // Dropbox url for retrieving a session ID
$arguments = '
{
"close": false
}
';
// Encode the url parameters
$url = $dropbox_endpoint_url . "?authorization=" . urlencode("Bearer " . $accessToken) . "&arg=" . urlencode($arguments);
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_HTTPHEADER, array(
'Content-Type: application/octet-stream',
));
curl_setopt($ch, CURLOPT_POST, true); // Tell CURL to use HTTP POST
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); // Tell CURL that we want the data to be returned in a way that can be assigned to our $response variable
$response = curl_exec($ch); // Obtain response
curl_close($ch); // Close the CURL connection
$session_ID = json_decode($response, true)['session_id']; // Parse the JSON encoded array to get the session ID
return ($session_ID); // Return the session ID
}
function UploadFileInChunks($session_ID, $file_path) {
// Dropbox allows upload sizes of 150 MB, but we will define our chunk size slightly smaller just to be safe.
$chunk_size = 1024 * 1024 * 140; // 140 MB (1024B = 1KB, 1024KB = 1MB)
$file = fopen($file_path, "r"); // Open the file as read-only
$loop_count = 0;
while (!feof($file)) { // While not at the end of file, upload the current chunk
$buffer = fread($file, $chunk_size); // Read the current chunk of the file into a buffer
$offset = $loop_count * $chunk_size; // Define the offset of the chunk
UploadFileChunk($session_ID, $offset, $buffer); // Upload the current chunk
$loop_count++; // Increment the loop count
}
fclose($file); // Close the file
}
function UploadFileChunk($session_ID, $offset, $file_chunk) {
// https://www.dropbox.com/developers/documentation/http/documentation#files-upload_session-append_v2
global $accessToken; // Get the global access token value
$dropbox_endpoint_url = "https://content.dropboxapi.com/2/files/upload_session/append_v2"; // Dropbox upload endpoint
// The arguments require the session ID and the chunk offset
$arguments = '
{
"cursor": {
"session_id": "'.$session_ID.'",
"offset": '.$offset.'
},
"close": false
}
';
// Encode the url parameters
$url = $dropbox_endpoint_url . "?authorization=" . urlencode("Bearer " . $accessToken) . "&arg=" . urlencode($arguments);
$ch = curl_init();
curl_setopt ($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_HTTPHEADER, array(
'Content-Type: application/octet-stream',
));
curl_setopt($ch, CURLOPT_POST, true); // Tell curl to use HTTP POST
curl_setopt($ch, CURLOPT_POSTFIELDS, $file_chunk); // Write the file chunk into the post stream
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); // Tell CURL that we want the data to be returned in a way that can be assigned to our $response variable
$response = curl_exec($ch); // Obtain response
curl_close($ch); // Close the CURL connection
}
function FinishUploadingChunks($session_ID, $offset, $file_name, $dbox_file_path) {
// https://www.dropbox.com/developers/documentation/http/documentation#files-upload_session-finish
global $accessToken; // Get the global access token value
$dropbox_endpoint_url = "https://content.dropboxapi.com/2/files/upload_session/finish"; // Dropbox finish upload endpoint
// The arguments require the session ID and the chunk size
$arguments = '
{
"cursor": {
"session_id": "'.$session_ID.'",
"offset": '.$offset.'
},
"commit": {
"path": "'.$dbox_file_path.$file_name.'",
"mode": "add"
}
}
';
// Encode the url parameters
$url = $dropbox_endpoint_url . "?authorization=" . urlencode("Bearer " . $accessToken) . "&arg=" . urlencode($arguments);
$ch = curl_init();
curl_setopt ($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_HTTPHEADER, array(
'Content-Type: application/octet-stream',
));
curl_setopt($ch, CURLOPT_POST, true); // Tell curl to use HTTP POST
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); // Tell CURL that we want the data to be returned in a way that can be assigned to our $response variable
$response = curl_exec($ch); // Obtain response
curl_close($ch); // Close the CURL connection
}
// -----------------------------------------------------------------------------
// -----------------------------------------------------------------------------
// This could be used for small files (smaller than 150 MB) but I would rather deal with only
// one way of uploading files. This function is here for future reference only if needed.
function DropboxUploadFile($file_path, $dbox_file_path) { // < 150MB
global $accessToken;
// https://www.dropbox.com/developers/documentation/http/documentation#files-upload
$dropbox_endpoint_url = "https://content.dropboxapi.com/2/files/upload"; // Dropbox upload endpoint
$file_name = basename($file_path);
// Upload arguements
// path - the path we are uploading the file to and the filename we are naming it in Dropbox
// mode - determines if we add, overwrite, or update the file if it already exists
$arguments = '
{
"path": "'.$dbox_file_path.$file_name.'",
"mode": "add"
}
';
$url = $dropbox_endpoint_url . "?authorization=" . urlencode("Bearer " . $accessToken) . "&arg=" . urlencode($arguments);
$file_to_upload = fopen($file_path, 'rb'); // Open the file
$filesize = filesize($file_path); // Get the file size
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_HTTPHEADER, array(
'Content-Type: application/octet-stream',
));
curl_setopt($ch, CURLOPT_POST, true); // Tell curl to use HTTP POST
curl_setopt($ch, CURLOPT_POSTFIELDS, fread($file_to_upload, $filesize)); // Write the file into the post stream
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$response = curl_exec($ch); // Obtain response
curl_close($ch);
fclose($file_to_upload); // Close the file
}
// -----------------------------------------------------------------------------
