Powershell connection to MySQL

Generally, powershell directly uses invoke-sqlcmd to connect to SQL Server. This is convenient, but sometimes you still need to connect to other databases, such as MySQL.

Here is a summary of powershell connection to MySQL:

Connecting to MySQL is through the. NET Framework, so you must first install mysql. Net connector (http://dev.mysql.com/downloads/connector/net)

If you do not want to install mysql. Net connector, you can copy it directly on other installed machines.MySQL. Data. DLL to the desired machine.

Connection method:

If mysql. Net connector is installed

 
[Void] [system. reflection. Assembly]: loadwithpartialname ("MySQL. Data")
 

If you use the mysql. Data. dll file directly

$ Mysqldatadll="C: \ scripts \ mysql. Data. dll"[Void] [system. reflection. Assembly]: Loadfrom ($ Mysqldatadll)
 

After reading the DLL, we can process it:

 $ Connectionstr = "  Server = 192.168.1.1; uid = user; Pwd = user123; database = mydb;  "  $ Querysql = "  Select MSG from messages limit 1;  " $ Connection = New- Object mysql. Data. mysqlclient. mysqlconnection  $ Connection . Connectionstring = $ Connectionstr  $ Connection  . Open ()  $ Command = New-object mysql. Data. mysqlclient. mysqlcommand ( $ Querysql , $ Connection  )  $ Dataadapter = New-object mysql. Data. mysqlclient. mysqldataadapter ( $ Command )  $ Dataset = New- Object System. Data. Dataset  $ Recordcount = $ Dataadapter . Fill ( $ Dataset  )  $ Dataset . Tables [0]

Here we will get a system. data. dataSet object. We use tables [0] To return 0th table sets. This can already return the dataset we need, but if we want to further process the row set:

$ Table=$ Dataset. Tables [0]Foreach($ Row In $ Table. Rows ){$ Row. MSG}
 

 

If you want to know the number of columns in this table, you can use the following method:

 
$ Table. Columns | format-table