Browse By

Unable to Use mysqldump on a Shared Host? Here’s an Alternative

Hard Drive Backup

I have used many shared hosting providers throughout my years as a webmaster. Shared hosts have many great points, namely, price, and then they have their major drawbacks. For me, one of the major drawbacks is the limited use of the machine on which your site is hosted.

For instance, I love phpMyAdmin for maintaining and managing my databases, but I am not a fan of the need to log in to backup the databases. The easiest way around that of course would be to execute the mysqldump command that comes bundled with MySQL. Unfortunately, most shared hosts, all that I have dealt with, disable the ability to use exec() or system() with PHP to execute the MySQL native command. If these were available, creating a cron job on a simple PHP file that ran mysqldump would have worked perfectly. However, this wasn't the case. After looking around for a long time at a possible way around this, I came up empty handed. Maybe I didn't look hard enough, or looked in all the wrong places, but I was left with nothing. I needed to back up my databases at regular intervals and found it frustrating to log into phpMyAdmin to do this each time. So I did what I should have done to begin with... wrote my own back-up tool.

It is pretty simple and self-explanatory. I did not create it as a function to be called, although you are more than welcome to do so. The main advantage to calling it, I think, would be to pass variables. For my needs, I simply set it up in the directory. I wanted it to run and back up whichever database needed to be. The script simply backs up the database specified in an sql file located in the same directory as the script itself. The file is named in this format, "DB_Backup_YourDatabase_YYYY-MM-DD.sql"; this could be easily modified to save anywhere you have permissions to save. The code is completely free and you can do what you would like with it. If you are feeling generous, throw a shout out my way or maybe even donate, but neither is not required.

This script was written and used on a server running PHP Version 4.4.2 and MySQL Version 4.1.22.

View Code | Download File



11 thoughts on “Unable to Use mysqldump on a Shared Host? Here’s an Alternative”

  1. Matt S Trout says:

    Wouldn’t it have been simpler to just write a 5 line shell script that called mysqldump?

    You’re already using HTML, CSS, JS and PHP on the project, and if you can use cron presumably some shell already. What’s the harm in a tiny amount -more- shell to do the job? (right tool for, and all that – PHP was never really designed for writing CLI stuff)

  2. Scott Spear says:

    Excellent point Matt. I wasn’t very familiar with shell scripts when I needed this script and just used my understanding of PHP to get the job done.

  3. Pingback: Database Management » Blog Archive » Unable to Use mysqldump on a Shared Host? Here’s an Alternative
  4. Trackback: Database Management » Blog Archive » Unable to Use mysqldump on a Shared Host? Here’s an Alternative
  5. Marcel says:

    Thanks,

    Nice script!
    Me server dont’support mysqldump, so this is a nice solution

  6. Stephen Aiena says:

    Thanks for the script. I have a client whose server doesn’t support mysqldump, so I too needed an alternative.

    I found two weaknesses with the script:

    1 – It determines what fields to encose in single quotes by looking for ‘varchar’ and ‘text’:
    (strstr($columns[$i],”varchar”) || strstr($columns[$i],”text”)) {

    But other field types need quotes. I had to add ‘datetime’:
    (strstr($columns[$i],”varchar”) || strstr($columns[$i],”text”) || strstr($columns[$i],”datetime”) ) {

    I assume other field types (e.g., BLOB) will have problems.

    2 – Many of my fields contain data that has been edited with a rich text editor (FCKeditor). I had to escapethe special characters using mysql_real_escape_string:
    for ($i=0;$i<=count($records)/2;$i++) {
    if ($i < count($records)/2-1) {
    if (strstr($columns[$i],”varchar”) || strstr($columns[$i],”text”) || strstr($columns[$i],”datetime”) ) {
    $contents .= “‘”.mysql_real_escape_string($records[$i]).”‘,”;
    } else {
    $contents .= $records[$i].”,”;
    }
    } else {
    if (strstr($columns[$i],”varchar”) || strstr($columns[$i],”text”) || strstr($columns[$i],”datetime”) ) {
    $contents .= “‘”.mysql_real_escape_string($records[$i]).”‘”;
    } else {
    $contents .= $records[$i].””;
    }
    }
    }

  7. Scott Spear says:

    Stephen,

    Thanks for bringing these points to my attention. I will take a look at the MySQL manual and see which data types I neglected to enclose in single quotes. I didn’t think about it too much because for the database I was testing on, those were the only cases I needed enclosed in single quotes.

    Thanks again for bringing these up and posting your updates. It will help make the script more useful to more people.

  8. pete says:

    really useful, thanks for this.

  9. silverbeat says:

    Hi,

    thanks for your work, but I can’t use it just the way it is for my database. You get the column names of a table by doing:

    $columns = explode(‘,’,$row[“Create Table”]);

    which also picks out indices and additional table information (e.g. charset) which are listed at the end of a SHOW CREATE TABLE result. What’s more, most of my unicode data (containing commas) is not quoted, but there are lots of empty quotes and multiple commas.

    In my case, the provider supports phpMyAdmin so a dump can be created manually. A php solution would be more convenient but also time-consuming. But in case I do adjust your script, I will post it here.

  10. silverbeat says:

    Hi again,

    I found a similar script to this one, written by David Walsh, found at:
    http://davidwalsh.name/backup-mysql-database-php

    That one worked for my database, except that it replaced NULL-values by an empty string. I could easily fix that by inserting a short statement before the value is added to the $return string into Walsh’s script:

    if (is_null($row[$j])) {
    $return .= ‘NULL’;
    } else {
    ……
    }

    It now does also quote numbers, but it works, and you should be able to easily adjust it to the needs of your database.

    I hope that helps anyone, and I also hope that I didn’t offend you by posting another script here.

    silverbeat

  11. Ritwick says:

    Hi Scott,

    Thanks for such great script and ending my Rn’D for backing up MySQL database in shared hosting via php without mysqldump.

    As per my requirement, I’ve added a few lines in your script to implement the –ignore-table option in mysqldump. It is as:

    1) Add the list of tables to be ignored in an array:
    $ignore_tab[0]= ‘tablename_01’;
    $ignore_tab[1]= ‘tablename_02’;
    $ignore_tab[2]= ‘tablename_03’;

    2) Compare the table names with the ignore table array:
    while ($tables = mysql_fetch_array($result))
    {
    if(!in_array($tables[0], $ignore_tab)) //new addition
    $TableList[] = $tables[0];
    }

  12. Gavin Boyd says:

    What would I need to do to modify this script to save as a sqlite database?

Comments are closed.

%d bloggers like this: