{"id":1850,"date":"2023-04-05T21:45:12","date_gmt":"2023-04-05T15:45:12","guid":{"rendered":"http:\/\/techsatwork.com\/?p=1850"},"modified":"2023-04-05T21:45:13","modified_gmt":"2023-04-05T15:45:13","slug":"how-to-access-aurora-mysql-from-python-code-via-a-bastion-server","status":"publish","type":"post","link":"https:\/\/techsatwork.com\/?p=1850","title":{"rendered":"How to access Aurora MySQL from Python code via a bastion server"},"content":{"rendered":"\n

This example assumes you have a bastion server setup that will allow connection to follow thru to an existing MySQL server. The python code is run on an EC2 server that has security group setup on the bastion server to allow connections from the EC2 server.<\/p>\n

We need the following info:<\/p>\n

    \n
  1. \n

    On the EC2 server make sure the SSHTunnerForwarder and pymysql libraries are installed using pip install<\/p>\n<\/li>\n

  2. \n

    Get the following info:<\/p>\n

      \n
    1. \n

      bastion server name<\/p>\n<\/li>\n

    2. \n

      pem file to connect to bastion server along with the userid<\/p>\n<\/li>\n

    3. \n

      Aurora mysql connection endpoint with the port<\/p>\n<\/li>\n

    4. \n

      Aurora userid and password<\/p>\n<\/li>\n

    5. \n

      Database name you want to connect to<\/p>\n<\/li>\n

    6. \n

      Ensure that the localhost resolves to 127.0.0.1 if not then figure out what the local ip is<\/p>\n<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n

      Here is a sample code that demonstrate how the python code can be constructed:<\/p>\n

      \n
      \n
      \u00a0<\/div>\n<\/code><\/span><\/span>\n

      import pymysql
      from sshtunnel import SSHTunnelForwarder

      #ssh bastion host information<\/p>\n

      ssh_host = ‘<bastion server name>’ \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0### bastion server name
      ssh_username = ‘ec2-user’
      ssh_pkey = ‘<location of the pem file>’ \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ### pem file for the bastion server
      ssh_port = 22

      # setting up the ssh tunnel

      ssh_tunnel = SSHTunnelForwarder(
      (ssh_host, ssh_port),
      ssh_username=ssh_username,
      ssh_pkey=ssh_pkey,
      remote_bind_address=(‘<Mysql connection end point server name>’, 3306) # MySQL end point and my sql port
      )

      #start the ssh tunnel

      ssh_tunnel.start()

      #mysql database connection info

      db_user = ‘<mysql user id >’
      db_password = ‘<password for the mysql userid>’
      db_name = ‘<name of the mysql database’
      db_host = ‘127.0.0.1’

      #connection string to mysql via ssh_tunnel

      mysql_conn = pymysql.connect(
      host=db_host, \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 # Use localhost, as the tunnel forwards the connection
      port=ssh_tunnel.local_bind_port,
      user=db_user,
      password=db_password,
      db=db_name
      )

      #connect connect and run the query via cursor

      cursor = mysql_conn.cursor()
      cursor.execute(‘SELECT * FROM <table name>’)
      for row in cursor.fetchall():
      print(row)

      #close mysql connection and stop the tunnel
      mysql_conn.close()
      ssh_tunnel.stop()<\/p>\n<\/code><\/span><\/span><\/code><\/span><\/span><\/code><\/span><\/span><\/code><\/span><\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

      This example assumes you have a bastion server setup that will allow connection to follow thru to an existing MySQL server. The python code is run on an EC2 server that has security group setup on the bastion server to allow connections from the EC2 server. We need the following info: On the EC2 server […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","site-transparent-header":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[961,358],"tags":[1050,686,1051,62,1049],"class_list":["post-1850","post","type-post","status-publish","format-standard","hentry","category-aws","category-how-to","tag-aurora","tag-aws","tag-bastion-server","tag-mysql","tag-python"],"_links":{"self":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1850","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1850"}],"version-history":[{"count":1,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1850\/revisions"}],"predecessor-version":[{"id":1851,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1850\/revisions\/1851"}],"wp:attachment":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1850"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1850"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1850"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}