I have used many shared hosts 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.
| 114 Readers
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)
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.
[...] Read the rest of this great post here [...]
Thanks,
Nice script!
Me server dont’support mysqldump, so this is a nice solution
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].”";
}
}
}
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.