SQL database tool

Sethioz · **Posted:** 22 May 2010 20:11

does anybody know a tool that can make a backup of my SQL database ? something that logs into my database using my username and password and automatically retreives the full backup and restores when necessary, so i don't have to do it manually by logging into myphpadmin and export.

SomaFM · **Joined:** 16 Aug 2007 06:25 **Posts:** 367

mysqldump is a command that will backup the database to a file.

Here is what I use:
mysqldump --opt --quick --compact -h$dbhost -u$dbuser -p$dbpass $dbname >$dumpfilepath

And of course replace the variables with your database info and backup file path. From there, you can gzip it, and upload it to an ftp server, etc. And you can setup your script as a cron task to make the backups automatic.

Sethioz · **Posted:** 24 May 2010 00:12

i don't have a server, it is web-hosting with included myphpadmin, i can't run any commands there, only way is thru my site (like some e107 plugin, which i tried and none worked) or with a tool that connects to myphpadmin, logs in and makes backup.

im not quite sure, but it should be able to execute this thru a php ?!

SomaFM · **Joined:** 16 Aug 2007 06:25 **Posts:** 367

Try exec (http://php.net/manual/en/function.exec.php)

Sethioz · **Posted:** 25 May 2010 15:52

too complicated. not that i cant handle it, but its the webhosting, it does not allow most of the tools, even e107 database backup tool did not work, gave some error. it uses old php4 version and nothing works there. i just want a simple external tool or internal that makes the backup locally (so i download later from ftp).

if you know a simple php script that would automatically dump the database locally, that would be great. one of the e107 plugins should email the database every day, but it gives same annoying error, its something about memory, there is nothing i can do about it, its the hosting.

i have not tested, but using logic, what if i would upload that file to some other site/forum, using some upload box, like attachment or whatever, that would leave every website very vulnerable ?! permissions ? or am i missing something else here.

SomaFM · **Joined:** 16 Aug 2007 06:25 **Posts:** 367

Code:

$dbhost = 'localhost';
$dbuser = 'username';
$dbpass = 'password';
$dbname = 'databasename';
$date = date("mdy-hia");

//where to put the backup
$filename = ("db-" . $date . ".sql");
$dumpfilepath = ("/home/user/backups/" . $filename);

//backing it up
exec("mysqldump --opt --quick --compact -h$dbhost -u$dbuser -p$dbpass $dbname >$dumpfilepath");

//compress your backup
exec("gzip -qf " . $dumpfilepath);

This will dump your database to a file, then gzip it for compression. All locally too, just change the backup path to what you wish. getcwd() is a useful php function if you don't know the full folder path. You can also take the gzipping out if you desire. If your webhost doesn't allow this, then it might be time to switch webhosts. I pay $6 a month and I have tons of control over my stuff. Though if it's free... then there you go. Couldn't hurt contacting them to see if they will open your access to let you run the above script.

aluigi · **Posted:** 26 May 2010 08:51

search the keyword:
mysqldump php
on google and you can find all the solutions that don't use mysqldump but simply the php code

Sethioz · **Posted:** 26 May 2010 22:01

lil bit confused how to run it, should i use another php file to access it or directly by going to that file, if that's the case, no way, because if other ppl are able to access it, they would get my database user and pass.

good Luigi, I just couldn't come up with the right word to search, i will look into it.

also that aint my hosting, im not paying for it, friend is hosting it for me and he is always bitching when i ask something, he has no problem with the site itself, but he always whines over those things, when i ask him to contact hosting or something. as long as my site runs more or less fine, i cant bother either, but database is other thing.

SomaFM · **Joined:** 16 Aug 2007 06:25 **Posts:** 367

Nobody can read the actual php code from the web, that's not how it works. If someone has the appropriate file permissions on your server, then yes, they will be able to open the file and read it, but if that ends up being the case you have bigger problems than someone knowing your database password.

Though if they know the link, they can run your backups. So make the link something that will never be guessed, or put the php file in a non public folder and run the script with cron. Cron is probably the preferred method. You may want to check into your own host (like surpasshosting.com, which I use). They have very cheap shared-hosting packages and offer tons of control to do what I've previously talked about.

aluigi · **Posted:** 28 May 2010 09:53

or maybe add a simple password check like:

Code:

<html>
<?php
    $mypwd = strip_tags(html_entity_decode($_GET["mypwd"]));

    if($mypwd && (strcmp($mypwd, "myrealpassword"))) {
        // put your code here
    } else {
        echo("wrong password");
    }
?>
</html>

Sethioz · **Posted:** 28 May 2010 18:56

nah Soma, i can't do any of that. if it would be in "private" folder, i cant run it again, unless internally. one way i tought of, is to let another file execute it and set .htaccess to deny. so from web you cant access it even with link.

Ill test your method Luigi, but im not so good with php hehe.

EDIT:

eem Luigi, how do i enter password ? it never asks for it and i cant put it into URL ?! because then i would have to put username too, which is not required.

EDIT:

Soma, thx, it works perfectly, i protected it with .htaccess for now, but what am i missing here guys ? why i cant see source code of it, when i go to it ?? what prevents me from loading the php code ? just in case i will deny access to it anyways, but now another request.

how can i make it execute like every 24h by overwriting the current db file ?
let me be more precise, what im planning, is to put a simple redirect code into my index.php, which will execute it when index is visited, but i only want it to be executed once in every 24h, so if index.php is visited more, then it will be ignored.

Sethioz · **Posted:** 28 May 2010 19:28

now i made chaos hihi :)

Code:

<?php


function logIP() { 
     $ipLog="e107_logs/database_backup.html"; 

     $register_globals = (bool) ini_get('register_gobals');
     if ($register_globals) $ip = getenv(REMOTE_ADDR);
     else $ip = $_SERVER['REMOTE_ADDR'];
     $date=date ("l dS of F Y h:i:s A");
     $log=fopen("$ipLog", "a+");

     if (preg_match("/\bhtm\b/i", $ipLog) || preg_match("/\bhtml\b/i", $ipLog)) {
          fputs($log, "IP: $ip - Date: $date<br>");
     } else fputs($log, "IP: $ip - Date: $date\n");
     fclose($log);
}
logIp();

require_once('/!database_backups/php_database_dump.php');


?>

..but obviously it will not work, because php_database_dump.php file is protected with .htaccess, deny all. so how can i make another php file execute it ? if i get this working thru another page, i would make menu for myself on my site, so i can just back it up with a single click.

EDIT:

fixed, i made chaos as i said, php script cannot be accessed if there is no print command, didnt know it :) here's the full script i am using right now.

Code:

<?php

function logIP() { 
     $ipLog="e107_logs/database_backup.html"; 

     $register_globals = (bool) ini_get('register_gobals');
     if ($register_globals) $ip = getenv(REMOTE_ADDR);
     else $ip = $_SERVER['REMOTE_ADDR'];
     $date=date ("l dS of F Y h:i:s A");
     $log=fopen("$ipLog", "a+");

     if (preg_match("/\bhtm\b/i", $ipLog) || preg_match("/\bhtml\b/i", $ipLog)) {
          fputs($log, "IP: $ip - Date: $date<br>");
     } else fputs($log, "IP: $ip - Date: $date\n");
     fclose($log);
}
logIp();

$dbhost = 'localhost';
$dbuser = 'database_user';
$dbpass = 'database_password';
$dbname = 'database_name';
$date = date("mdy-hia");

//where to put the backup
$filename = ("db-" . $dbname . ".sql");
$dumpfilepath = ("/home/sites/mysite.com/public_html/" . $filename);

//backing it up
exec("mysqldump --opt --quick --compact -h$dbhost -u$dbuser -p$dbpass $dbname >$dumpfilepath");

//compress your backup
exec("gzip -qf " . $dumpfilepath);

?>

yes, it logs ip too, im paranoid sometimes and want to know if somebody have accessed it without me knowing.
thanks both of you, works fine. just trying to get it working so it makes it once per 24h, otherwise its perfect now.

Sethioz · **Posted:** 28 May 2010 21:35

i modified it myself and now it works even better :) now it doesn't even need password user user, i just used this

Code:

require_once('class2.php');

$dbuser              = $mySQLuser;
$dbpwd                  = $mySQLpassword;
$dbname              = $mySQLdefaultdb;
$dbhost              = $mySQLserver;

but im sure that it only works for e107, in other site systems, must be included the file that handles other functions.

Sethioz · **Posted:** 15 Aug 2010 12:32

Soma can you post me a detailed example of cron command to make a backup ?
please include username and password (just example ones, like yourpass and youruser).

SomaFM · **Joined:** 16 Aug 2007 06:25 **Posts:** 367

I would imagine the command in my first post would do the trick:

mysqldump --opt --quick --compact -h localhost -u username -p password databasename > /home/user/mysqlbackups/backup.sql

Might require some tweaking. So to back it up every night you might put this into your crontab:

0 0 * * * mysqldump --opt --quick --compact -h localhost -u username -p password databasename > /home/user/mysqlbackups/backup.sql

I do it through a php script so it may be a little different. I run a php script through cron, not the actual command above.

Sethioz · **Posted:** 19 Aug 2010 22:37

because of gay hosting my site is down atm (idiots couldnt confirm payment). so i cant test atm, but it uses cpanel and there is special cron jobs, i dont really understand how you can do that thru php script.

do i use the full command you put ? including ">" (arrow) ?

SomaFM · **Joined:** 16 Aug 2007 06:25 **Posts:** 367

Yes. I run it through a php script exactly like I showed in my example above.

Sethioz · **Posted:** 28 Aug 2010 19:20

I tested, it makes a blank file only, does not grab the actual database.
any suggestions ?
here's how it looks like

Luigi Auriemma

SQL database tool