UBB.Developers Forums UBB.threads 6 V5.php Modifications Auto MYSQL Backup

Hello folks,

didn't see anything to do this so I wrote a small script that automatically dumps the database to a file.

you just need to download attachment, rename to PHP, edit and replace path to mysqldump, backup folder and format of filename.

Then simply copy to your admin folder and call it from there.

don't forget to create the backup dir for added security I made mine above webroot.

this file can also be called by cron.

Feedback is welcome

Cheers

Charles

Thanks! been wanting to replace my cgi script.

well already working on the restore part

I might also add a backup of the main threads directory since some files in it are important

OK just did a new version that enables putting a backup back

tried to keep it simple, for the moment backup has to be on server will try to see how to upload it first.

If a PHP guru could check my code to see if I didn't mess anything, that would be great

Charles

Nice one.

This would be one feature that would be a excellent addition to the core package, as loosing the database has some fairly major consequences to the running of threads...

Thanks again.

The script looks useful.

I spent some time reading the MySQL manual to figure out how to do backups, and came up with a few issues which may affect your script. If you're a MySQL guru, perhaps you could look at my post and give me your opinion.

your idea is good, but there is a real problem I think. You are gonna dump the WHOLE database at one time. This could crash if
a) the isp has memory restrictions for scripts
b) the isp has time restrictions for script

My table w3t_Posts has 30MB and every time I wanna backup with such a funktion the script dies with an error like "Mysqldump rans out of memory"

So I wrote a recursivly script to do the work and one which do the work for at least one table.

I´ve to make some changes to it and the I share the scripts this weekend.

OK cola I added a function to the script:

it will first store all tables in an array and then will dump them one by one.

tell my how its works for you

Dave I replied to your post

I saw the reply. Thanks for answering my questions.

OK updated version (again)

added the -opt tag for better dump

restore now uses all files in a specified dir to copy into database.

Feedback is welcome since I am not sure my loops are very well coded

1) Shouldn't "-opt" be "--opt"?

2) You could optimize the loops slightly by removing processing that doesn't need to be done on every loop iteration, but I doubt if it really matters here, since there are only 17 tables.

code:

$COMMAND_DO = $PATHTOMYSQLDUMP . "mysqldump -opt -h" . $config['dbserver']
    . " -u" . $config['dbuser'] . " -p" . $config['dbpass']
    . " " . $config['dbname'] . " ";

for ($i=0; $i<count($tb_names); $i++) {
    $table_name = $tb_names[$i];
    echo "$table_name<br />";
    $COMMAND_DO .= "$table_name > $DIRNAME.$table_name.sql";

---

Also (since you asked for feedback on the loops ), my preference would be to use foreach, since the index isn't needed inside the loop here:

code:

$COMMAND_DO = $PATHTOMYSQLDUMP . "mysqldump --opt -h" . $config['dbserver']
    . " -u" . $config['dbuser'] . " -p" . $config['dbpass']
    . " " . $config['dbname'] . " ";
foreach ($tb_names as $table_name) {
    echo "$table_name<br />";
    $COMMAND_DO .= "$table_name > $DIRNAME.$table_name.sql";

---

Nice work guys, this would work on v6 as well, eh? If I remember right, v6 added a table over previous versions tho

Dave, I tried to use your code but the script went berserk, something must be wrong.

Allen, this will work on any version but I am still unsure of the limitations that could be caused by a big table.

here is mine. runs through a cron job.

It creates SEVERAL backups, because often there is a corruption and it already is in 1-2 backups before I discover it. I back up every 12 hours

[root@a3p mario]# crontab -l
# DO NOT EDIT THIS FILE - edit the master and reinstall.
# (/tmp/crontab.7136 installed on Sun Jul 7 09:50:34 2002)
# (Cron version -- $Id: crontab.c,v 2.13 1994/01/17 03:20:37 vixie Exp $)
28 */12 * * * /root/cron/cron_mysqlbackup

[root@a3p mario]#


If you have passwords, of course, add the passwords
Note the NICE!!

Question:
what happens to writes during backup? mysql can back up for half an hour during writes? If it blocks the database, I might as well remove the nice..


[root@a3p mario]# cat /root/cron/cron_mysqlbackup
mv /opt2/backups_mysql/ac2backup.gz.13 /opt2/backups_mysql/ac2backup.gz.14
mv /opt2/backups_mysql/ac2backup.gz.12 /opt2/backups_mysql/ac2backup.gz.13
mv /opt2/backups_mysql/ac2backup.gz.11 /opt2/backups_mysql/ac2backup.gz.12
mv /opt2/backups_mysql/ac2backup.gz.10 /opt2/backups_mysql/ac2backup.gz.11
mv /opt2/backups_mysql/ac2backup.gz.9 /opt2/backups_mysql/ac2backup.gz.10
mv /opt2/backups_mysql/ac2backup.gz.8 /opt2/backups_mysql/ac2backup.gz.9
mv /opt2/backups_mysql/ac2backup.gz.7 /opt2/backups_mysql/ac2backup.gz.8
mv /opt2/backups_mysql/ac2backup.gz.6 /opt2/backups_mysql/ac2backup.gz.7
mv /opt2/backups_mysql/ac2backup.gz.5 /opt2/backups_mysql/ac2backup.gz.6
mv /opt2/backups_mysql/ac2backup.gz.4 /opt2/backups_mysql/ac2backup.gz.5
mv /opt2/backups_mysql/ac2backup.gz.3 /opt2/backups_mysql/ac2backup.gz.4
mv /opt2/backups_mysql/ac2backup.gz.2 /opt2/backups_mysql/ac2backup.gz.3
mv /opt2/backups_mysql/ac2backup.gz.1 /opt2/backups_mysql/ac2backup.gz.2
mv /opt2/backups_mysql/ac2backup.gz /opt2/backups_mysql/ac2backup.gz.1


/bin/nice /usr/bin/mysqldump db4 >/opt2/backups_mysql/ac2backup


/bin/nice /bin/gzip /opt2/backups_mysql/ac2backup