Is there a way to get a DB backup from within PW?

[MarcC]

I am using a client's shell account and just realized they don't have mysqldump installed, and I don't have access to any control panel tools like the typical PHPMyAdmin, etc.

Is there another way to grab a database backup, or generate one within ProcessWire? Thanks.

[formmailer]

I assume you aren't allowed to install mysqldump using the shell account, but can't you just download and install PHPMyAdmin?

That should run fine on most hosts.

[MarcC]

I only have one database...not sure if it's a good idea to cram all those tables together. Thoughts?

[Pete]

PHPMyAdmin would be my preferred choice. Unless you have literally hundreds of thousands of pages, the single-file dump that it creates is fine, and you can also specify that it be zipped prior to download.

On a slightly related note (that doesn't help you unfortunately ) I have some excellent backup scripts that run via cron jobs that back up site and database information that I'll have to post here at some point. They use system commands that I imagine you wouldn't be able to run on your hosting, but might be useful for others.

[spoetnik]

If al you need is a tool to dump and import mysql database, look at the tool called "Dumper" http://www.mysqldumper.net/

Otherwise, look for a tool called "SQLBuddy" http://www.sqlbuddy.com/

Both are much "lighter" than phpmyadmin.

[ryan]

Maruchan, these are all good suggestions. Also look at the ProcessExportProfile module which includes a PHP native database export function. Perhaps I should repurpose it to module just for doing DB backups.

[MarcC]

Thanks for all of those suggestions. I installed SQLBuddy in about 2 minutes and had a backup soon after that. Great little app. Pete, I have an auto-backup script I like to use but I thought it used mysqldump. Anyway, I'd love to see your scripts.

[Pete]

Here's my script.

I looked through it and it was showing it's age a bit in terms of how scrappy the code was (and the email bit in particular is unnecessarily repeated) but it works and I've commented various lines and made it slightly less horrendous to follow. You'll have to look at the various vars like the paths and database login details and adjust them to suit your site.

I then created /home/mysite/backups and inside that backups folder I put the script and folders called "site" and "db". These are all required steps. I keep it outside of the public_html folder - the cron job runs it just fine there and there's no danger of someone being able to download your backups

You would then simply set a cron job to run daily at a sensible time to kick off the backup.

The script is adapted only slightly from techniques on this very useful website: www.tips-scripts.com

To actually get the backups from the server to your PC on a regular basis, I use this program: http://2brightsparks.com/syncback/sbpro-features.html . It's reasonably-priced and you can schedule backups from FTP which does the job. Granted you could also achieve the same in Windows with a .bat file and Windows Scheduler (and I have done in the past) but since I also use this backup program for other stuff (mirroring external hard drives etc) I just do it this way.

EDIT: I know there's a bunch of repeated code in there, and thinking about it I've just thought of an excellent way to do this on my VPS so that from the root account it goes through each website in sequence so I only need one copy of the script, but it does give you an idea of what you can do with a few lines of code and the right privileges on your server

cronbackup.zip

[ryan]

Great solution, thanks for posting this Pete. I figure I should follow-up and post the route that I use too, though this one depends on the server running unix. It creates rotating backups off your non-web-accessible home directory with a cron job, and then a cron job on your machine (or another server) copies the backups over every day.

1. Login to your unix-based web account (SSH or FTPS/SFTP) and create a directory off your non-web-accessible home directory where the backups will be stored. I call mine /db-backups/:

mkdir /home/your-account/db-backups

2. Create a file in your home directory called .my.cnf (starting with a period). This holds your DB connection information. Place the following into that file, replacing "mysql_username" with your database username, and "mysql_password" with your database password:

/home/your-account/.my.cnf

[client]
user=mysql_username
pass=mysql_password

If working on a shared server or some environment where other accounts can get into your files, make sure that .my.cnf file isn't readable by anyone else. i.e. type "chmod og-rwx .my.cnf" to remove (o)ther and (g)roup read/write/execute access to it.

3. Create another file in your non-web-accessible home directory called db-backup and paste the following in there. Update the first two lines in the script to point to the directory you created in step 1 (DB_BACKUP) and the name of the database you want to backup (DB_NAME).

/home/your-account/db-backup

#!/bin/bash

# modify the following to suit your environment
DB_BACKUP="/home/your-account/db-backups"
DB_NAME="your-db-name"

# title and version
echo ""
echo $DB_NAME
echo "----------------------"
echo "* Rotating backups..."
rm $DB_BACKUP/$DB_NAME.5.sql
mv $DB_BACKUP/$DB_NAME.4.sql $DB_BACKUP/$DB_NAME.5.sql
mv $DB_BACKUP/$DB_NAME.3.sql $DB_BACKUP/$DB_NAME.4.sql
mv $DB_BACKUP/$DB_NAME.2.sql $DB_BACKUP/$DB_NAME.3.sql
mv $DB_BACKUP/$DB_NAME.1.sql $DB_BACKUP/$DB_NAME.2.sql

echo "* Creating new backup..."
mysqldump $DB_NAME > $DB_BACKUP/$DB_NAME.1.sql
echo "----------------------"
echo "Done"

Note: I found this code somewhere else online a couple years ago and don't remember where to properly credit it.

4. Save the above and make it executable:

chmod u+x db-backup

If you are connected via SSH, test it out to make sure it's working by typing:

./db-backup

It should create the first backup in your ./db-backups/ directory.

5. Setup a daily cron job to execute that file: /home/your-account/db-backup … most web hosting accounts have some ability to setup cron jobs in the control panel.

6. Now your server is keeping rotating backups in your account. Next I'd recommend going further and copying them to another machine, automatically every day. How you do this depends on whether you are going to use SFTP/FTPS or SSH (with rsync). Its preferable not to use regular FTP since it's not secure.

In my case, I've setup a cron job on my OS X desktop to copy over the files to my computer every day. It executes a file that looks like this:

#!/bin/bash
/usr/bin/rsync --archive --rsh=/usr/bin/ssh --verbose user@domain.com:db-backups/* /Users/ryan/Backups/db/

That copies those rotating backups to a /Users/ryan/Backups/db/ directory on my computer. In order for the above to work, you'd have to already be using SSH with your account and have your SSH keys assigned so that you can connect without typing your login/password. If anyone is running a similar setup, I'll be glad to tell you how to do that. But I know there are other ways to automate this task and the approach you use here kind of depends on the platform and whether you have SSH access on the server.

This setup is pretty bulletproof, but I'd like to have some module in PW in the future that will let it do all of this for you – Automatic backups from one server to another.

[renobird]

Ryan,

Just implemented this for the 3rd or 4th time now — I've been meaning to say thanks.

Dead simple, and works like a charm.

[Ivan Gretsky]

Advancement comes over time. See here for some contemporary options.

[Peter Knight]

On 19/12/2011 at 8:51 AM, Pete said:

 

To actually get the backups from the server to your PC on a regular basis, I use this program: http://2brightsparks.com/syncback/sbpro-features.html . It's reasonably-priced and you can schedule backups from FTP which does the job. Granted you could also achieve the same in Windows with a .bat file and Windows Scheduler (and I have done in the past) but since I also use this backup program for other stuff (mirroring external hard drives etc) I just do it this way.

 

Used to love this app when I was a Windows user. Great to see it mentioned. Often asked the guys to create a Mac version.