Make backup of Large mysql databases

DBCare – Large MySQL DB Backup & Restore

The Situation (large database backup):

I was once stuck in a situation where I have to make backup of a large mysql database (150mb), big enough to crash phpmyadmin and mysql workbench (or mysql gui tools).
However, I resolved that problem by backing up the database in pieces and restoring to my local machine in the same way.
Now, after quite a long time, I have to do this again, and after a lot of searching and googling I found out that one guy at phpclasses had made a class which can make backup in repeated ajax calls backing up only 50,000 statements in one call. I was instantly stuck by this Idea and worked on this class.

The Solution:

So that it can now separately backup tables and views, and also make extended insert statements like phpmyadmin and best of all, gzip the file for download.

The solved problems & errors:

  • php max execution time limit error
  • Lost connection to mysql server during query error
  • mysql max execution time exceeded error
  • mysql server not responding error

So that’s it? backup only?:

The next phase was how to restore the same. This I developed on my own and made a function to restore the database on repeated ajax calls, executing 25,000 statements in one call.

Presenting DB Care:

This way I was able to make a script which can backup and restore databases of virtually any length and will give you a gzipped file on completion.

Features:

  • Currently you can make backup of remote servers only, which is very easy if you have cpanel username and password, as cpanel mysql remote access (only if your hosting provider allows this).
  • The beauty of the script is that its completely ajax based, and allows you to select database and tables before making a backup. Also you can select whether to have the structure only or database, with optional comments and extended inserts.
  • The backup file is downloadable on the same session you created it, and if you logout from the session, the file will be lost.
  • The download link is accessible for 24 hours only, after it will be deleted permanently from the server.
  • Your login credentials are stored temporarily on a file, which is again session based, and is deleted after you press logout button, or if you forget to do so, it will be deleted after 24 hours from the server permanently
  • We do not store username and password for our purpose, but we do log the mysql server name, your ip address, the log time, and the filename you download.

The Design:

  • The site is designed using some small html5 tricks
  • It heavily uses jquery
  • Its Totally Ajax based, and I mean it, totally.
  • line by line, clearly marked notification of success and errors of each mysql statement generated.

The Looks:

The Login Screen
db-care-screen-shot
The Backup UI
db-care-screen-shot-backup-ui
And The Restore UI

db-care-screen-shot-restore-ui

The Flow of Backup:

  • The flow is simple, you first enter your mysql server’s details and username, password, it will direct you to database list
  • selecting database will show a table list
  • selecting table[s] will show you the backup base, where a simple press on backup button will start the backup and a loading image will show.
  • You will be notified about the progress as each line of database backup will be pasted back in the backup box after each ajax request’s completion.
  • Sit back and relax, the ajax call will post itself again unless the backup is completed.
  • A backup completed… notice will appear after completion and surely you can’t miss it.

The Flow of Restore:

  • Restore follows the same procedure as backup, just you don’t have to select tables in it.
  • The restore is also ajax based and reposts itself unless the script file is completely executed.

Limits?

  • Remote Mysql Server Access Only, No Localhost and other items
  • Can Restore only backup files or files present on your session-specific folder, no UI is present for file upload, and for that you have to wait.

Security??

Currently the scripts running on the server are safe from possible SQL Injection Attack.
Suggest me what else you think must be there for security,

You need to test on your local server:

  • Keep in touch, I will soon release the downloadable version, and don’t worry, its under 100 kb in zipped form and under 200kb in total.
  • This script is not a single file, like the blazing fast adminer.php, but still the size is small.
  • I am planning to place a small fee on this script’s download.

What Else? Check it out:

So Enjoy the beauty of ajax-jquery-php-html5-mysql in one place.
Click below to access the site.
DBCare – Large MySQL DB Backup & Restore

1 thought on “Make backup of Large mysql databases

Leave a Reply

Your email address will not be published. Required fields are marked *

*