Avatar of prowebinteractiveinc
prowebinteractiveinc
 asked on

backup mysql db like webAdmin

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
PHP

Avatar of undefined
Last Comment
prowebinteractiveinc

8/22/2022 - Mon
tailoreddigital

prowebinteractiveinc

ASKER
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.
Ray Paseur

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
prowebinteractiveinc

ASKER
it looks complicated !
Ray Paseur

It's not complicated when compared to rebuilding a data base that wasn't backed up!
prowebinteractiveinc

ASKER
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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Ray Paseur

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.
prowebinteractiveinc

ASKER
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
Ray Paseur

Maybe a different approach.  Do you have phpMyAdmin installed?
Your help has saved me hundreds of hours of internet surfing.
fblack61
prowebinteractiveinc

ASKER
yes I have it installed.
ASKER CERTIFIED SOLUTION
Ray Paseur

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
prowebinteractiveinc

ASKER
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
prowebinteractiveinc

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.