Month: June 2018

Accessing PostgreSQL via SSH Putty tunnel

To close the port 5432 for any traffic or don’t want to configure PostgreSQL to listen to any remote traffic, use SSH Tunneling to make a remote connection to the PostgreSQL instance at AWS.

Follow these steps to connect PostgreSQL using SSH Tunneling at AWS:

  1. Open PuTTY. Setup server session in Putty.
  2. Go to Connection > SSH > Tunnels
  3. Enter 8000 in the Source Port field.
  4. Enter 127.0.0.1:5432 in the Destination field.
  5. Click the “Add” button.
  6. Go back to Session, and save, then click “Open” to connect.
  7. This opens a terminal window. After connection leaves that alone.
  8. Open pgAdmin and add a connection.
  9. Enter localhost in the Host field and 8000  in the Port field.
  10. Specify a Name for the connection, and the username and password. Click OK.
What is it doing? PuTTY is intercepting communications sent from pgAdmin to localhost:8000. The information is transferred across the internet via SSH, on port 22. When it arrives there, the SSH server sends the information on to PostgreSQL via port 5432. As far as PostgreSQL knows, the traffic came in locally, on the correct port.

Configure MySQL 5.6 LONGBLOB for large binary data

The reason for this issue is a change in MySQL 5.6.20 as one could read in the change log:

As a result of the redo log BLOB write limit introduced for MySQL 5.6, the innodb_log_file_size setting should be 10 times larger than the largest BLOB data size found in the rows of your tables plus the length of other variable length fields (VARCHAR, VARBINARY, and TEXT type fields). No action is required if your innodb_log_file_size setting is already sufficiently large or your tables contain no BLOB data.

Set or increase the value of the innodb_log_file_size option in my.ini below the [mysqld] section. Its default value is 48M. Setting it to

[mysqld]
innodb_log_file_size=256M

Be careful when changing the value of innodb_log_file_size. Follow these steps to do this safely:

  • Shut the server down cleanly and normally.
  • Shutting down MySQL may not be as simple as just service mysql stop!
  • Following things should be done to shut down mysql server normally and cleanly:
    1. Double check the instance you are going to shutdown!!
    2. Stop Replication
    3. Flush the dirty pages
    4. Check the long running transactions
    5. Dump and reload the buffer pool
  • Move away (don’t delete) the log files, which are named ib_logfile0, ib_logfile1, and so on.
  • Check the error log to ensure there was no problem shutting down.
  • Then restart the server and watch the error log output carefully.
    • There should see InnoDB print messages saying that the log files don’t exist. It will create new ones and then start.
  • Verify that InnoDB is working. If it’s working, then the old log files can be deleted.