MySQL Backup Script with PHP and S3 : Jason Byrne's Blog

MySQL Backup Script with PHP and S3

March 20 2009 by Jason Byrne

Tonight I update my previous script to backup a MySQL database. I am a web developer and not a system administrator, but I have to wear multiple hates and act like a system administrator. But I hate it! So when I go out on Google and look for system admin code it all is written in perl or bash, which yeah I know it's easy but I'm just not as familiar with. So I always want to write my scripts in pure PHP which I could write in my sleep. So here is my attempt at a database backup script that will not interupt my high traffic site too much (thanks to mysqlhotcopy), will be zipped up, and sent over to Amazon S3 for that secure sleep-at-night feeling an off-site backup brings!

I have expanded upon it to make it more customizable and added some bells and whistles. I also added a lot of comments in the code. The features include...

Backup one more more databases with mysqlhotcopy
Gzip the backup and store locally
Optionally upload the backup to Amazon S3 account using the s3-bash script. You will need to put a file called secret.txt that contains ONLY your s3 secret key in the same folder.
Optionally delete yesterday's backup from S3
Optionally delete the local backups
Optionally email a report to yourself including the length of time the backup took and the size of the gzipped backup file

Requirements...

Obviouslly you need to be running a LAMP system (Linux, Apache, MySQL, PHP)
Must have cURL installed on your server
Need to download this S3.php class and place it in the same directory.

The code:

<?php /* CONFIGURE THESE VARIABLES */ // Database connection $mysql_user = 'YOUR-DB-USER'; $mysql_pass = 'YOUR-DB-PASSWORD'; $mysql_db = array('YOUR-DB1', 'YOUR-DB2'); // Array of all databases you want to backup // Place to store backup locally $backup_path = '/second_drive/backups/mysql/'; // Email address to send report (optional) $email_address = "YOUR@EMAIL.COM"; // Amazon S3 info (optional) $s3bash_path = '.'; $s3_key = 'YOUR-S3-ACCESS-KEY'; $s3_secret = 'YOUR-S3-SECRET-KEY'; $s3_bucket = 'YOUR-S3-BUCKET'; // Delete yesterday's backup? $delete_yesterday = true; // Delete local backup? (only if using s3) $delete_local = false; /* RUN THE BACKUP */ // Required file require_once('S3.php'); // Time variables $startime = time(); $yesterday = date('Ymd', strtotime('-1 day')); $today = date('Ymd'); // Create today's folder echo "Creating today's folder...n"; if (!file_exists("$backup_path/$today")) { mkdir("$backup_path/$today"); } // Making today's backup echo "Creating hot copy...n"; // Loop through each database and run mysqlhotcopy foreach ($mysql_db as $dbname) { system("mysqlhotcopy --user=$mysql_user --password=$mysql_pass $dbname $backup_path/$today/"); } // Tar and gzip today's backup echo "Creating tarball...n"; $gzipfile = "$backup_path/$today/backup.tgz"; system("tar -czvf $gzipfile $backup_path/$today/*"); // Get size of backup $a = array("B", "KB", "MB", "GB", "TB", "PB"); $pos = 0; $size = filesize($gzipfile); while ($size >= 1024) { $size /= 1024; $pos++; } $fsize = round($size,2)." ".$a[$pos]; // Remove yesterday's backup if ($delete_yesterday) { if (file_exists("$backup_path/$yesterday")) { echo "Deleting local backup from yesterday...n"; system("rm -Rf $backup_path/$yesterday/*"); system("rmdir $backup_path/$yesterday"); } } // Send to Amazon if ($s3_key && $s3_secret) { // Upload today's file echo "Uploading to Amazon...n"; // Instantiate s3 class $s3 = new S3($s3_key, $s3_secret); // Upload file if ($s3->putObjectFile($gzipfile, $s3_bucket, "mysql.$today.tgz", S3::ACL_PRIVATE)) { // Delete yesterday's file if ($delete_yesterday) { echo "Delete yesterday's backup on Amazon...n"; $s3->deleteObject($s3_bucket, "mysql.$yesterday.tgz"); } // Delete today's file if ($delete_local) { if (file_exists("$backup_path/$today")) { echo "Deleting local backup from today...n"; system("rm -Rf $backup_path/$today/*"); system("rmdir $backup_path/$today"); } } } } // Email report if ($email_address) { echo "Emailing report...n"; $completed = (time() - $startime); $completed = floor($completed/60) . ' minutes and ' . ($completed%60) . ' seconds'; $message = "Tonight's backup completed in $completed. Gzipped file size: $fsize"; mail($email_address, "MySQL Backup - " . date('m/d/Y'), $message); } // Done echo "Donen"; ?>

Edit Post

Tags: Web-Development, PHP, MySQL, Scripts, Linux, S3, Server-Administration

Comments

March 22 by
Paul

Whenever I run this, I get a zero-sized output. Any ideas on a solution? I have commented out the Amazon S3 part, but everything is the same.
March 22 by
Jason

@Paul the tgz file is created but is 0 bytes?
March 31 by
Jim

This is just what I was looking for --- an easy way to ensure my databases are backed up. When I get this up and running I'll report back. Many thanks.
April 11 by
Brian

Whenever i run this i get the following error. Parse error: syntax error, unexpected ')', expecting '(' in *host*/sql_backup.php on line 78 That line is: if ($s3->putObjectFile($gzipfile, $s3_bucket, "mysql.$today.tgz", S3::ACL_PRIVATE)) { I know PHP fairly well but i dont get why this would be happening, there are the correct amount of parenthesis, phpDesigner 6.5 doesnt detect any errors in the code and that app sees every potential error. I have looked in the variables for extra parenthesis, looked everywhere even the s3.php class file and cannot find anything.
June 10 by
yz

Louis Vuitton Gucci Shoes Hermes Handbags christian louboutin nike shoes replica bags Louis Vuitton Gucci Bags louis vuitton herve leger Rolex Watches Omega Watches
June 10 by
yz

Louis Vuitton Gucci Shoes Hermes Handbags christian louboutin nike shoes replica bags Louis Vuitton Gucci Bags louis vuitton herve leger Rolex Watches Omega Watches
June 30 by
susie

ghd mk4 gold ghd gold ghd mk4 gold styler ghd purple ghd iv styler dark ghd dark ghd salon ghd pretty in pink pink ghds ghd hair straighteners hair straightener sydeny ghd flat iron ghd limited edition ghd iv styler ghd black
June 30 by
susie

ghd mk4 gold ghd gold ghd mk4 gold styler ghd purple ghd iv styler dark ghd dark ghd salon ghd pretty in pink pink ghds ghd hair straighteners hair straightener sydeny ghd flat iron ghd limited edition ghd iv styler ghd black
July 13 by
ed hardy

I totally love this article. I think you could write some other things to make your blog more complete.For example： ed hardy clothes.vibram p90x workout . mbt.ed hardy shirts.vibram five fingers kso trek.mbt sapatu shoes. cheap ghd straighteners ugg boots discount ugg boots cheap replica louis vuitton handbag ed hardy I thought that many people can like.
July 13 by
gucci shoes

men gucci shoes women gucci shoes gucci sneakers discount gucci shoes cheap gucci shoes men gucci boots women gucci boots gucci leather shoes gucci ankle boots gucci booties
July 15 by
Louis Vuitton Gucci Hermers

Louis Vuitton handbags Louis Vuitton shoes Louis Vuitton wallets Christian louboutin boots christian louboutin christian louboutin discount louis vuitton handbags louis vuitton wallet louis vuitton purse louis vuitton bags Hermes Handbags Hermes Wallets zoeslxxTT Hermes Scarves
July 18 by
dfgsdf

chanel gucci hermes louis vuitton discount handbags vertu phones designer handbags reviews replica handbags replica phones vertu replica tag heuer phone mobiado cartier-phones goldvish rolex mobile porsche phone replica blackberry iphone replica
July 19 by
louis vuitton

Buds louis vuitton this summer chanel handbags skirts louis vuitton shoes become big louis girls love vuitton this skirt louis vuitton bag most prominent chanel feature is the handbags crotch of the louis vuitton handbag cut, I was louis vuitton shoes a little vuitton shoes narrow skirt, looks gucci bags like an inverted burberry watches , the budding louis vuitton wallets flower, shy louis vuitton bags fashion chanel louis shoes and moving it burberry bag sweet and burett watches lovely. Whether christian louboutin boots one-piece Christian Louboutin boots models, or high christian louboutin shoes waist fendi section; whether fendi to attend gucci bags parties, or gucci shoes shopping Hamilton Watches trips, so that fashion chanel you become fashion ladies the most shining one. High waist skirtfashion show is best to extend thefifa 2010 legs of the visual effects, coupledgucci handbags with the “bud” stylegucci louis vuitton even more girls gucci sale in graceful lines. Although this skirt very good ride, but the choice must fashion chanel pay attention to the length of the skirt, preferably handbags information something shorter, or woven becomes rigid. Although jeans skirt handbags lady has become a summer wardrobe MM’s retention money, but a long time you will feel fashion chanel tired of it? It does not matter! Bud denim skirt jerseys guide will make your vision is absolutely full of freshness! To note that the time must not be less with belt Oh.
July 21 by
ghd

We offer ghd straighteners worldwide.You can get them with high quality but at low price from our store.ghd straighteners cheap will give you a big surprise.We sale various styles ghd hair straighteners and the most popular ones are the ghd purple and ghd precious, p90x and insanity workout.They are the perfect hair straighteners which you should not miss.In addition, we have numerous other ghd series.I'm sure you will get a perfect ghd hair straighteners which macthes with your taste. For most people ghd straighteners cheap is a god-given chance. In addtion, if you pay a little attention to ghd straighteners you will find that ghd loved by many celebraties who appear on the fashion magazines with new hair styles created by the hair straighteners.You may say why it is must be ghd straighteners? You know ghd is a famous brand in hair salon field. If you are lucky enough, you will find ghd straighteners cheap which is at a lower price but with the same good quality.
July 24 by
lv

Ask any woman andlouis vuitton she will tell you that a handbag is the one must-have omega watches accessory. Over the last burberrycentury Louis Vuitton has established itself as a leading brand name when Christian Louboutinit comes to fashion accessories. LV’s designs are meticullously detailed, well-crafted andlouis vuitton bags as a result extremely expensive.burett watcheslouis bags Because Louis Vuitton bags louis are held in such high regard, they watches have become a symbol of wealth,handbag mostly worn by only the rich and/or famous.Not only are Louis Vuitton handbags extremely burettfunctional, giving you aChristian Louboutin shoes ton of space for storing your louis vuittondaily necessities, but they omegacan also enhance any outfit. This is important because even burberry handbagthe most luxurious outfit will look incomplete vuittonif it you leave out a stylish bag or purse. The advantageChristian Louboutin boots of an LV bag is that it looks good with almost any outfit, Diamonds, shoes, flowers, bags…And LV is louis vuitton luggageone of the brands which women would never missed. Well, I am not one of them.replica louis vuitton But why LV? What I don’t really louis vuitton handbagsunderstand is what is so special about this brand louis vuitton handbagsthat women saved to just buy one Lv bag louis vuitton bagsor even their imitation? I had done somelouis vuitton shoes louis bags research, trying to find outlouis vuitton more about LV. Louis Vuitton was foundlouis vuitton shoes way back in 1854. It’s all started off for its quality trunks (large luggage box with hinged lid), lightweight, airtight and the ability to stack for ease with voyages. In 1888, “L. Vuitton trademark” was introduced in response to the continuing of imitation.Vuittonreplica louis vuitton family actively supported Nazi louis vuitton bagsGermany (for the financial benefit of the company) during the Vichy regime (the government of France from July 1940 to August 1944). Since then, chanel handbagsthe look of the leather was utilized in everything from small purses and wallets to larger pieces of luggage.louis bags In 1978, it opened the first stores in Tokyo and Osaka. In 1983, in its presence in Asia with the opening of a store in Taipei and Seoul in 1984. Until now, the varies from luxury leather goods to fashion accessories and jewelery. It You canbags information have a look at the collection of bags miu miuvarious replica designer handbags and order your piece. There are series of style bags, basketball newspurses and wallets. Replica Louis Vuitton Handbags, Chanel Replica Handbags, and all other Replica Designer Handbagschanel information.louis bags The models of thechanel sale replica bags are mind blowing. The prices are amazing and affordable when compared to the original pieces.christian louboutin miu miuThe originalcitizen replica products design and techniques are noted by the designers in any of their exhibitions or in their introduction parties. coach burberry After noting down all the necessary details they design their own replica handbags without any modifications louis bags and the handbags will look exactly like the same. Our replica designer handbags are designed with good craftsmanship and the leather used in manufacturing the handbags is best quality leather. The handbags are available in various sizes that fulfill your desire.
July 25 by
Tiffany jewelry

Tiffany jewelry manufacturers directory,cheap jewelry - lots of registered importers and exporters. Cheap tiffany jewelry manufacturers, Tiffany & co jewelry suppliers, tiffany jewelry wholesales, Tiffany Watches,exporters, sellers, traders and gold tiffany jewelry Distributors from China and around the world at www.goldtiffanyjewelry.com Tiffany jewelry exporters, Tiffany Accessories, Tiffany Bangles, Tiffany Bracelet, Discont Tiffany Bracelet, Discount Tiffany Cufflinks, Fashion Tiffany Earring, Tiffany Necklaces , Discount Tiffany Necklaces, Tiffany Rings, Tiffany Watches
July 25 by
xiaocao

offers automobileWe have louis vuitton handbagsGreat Barrington with offices in Buy Cheap EbagSellGucci handbags Buy CheapGreat Barrington independent the most ReplicaLV bags Luxury HandbagsWe have
July 26 by
ugg boots

UGG 5163 Boots
July 26 by
ugg boots

UGG 5825 Bootsugg boots
July 26 by
ugg boots

Red Lust GHD IV Styler
July 26 by

Chloe handbags&discount Chloe handbags
July 26 by
ugg boots

UGG Classic Argyle Knit
July 26 by
ugg boots

These well-known UGG Classic Cardy are now becoming more and more fashionable and popular, to some extent. I believe you may ask what the shoes can bring us. As I know, if one person struck by lightning in Holland, his family must put all his Classic Cardy Boots up as soon as possible. And in the fairy tale, the shoes are more magic indeed. Now, I always say that the UGG Classic Cardy Boots are so magic and popular.
July 27 by
LILEI

I know a software named EZ AVI To MPEG Converter can Convert AVI Video movie to MPG/MPEG,MPEG-1/MPEG-2 format. Only $12.95 USD
With a very easy to use interface you can also convert video file. your work will become very easy. You can download this software free from here: http://www.ezvideotools.com/avi-to-mpeg-converter.htm
July 27 by
NFLjerseys

A good website to purchase cheap jerseys is nfljerseys-shopping.com, here football jerseys are sold at discount, most NFL jerseys are having promotion now. Don’t miss such a distinct opportunity.
July 28 by
thefirstwatches

Officials at the dolce & gabbana replica handbags Postal Service, source of the bally handbags most complaints, say employee complaints chloe handbags replica recently fell by 77 percent in three tod's handbag regions offering mediation.Timely replica handbags . Trendy. Brand name watches dior handbags are more than a luxury; they're replica burberry handbags an obsession with Ashford.com. bally replica handbags Only the most fashionable, reliable timepieces burberry replica handbags will do. All 100% authentic cheap Handbags with serial numbers. All Mulberry handbags brand-new.Brand name watches in stock. replica chopard 30 days satisfaction guaranteed! concord for sale It's a passion that began in 1997 Tudor for sale , and a legacy that has made Ashford.com one Alain Silberstein for sale of the largest, most trusted swiss watches dealers online. With over 3,000 luxury omega replica to choose from priced way below Tudor replica retail, plus a stunning selection of replica Glashutte jewelry, it's no wonder. Movado, Ebel, replica DeWitt Bulova, Cartier, Gucci and more are replica Glashutte available among 40+ top brands, all with dior replica handbags free shipping.
July 29 by
Tiffany Jewelry

Tiffany Jewelry itself has no problems in Tiffany & Co quality. Now in the Tiffany Jewellery market, there are many kinds of replica Discount Tiffany Jewelry silver Tiffany Silver Jewelry. If you don't mind replicas Tiffany Silver, that's ok. If you don't want replicas Silver Jewelry, then how to choose a reliable Tiffany UK supplier is pivotal. After all, no one wants to get the fake Tiffanys with a lot of money. Among silver Tiffany's, Tiffany 1837 is the hot pick of many people. Cheap Tiffany is a brand name around the Discount Tiffanys world. Whether used as a gift or Return to Tiffany bought for oneself, Tiffany jewelry will never make people embarrassed Tiffany and Co. As a big brand in the Tiffany Co world, the quality is Tiffany Co Jewellery out of question. The after service of Tiffany Co Jewelry silver Tiffany Discount also assures people the reliability of Tiffanys Jewelry.

Jason Byrne's Blog

MySQL Backup Script with PHP and S3

Comments

Leave a Comment

Similar Posts

Recent Posts