Blog Post Icon
Blog
03/14/2017

How to use PHP as a web service to backup MySQL over HTTPS to a remote destination

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.

At Shift8, we cater to all sorts of businesses in and around Toronto from small, medium, large and enterprise projects. We are comfortable adapting to your existing processes and try our best to compliment communication and collaboration to the point where every step of the way is as efficient as possible.

Our projects are typically broken into 5 or 6 key “milestones” which focus heavily on the design collaboration in the early stages. We mock-up any interactive or unique page within your new website so that you get a clear picture of exactly how your design vision will be translated into a functional website.

Using tools like Basecamp and Redpen, we try to make the process simple yet fun and effective. We will revise your vision as many times as necessary until you are 100% happy, before moving to the functional, content integration and development phases of the project.

For the projects that are more development heavy, we make sure a considerable amount of effort is spent in the preliminary stages of project planning. We strongly believe that full transparency with a project development plan ensures that expectations are met on both sides between us and the client. We want to ensure that the project is broken into intelligent phases with accurate budgetary and timeline breakdowns.

Approved design mock-ups get translated into a browse-ready project site where we revise again and again until you are satisfied. Client satisfaction is our lifeblood and main motivation. We aren’t happy until you are.

Need Web Design?

Fill out the form to get a free consultation.

shift8 web toronto – 416-479-0685
203A-116 geary ave. toronto, on M6H 4H1, Canada
© 2023. All Rights Reserved by Star Dot Hosting Inc.

contact us
phone: 416-479-0685
toll free: 1-866-932-9083 (press 1)
email: sales@shift8web.com

Shift8 Logo