Page 1 of 1
Creating a mysql backup job
Posted: Mon Feb 07, 2005 10:15 am
by Burrito
I currently have a scheduled task (using windows task manager) to open up an instance of IE that opens a php page which creates a batch file (daily). I then have another scheduled task that runs that batch file (about 5 mins after the first one runs) which does a dump of my database to an .sql file which I then store offline.
Can anyone suggest a different (better?) method of getting backups of my mysql db (something that is free).
as mentioned this is on a 'doze box with mysql 4.1 and php 4.x.x.x.
thx,
Burr
Posted: Mon Feb 07, 2005 10:23 am
by feyd
run mysqldump via exec() or siblings on a CLI php through scheduled tasks.
There's also the possibility to create an actual application that makes the console call so no window pops on screen, if such a thing is needed.
Posted: Mon Feb 07, 2005 11:09 am
by djot
Posted: Mon Feb 07, 2005 3:05 pm
by Burrito
once again, Feyd saved the day.
here's what I ended up with:
Code: Select all
<?
$now = date("Y-m-d",time());
shell_exec("s:\\mysql\\bin\\mysqldump -u root -pMyPass mydatabase > s:\\backup"e;.$now."-mydatabase-data.sql");
echo "backup has been done";
shell_exec("tar -cf ".$now."-mydatabase-data.tar ".$now."-mydatabase-data.sql");
shell_exec("gzip -9 ".$now."-mydatabase-data.tar");
unlink($now."-mydatabase-data.sql");
?>
I'm running that from task manager
will back up everynight and compress the living hell out of it.
I must say something here, Feyd went above and beyond the call of duty to assist with this one...even called me on the phone.
mucho kudos Feyd...thx again!
Burr
Posted: Mon Feb 07, 2005 8:03 pm
by Burrito
I jumped the gun on this one:
I have two probs with this schema:
1) it seems to be processing all of the commands at once and not waiting until the first command(s) are finished before executing them. In other words it starts the mysql dump, then immediately tars my file, and gzips it...this worked fine before because I wasn't dumping much data, but when I just tried it on a production environemnt with data ~500mb it does it all at once and therefore creates my gzip file with no data (or very little data)
2) this might be somewhat of a problem with number 1: I get an error that says:
D:\backup>php backup.php
Content-type: text/html
X-Powered-By: PHP/4.3.4
<br />
<b>Fatal error</b>: Maximum execution time of 30 seconds exceeded in <b>D:\back
up\backup.php</b> on line <b>4</b><br />
D:\backup>
any suggestions?
Posted: Mon Feb 07, 2005 8:35 pm
by feyd
may need to use exec() or play with sleep() on it.. as the data may take some time to write out. There's a potential that using the "-q" option in the mysqldump call, as by default mysqldump caches the data until it's finished, this may have something to do with the apparent shadow skipping. The redirect from the command may also have something to do with it as noted in the shell_exec()'s docs I think..
Posted: Mon Feb 07, 2005 8:45 pm
by Burrito
exec() almost got me there...it is now not running all of the commands at once, but i"m still getting the timeout issue:
"<b>Fatal error</b>: Maximum execution time of 30 seconds exceeded in <b>D:\back
up\backup.php</b> on line <b>4</b><br /> "
but I"m not buying that...because it's taking about 3 mins to execute the first line and it's getting almost all of the data, so I'm thinking that something else is causing the hiccup...but for the sake of argument (and to let me know while I'm looking for other probs) is there a way to adjust the maximum execution time (in php.ini maybe) or locally on the page itself?
edit: after more careful review, it's dying on the echo statement (line 4). I'm guessing that the whole page has 30 secs to execute or it dies and since the first command is taking WELL over 30 secs, it bombs the rest out....leading me back to my execution adjustment option
Posted: Mon Feb 07, 2005 8:47 pm
by feyd
Posted: Tue Feb 08, 2005 3:06 pm
by Burrito
ok, my process is all good and I'm happy.
HOWEVER: (of course yet another however here)...this relates to another issue I had in another thread (stupid character sets).
when I checked the data today to see how it exported, it's all there and nicely packed up in my .gz file but it has all the POS latin char set characters. I ended up having to open the .sql file in textpad (thx Feyd...timing couldnt' have been better) in ANSI char mode then save it out in ANSI char mode to keep all of the weird chars.
In the future I'd like to alleviate that step if possible and just export it with the char set that it knows. All of my tables (and DB) are using UTF8 which, I thought, took care of that...but apparently not.
I see I have the option of using --default-character-set=[name] on my sql export, but now I have no clue what to use there (ansi isn't an option)...and UTF8 apparently ain't gonna cut it.
I'm at a loss here??
any suggestions?
I would try them all, but there are about 1.2 million optiosn to try and if someone jsut knows...that'd save me heaps of time.
Posted: Tue Feb 08, 2005 3:16 pm
by feyd
might want to try utf8.. the default export is probably on the latin charset..
Posted: Tue Feb 08, 2005 3:22 pm
by Burrito
negatory:
still get this poo "Â Â Â" etc.
Posted: Tue Feb 08, 2005 3:27 pm
by feyd
unicode ?
Posted: Tue Feb 08, 2005 3:39 pm
by Burrito
not sure it will let me drill that deep into the char sets:
I think I can only use char set categories: ie utf8, latin1 etc.
here's what happens when I try drilling into the utf8 char set:
S:\MySQL\bin>mysqldump -u root -pmypass --default-character-set=utf8_unicode_ci atutor > s:\backup\utback.sql
mysqldump: Character set 'utf8_unicode_ci' is not a compiled character set and is not specified in the 'C:\mysql\\share\charsets\Index.xml' file
S:\MySQL\bin>
there is not just a unicode char set category that I can tell..I tried replacing the utf8_unicode_ci with just unicode and same error.
Posted: Tue Feb 08, 2005 3:50 pm
by Burrito
You're not going to belive it:
after looking here
http://dev.mysql.com/doc/mysql/en/charset-map.html
and trying almost all of them, I decided to try latin1 just for the hell of it...guess what?
it worketh...now I'm confused as to what the default one was? My db and tables were created with utf8 and when I exported forcing utf8 it had the crap in it.
but I KNOW that when I imported the data, I HAD to use utf8 or else I got the junk...weird stuff goign on here batman...weird stuff indeed.
ohwell, I think I'm working now.
thx again.
burr