|
Create phpbb database backup with MySQL dump and cron
This might be useful, an article on how to create mysql backups using mysqldump.
<update: see the 4th post in this thread for the complete solution> :: Code :: # mysqldump -u <username> --password=<password> -h <host> sample_db > /usr/backups/mysql/sample_db.2004-12-16
// to automate the date, append this after sample_db. .$(date +%F) When I tried this with the -p switch, for password, it didn't work on my server, but the --password= switch did. I also couldn't get it to append the date to the file name, I'll have to see what went wrong there. Back to top |
|
That method won't add the drop tables syntax you need in the SQL, although it does put out a much cleaner batch of SQL, quite a bit smaller than the PHPBB version.
I think it's better to use the native PHPBB backup utility however, unless you want to first delete the old db, recreate it, then recreate it using the dump file. Note, once your database gets above about 3 megabytes or so, you can't use the restore utility in phpbb without chopping up the database. However, you can simply import the backup file, which includes the drop tables sql that is necessary to redo your phpbb forum if required. The syntax for the command line is very easy: :: Code ::
%mysql -u <username> --password=<password> -h <server name> <database name> < <path to file on server> Example: %mysql -u frederick --password=3cW5fK -h sd12x.yourhost.com phpbb < /usr/home/frederick/db_backups/12-12-04.sql hit enter. This overcomes the FTP upload size limit problem with phpbb and restoring the db. Also useful for maintaining a local version of the forum once it gets above the 3 mB limit. Back to top |
|
I found an even slicker version of that backup script at tech-recipes.
It features a full cron job implementation of the mysqldump, as well as the ability to backup multiple databases. Nice one, bofh468. Back to top |
|
I finally got around to implementing a version of phpbb backup through cron.
Note, you have to find the paths for the following on your server: sh mysqldump to find the paths, run this command through ssh, or ask your hoster what the paths are (I'd find it myself though, it's more reliable) :: Code :: which sh
#will print out something like this: /bin/sh #then find mysqldump: which mysqldump #which will output something like this: /usr/local/bin/mysqldump With this information you're ready to proceed. In the following, replace [username], [password], [server], [databasename], with the correct information for your database. Also replace the path to sh and mysqldump with the path you found above. Also, for BFILE, change this to the path that you are going to put the backup in, I'll use an example generic path. Important :: you must set read/write/execute permissions on the folder the backups get written to, 777, or rwx-rwx-rwx, or the script won't be able to write out the new backup. Note further that on many servers the database server is 'localhost'. Only if you use an external database server would you put something other than localhost in that spot. I'll use localhost in the sample. :: Code :: #! /bin/sh
BFILE=/usr/www/username/backups/phpbb-$(date +%y-%m-%d).sql.gz /usr/local/bin/mysqldump --add-drop-table -h localhost --user=[username] --password=[password] [database name] | gzip -c > $BFILE Save this file, call it say: /usr/www/username/includes/stuff/phpbb_backup.sh This will create a backup file located in /usr/www/username/backups/ and it will be called phpbb-[today's date].sql.qz. For example, phpbb-05-11-23.sql.gz I make it year month day so that the files will be easy to organize cronologically. You can also use bzip, that will produce smaller backup files, just change gzip to bzip in that case. Many windows unzipping utilities however don't recognize bzip, but they do recognize gzip compression. If you're only using this stuff on unix type systems and don't run a local development version of your site bzip should be fine. set up the cron job Next create the cron job. On many shared hosting providers you can do that by either contacting customer support or through a control panel, make the job run, run as often as you feel necessary, depending on how much activity the forums get, I'd do it at a minimum daily, more on busy forums. You can follow one of the above links to learn how to create a cron job through ssh or the command line, we won't get into that part here. The cron command is just the location of your script, whereever you put it. I'd put it below the website root so nobody can snoop around, and definitely do not put the output backup file above site root or anyone at all could download your entire phpbb database. The cron job will take this as its command, plus of course the frequency and time of running it: /usr/www/username/includes/stuff/phpbb_backup.sh Test this through ssh before setting up the cron job, once you've tested it, and you see the new backup in the backup folder, you're set, create the cron job, and your phpbb will automatically back itself up every day or however often you set it to backup. Obvioulsy, download the backups now and then, fairly often, to your local box so you have multiple copies of your backups, if the server totally fails, if you get hacked, or whatever, you want to be able to recreate the forums as quickly as possible, with as little loss of data as possible. Back to top |
|
Restore back?
How do you then restore from a file?
gunzip phpbb-05-11-23.sql.gz ? mysql -uxUSERNAME -pPASSWORD xDATABASENAME <phpbb-05-11-23.sql ?? Or somthing like that. I'm not too mysql literate. Thanks Stan Back to top |
|
Yes, like it said above:
:: Code :: Example:
%mysql -u frederick --password=3cW5fK -h sd12x.yourhost.com phpbb < /usr/home/frederick/db_backups/12-12-04.sql If your db is on localhost, which most mysql stuff is, you don't need the -h <database server name> information. I've had problems with -p <password>, so I use --password=<password>. You just need the path to the file to restore from, that's about it, try it, it's easy to do. It also works MUCH better than phpBB's native backup and restore utility, much faster. As always, it's much safer to play with a local test install of your db stuff before using the techniques live, but this one is pretty safe as they go. There are more options available that very busy forums should use, like locking the database before backup and before restore, but the mysql information page on mysqldump can fill you in on all those extras. That would be either --add-locks or --lock-tables, read up on it to learn more if that's of interest. Back to top |
|
All times are GMT - 8 Hours |