Tag Archives: Powershell

MySQL Powershell Backup Script

2 Replies

This is easily one of my most commonly used Powershell scripts, and a variation on it runs on my servers at least once a day.  It is essentially a very simple script: it contacts a specified MySQL server and then generates a .SQL backup files for each database found.  It prefixes each backup filename with the day name (monday, tuesday etc.) so if you run it on daily basis, it gives you a seven day backup rotation for your databases.

As always, the use of this script and the accompanying files is done entirely at your own risk. No guarantee either direct or indirect is implied regarding the security, stability, reliability, impact or performance of this script.

The zip file includes copies of mysqldump.exe and mysql.data.dll that you may require.  For details on how to configure and use the script, please see below.

Download the script (ZIP file)

Configuration

There are a few items you will need to configure before you can use the script in anger.  All of the configuration options are presented at the top of the script, so you don’t need to delve into the main chunk of code.

Backup Store Folder

This is where the MySQL backup files will be placed.  Please enter the path here, but do not include any trailing ‘\’

MySQL User Account

You will require a MySQL user account that has SHOW DATABASES, SELECT, LOCK TABLES and RELOAD permissions.  It is also highly advised – especially if performing backups from a remote machine – that this user’s permissions are also limit to readonly.

mysqldump Path

mysqldump is the application that actually creates the backup file.  It is typically installed with MySQL, but if you are running the script remotely or don’t know the file’s location, mysqldump.exe is included in the ZIP file.

MySQL .Net Connector

The script requires the MySQL .Net Connector driver.  This can either be installed, or you can directly load the DLL file.

Using the connector

  1. Download and install the connector from http://dev.mysql.com/downloads/connector/net/
  2. Uncomment Line 38 of the script.  This should say system.reflection.assembly]::LoadWithPartialName(“MySql.Data”)
Loading the library directly (the default method)
If you don’t wish to install the .Net driver, you can directly load the DLL file instead.  The required DLL file is included as part of the ZIP file.
  1. Uncomment line 43 of the script.  This should say Add-Type -Path “C:\Data\Backups\MySQL\MySql.Data.dll”
  2. Amend the path of line 43 to point to the location of the DLL file

Running the script

You can either run the script manually (through Powershell), or automate it through Task Scheduler.  To make life a little easier I tend to create a batch file containing the Powershell command and then invoke the batch file through the task scheduler.

The general format of the command to include in the batch file is

%windir%\System32\WindowsPowerShell\v1.0\powershell.exe “c:\backups\Generic7DayBackup.ps1”

Lastly…

This is by design a very simple script.  If you have any suggestions on how to improve it, please let me know by posting a comment.

 

Getting System Information from the command line or Powershell

1 Reply

Although there are any number of tools available to gather and collate information regarding the configuration and composition of a computer, these are often overkill when all you wish to learn is one or more basic details like the processor type, memory, computer name etc.

A quick shortcut is to gather this information from the command line or Powershell.  Windows XP and later includes in the systeminfo utility. Typing this at the command prompt will produce information similar to this:

c:\systeminfo
Host Name: SOMEPC
OS Name: Microsoft Windows 7 Ultimate
OS Version: 6.1.7100 N/A Build 7100
OS Manufacturer: Microsoft Corporation
OS Configuration: Member Workstation
OS Build Type: Multiprocessor Free
Registered Owner: SomeOwner
Registered Organization:
Product ID: 00428-321-7001132-70186
Original Install Date: 04/05/2009, 10:29:33
System Boot Time: 28/09/2009, 13:50:08
System Manufacturer: Dell Inc.
System Model: Latitude D820
System Type: X86-based PC
Processor(s): 1 Processor(s) Installed.
[01]: x86 Family 6 Model 14 Stepping 8 GenuineIntel ~2000 Mhz
BIOS Version: Dell Inc. A09, 04/06/2008
Windows Directory: C:\Windows
System Directory: C:\Windows\system32
Boot Device: \Device\HarddiskVolume2
System Locale: en-gb;English (United Kingdom)
Input Locale: en-gb;English (United Kingdom)
Time Zone: (UTC) Dublin, Edinburgh, Lisbon, London
Total Physical Memory: 3,326 MB
Available Physical Memory: 858 MB
Virtual Memory: Max Size: 8,313 MB
Virtual Memory: Available: 5,500 MB
Virtual Memory: In Use: 2,813 MB
Page File Location(s): C:\pagefile.sys
Domain: somedomain.lan
Logon Server: \\SOMEDC
Hotfix(s): 4 Hotfix(s) Installed.
[01]: KB958830
[02]: KB969497
[03]: KB970789
[04]: KB970858
Network Card(s): 3 NIC(s) Installed.
[01]: Intel(R) PRO/Wireless 3945ABG Network Connection
Connection Name: Wireless Network Connection
DHCP Enabled: Yes
DHCP Server: 10.10.0.1
IP address(es)
[01]: 10.10.0.100
[02]: fe80::901:8ac7:5a6b:1f56
[02]: Broadcom NetXtreme 57xx Gigabit Controller
Connection Name: Local Area Connection
Status: Media disconnected

If you are using Powershell – if not, why not? – the get-wmiobject win32_computersystem command will return rudimentary details regarding the host PC.

PoSH>get-wmiobject win32_computersystem
Domain              : somedomain.lan
Manufacturer        : Dell Inc.
Model               : Latitude D820
Name                : SOMEPC
PrimaryOwnerName    : SomeOwner
TotalPhysicalMemory : 3487690752