Topic: very big database backup problem

Hello

Have you any idea for daily backup my 'tiny' database wink

PunBB 1.2.14
Operating system: Linux
PHP: 5.1.6 - Show info
Accelerator: N/A (?? gzip works fine)
MySQL 5.0.26-log
Rows: 4186412
Size: 187.35 MB

I still can do this with MySQL-Front, but sometimes it exced time limit hmm
When I tried to use DB Management or DB Backup plugin, my server gives me Internal Error hmm (about after 5 seconds)

Some datas from phpinfo()

max_execution_time 30s
max_input_time 600s
memory_limit 20M
safe_mode Off

mysql.connect_timeout 60s

YonasH's repository + Extensions Directory = PunBB Extensions Online Library (in progress....)

Away. I will be back soon.

Re: very big database backup problem

I would use mysqldump to generate backups for a database that size

Re: very big database backup problem

it works smile

I made backup.php file:

<?
system("mysqldump --host=myhost --port=myport --user=myuser --password=mypass mydatabase > myfile.sql");
echo "done";
?>

How to backup only selected tables? (I don't need to backup *search* tables)
Can I do this using mysqldump?
Just write yes/no wink I'll find solution and write it here smile

YonasH's repository + Extensions Directory = PunBB Extensions Online Library (in progress....)

Away. I will be back soon.

Re: very big database backup problem

Yes tongue

5 (edited by YonasH 2007-02-10 12:59)

Re: very big database backup problem

Smartys wrote:

Yes tongue

Thank you for you help tongue

backup.php

<?
echo "Database backup....<br>";
system("mysqldump --host=myhost --port=myport --user=myuser --password=mypass --quick --ignore-table=mybase.(prefix)search_cache --ignore-table=mybase.(prefix)search_matches --ignore-table=mybase.(prefix)search_words --ignore-table=mybase.(prefix)online mybase > myfile.sql");
system("zip myfile.zip myfile.sql");
echo "<br>Done.";
?>

I'll build plugin for my self, and then give it to you all smile

YonasH's repository + Extensions Directory = PunBB Extensions Online Library (in progress....)

Away. I will be back soon.

6 (edited by YonasH 2007-02-10 18:32)

Re: very big database backup problem

yonash wrote:

I'll build plugin for my self, and then give it to you all smile

I've done it smile

Please help me to check language. My english isn't great.

<?php
/***********************************************************************
  
  Copyright (C) 2007 YonasH (yonash@yonash.pl)

  This software 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 software 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.

  You should have received a copy of the GNU General Public License
  along with this program; if not, write to the Free Software
  Foundation, Inc., 59 Temple Place, Suite 330, Boston,
  MA  02111-1307  USA

************************************************************************/

// Make sure no one attempts to run this script "directly"
if (!defined('PUN'))
    exit;

// Tell admin_loader.php that this is indeed a plugin and that it is loaded
define('PUN_PLUGIN_LOADED', 1);
define('PLUGIN_VERSION', '1.0');

// If you want to backup search tables as default
//define('PUN_BACKUP_SEARCH', 1);

// If you want to backup online table as default
//define('PUN_BACKUP_ONLINE', 1);

// Default compression 0 - none ; 1 - zip ; 2 - gzip
define('PUN_BACKUP_COMP', 1);

// Default backup dir
define('PUN_BACKUP_DIR', 'backup');

//
// Increase maximum execution time, but don't complain about it if it isn't
// allowed.
//
@set_time_limit(0);

if (isset($_POST['backup']))
{
    $skip_tables = (!empty($_POST['skip_table'])) ? ($_POST['skip_table']) : array();
    $compress = (isset($_POST['compression'])) ? ($_POST['compression']) : 'none';
    $backup_host = (strpos($db_host,':')) ? substr($db_host,0,strpos($db_host,':')) : $db_host;
    $backup_port = (strpos($db_host,':')) ? substr($db_host,strpos($db_host,':')+1,255) : '3306';
    $backup_file = PUN_BACKUP_DIR."/";
    $backup_file .= ($db_prefix) ? $db_prefix : $db_name."_";
    $backup_file .= date('ymd');
    
    $skip_temp = '';
    for($i = 0; $i < count($skip_tables); $i++)
        $skip_temp .= "--ignore-table=".$db_name.".".$skip_tables[$i]." ";
    
    if(system("mysqldump --host=".$backup_host." --port=".$backup_port." --user=".$db_username." --password=".$db_password." --quick ".$skip_temp.$db_name." > ".$backup_file.".sql",$rtrn) === false)
        message('Shell mysqldump error: '.$rtrn);
    
    if ($compress == 'zip')
        if(system("zip -q ".$backup_file.".zip ".$backup_file.".sql",$rtrn) === false)
            message('Shell zip error: '.$rtrn);

    if ($compress == 'gzip')
        if(system("gzip ".$backup_file.".gz ".$backup_file.".sql",$rtrn) === false)
            message('Shell gzip error: '.$rtrn);
        
    if ($compress != 'none')
        system("rm ".$backup_file.".sql",$rtrn);

generate_admin_menu($plugin);
?>
    <div class="block">
        <h2><span>Database Shell Backup - v<?php echo PLUGIN_VERSION ?></span></h2>
        <div class="box">
            <div class="inbox">
                <p>Database backup successed.</p>
            </div>
        </div>
    </div>
<?php
}
else
{
generate_admin_menu($plugin);
?>
    <div class="block">
        <h2><span>Database Shell Backup - v<?php echo PLUGIN_VERSION ?></span></h2>
        <div class="box">
            <div class="inbox">
                <p>Lets you perform complete database backup. You must have permit to use PHP "system" command.<br />Created by: YonasH (with some use of DB Management plugin code)</p>
            </div>
        </div>
    </div>
    <div class="blockform">
        <h2 class="block2"><span>Database Backup</span></h2>
        <div class="box">
            <form method="post" action="<?php echo $_SERVER['REQUEST_URI'] ?>">
                <div class="inform">
                    <fieldset>
                        <legend>Backup options</legend>
                        <div class="infldset">
                            <p>Here you can back up all your PunBB-related data. If your server supports it you may also compress the file to reduce its size before download.</p>
                            <table cellspacing="0">
                                <tr>
                                    <th scope="row">Select tables to SKIP</th>
                                    <td>
<?php
$sql = 'SHOW TABLE STATUS';
if (!$result = $db->query($sql))
{
    message('Tables error');
}
//autoskip some tables
$pun_skip_tables = array();
if (!defined('PUN_BACKUP_SEARCH'))
    $pun_skip_tables = array('search_cache', 'search_matches', 'search_words');
if (!defined('PUN_BACKUP_ONLINE'))
    $pun_skip_tables[] = 'online';

//default compression metod autocheck
$comp_temp = array();
$comp_temp[PUN_BACKUP_COMP] = ' checked="checked"';

while ($row = $db->fetch_assoc($result))
{
    $name_tabl = $row['Name'];
    $name_temp = ($db->prefix) ? substr($name_tabl,strlen($db->prefix),255) : $name_tabl; //name without prefix

    if (in_array($name_temp,$pun_skip_tables))
        echo "\t\t\t\t\t\t\t\t\t\t".'<input name="skip_table[]" type="checkbox" value="'.$name_tabl.'" checked="checked" /> '.$name_tabl.'<br />'."\n";
    else
        echo "\t\t\t\t\t\t\t\t\t\t".'<input name="skip_table[]" type="checkbox" value="'.$name_tabl.'" /> '.$name_tabl.'<br />'."\n";
}
?>
                                    </td>
                                </tr>
                                <tr>
                                    <th scope="row">Compression type</th>
                                    <td>
                                        <input type="radio" name="compression" value="none"<?php echo $comp_temp[0] ?> /> None<br />
                                        <input type="radio" name="compression" value="zip"<?php echo $comp_temp[1] ?> /> Zip<br />
                                        <input type="radio" name="compression" value="gzip"<?php echo $comp_temp[2] ?> /> Gzip<br />
                                    </td>
                                </tr>
                            </table>
                        </div>
                    </fieldset>
                </div>
            <p class="submitend"><input type="submit" name="backup" value="Start backup" class="mainoption" /></p>
            </form>
        </div>
    </div>
<?php
}
YonasH's repository + Extensions Directory = PunBB Extensions Online Library (in progress....)

Away. I will be back soon.

Re: very big database backup problem

i think u need a

?>

at the end of that smile

~thegleek

Re: very big database backup problem

No need for that, normally smile

Re: very big database backup problem

elbekko wrote:

No need for that, normally smile

seriously? since when? the 5,000 php files i got from every project you name out here has the ?> ending to it.. it closes off a php code section. it's a proper way to code... its SYNTAX dude...

~thegleek

Re: very big database backup problem

No, Bekko is right.
At the end of a PHP file there's no need to have a closing PHP tag. It's optional. The reason for not having it is that if you accidentally put a space outside of the PHP tag, it can cause issues if you haven't sent headers, cookies, etc yet.
config.php doesn't use a closing tag for that reason wink

Re: very big database backup problem

Smartys wrote:

No, Bekko is right.
At the end of a PHP file there's no need to have a closing PHP tag. It's optional. The reason for not having it is that if you accidentally put a space outside of the PHP tag, it can cause issues if you haven't sent headers, cookies, etc yet.
config.php doesn't use a closing tag for that reason wink

i must have some crystal-methafied version of punbb then.. cuz my config shows:

<?php
        $db_type = 'mysql';
        $db_host = 'xxx';
        $db_name = 'xxx';
        $db_username = 'xxx';
        $db_password = 'xxx';
        $db_prefix = 'xxx';
        $p_connect = false;

        $cookie_name = 'xxx';
        $cookie_domain = 'xxx';
        $cookie_path = 'xxx';
        $cookie_secure = 0;
        $cookie_seed = 'xxx';

        define('PUN', 1);
?>

take special note on the very -last- line of that...

~thegleek

Re: very big database backup problem

You must've added that yourself then (or some silly auto-completing IDE).

13 (edited by thegleek 2007-02-11 23:51)

Re: very big database backup problem

elbekko wrote:

You must've added that yourself then (or some silly auto-completing IDE).

k so you got me on that one... according to the install.php:

        /// Display config.php and give further instructions
        $config = '<?php'."\n\n".'$db_type = \''.$db_type."';\n".'$db_host = \''.$db_host."';\n"
.'$db_name = \''.$db_name."';\n".'$db_username = \''.$db_username."';\n".'$db_password = \''
.$db_password."';\n".'$db_prefix = \''.$db_prefix."';\n".'$p_connect = false;'."\n\n"
.'$cookie_name = '."'punbb_cookie';\n".'$cookie_domain = '."'';\n".'$cookie_path = '."'/';\n"
.'$cookie_secure = 0;'."\n".'$cookie_seed = \''.substr(md5(time()), -8)."';\n\ndefine('PUN', 1);";

still makes no sense to not END something when you start it... its proper syntax ettiquette!

its like not ending an XML tag.. or not ending a print "statement

or not ending a sql query delete from users where id=1 (that is inside mysq cli (not outside), you always need a ; to end it

so many examples! what about not closing a <a href=""> link without a </a> ??

~thegleek

Re: very big database backup problem

still makes no sense to not END something when you start it... its proper syntax ettiquette!

No, opening tags do not always need closing tags. If you have an issue with that, take it up with the PHP developers.

its like not ending an XML tag.. or not ending a print "statement

Yes, except syntax there demands that you do things a certain way. PHP does not dictate that a script MUST end in HTML mode as opposed to PHP mode.

or not ending a sql query delete from users where id=1 (that is inside mysq cli (not outside), you always need a ; to end it

Not even the same thing, there's no opening "tag" to close

so many examples! what about not closing a <a href=""> link without a </a> ??

How about a line break? In HTML 4.01 you don't need a closing tag for <br>. You need the tag to be self closing in XHTML due to the whole XML thing wink

Re: very big database backup problem

argh! of all the 7+ years i've been coding in php, i have always used an ending ?>

i really can't come up with one single reason why it's beneficial to not end a <?php with one. /me sighs.

~thegleek

Re: very big database backup problem

i really can't come up with one single reason why it's beneficial to not end a <?php with one

Smartys wrote:

At the end of a PHP file there's no need to have a closing PHP tag. It's optional. The reason for not having it is that if you accidentally put a space outside of the PHP tag, it can cause issues if you haven't sent headers, cookies, etc yet.
config.php doesn't use a closing tag for that reason

17 (edited by YonasH 2007-02-12 00:40)

Re: very big database backup problem

There is so many places where you can talk about "?>" wink
Maybe someone will answer my question. Have my plugin correct english?


btw. I do not use "?>" wink

YonasH's repository + Extensions Directory = PunBB Extensions Online Library (in progress....)

Away. I will be back soon.

Re: very big database backup problem

yonash wrote:

There is so many places where you can talk about "?>" wink
Maybe someone will answer my question. Have my plugin correct english?


btw. I do not use "?>" wink

Very good, I only noticed one thing

 <p>Lets you perform complete database backup. You must have permit to use PHP "system" command.<br />Created by: YonasH (with some use of DB Management plugin code)</p>

should be

 <p>Lets you perform a complete database backup. You must have permission to use PHP's "system" command.<br />Created by: YonasH (with some use of DB Management plugin code)</p>

Re: very big database backup problem

This sounds like an excellent tool!   I like the idea of backing up the DB w/out the search table (since this is such a large portion of the DB and can be re-created easily).   

I hope to see further tweaking refining of this.  Keep up the good work!

Rob Ludlow 
www.Nifty-Stuff.com - Repository of all Stuff Nifty!
www.reviewum.com - Professor Ratings + Teacher Reviews