I thought I would post the following handy little scripts, which have proved invaluable to us.
Nightly Database Backups
The first script simply makes a backup of a SoftSlate Commerce database. We run this guy nightly for all of our clients. Most likely you already have a backup system in place, but many times it may be hard to get at a snapshot of data from a particular day. Occasionally it can be very valuable to load up a backup in order to perform auditing or even restore specific pieces of data that were changed by mistake (eg the pricing or descriptions of products that were changed when they shouldn’t have been). The backups we make have come in handy many times for these sorts of reasons – to essentially bail out our clients from a mistake they’ve made. First, for MySQL:
#!/bin/sh
dir=/var/local/databaseBackups
username=user
databaseName=database
password=pass
cd $dir
mydate=$(date +%Y%m%d_%H%M%S)
mysqldump -u $username -p$password --skip-lock-tables --single-transaction --quick $databaseName > $dir/$databaseName.$mydate.dump.sql
gzip $dir/$databaseName.$mydate.dump.sql
chmod 400 *.gz
find $dir/$databaseName.*.dump.sql.gz -mtime +30 -exec rm -f {} \;
And now for PostgreSQL:
#!/bin/sh
dir=/var/local/databaseBackups
username=user
databaseName=database
export PGPASSWORD=password
cd $dir
mydate=$(date +%Y%m%d_%H%M%S)
pg_dump -Ox -U $username > $dir/$databaseName.$mydate.dump.sql
gzip $dir/$databaseName.$mydate.dump.sql
chmod 400 *.gz
find $dir/$databaseName.*.dump.sql.gz -mtime +30 -exec rm -f {} \;
As you can see these scripts purge any backups older than 30 days (which you can adjust by tweaking the scripts). You can put this in your server’s crontab to keep a running set of backups covering the last x days of data.
Restoring a Piece of Data from a Backup
When it comes time to restore one of these backups, you can create a new, separate database and load the backup file into it – using MySQL here for example:
mysql> create database backup_20130816;
Gunzip the backup and load it into the new database:
gunzip softslate.20130816.dump.sql.gz
mysql -u user -p backup_20130816 < softslate.20130816.dump.sql
Then, as an example of how you can restore specific pieces of data, try using an update statement across multiple databases. Using MySQL again as an example, where we want to restore all products' prices:
mysql> update softslate.sscProduct live join backup_20130816.sscProduct backup on live.productID = backup.productID set live.unitPrice = backup.unitPrice;
Pretty sweet, if you need to go backwards in time for a certain piece of data!
Clearing Personal Information from a SoftSlate Commerce Database
This next recipe clears out all personal information about customers from a SoftSlate database. We have run it countless times to prep data for our development or staging environments. Every once in a while, you need to refresh your dev environment with data from the live store, so you can get the latest products and other settings. You might also want to refresh the customer and order tables so you can work with a realistic set of data if you need develop code against the orders and customers. But you don't want it to be too realistic - any personal information about the customers should be cleaned out under the principle that only those who need to know the info should have access to it.
The process we use is to make a back up of the database and load it into a new, separate database.
mysqldump -u user -p --skip-lock-tables --single-transaction --quick softslate > softslate.dump.sql
mysql> create database for_staging;
mysql -u user -p for_staging < softslate.dump.sql
In the new database run the following SQL to clear out the customer information:
update sscOrder set firstName = 'John', lastName='Doe', organization = null, phone1='555-555-5555', email1=concat('bogus-', orderID, '@softslate.com'), userName=concat('bogus-', orderID, '@softslate.com'), address1 ='123 Main Street';
update sscOrderDelivery set firstName = 'John', lastName='Doe', organization = null, phone1='555-555-5555', email1=concat('bogus-', orderID, '@softslate.com'), address1 ='123 Main Street';
update sscPayment set creditCardName='John Doe', creditCardNumber=null, value6='000000', value7='0000000000';
update sscCustomer set firstName = 'John', lastName='Doe', organization = null, phone1='555-555-5555', userName = concat('bogus-', customerID, '@softslate.com'), email1 = concat('bogus-', customerID, '@softslate.com'), address1 ='123 Main Street';
update sscCustomerAddress set firstName = 'John', lastName='Doe', organization = null, phone1='555-555-5555', email1 = concat('bogus-', customerID, '@softslate.com'), address1 ='123 Main Street';
Finally make a back up of the new, cleaned-up database, and use *that* in your development or staging environment:
mysqldump -u user -p --skip-lock-tables --single-transaction --quick for_staging > for_staging.dump.sql
I hope these come in handy, and I'd be curious to know if anyone has similar (or better scripts) you use for these tasks?
