How to backup MySQL db using php?

Discussion & Help on SQL and in matters relating to DBMSs such as MySQL and PostgreSQL.

How to backup MySQL db using php?

Postby anggit » Sat Mar 05, 2011 5:02 am

Anyone have coolest tips/tricks to backup mysql db using php? Please post it here. Thanks ;)
anggit
Junior Member
 
Posts: 53
Joined: Tue May 18, 2010 8:26 am

Re: How to backup MySQL db using php?

Postby Max » Sun Mar 06, 2011 12:59 pm

What do you mean by that? What do you want the end result to be?
Max
Administrator
 
Posts: 1027
Joined: Sat Jul 18, 2009 3:17 pm

Re: How to backup MySQL db using php?

Postby anggit » Tue Mar 08, 2011 7:44 am

Like generate .sql or else that can be restored in server. Backup without phpmyadmin & cpanel but with your own php.
anggit
Junior Member
 
Posts: 53
Joined: Tue May 18, 2010 8:26 am

Re: How to backup MySQL db using php?

Postby Max » Tue Mar 08, 2011 7:51 am

Why can't you use cPanel or phpmyadmin?
Max
Administrator
 
Posts: 1027
Joined: Sat Jul 18, 2009 3:17 pm

Re: How to backup MySQL db using php?

Postby anggit » Tue Mar 08, 2011 9:17 am

Can download directly without login first. So can sent them to gmail as backup, etc.
anggit
Junior Member
 
Posts: 53
Joined: Tue May 18, 2010 8:26 am

Re: How to backup MySQL db using php?

Postby Max » Tue Mar 08, 2011 9:22 am

gmail doesn't support large attachments anyway, it's probably not the best way to do it. But it is possible - phpmyadmin does it after all, so you may consider checking out it's source code.
Max
Administrator
 
Posts: 1027
Joined: Sat Jul 18, 2009 3:17 pm

Re: How to backup MySQL db using php?

Postby anggit » Tue Mar 08, 2011 9:40 am

Yes that's just ex. Mr Max. I do not really understand what the language used on phpmyadmin and where is exactly source code located. So i ask here if there's someone have kinda source code like that. Btw where your timezone. Good evening :D
Last edited by anggit on Tue Mar 08, 2011 9:55 am, edited 1 time in total.
anggit
Junior Member
 
Posts: 53
Joined: Tue May 18, 2010 8:26 am

Re: How to backup MySQL db using php?

Postby Max » Tue Mar 08, 2011 9:54 am

As implied my it's name, it is PHP.

http://www.phpmyadmin.net/home_page/downloads.php
Max
Administrator
 
Posts: 1027
Joined: Sat Jul 18, 2009 3:17 pm

Re: How to backup MySQL db using php?

Postby anggit » Tue Mar 08, 2011 10:11 am

Thx. I just found a little phpmyadmin, maybe it's easier http://phpminiadmin.sourceforge.net/ :d
anggit
Junior Member
 
Posts: 53
Joined: Tue May 18, 2010 8:26 am

Re: How to backup MySQL db using php?

Postby anggit » Sun Mar 20, 2011 4:00 pm

Gotcha!
Here is my pure code!
Code: Select all
<?php
/*
Project Name: Cool Backup
Project Description: Just an example of PHP for MySQL backup.
Project URI: http://coolbackup.sourceforge.net
Version: 1.0
(c) 2010-2011 http://www.anggit.com

This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.
*/

$zone = 7; // GMT+7
$file = gmdate('d-m-Y', time() + 3600 * $zone).'.sql'; // Filename

//* Host, User, Password *//
mysql_connect("localhost", "root", "") or die ("Unable to connect to database");

//* Database *//
mysql_select_db("db_name");

$tables = array();
$show = mysql_query("SHOW TABLES");
while($view = mysql_fetch_row($show)) {
$tables[] = $view[0];
}

foreach($tables as $table) {

$sql = mysql_query("SELECT * FROM $table");
$cols = mysql_num_fields($sql);
$rows = mysql_num_rows($sql);
$result .= "DROP TABLE IF EXISTS $table;\n";
$line = mysql_fetch_row(mysql_query("SHOW CREATE TABLE $table"));
$result .= "$line[1];\n";

if ($rows) {
$result .= "INSERT INTO $table VALUES\n";

//* Begin looping rows *//
for($r=0; $r<$rows; $r++) {
$result .= "(";
$lines = mysql_fetch_row($sql);

//* Begin looping cols *//
for($c=0; $c<$cols; $c++) {
$lines[$c] = mysql_real_escape_string($lines[$c]);
$result .= "'$lines[$c]'";
if ($c<($cols-1)) {
$result .= ", ";
}
}
$result .= ")";
if ($r<($rows-1)) {
$result .= ",\n";
}
}
//* End looping cols *//

$result .= ";\n";
}
//* End looping rows *//

}

//* Output *//
header("Content-Type: text/x-sql");
header("Content-Disposition: attachment; filename = $file");
echo $result;
?>

Download: http://master.dl.sf.net/project/coolbac ... backup.zip
Test and Enjoy! :) Thanks.
anggit
Junior Member
 
Posts: 53
Joined: Tue May 18, 2010 8:26 am

Re: How to backup MySQL db using php?

Postby anggit » Sat Jun 18, 2011 11:42 pm

Note: this is only php example. Cannot export high rows. Depend on memory_limit
anggit
Junior Member
 
Posts: 53
Joined: Tue May 18, 2010 8:26 am

Re: How to backup MySQL db using php?

Postby Max » Sun Jun 19, 2011 12:53 am

anggit wrote:Can download directly without login first. So can sent them to gmail as backup, etc.


This actually sounds really bad now that I think about it...anyone will be able to get your database if you have a backup script which doesn't require a login!
Max
Administrator
 
Posts: 1027
Joined: Sat Jul 18, 2009 3:17 pm

Re: How to backup MySQL db using php?

Postby anggit » Sun Jun 26, 2011 11:16 pm

Yes, but we can protect by specifying the IPs who can downloading.
anggit
Junior Member
 
Posts: 53
Joined: Tue May 18, 2010 8:26 am

Re: How to backup MySQL db using php?

Postby Max » Mon Jun 27, 2011 10:45 am

I still think it's better to only back up form cPanel. Backing up is not a simple process, especially with larger sites, that's why there are special processes for doing this - ie. because it can take up a considerable amount of resources, and is a potential security risk if someone ill-intentioned manages to get a backup of your site without your permission.
Max
Administrator
 
Posts: 1027
Joined: Sat Jul 18, 2009 3:17 pm


Return to Database Development

Who is online

Users browsing this forum: No registered users and 1 guest

cron