How to Backup MySQL and FTP Scripting with PowerShell

Listen to this text

I’m a .NET developer, not a system admin. As such, I never properly learned how to write anything aside from “echo” this, “dir” that, and “ipconfig -toomuchinfo” to a command window. But now, since I manage my own server on Amazon AWS, I wanted a neat non-c# console-app way of backing up my MySQL Database and FTP-ing the resultant SQL file to another location. Because Amazon storage is expensive and my other hosting provider (HostGator) has almost unlimited storage space, I wanted to FTP things there, as a safe, inexpensive backup solution. This is the poor man’s disaster recovery, which like most poor-man solutions, works surprisingly well.

I challenged myself to do this whole task in just Powershell and windows commands on the task scheduler, and the solution I came up with seems to work really well. Here it is:

1. First, I backup my MySQL database. Create a backup directory, like: “c:\backup\”. Then, use the MySQL backup utility: this is a file called “mysqldump.exe”. If you installed MySQL on your computer/server, the exe is somewhere on your computer. Just find it and copy it into your shiny new backup folder. Open up Notepad or your favorite text-editor (like VIM if you still use that) and create a simple “.BAT” file that runs mysqldump on your database which spits out a SQL backup file in your backup folder. I name the file after the current date: so today’s file would be “backup.20161101.sql”. The backup script is really just one line of text that reads like the following:

mysqldump.exe --single-transaction -u[username] -p[password] [your_database_name] > "C:\backup\backup.%date:~10,4%%date:~7,2%%date:~4,2%.sql"

(Note: replace the contents in [brackets] with your actual situation. Don’t include the “[]” and don’t add any spaces. Notice, that username and password arguments strangely don’t have a space between the “-u” or “-p”).

2. Double-click the BAT file to make sure it works and creates a SQL file for you.

3. Next, prepare your Powershell file that will FTP your file away. Powershell files are plain-text files which end in a “.ps1” extension. Before you get started with Powershell, you need to change the execution policy of Powershell on the server. The execution policy is by default crippled for security reasons. To change this, open up Powershell command prompt as administrator and run this command:

PS C:\> Set-ExecutionPolicy -ExecutionPolicy RemoteSigned

This will allow you to run your own Powershell commands but still requires downloaded scripts to have a signed certificate to execute. You will likely get a scary warning when you change this. (Please read up more on execution policies before doing this.)

4. Finally, create your Powershell script that will FTP your SQL files to your FTP server. Because the file name scheme I have is predictable, I know what the name of the file is that I want uploaded each day. Here is the script I created; just copy this to a text file and replace the [brackets] with your situation and save the file. I called the file “FtpDataFile.ps1”:

#we specify the directory of the file we want to upload
$Dir="D:/backup/"

#ftp server
$ftp = "ftp://[ftpUrl]"
$user = "[FTPusername]"
$pass = "[FTPPassword]"

$webclient = New-Object System.Net.WebClient
$webclient.Credentials = New-Object System.Net.NetworkCredential($user,$pass)
$item = "backup.$(Get-Date -Format 'yyyyddMM').sql"

"Uploading $($Dir + $item) ..."
$uri = New-Object System.Uri($ftp+$item)

$webclient.UploadFile($uri, $Dir + $item)

5. Set up another task that runs your Powershell file on the server (space it out at least 15 minutes after the MySQL backup task). In order to run Powershell on the command window, you need to essentially execute the “Powershell.exe” process and pass the script file as an argument. So it would look like this:

c:\> PowerShell -File .\FtpDataFile.ps1

(Note: The “.\” is a relative directory symbol meaning, “directory above this one”; you may need to adjust your path.)

You can easily consolidate all this to just one Powershell script. And if you do, be sure to put in a wait/sleep command in there between the two tasks to make sure the SQL file is finished building before FTP-ing it away.

The CFP List: www.cfplist.com

Listen to this text

As a professional in literature, you are constantly on the prowl to present at conferences. So, as you can imagine, grad students are strongly encouraged to attend and submit papers to conferences whenever possible. “Real” conferences, to me, are super intimidating still. I can’t help but have nightmares that someone is going to ask me a question that I can’t answer. Or worse, a question to which I can’t even figure out the question.

Lately I’ve been scouring the web for friendly graduate student conferences which I think I may be able to handle. Unfortunately, there are very few sites that have a nice rich repository of CFPs (Call for Papers). Sorry, “CFPs”, the acronym I had to learn lately, is basically a beacon call for academics to submit a proposal to present. So a fellow English Grad student and I created our own CFP database called cfplist.com. The goal is to warehouse all CFPs floating out there. Well, we’ll see if it takes off!

Make sure to check it out: http://www.cfplist.com

iPod Touch. tap…tap…tap

Alright, I caved. I bought a new iPod touch. Now I’m sitting next to my laptop (but I’m not on my laptop), tethered by a stylish white USB cable, and tapping away at a 2 inch keyboard with my right index figure and writing a post. I imagine I most look something like Sloth from the Goonies over here, straining over this tiny dainty device, trying with marginal sucess at limiting my large simian, awkward fingers, from not mashing more than one key at once. It may take me 8 minutes or so to tap-out a complete sentence, but I will have to admit, I feel rather suave and 2.0 doing it. I just need a pair of black frame glasses, a cup of starbucks nonfat soy milk latte, and a black ribbed crew shirt to make the transformation official.

More literally speaking, I actually did purchase the 2.0 software upgrade which enables the installing of little 3rd party apps. I got the WordPress one (which is one of the few free ones) and so far it’s working great!

Well, Sloth is starting to get a little ancy doing all this finger taping, so I’m just going to turn in, for now.

Old Technologuy

I’m Asian, dorky, and male. All those things combined might suggest that I would want an Iphone. However, truth be told, I do not.

I don’t know when or why, but I’ve lost all interest in new technology. I was sad when Firefox automatically updated me to the latest version of their browser. I was disappointed when Microsoft finally released Windows Vista, and all its 6 different flavors—each equally indecipherable from the next: Windows Vista Home Media Version, Windows Vista Business, Windows Vista Poor-man’s edition, Windows Vista Poor-man’s edition Professional.

Is this the first road sign to Geezerville? Am I really only a few miles away?’

When and how did Myspace lose my interest, and gain my scorn? When did the Internet seem so prosaic? Haven’t I seen this site before? Haven’t I read this commentary already? Or at least a dozen like them? Why should I upload my photo’s to this site? Do I really want the whole world seeing them, does the world even care? Oh look a dancing baby, now isn’t that stupid. Forward this to 15 friends? I don’t HAVE 15 friends! Or at least I wont anymore if I forward this to them. And forward this back to the sender to confirm that we are indeed friends. Are you kidding me?

Give me a newspaper any day. I don’t care if the providence journal is free online. “Ahead of TIME: read it online before it comes in the mail”. Clever email title, but no thank you. I’ll wait.

You mean I can change my policy on the website? Wait, does this mean you’re not going to do it over the phone? Hello? Do I have to press “1” for you to keep listening?

… hello ?

(dial-tone …)

ISBN-10 to ISBN-13 Converter Tool

Listen to this text

I work with a lot of textbook inventories at work, and we had a recent issue with the new ISBN13 standard. The internet had no really good tools out there so I created my own little one.

Unfortunately, it is only really useful for a very small group of people—but useful nonetheless. It is an ISBN10 to ISBN13 conversion tool. As you most likely don’t know, the standard for ISBN numbers have changed. They are no longer 10 digits, they are actually 13 digits (whoa!). Now, all the old ISBN10 numbers need to be retro-converted to ISBN13 numbers—which is a fairly simply process, but requires a little algorithm.So, reaching into the interminable wellspring of my largess, I created a free online tool that will do the conversion for people, for free. You may ask, “aren’t there already online tools that could do this?” To this, silly question, I reply, “Of course!” The Internet has made it such that every good idea you come up, someone else has already thought of it, implemented it, and Google has already allocated funds to buy it.

However, in this special case, I defer to the “mine is better” clause. Most of the tools I saw online can only do one number at a time; mine can do a whole slew of them!

Hopefully, “someone” will find this useful.

Here’s the Link! FREE BATCH ISBN-10 to ISBN-13 Online Converter Tool.

Max OSX on x86 PCs

Who said you need a Mac to run Mac OSX?

Mac OS X on my Windows PC

I was able to install Mac OSX Panther on top of Windows XP the other day using an amazing little tool called PearPC. It’s not really a “tool” per se (Mardigan’s a tool) it’s more of an emulator that mimics the PowerPC ISA on an Intel machine. And, as an obvious consequence, running Mac OS X this way is pretty slow– but still usable.

Spam Woes

Spam–the mosquitoes of the Internet. Since I planted my new site on some fresh soil, it has become almost a part-time job for me to try and contain the build-up of Spam from overrunning my site. Every new comment, that has something to do with Texas Holdem, or Poker, or Viagra, or that may contain a slew of links, all to sites that serve no purpose other than surreptitiously installing spy-ware on some unsuspecting victim (who is not using Firefox), and flood their computer screen with a dozen pop-up advertisements for things no one has an interest of purchasing– it has me seething at the teeth.

There are two people I would like to meet in the Spam Market. First, the filthy hoodlums that write the life-giving code behind Spam. Second, the idiots that are actually purchasing this stuff. I imagine they exist? I mean, if spammers weren’t reeling in a few pigeons for all their efforts, would they even bother?

Evil Spyware

One day I was looking for naked pictures of former attorney general Janet Reno on Google, and I clicked on this link that seemed promising. Lo and behold, about 20 windows started to pop-up, one after another. I’d close one, two more would appear, asking me to install things, and I kept saying “no”. And not the polite “no”, it was the loud and colorfully languaged “no�. Then, after clicking “no” about a dozen times, and seeing that the sea of questions were no way relenting, I did the vulcan live long and prosper on my keyboard to get it to just terminate my web browser. But, it was too late. After I restarted my computer, all of sudden it had become a nesting ground to a gross brood of various new programs that surreptitiously installed itself –like “Bargain Buddy”, and “Cool Web Search”, oh, and the ever popular and bitch to remove “Home Search Assistant”.

I was mortified by this. I think it irks computer science guys even more when stuff like this happens to them–because (arrogantly) we’re better than this. Anyway, today I finally got rid of all my spyware, without having to reinstall windows. I had one particularly pernicious one that couldn’t be removed by conventional means and proved to be quite disabling. It caused my AIM to crash, and slowed my computer down significantly; it went by the name “Home Search Assistant”.

If anyone has this listed on their “Add/Remove” programs list, and having trouble removing it, there is a great step-by-step tutorial online here that will help you out.

Anyway, I finally went Mozilla Firefox all the way now; you won�t catch me dead using Internet Explorer. I recommend you do the same if you happen to like your computer– download it: www.getfirefox.com

FYI: I was obviously kidding about looking for naked pictures of Janet Reno

(It was really for Ruth Ginsberg )

Spamming the Spammers

We all hate those pathetic internet scammers. Have you got the one about inheritance money in Nigeria?

Anyway, Andreas, a friend of mine from my csc436 class pointed out this neat website run by a group called Artists against 419’ers, solely dedicated to fighting back internet scammers. The way it works is pretty clever, from a computer science perspective, and it�s not very hard to understand, let me try and explain:

See, when you purchase hosting from an internet site, like my website for example, you pay for something called bandwidth. My website provider allows me to have 5 GB of transfer a month, meaning every time you load this site it takes away from my 5 GB allowance. Now to put these numbers in perspective, a visit to my site will deduct about 45k or .00042915 GB from my 5 total–which as you can see, is tiny, and with my current modest readership, there is just no way in hell I will ever exceed 5GB. But, if for some reason, I get a million people reading my site, instead of, well… 5, I just might go over this allowance.

Using this same idea, this anti-spam group has created a website that you can go to that will keep reloading websites of known spammers. You simply have to go to a website (make sure you internet cache is disabled) and when enough people do this, the websites of these spammers will eventually exceed their allotted bandwidth, bringing their websites down.

Ah yes, sweet revenge.

Here�s the link