Is the site too huge to make a backup before touching the upgrade button?
mysqldump and so on I mean…[/quote]Our forum database is now over 2GB (brand new phpBB forums start at about 190MB), so this isn’t something I would want to download over bandwidth – particularly my slow connections in China.
For security purposes, backup of the SQL resources is done by request of our webhost. They are based in New Jersey, so they aren’t available during the afternoon in Shanghai/Taipei. More importantly, I haven’t learned how to restore a mysqldump from our server and reboot our SQL engine (actually, I’m just scared to screw that up). If this is something I can eventually do on my own, I’ll learn it
I’m hoping we can do one more server-side back up now, then I can make some long-awaited modifications to the forums
[quote=“Goose Egg”][quote=“engerim”]
Is the site too huge to make a backup before touching the upgrade button?
mysqldump and so on I mean…[/quote]Our forum database is now over 2GB (brand new phpBB forums start at about 190MB), so this isn’t something I would want to download over bandwidth – particularly my slow connections in China.[/quote][quote=“Goose Egg”]I’m hoping we can do one more server-side back up now, then I can make some long-awaited modifications to the forums[/quote]
You should ask them for a ssh console on the server.
I do backup’s with mysqldump regulary on my shared hostings.
Goes like this:
mysqldump --skip-extended-insert -u myusername -h localhost --password="mypassword" mydbname | gzip > `date +%F--%H-%M-%S.sql.gz`
Result is a file like “2009-08-19–13-29-41.sql.gz”
And this can be easily put in a [wikipedia]crontab[/wikipedia] if they allow you to use enough resources and used for a complete restore.
I download this db as a backup for myself (via [wikipedia]rsync[/wikipedia]) and then I store it on a external hard drive with all the other backup files.
The main reason is that in some cases (when the providers server crashed for one or the other reason) the sql server didn’t probably write all recent changes to the disk yet.
And even if webhoster makes daily backups of the “physical” mysql database files those changes won’t be included unless they stop the mysql daemon before starting the backup. So depending on what operation was just going on at the database this is quite risky.
In case of a crash some changes might get lost or in the worst case the database could get lost (due to corruption).
[wikipedia]rsync[/wikipedia] can work over ssh (and only copies what was “changed” if told so). I run it like this:
mkdir backuphere
cd backuphere
rsync -n --delete --exclude directory/no_need_for_backup --exclude some-otherstuff -avz --rsh "ssh -l mysshlogin" mywebhoster.com:. . > ../up.log
-n means “dry run” . I can then see in up.log which files on the local backup would have been deleted or updated. If thats fine I run the same command again without the -n. And just leave the computer run overnight. My database & files are photos (totally 160 GB). I backup it weekly from a US webhoster.
Hope this helps a bit for your backups 
Hi engerim
Thank you for these notes. They will help me become more self-sufficient on the server 
Much of what you described is already what we do:
-
I have ssh access, and whenever I do a major modification of the files, cloning the relevant directories for backup using cp is the fastest way
-
We have a daily 24 hour backup, which is what I was relying on before the Morakot weekend; I believe this is set up as a cron job and includes a dump not only of our sql server, but an ‘image’ of our files
-
And here…[quote=“engerim”]And even if webhoster makes daily backups of the “physical” mysql database files those changes won’t be included [color=#800000]unless they stop the mysql daemon before starting the backup[/color][/quote]…this sounds like why we go down for 10 minutes when I warn that we are going to do a Server-side Backup
Thank you for the advice about rsync, I’ll read up more about and talk to HostExpress – we have a limit on how much bandwidth throughput we are allocated before we get hit with a per use bill. This is why I wouldn’t do that 2GB download, or a similar recovery via phpMyAdmin
My problem isn’t actually with the backups. My fear is about the Restorations! Worrying about what could go wrong restoring 2GB of stuff freaks me out.
I guess these commands would be what I’d need
mysqlimport -u [uname] -p[pass] [dbname] [backupfile.sql]
Maybe rsync, then, is the key, since it only means the changes (since last back up?) are passed along.
Many thanks again for pointing me in the right direction 