#
# mysqldump.ps1
#

param([String]$s='localhost', [String]$u='root', [String]$p='P@ssw0rd', [String]$out='C:\Backups')

Write-Host "mysqldump.ps1 - Tool for mysql backup of all databases in an server."
Write-Host "Usage:"
Write-Host "  -s:	[localhost] Servername or IP address"
Write-Host "  -u: 	[root] Username"
Write-Host "  -p: 	[P@ssw0rd] Password"
Write-Host "  -out:	[localhost] Output folder to storage dumps"
Write-Host ""
Write-Host ""

$connectionString = 'Server=' + $s + ';Database=mysql;Uid=' + $u + ';Pwd=' + $p
$date = (Get-Date).ToShortDateString().Replace('/', '')
$folder = "$out\$date"

# Create folder if not exists
New-Item -ItemType Directory -Force -Path $folder | Out-Null

try
{
	# Load MySql.Data
	[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
}
catch
{
	$e = $_.Exception.Message
	Write-Host $e
	exit;
}

$connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

# Check if database is closed
If ($connection.State -eq "System.Data.ConnectionState.Closed")
{
	Write-Host $e
	exit;
}

# Get Databases from Server
$q = 'SHOW DATABASES'
$command = New-Object MySql.Data.MySqlClient.MySqlCommand($q, $connection)
$adapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($command)

$ds = New-Object System.Data.DataSet
$adapter.Fill($ds)
$count = 0

# Run all database and try make a dump
Write-Host "Exporting..."

foreach ($rs in $ds.Tables[0].Rows)
{
	$db = $rs['database']
	$msg = ""

	if ($db -eq 'information_schema' -or $db -eq 'mysql')
	{
		continue
	}

	$exported_name = $db + "_" + $date + ".sql"

	# Execute mysqldump
	try
	{
		&mysqldump.exe --user=$u --password=$p --result-file="$folder\$exported_name" --databases $db | Out-Null
		$msg = "Database OK '" + $db + "'"
		$count++
	}
	catch
	{
		$msg = "Database ERROR '" + $db + "'"
	}

	Write-Host $msg
}

Write-Host ""
Write-Host "Total $count exported"
Write-Host "Backup saved here '$folder'"
Write-Host ""

# Close connection
$connection.Close()