人人IT網

人人IT網

當前位置: 主頁 > 數琚庫 >

使用Powershell實現數據庫自動化運維

時間:2016-11-07 18:59來源:Internet 作者:Internet 點擊:
使用Powershell實現數據庫自動化運維目前市場上針對SQLServer的圖書,好的原創圖書屈指可數,翻譯的圖書較多,但只限於專門針對SQL Server數據庫本身的開發、管理和商業智能。而針對P

使用Powershell實現數據庫自動化運維


目前市場上針對SQLServer的圖書,好的原創圖書屈指可數,翻譯的圖書較多,但只限於專門針對SQL Server數據庫本身的開發、管理和商業智能。


而針對PowerShell的圖書,可以說大多为MSDN裏的學習筆記,主要針對Windows操作系統的管理。

微軟在大的布局和技術動向來看,力推雲平台。而平台化的基礎是自動化、高可用。那麼細化到最基礎的技術着眼點,微軟正在大力開發其所有服務器端產品對 PowerShell的支持。針對SQL Server來說,運用好PowerShell這項技能來將管理任務自動化,才能實現進一步的平台化,它是雲平台的基石。


國外大力推廣的DEVOPS、開發型運維,不僅僅讓數據庫管理員或系統管理員局限於手工來操作繁雜的日常工作,這样風險極大。學習PowerShell來提升腳本開發能力,讓日常工作化繁为簡,是大勢所趨。


我翻譯了一本微軟MVP的《PowerShell V3 -- SQL Server 2012數據庫自動化運維權威指南》,這本書涉及的知識點非常全面、實用性很強。對SQL Server DBA來說,是提高數據庫管理技能的利器。


下面我通過兩個實例來講解下,使用PowerShell如何實現對SQL Server和MongoDB的自動化運維的。


一、 恢复SQL Server數據庫到一個時間點


在本方案中,我們將使用不同備份文件恢复到一個時間點。


准備


在本方案中,我們將使用AdventureWorks2008R2數據庫。你也可以選擇你的開發環境中的你更喜歡的數據庫。


AdventureWorks2008R2數據庫有一個包含一個單獨數據庫文件的文件組。我們將使用來自以下三種不同的備份類型的三個不同備份文件,來基於時間點恢复數據庫到另一個SQL Server實例:

  • 完整備份

  • 差異備份

  • 事務日志備份


我們可以使用PowerShell,像在之前的方案描述的,在AdventureWorks2008R2數據庫上創建這三種類型的備份。如果你對T-SQL相當熟悉,你也可以使用T-SQL備份命令。


为了幫助我們驗證是否基於時間點的恢复的結果是我們期待的,在做任何類型的備份之前,創建一個時間戳標識的表。相應的,創建一個表,並在備份前插入一個時間戳標識的記錄到表中。


將這些備份放在C:\Backup\目錄。

clip_image001


你可以使用下面的腳本來創建你的文件,6464 - Ch06 - 10 - Restore a database to a point in time - Prep.ps1,它包含在本書的可下載文件中。當腳本執行完整後,你應該在AdventureWorks2008R2數據庫中有時間戳標識的Student表,以一分钟的間隔創建,類似於下面的截屏:

(譯者注:可以從https://www.packtpub.com/books/content/support/10233下載該書代碼。)


clip_image002


對於我們的方案,我們將恢复AdventureWorks2008R2數據庫到另一個實例,KERRIGAN\SQL01,到2015-07-27 02:51:59。意味着,在基於時間點的恢复完成後,我們將只有四個時間戳標識的Student表在KERRIGAN\SQL01在恢复的數據庫上:

  • StudentFull_201507270247

  • StudentDiff_201507270249

  • StudentTxn_201507270250

  • StudentTxn_201507270251


如何做…


为了使用完整、差異和一些事務日志文件恢复到一個時間點,遵循如下步驟:

1. 通過“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打開PowerShell控制台。


2. 導入SQLPS模塊:  

#import SQL Server module    
Import-Module SQLPS -DisableNameChecking


3. 添加如下腳本並運行:  

$instanceName = "KERRIGAN\SQL01"    
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName    
#backupfilefolder    
$backupfilefolder = "C:\Backup\"    
#look for the last full backupfile    
#you can be more specific and specify filename    
$fullBackupFile =     
Get-ChildItem $backupfilefolder -Filter "*Full*" |    
Sort -Property LastWriteTime -Descending |    
Select -Last 1    
#read the filelist info within the backup file    
#so that we know which other files we need to restore    
$smoRestore = New-Object Microsoft.SqlServer.Management.Smo.Restore    
$smoRestore.Devices.AddDevice($fullBackupFile.FullName, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)    
$filelist = $smoRestore.ReadFileList($server)    
#read headers of the full backup file,     
#because we are restoring to a default instance, we will    
#need to specify we want to move the files    
#to the default data directory of our KERRIGAN\SQL01 instance    
$relocateFileList = @()    
$relocatePath = "C:\Program Files\Microsoft SQL Server\MSSQL11.SQL01\MSSQL\DATA"    
#we are putting this in an array in case we have     
#multiple data and logfiles associated with the database    
foreach($file in $fileList)    
{    
#restore to different instance    
#replace default directory path for both     
$relocateFile = Join-Path $relocatePath (Split-Path $file.PhysicalName -Leaf)    
$relocateFileList += New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($file.LogicalName, $relocateFile)    
}    
#let's timestamp our restored databasename    
#this is strictly for testing our recipe    
$timestamp = Get-Date -Format yyyyMMddHHmmss    
$restoredDBName = "AWRestored_$($timestamp)"    
#====================================================    
#restore the full backup to the new instance name    
#====================================================    
#note we have a NoRecovery option, because we have    
#additional files to restore    
Restore-SqlDatabase `    
-ReplaceDatabase `    
-ServerInstance $instanceName `    
-Database $restoredDBName `    
-BackupFile $fullBackupFile.FullName `    
-RelocateFile $relocateFileList `    
-NoRecovery    
#====================================================    
#restore last differential    
#note the database is still in Restoring State    
#====================================================    
#using PowerShell V2 Where syntax    
$diffBackupFile =     
Get-ChildItem $backupfilefolder -Filter "*Diff*" |    
Where {$_.LastWriteTime -ge $fullBackupFile.LastWriteTime} |    
Sort -Property LastWriteTime -Descending |    
Select -Last 1    
Restore-SqlDatabase `    
-ReplaceDatabase `    
-ServerInstance $instanceName `    
-Database $restoreddbname `    
-BackupFile $diffBackupFile.FullName `    
-NoRecovery    
#====================================================    
#restore all transaction log backups from last    
#differential up to 2015-07-27 02:51:59    
#====================================================    
#identify the last txn log backup file we need to restore    
#we need this so we can specify point in time    
$lastTxnFileName = "AdventureWorks2008R2_Txn_201507270251"    
$lastTxnBackupFile =     
Get-ChildItem $backupfilefolder -Filter "*$lastTxnFileName*"    
#restore all transaction log backups after the     
#last differential, except the last transaction    
#backup that requires the point-in-time restore    
foreach ($txnBackup in Get-ChildItem $backupfilefolder -Filter     
"*Txn*" |     
Where {$_.LastWriteTime -ge $diffBackupFile.LastWriteTime -and     
$_.LastWriteTime -lt $lastTxnBackupFile.LastWriteTime} |    
Sort -Property LastWriteTime)    
{    
Restore-SqlDatabase `    
-ReplaceDatabase `    
-ServerInstance $instanceName `    
-Database $restoreddbname `    
-BackupFile $txnBackup.FullName `    
-NoRecovery     
}     
#restore last txn backup file to point in time    
#restore only up to 2015-07-27 02:51:59    
#this time we are going to restore using with recovery    
Restore-SqlDatabase `    
-ReplaceDatabase `    
-ServerInstance $instanceName `    
-Database $restoreddbname `    
-BackupFile $lastTxnBackupFile.FullName `    
-ToPointInTime "2015-07-27 02:51:59"


如何實現…


在本方案中,我們使用Restore-SqlDatabase cmdlet,與Backup-SqlDatabase相對的cmdlet在SQL Server 2012中被介紹。


讓我們從高層概覽下如何實施時間點恢复,然後我們可以細化,並解釋包含在本方案中的片段:

1. 收集你的備份文件。

  • 識別包含你想恢复的時間點的最後事務日志備份文件。


2. 恢复最後的好的完整備份使用NORECOVERY。


3. 恢复最後的在完整備份後的差異備份使用NORECOVERY。


4. 恢复在差異備份後的事務日志備份:

  • 使用NORECOVERY恢复,直到包含你想恢复的時間點的日志文件備份。你需要恢复最後的日志文件備份到一個時間點,也就是說,你需要指定需要恢复的時間。最後,使用WITH RECOVERY恢复數據庫,使得數據庫可訪問並以備使用。

  • 或者,你可以使用NORECOVERY恢复所有的事務日志備份文件,在包含你想恢复到的時間點的日志備份前。接下來,使用WITH RECOVERY恢复最後的日志備份到一個時間點,那就是說,你需要指定恢复到何時。


步驟1 � 收集你的備份文件


你需要收集你的備份文件。它們不必位於相同的目錄或驅動設備,但這样理想些,這样可以簡化你的恢复腳本,你將有一個統一的目錄或驅動設備去参照。你也需要這些文件的讀許可權限。


在我們的方案,我們簡化了這個步驟。我們收集了完整,差異和事務日志備份文件,存儲它們到C:\Backup\目錄,易於訪問。如果你的備份文件位於不同的位置,你只需要适當的調整你腳本的参照目錄。


一旦有了這些備份文件,假設你遵循着文件命名規範,你可以過滤你目錄中的所有完整備份。在我們的示例中,我們使用命名規範databasename_type_timestamp.bak。對於這個方案,我們通過在文件名中指定關鍵字或匹配模式來抽取完整備份文件。我們使用Get-ChildItem過滤最新的完整備份文件:

#look for the last full backupfile
#you can be more specific and specify filename
$fullBackupFile =
Get-ChildItem $backupfilefolder -Filter "*Full*" |
Sort -Property LastWriteTime -Descending |
Select -Last 1


一旦你有了完整備份的句柄,你可以讀取存儲在備份文件中的文件列表。你可以使用SMO Restore對象的ReadFileList方法。讀取文件列表可以幫助你通過抽取你需要恢复的數據和日志文件的文件名來實現自動化。

#read the filelist info within the backup file
#so that we know which other files we need to restore
$smoRestore = New-Object Microsoft.SqlServer.Management.Smo.Restore
$smoRestore.Devices.AddDevice($fullBackupFile.FullName, [Microsoft.
SqlServer.Management.Smo.DeviceType]::File)
$filelist = $smoRestore.ReadFileList($server)


當讀取文件列表時,你可以抽取的一個屬性是存儲的文件的類型:

clip_image003


不同的類型为:

  • L代表日志文件

  • D代表數據文件

  • F代表全文目錄


步驟2 � 使用NORECOVERY恢复最後的好的完整備份


在恢复操作的第一步是恢复最後的已知好的完整備份。這提供了你一個基線,基於此你可以恢复額外的文件。NORECOVERY選項非常重要,它保持(或不回滾)未提交的事務,並允許額外的文件被恢复。我們將會使用NORECOVERY選項在我們真個恢复過程中。


因为完整備份總是第一個需要恢复的文件,所有的准備工作需要就緒,此時移動文件也開始。


對於我們的方案,我們想去恢复數據庫,從源默認實例KERRIGAN到另一個實例KERRIGAN\SQL01。因此,我們需要移動我們的文件,從存儲我們備份文件的路徑,到我們想去使用的新路徑。在這個例子中,我們只想從默認實例的默認數據目錄,移動到命名實例KERRIGAN\SQL01的數據目錄。我們從文件列表的原始數據和日志文件獲取完整的路徑,使用我們想去恢复到的新位置來替代完整路徑。在下面片段中的高亮代碼顯示了如何修改位置:

$relocateFileList = @()

$relocatePath = "C:\Program Files\Microsoft SQL Server\MSSQL11.SQL01\

MSSQL\DATA"

#we are putting this in an array in case we have

#multiple data and logfiles associated with the database

foreach($file in $fileList)

{

#restore to different instance

#replace default directory path for both

$relocateFile = Join-Path $relocatePath (Split-Path $file.

PhysicalName -Leaf)

$relocateFileList += New-Object Microsoft.SqlServer.Management.

Smo.RelocateFile($file.LogicalName, $relocateFile)

}


注意,我們的數組包含了Microsoft.SqlServer.Management.Smo.RelocateFile對象,將包含我們數據庫文件的邏輯和(重定位的)物理文件名。

$relocateFileList += New-Object Microsoft.SqlServer.Management.Smo.
RelocateFile($file.LogicalName, $relocateFile)


为了恢复我們的數據庫,我們只使用Backup-SqlDatabase cmdlet。這裏有一對很重要的選項,像RelocateFile和NoRecovery。

#restore the full backup to the new instance name
#note we have a NoRecovery option, because we have
#additional files to restore
Restore-SqlDatabase `
-ReplaceDatabase `
-ServerInstance $instanceName `
-Database $restoredDBName `
-BackupFile $fullBackupFile.FullName `
-RelocateFile $relocateFileList `
-NoRecovery


步驟3 � 在完整備份恢复完後,使用NORECOVERY恢复最後的好的差異備份

一旦完整備份恢复,你可以添加最後的好的差異備份跟隨着完整備份。他並不是一個集成的過程,因为在這點上我們已經恢复了基礎數據庫並重定位了我們的文件。我們需要使用NORECOVERY恢复差異備份,阻止未提交的事務被回滾:

#using PowerShell V2 Where syntax

$diffBackupFile =

Get-ChildItem $backupfilefolder -Filter "*Diff*" |

Where {$_.LastWriteTime -ge $fullBackupFile.LastWriteTime} |

Sort -Property LastWriteTime -Descending |

Select -Last 1

Restore-SqlDatabase `

-ReplaceDatabase `

-ServerInstance $instanceName `

-Database $restoreddbname `

-BackupFile $diffBackupFile.FullName `

-NoRecovery


注意,在你的環境中,你可能有,也可能沒有一個差異備份文件。如果沒有,不用擔心,它不會影響到你的可恢复性,只要所有的事務日志文件可用於恢复。


步驟4 � 在恢复差異備份後恢复事務日志


在我們恢复了差異備份文件,我們開始恢复我們的事務日志備份文件。這些事務日志備份文件應該是跟隨着你的差異備份。你可能需要,或不需要跟隨着差異備份的日志文件的完整集合。如果你需要恢复直到數據庫故障點,你將需要恢复所有的事務日志備份包括尾日志備份。如果不是,你只需要知道你想恢复的事件點的備份文件。


對於我們的方案,我們識別出我們想去恢复的最後日志備份文件。這很重要,因为我們需要知道如何使用PointInTime参數,當我們使用這個特定的事務日志備份文件時。

#identify the last txn log backup file we need to restore
#we need this so we can specify point in time
$lastTxnFileName = "AdventureWorks2008R2_Txn_201507270252"
$lastTxnBackupFile =
Get-ChildItem $backupfilefolder -Filter "*$lastTxnFileName*"


對於所有其他的事務日志備份文件,我們遍曆所有的備份目錄,恢复所有的在最後差異備份後的,在我們想去恢复的最後事務日志備份文件之前的所有.txn文件。我們也需要通過WriteTime参數來排序這些文件,以便於我們依次恢复它們到數據庫。注意,我們需要使用NORECOVERY恢复所有的這些文件。

foreach ($txnBackup in Get-ChildItem $backupfilefolder -Filter "*Txn*"

|

Where {$_.LastWriteTime -ge $diffBackupFile.LastWriteTime -and

$_.LastWriteTime -lt $lastTxnBackupFile.LastWriteTime} |

Sort -Property LastWriteTime)

{

Restore-SqlDatabase `

-ReplaceDatabase `

-ServerInstance $instanceName `

-Database $restoreddbname `

-BackupFile $txnBackup.FullName `

-NoRecovery

}


一旦所有的這些文件恢复後,然後我們准備恢复最後的事務日志文件。一旦這個文件恢复,數據庫需要可訪問,所有的未提交事務需要被回滾。


有兩個方法去實現。第一個方法,我們在這個方案中使用的,是去使用ToPointInTime参數恢复最後的文件,並且不使用NoRecovery参數。

Restore-SqlDatabase `
-ReplaceDatabase `
-ServerInstance $instanceName `
-Database $restoreddbname `
-BackupFile $lastTxnBackupFile.FullName `
-ToPointInTime "2015-07-27 02:51:59"


另一個方法是恢复最後的事務日志文件,也使用NoRecovery,但是在最後添加另一個命令,使用WITH RECOVERY恢复該數據庫。實際上,一直以來使用NORECOVERY恢复所有需要的事務日志備份文件更为安全。它更安全,是因为當我們突然使用WITH RECOVERY恢复一個文件,糾正它的唯一方法是重做整個恢复過程。這可能對於小型數據庫沒多大關系,但是對於大型數據庫可能非常消耗時間。


一旦我們確認所有需要的文件已經被恢复,我們就可以使用WITH RECOVERY來恢复數據庫。在我們的方案中,一個方法是使用T-SQL語句,並傳遞該語句到Invoke-Sqlcmd:

#get the database out of Restoring state
#make the database accessible
$sql = "RESTORE DATABASE $restoreddbname WITH RECOVERY"
Invoke-Sqlcmd -ServerInstance $instanceName -Query $sql


RESTORE DATABASE命名使得我們的數據庫從一個正在恢复中的狀態,到可訪問和以備使用狀態。RESTORE命名回滾了所有未完成的事務,並讓數據庫以備使用。


二、 使用PowerShell調用MTools分析MongoDB性能並發送郵件


在MongoDB日常運維中,經常需要查看連接數的趨勢圖、慢查詢、Overflow語句、連接來源。任何數據庫的DBA都應該對數據庫情況進行定期的巡檢,以清楚了解數據庫的運行情況,健康狀況,隱患等等。MTools工具應運而生,它帶给DBA極大地幫助。


Mtools簡介


Mtools是由MongoDB Inc 官方工程師所寫,設計之初是为了方便自己的工作,但是隨着MongoDB用戶的增加,越來越多的朋友也開始使用Mtools,也越來越感受到Mtools帶來的便捷。


Github地址如下:

Mtools github地址


Mtools主要有以下組件:

  • mlogfilter

  • mloginfo

  • mplotqueries

  • mlogvis

  • mlaunch

  • mgenerate


首先,我們來簡單介紹下 mlogfilter,mloginfo和mplotqueries。


mlogfileter我們可以簡單理解为日志的過滤器,参數如下:


mlogfilter [-h] [--version] logfile [logfile ...]

[--verbose] [--shorten [LENGTH]]

[--human] [--exclude] [--json]

[--timestamp-format {ctime-pre2.4, ctime, iso8601-utc, iso8601-local}]

[--markers MARKERS [MARKERS ...]] [--timezone N [N ...]]

[--namespace NS] [--operation OP] [--thread THREAD]

[--slow [SLOW]] [--fast [FAST]] [--scan]

[--word WORD [WORD ...]]

[--from FROM [FROM ...]] [--to TO [TO ...]]


示例:

通過mlogfilter查詢日志中某個表的slow log(超過100ms的)

mlogfilter --namespace xxx.xx --slow 100 mongod.log-20160611


mloginfo可以過滤總結出slow query的情況,以及为日志中各類最常常出現情況進行統計,参數如下:


mloginfo [-h] [--version] logfile

[--verbose]

[--queries] [--restarts] [--distinct] [--connections] [--rsstate]


示例:

通過mloginfo統計日志中connections的來源情況

mloginfo mongod.log-20160611 --connections


mplotqueries相對复雜一些,功能是可以根據需求畫圖,以便更直觀的找出問題或者隱患所在,参數如下:


mplotqueries [-h] [--version] logfile [logfile ...]

[--group GROUP]

[--logscale]

[--type {nscanned/n,rsstate,connchurn,durline,histogram,range,scatter,event} ]

[--overlay [ {add,list,reset} ]]

[additional plot type parameters]


示例:

通過mplotqueries對連接情況進行分析,時間塊單位1800(30min)

mplotqueries mongod.log-20160611 --type connchurn --bucketsize 1800 --output-file 01-9.png


解决方案


筆者將在Windows上安裝MTools工具來分析mongod.log日志,然後通過Powershell發送郵件。

1. 將Windows備份機目錄掛載到MongoDB本地目錄下,將LogRotate切換後的最新一個日志拷貝到備份目錄。

参考博文:《在Linux下掛載Windows系統共享目錄》


2. 在Windows服務器上安裝Mtools。

参考博文:《在64位Windows Server 2008 R2上安裝mtools》


3. 編寫PowerShell腳本,通過Mtools分析日志文件,並發送郵件。

Github源碼地址:http://go.rritw.com/github.com/UltraSQL/MongoDBDailyReport.git


使用方法:

a) 將DBA模塊放到相應的Modules\DBA目錄下。

b) 在配置文件中加載模塊:Import-Module DBA -Force。

c) 創建任務計劃,定時執行該MTools.ps1腳本。  
clip_image005

本文出自 “SQL Server Deep Dive” 博客,請務必保留此出處http://go.rritw.com/ultrasql.blog.51cto.com/9591438/1870221


From:51CTO
頂一下
(0)
0%
踩一下
(0)
0%
------分隔線----------------------------
發表評論
請自覺遵守互聯網相關的政策法規,嚴禁發布色情、暴力、反動的言論。
評價:
表情:
驗證碼:點擊我更換圖片
欄目列表
推薦內容