Solved

backup mysql db like webAdmin

Posted on 2013-01-04
13
303 Views
Last Modified: 2013-01-07
I would like to create a php function to export the structure and data from my mySQL db   and save it as file.sql.

I will then add this function to my list of scheduled tasks

I am using the following to connect to the db:
	function dbConnect()
	{
		$mysql_local = ""; 
		$mysql_db = ""; 
		$db_user = ""; 
		$db_user_pass = "";		
		if(!isset($oConn))
		{
			$oConn = @mysql_pconnect($mysql_local,$db_user,$db_user_pass) or die ("Could not connect to local server");
			mysql_select_db($mysql_db) or die ("Unable to select database");				
		}
	}

	function dbDisconnect()
	{
		if(isset($oConn))
		{
			mysql_close ($oConn);
		}		
	}

Open in new window


Thanks
0
Comment
Question by:prowebinteractiveinc
  • 7
  • 5
13 Comments
 
LVL 23

Expert Comment

by:tailoreddigital
ID: 38745939
0
 

Author Comment

by:prowebinteractiveinc
ID: 38745952
Im getting this error..
There was a warning during the export of dbName to ~/chooseFilenameForBackup.sql

Im looking for it to do exactly what webAdmin does, accept, it writes the contents to a file in a folder on the server.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 38746627
0
 

Author Comment

by:prowebinteractiveinc
ID: 38746778
it looks complicated !
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 38746830
It's not complicated when compared to rebuilding a data base that wasn't backed up!
0
 

Author Comment

by:prowebinteractiveinc
ID: 38748756
Hi Ray. I found the code below on the page of the link you sent me. I see that it connects through root, and takes a copy of all the databases, what if I want just one particular database and dont have root access, but just need my db anyways and to save it to a folder with in the root of the website im working on. can I not just connect to it like I connect to it now to do the mysqldump ? what can I modify in the code below to do exactly what I need ?

Thanks
    $username = "root";
    $password = "password";
    $backup_dir = "/mnt/backup";
    $dump = "/usr/bin/mysqldump";
    $grep = "/bin/grep";
    $gzip = "/bin/gzip";

        // This should not need changing from here

    function sql_dict($sql){
        $x = mysql_query($sql);
        if ($x) return mysql_fetch_assoc($x);
    }
    
    function cleanup($dir){
        $d = dir($dir);
        echo "Path: " . $d->path . "\n";
        while (false !== ($entry = $d->read())) {
            if ($entry=="." or $entry=="..") continue;
            $e = str_replace(".sql.gz","",$entry);
            $x = sql_dict("describe $e");
            if (!$x) {
                print "Removing old backup file [$entry]\n";
                unlink("$dir/$entry");
            }
        }
        $d->close();
    }

    function crc32_file($filename)
    {
          global $gzip;
        $x = exec("$gzip --list --verbose $filename");
        $x = explode(" ",$x);
        return $x[1];
    }

    if (mysql_connect("localhost",$username,$password)) print "Connected.\n";
    else die("Failed to connect to database."); 
    $dbs = mysql_query("show databases");
    if ($dbs) while ($db = mysql_fetch_array($dbs, MYSQL_ASSOC)) {
        $db = $db['Database'];
        if ($db=="information_schema") continue;
        if (mysql_select_db($db)) print "Selected [$db]\n";
        else die("Failed to select db [$db]");
        foreach (array("schema","data") as $pass){
            $sql = mysql_query("show tables");
            $day = date("l");
            if ($pass=="schema") $dir = "/$backup_dir/$db/schema";
            else $dir =  "/$backup_dir/$db/$day";
            if (!file_exists($dir)) system("mkdir -p $dir");
            if (!file_exists($dir)) die("Couldn't Create $dir");
            if ($pass=="data"){
                $latest = "/$backup_dir/$db/latest";
                unlink($latest);
                system("/bin/ln -s \"$dir\" \"$latest\"");
            }            
            cleanup($dir);
            if ($sql) while ($s = mysql_fetch_assoc($sql)) {
                if (!isset($s["Tables_in_{$db}"])) {
                    print "no result";
                    print_r($sql);
                    die();
                }
                $t = $s["Tables_in_{$db}"];
                if (
                 $pass=="schema" ) $data = "--no-data";
                 else $data = "--lock-tables";
                 $tab = $t;
                 $lim = 30;
                 if (strlen($tab)>$lim) $tab = substr($tab,0,$lim-3)."...";
                 while (strlen($tab)<30) $tab .= " ";
                print "BACKUP: $pass : $day : $db : $tab : ";
                if ($pass=="data"){
                    print "Check : ";
                    $check = sql_dict("check table $t");
                    $check = $check['Msg_text'];
                    print "$check : ";
                    if ($check != "OK") {
                        print "Repair";
                        $repair = sql_dict("repair table $t");
                        $repair = $repair['Msg_text'];
                        print " : $repair : ";
                    }
                    if ($day=="Sunday"){
                        // optimize
                        print "Optimize : ";
                        $type = sql_dict("show table status like '$t'");
                        $type = $type['Engine'];
                        if ($type=="MyISAM") sql("optimize table $t");
                        if ($type=="InnoDB") sql("alter table $t engine='InnoDB'");
                    }
                }
                if (isset($argv[1])){
                    print "Skipping dump\n";
                } else {
                    $temp = "/tmp/backup.$t.sql.gz";
                    $out  = "$dir/$t.sql.gz";
                    print "Dump : ";
                    $cmd = "$dump -u$username -p$password $data --quick --add-drop-table $db $t | $grep -v 'Dump completed' | $gzip -n > $temp";
                    system($cmd);
                    print "CRC32 : ";
                    if (!file_exists($out)){
                        print "Saving  : ";
                        $cmd = "/bin/mv $temp $out";
                        system($cmd);
                    } else {
                        $md5  = crc32_file($temp);
                        $nmd5 = crc32_file($out);
                        if ($md5!=$nmd5) {
                            print "Saving  : ";
                            $cmd = "/bin/mv $temp $out";
                            system($cmd);
                        } else {
                            print "Skipped : ";
                            unlink($temp);
                        }
                    }
                    $size = filesize($out);
                    print "[$size]\n";
                }
            }
        }
    }
?>

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 38748862
I don't know.  You would have to go through the code line-by-line.  I would probably look for the part where it finds all of the data bases, and then right after that part I would override the list of data bases with the name of the data base that I want.
0
 

Author Comment

by:prowebinteractiveinc
ID: 38748959
ok, well I have no permissions to the file now. Im going to to use chown but how can I dynamically get my username if I use exec('whoami'); it gives me apache
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 38748967
Maybe a different approach.  Do you have phpMyAdmin installed?
0
 

Author Comment

by:prowebinteractiveinc
ID: 38749098
yes I have it installed.
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 38749111
You can use phpMyAdmin to do backups. I've never tried to automate any processes with it, but there may be some hope ;-)

I can show you a script that will back up a single table.  If you can give it a list of tables (or call it repeatedly passing a single table each time) that might be a workable option.
0
 

Author Comment

by:prowebinteractiveinc
ID: 38749166
OK.  in php my admin when you go to export you can choose to export structure and/or data
then you choose the compression. if no compression is chosen, then it shows the export in a large textarea - this is what im looking for in the end. Im want to save all the tables and data in an uncompresed file. im also having issues with permissions now. as you can see on line 3  the directories are being made. how do I chown and / or chmod for all the parent directories
if ($pass=="schema") $dir = "/$backup_dir/$db/schema";
else $dir =  "/$backup_dir/$db/$day";
if (!file_exists($dir)) system("mkdir -p $dir");
if(!chmod($dir, 0777)){ print "Permissions change error."; };

Open in new window

Thanks
0
 

Author Comment

by:prowebinteractiveinc
ID: 38751113
Ray_Paseur for future reference. the follwing line of code is all that is needed to do what I needed.
exec("mysqldump --user=" . $db_user . " --password=" . $db_user_pass . " --host=" . $mysql_host . " " . $mysql_db . " > " . $dir);

Open in new window


Thanks for your assistance.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now