Jump to content

Need help connecting to Amazon Web Services (AWS) using AutoIT MySQL UDF


Go to solution Solved by poila,

Recommended Posts

Hi,

I recently got a request to transfer databases from a Microsoft Server to an online Amazon Web Services (AWS) server.

I was able to export all the data from MySQL Workbench and use phpMyAdmin to import the data into that AWS instance.

The issue is that my AutoIT program uses a MySQL UDF (see: '?do=embed' frameborder='0' data-embedContent>>) and it uses ODBC connectors.

When I changed the string containing the path of the server from "localhost" to the provided IP address, it could not detect any connection, even though I could access that IP address from my Chrome browser and access phpMyAdmin.

I am also wondering and searching for how do I really use PuTTY.exe and PSftp.exe as well as the private key to connect via SSH in the background.

How do I accomplish this with AutoIT? Is this approach recommended, or is there a more elegant and secure way of connecting to AWS EC2 instance with AutoIT?

Additionally, is it necessary to tweak some settings in AWS (eg. my.conf) to enable incoming connections from specified IP addresses?

Thanks!

Edited by poila
Link to post
Share on other sites

(Bumping thread...)

Attempted to solve the problem of trying to connect to MySQL database in AWS via SSH, but was unsuccessful.

Read this link prior ('?do=embed' frameborder='0' data-embedContent>>) but still confused.

I've already set remote port forwarding to 3306 in /etc/my.cnf, still confused; what else is required to enable SSH connection, if used in conjunction with the MySQL UDF?

port=3306
bind-address=0.0.0.0

I could connect to the AWS instance using PSftp.exe (get from a Windows Command Prompt and open the remote working directory

(I didn't write it into AutoIT syntax though... not sure if this is needed to ensure a connection to a remote MySQL database via SSH):

psftp.exe userNameAtAWS@XX.XX.XX.XX -i privateKeyFile.ppk

AutoIT Code sample:

#include "MySQL.au3"
; This UDF is by cdkid

Global Const $serverUser = "serverUser"
Global Const $serverPass = "serverPassword"

Global $const_fileServerName = "server.ini"

; server.ini contains the text, "XX.XX.XX.XX", which is the IP address accessible by browser but not by AutoIT

Func connectToMySQL() ;     Establishes connection with mySQL Database
    Local $mySQLServerName = FileRead($const_fileServerName)
    $global_normalSQLInstance = _MySQLConnect($serverUser, $serverPass, "databaseName", $mySQLServerName)
    $global_SQLInstance = $global_normalSQLInstance
    If $global_normalSQLInstance = 0 Or $global_tutorialSQLInstance = 0 Then
        Return 0
    Else
        Return 1
    EndIf
EndFunc

Small update: Using PuTTY again, I used the command sudo iptables -L -n and found out that all incoming connections are allowed (these are the firewall rules).

So I realised that it might have to do with the security group's rules in that AWS instance. I don't have direct access to it, so I might update this post again once I got hold of the engineer with access rights to it.

Edited by poila
Link to post
Share on other sites
  • 2 weeks later...
  • Solution

(Bumping thread... again... and if it annoys you... FORGIVE ME!)

Seriously though...

Turns out that this is not so much of an AutoIT issue but more of an AWS Security Groups issue.

Simple solution is open port 3306 via AWS Console, add MySQL to list of programs with permission to allow access to port 3306, restart MySQL like this:

sudo service mysqld restart

And re-ping to the specific IP address for that AutoIT program (eg. XX.XX.XX.X).

For another sanity check, use telnet to check if the MySQL connection can be done remotely.

(Do this via Command Prompt in Windows)

telnet XX.XX.XX.X 3306 

Problem solved. Note that however, because that AutoIT program is accessing the MySQL server from AWS, the performance will be significantly hit because of longer-distance connections.

Additionally, table names in the MySQL database are CaSe-SenSiTive. This means a major refactoring of the AutoIT script if it gets unsanitary (using myTable and MyTable interchangeably... yay).

Edited by poila
Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By argumentum
      $sQueryUpdateTime = "select intUpdateTime from tblStudies " . $where . " ORDER BY intUpdateTime DESC limit 1"; $rs = mysqli_query($conn, $sQueryUpdateTime); $row = mysqli_fetch_assoc($rs); the above used to take 300+ ms. to query. Then I set it as index and takes 30 ms. Cool.
      $sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC limit $offset,$rows"; // takes 30 ms. on the indexed int $sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC , PatientName ASC limit $offset,$rows"; // takes 300 ms. due to "PatientName" been a text field, even as I did index it So my observation is that "PatientName" takes a long time to sort, even tho "$rows = 20". Sorting text in 20 rows should be fast.
      ..tho, I find that any 2nd argument in the ORDER BY is just slow.
      Is there a way to query this in a way to have a faster result back ?
      Thanks
      PS: added ADD INDEX `StudyDate_2` (`StudyDate`, `PatientBirthDate`) USING BTREE;  and searched by those two with not much speed change ( StudyDate and PatientBirthDate are integer ).
    • By argumentum
      I was thinking but I don't have the experience, so you may have the experience.
      I was thinking to chop a DB with 100.000 ( 20 columns ) in 10.000 DB chunks ( 10 DBs ) and query concurrently all of them to speed up a search, then add the results. Is that a sound idea ? Or will I run in trouble down the road. Should a DB better be keep in one piece ?
      The DB is now in MySQL. I wanna do all this chopping and use SQLite. 
      Thanks
      [solved]
    • By Case85
      Hi for all!
       
      After a long time I wasted to find the best way to connect to any external MySQL server, I'm ready to hire "C" or "C++" developer to use MySQL connector dll file for create a stable way to connect to a MySQL server.
      I already tried to use the UDF "miniSQL - A simple Standalone PDO-syntax-like MySQL-library with AutoitObject" which is working perfect (this way what I looking for), but unfortunately after 1-2 hours usage failed and exit the script.
      I plan to use the MySQL connector heavily and continuously on long time.
      The developed connector must be have a small memory footprint.
      My budget is moderated, I need this connector quick as possible, please contact with me in PM for more details.
      Thank you for all.
    • By rynow
      romaSQL
      This autoIt UDF is built on the concept of Laravel Query & doctrine.
      RomaSQL provides a new, comfortable and easy to use way for SQL-queries in autoIt.
      Most of the common SQL-queries are supported already and more are coming soon.
      All of your support is much appreciated.
      Connections
      For the connection the object ADODB is used. Therefore the connection string is based on ODBC.
      You can also use OLEDB connection strings or other database connections.
      In order for this to work your add-ons have to be installed in the function: __4ern_SQL_Connection.
      I’d be very glad if you shared your modifications with me.
      Currently supported connections
      -       MySQL (odbc)
      -       Microsoft SQL Server (odbc)
      -       SQLite (odbc)
      -       Microsoft Access (odbc)
      Command reference
      $SQL_connect; establishing connection $SQL_returnType; return a Array or Dictionary ('oDict') Object (Default = Array) $SQL_setDefaultTable; Default Tablename $SQL_setDefaultKey; Default Colmn Key (Default = id) $SQL_debug; if True, show SQL Statment in Console $SQL_get $SQL_update $SQL_delete $SQL_insertInto $SQL_take $SQL_limit $SQL_table $SQL_select $SQL_distinct $SQL_where $SQL_orWhere $SQL_whereBetween $SQL_whereNotBetween $SQL_whereIn $SQL_whereNotIn $SQL_whereNull $SQL_whereNotNull $SQL_having $SQL_orHaving $SQL_havingBetween $SQL_havingNotBetween $SQL_havingIn $SQL_havingNotIn $SQL_havingNull $SQL_havingNotNull $SQL_groupBy $SQL_orderBy  
      Examples
      establishing connection
      ;-----/ ; SQLite Connection ;-----/ $SQL_setDatabase('sqlite') $SQL_connect('C:\project.db') ;-----/ ; Access Connection ; Database, User, Password ;-----/ $SQL_setDatabase('access') $SQL_connect('C:\project.mdb') ;or as Admin $SQL_connect('C:\project.mdb', '4ern', 'root') ;-----/ ; SQLServer Connection ; Database, User, Password, Server, Driver ;-----/ $SQL_setDatabase('sqlserver') $SQL_connect('myDB', '4ern', 'root', 'localhost') ;or with Driver $SQL_connect('myDB', '4ern', 'root', 'localhost', 'SQL Server') ;-----/ ; MySQL Connection ; Database, User, Password, Server, Driver ;-----/ $SQL_setDatabase('mysql') $SQL_connect('myDB', '4ern', 'root', 'localhost') ;or with Driver $SQL_connect('myDB', '4ern', 'root', 'localhost', 'MySQL ODBC 5.2 UNICODE Driver')  
      simple SQL query
      $SQL_table('albums') $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif  
      Select
      $SQL_table('albums') $SQL_select('id', 'Name', 'Artist', 'Song') ;or pass to an Array Local $aSelect = ['id', 'Name', 'Artist', 'Song'] $SQL_select($aSelect) $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif where
      $SQL_table('albums') $SQL_select('id', 'Name', 'Artist', 'Song', 'Votes') $SQL_where('Artist', 'adele') $SQL_where('Votes', '>=' ,'9') $SQL_orWhere('Artist', '=' ,'Rag'n'Bone Man') ;or pass to an 2dArray Local $aSelect = [['Artist','adele'],['Votes', '>=' ,'9']] $SQL_where($aSelect) $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif  
      If you need more examples, then tell me exactly what you need.
      I hope you like my UDF and find some use for it.
      ---
      ->DONWLOAD romaSQL
       
       
×
×
  • Create New...