Listen to this textI’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.