Stress-free way to automate MySQL Database with PHP.

Josiah Ovye Yahaya
Apr 2 · 4 min read
Simple Backup library — Storing after export to a directory

Databases are the fundamental part of any app. You need a database to be able to store and retrieve information about certain things or people. But, there comes a time where these data, for some unfortunate reasons, get wiped out or ... This is why every application with internal data storage must have backup support.

Backup tools

These are computer programs (pieces of codes) used to perform backup; they create supplementary exact copies of files, databases or entire computers. These programs may later use the supplementary copies to restore the original contents in the event of data loss. — Wikipedia.

I have been writing PHP codes for some years and understand the important of backing up especially my database on certain intervals (Daily, Weekly or Monthly). However, using a tool like PHPMyadmin, can easily help you with database export and import. But, this is redundant as it’s not automated; that is to say, you need to do that manually.

There are a couple of simple ways to dump your MySQL database in PHP using mysqldump client utility. But, this doesn’t go well for PHP applications on shared hosting.

Simple Backup PHP Library

Simple Backup, is a PHP library for creating backups and restores. It’s a clean, simple and fluent library that requires no dependency e.g mysqldump CLI to help you with backing up your application’s database.

I built this library because I needed to add a backup and restore feature to one simple PHP application which will be hosted on a shared hosting platform. I got some libraries but they couldn’t give me exactly what I wanted.

Creating Backups with the library

To get started, you need to install it using composer. If you don’t have composer, kindly install it on your machine.

composer require coderatio/simple-backup

Exporting

To export or backup your database, do this:

require 'vendor/autoload.php';

use Coderatio\SimpleBackup\SimpleBackup;

// Set the database to backup
$simpleBackup = SimpleBackup::setDatabase(['db_name', 'db_user', 'db_password', 'db_host'])
->storeAfterExportTo('pathtostore', 'file_name (optional)');

Here, we require the composer generated autoload.php file. This file, has the mappings for our SimpleBackup library. Then, we set our database with the ::setDatabase() static method. It takes an array of the database settings. And lastly, we chain the ->storeAfterExportTo() which takes the path you want to export the database in your application and a second optional parameter if you want to set a custom name for the export.

If you want to download the database to your machine instead, you should replace the ->storeAfterExportTo() with ->downloadAfterExport(). Note that this method takes only one parameter which is a custom export name.

See example below:

require 'vendor/autoload.php';

use Coderatio\SimpleBackup\SimpleBackup;

// Set the database to backup
$simpleBackup = SimpleBackup::setDatabase(['db_name', 'db_user', 'db_password', 'db_host'])
->downloadAfterExport($file_name (optional));

Restoring or Importing

To restore or import your exported database, do this:

require 'vendor/autoload.php';

use Coderatio\SimpleBackup\SimpleBackup;

// Set the database to backup
$simpleBackup = SimpleBackup::setDatabase(['db_name', 'db_user', 'db_password', 'db_host (optional)']])
->importFrom('pathtosql_file or sql_contents');

/**
* You can then dump the response like this.
*
* @return object
**/
var_dump($simpleBackup->getResponse());

All that your’e required to do is set the database as earlier explained, then, use the ->importFrom() method. This method, accepts one parameter which is either an SQL file or SQL contents.

Automating with the library

Automating backup process and restore with this library is very simple. You can easily create a PHP script to periodically backup your application database and setup Cron on your hosting account to either do that daily, weekly or monthly.

Summary

From what we’ve outlined so far, this library can backup your database to a directory in your project, download database backup to your machine and easily restore or import the database. The library has some other amazing features like limiting table rows to be exported, adding SQL where clauses to tables before exporting e.t.c.

I invite you to check out the library on my Github account HERE. Don’t forget to give a star if it’s worth it..

Kindly submit issues using the issues tab on github or fork the repo and make contributions.

You may find me on Twitter here.

Thanks for reading and clapping (:

Josiah Ovye Yahaya

Written by

Full Stack Developer. PHP, JavaScript, Python.