Stress-free way to automate MySQL Database with PHP.

Josiah Ovye Yahaya
Apr 2 · 4 min read

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.

So, how do you automate this process in PHP?

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.

Exporting

To export or backup your database, do this:

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:

Restoring or Importing

To restore or import your exported database, do this:

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.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade