Hello!
Following with the theme of our last post, we thought it might be useful to demonstrate how to create a pure PHP based web service to backup your MySQL database to a remote destination (also with PHP) over a secure HTTPS connection.
High level, all we will be doing is iterating over all the tables of the database and generating the database data as JSON, transmitting it to the receiving end over an AJAX HTTPS post. We’ll save it for a separate post, but in this scenario you would also likely want to iterate over the JSON data on the receiving end in order to process and create the database backup on the receiving end’s MySQL instance.
Trigger the backup
In our scenario we would be implementing this solution as a WordPress plugin. There’s no point in going into it specifically in that context because it is most likely more useful as a generalized solution that people using all sorts of different PHP based implementations can use.
Lets say we simply want to trigger the backup by clicking a button :
Clone MySQL
What we want to do is trigger an action when that button above is clicked. We dont want to trigger a PHP function yet, because we want to be as accommodating to any delays in the execution of the function that will ultimately iterate over the entire MySQL database. Some web hosting environments will implement restrictions on PHP’s execution times to avoid a shared environment where one client has executed a bad script that consumes all memory.
So we want to implement jQuery and Ajax to process the request to iterate across the MySQL database and to transport the data to the remote location.
jQuery(document).on( 'click', '#mysql-backup-button', function() {
var button = jQuery(this);
var url = button.attr('href');
jQuery.ajax({
type : 'post',
url : url,
data : {
action : 'mysql-database-backup'
}
});
Whats happening? Well after clicking the button, we are triggering an Ajax POST to the href url of the button, in this case it would be /index.php?mysql-backup.
In that index.php script, we will listen for a POST request that contains the data action : mysql-database-backup
PHP Function to iterate over a MySQL database to back it up
Lets switch back over to PHP. We want to create a listener in the index.php file to listen for the _POST request :
if ($_POST['action'] == 'mysql-database-backup') {
// execute your database dump here
}
Now that we have a condition to wait for a backup request, we can get to the important stuff. That is to backup your MySQL database!
try{
$db = new PDO("mysql:host=127.0.0.1");
}catch(PDOException $e ){
echo "Error: ".$e;
}
$db_array = array();
$result_t = array();
$result = $db->query("SHOW TABLES");
while ($row = $result->fetch(PDO::FETCH_NUM)) {
$query = $db->prepare("SELECT * FROM " . $row[0]);
$query->execute();
$result_t['table'] = $row[0];
$result_t['data'] = $query->fetchall();
array_push($db_array, $result_t);
}
echo json_encode($db_array);
?>
So the above code is a very rough and rudimentary way of sorting and organizing all the data in each table and echoing the result as a JSON encoded string, perfect for transporting to a receiving end via another one of those AJAX Posts.
Taking the JSON data and sending it to an external URL over HTTPS
Remember that AJAX post we did earlier? We want to expand on that function with another AJAX post as part of the preceeding AJAX post’s success function, in a way joining the chain of event’s we have set up in order to accomplish the task of exporting MySQL data to a remote destination.
jQuery(document).on( 'click', '#mysql-backup-button', function() {
var button = jQuery(this);
var url = button.attr('href');
jQuery.ajax({
type : 'post',
url : url,
data : {
action : 'mysql-database-backup'
},
success : function( response ) {
var mysql_send = jQuery.parseJSON(response);
jQuery.ajax({
type : 'POST',
url : 'https://remote-location.com/receive-mysql',
headers: {
"Authorization" : "Bearer AUTH_KEY",
"Accept" : "application/json",
},
data : {
action : 'mysql_clone',
dbdata : mysql_send.data
},
success : function( response ) {
console.log('Success!');
}
},
error: function( jqXhr, textStatus, errorThrown ){
console.log('error!!!');
}
});
}
});
You can see the original AJAX post along with the newly created second ajax post, implemented as a condition of first request’s success. One might wonder why two ajax queries (one an internal post and one an external post) may be necessary? Well in WordPress, if you were to develop a plugin certain triggers like this would need to be protected by Nonces in order to avoid XSS or unauthenticated requests.
You can also see that the json response returned in the PHP function should be piped into the Ajax request, parsed by the jQuery.parseJSON function and sent as a POST field called dbdata.
In the php function that is listening for the original POST, you could write a built in function that validates the original button click as authenticated, validated or anything along those lines. Of course there is likely many many other ways to accomplish this feat.
The receiving end will have to receive the AJAX post , parse the JSON data and either import it to a new “replicated” database or save it as a file perhaps (As an example).
Hopefully this tutorial has helped someone out there who is interested in migrating data, implementing data replication or a remote backup service perhaps.