Luigi Auriemma

aluigi.org (ARCHIVE-ONLY FORUM!)
It is currently 19 Jul 2012 14:27

All times are UTC [ DST ]





Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 18 posts ] 
Author Message
 Post subject: SQL database tool
PostPosted: 22 May 2010 20:11 

Joined: 24 Sep 2007 02:12
Posts: 1114
Location: http://sethioz.co.uk
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.


Top
 Profile  
 
 
 Post subject: Re: SQL database tool
PostPosted: 23 May 2010 18:42 

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.


Top
 Profile  
 
 Post subject: Re: SQL database tool
PostPosted: 24 May 2010 00:12 

Joined: 24 Sep 2007 02:12
Posts: 1114
Location: http://sethioz.co.uk
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 ?!


Top
 Profile  
 
 Post subject: Re: SQL database tool
PostPosted: 25 May 2010 01:40 

Joined: 16 Aug 2007 06:25
Posts: 367
Try exec (http://php.net/manual/en/function.exec.php)


Top
 Profile  
 
 Post subject: Re: SQL database tool
PostPosted: 25 May 2010 15:52 

Joined: 24 Sep 2007 02:12
Posts: 1114
Location: http://sethioz.co.uk
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.


Top
 Profile  
 
 Post subject: Re: SQL database tool
PostPosted: 26 May 2010 02:46 

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.


Top
 Profile  
 
 Post subject: Re: SQL database tool
PostPosted: 26 May 2010 08:51 

Joined: 13 Aug 2007 21:44
Posts: 4068
Location: http://aluigi.org
search the keyword:
mysqldump php
on google and you can find all the solutions that don't use mysqldump but simply the php code


Top
 Profile  
 
 Post subject: Re: SQL database tool
PostPosted: 26 May 2010 22:01 

Joined: 24 Sep 2007 02:12
Posts: 1114
Location: http://sethioz.co.uk
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.


Top
 Profile  
 
 Post subject: Re: SQL database tool
PostPosted: 28 May 2010 05:39 

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.


Top
 Profile  
 
 Post subject: Re: SQL database tool
PostPosted: 28 May 2010 09:53 

Joined: 13 Aug 2007 21:44
Posts: 4068
Location: http://aluigi.org
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>


Top
 Profile  
 
 Post subject: Re: SQL database tool
PostPosted: 28 May 2010 18:56 

Joined: 24 Sep 2007 02:12
Posts: 1114
Location: http://sethioz.co.uk
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.


Top
 Profile  
 
 Post subject: Re: SQL database tool
PostPosted: 28 May 2010 19:28 

Joined: 24 Sep 2007 02:12
Posts: 1114
Location: http://sethioz.co.uk
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.



Top
 Profile  
 
 Post subject: Re: SQL database tool
PostPosted: 28 May 2010 21:35 

Joined: 24 Sep 2007 02:12
Posts: 1114
Location: http://sethioz.co.uk
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.


Top
 Profile  
 
 Post subject: Re: SQL database tool
PostPosted: 15 Aug 2010 12:32 

Joined: 24 Sep 2007 02:12
Posts: 1114
Location: http://sethioz.co.uk
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).


Top
 Profile  
 
 Post subject: Re: SQL database tool
PostPosted: 15 Aug 2010 20:15 

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.


Top
 Profile  
 
 Post subject: Re: SQL database tool
PostPosted: 19 Aug 2010 22:37 

Joined: 24 Sep 2007 02:12
Posts: 1114
Location: http://sethioz.co.uk
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) ?


Top
 Profile  
 
 Post subject: Re: SQL database tool
PostPosted: 21 Aug 2010 09:54 

Joined: 16 Aug 2007 06:25
Posts: 367
Yes. I run it through a php script exactly like I showed in my example above.


Top
 Profile  
 
 Post subject: Re: SQL database tool
PostPosted: 28 Aug 2010 19:20 

Joined: 24 Sep 2007 02:12
Posts: 1114
Location: http://sethioz.co.uk
I tested, it makes a blank file only, does not grab the actual database.
any suggestions ?
here's how it looks like


Attachments:
cPanel X_1283019559250.png
cPanel X_1283019559250.png [ 5.42 KiB | Viewed 809 times ]
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 18 posts ] 

All times are UTC [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for: