Skip to main content

MySQL Creating a SSH tunnel using PuTTY

Prerequisites:
• MySQL is installed.
• MySQL is configured to listen on localhost (127.0.0.1). This is enabled by default.

How to Access MySQL Remotely by Creating an SSH Tunnel with PuTTYPermalink
This section will show you how to create an SSH tunnel to MySQL on Windows, using the PuTTY tool.

Setting Up the Tunnel
First, you need to establish a basic connection to your node:
1. Download PuTTY.
2. Save PuTTY to your desktop.
3. Double-click the PuTTY file to begin - no need to install. You will see the following window:





1. Enter the hostname or IP address of your Linode in the Host Name (or IP address) field.
2. In the left-hand menu, go to Connection -> SSH -> Tunnels.
3. In the Source port field, enter 3306.
4. In the Destination field, enter 127.0.0.1:3306. See the final configuration below:


1. Click Open to start the SSH session.
2. If you haven’t logged in to this system with PuTTY before, you will receive a warning similar to the following. Verify that this server is the one to which you want to connect, then click Yes:


Note:
This warning appears because PuTTY wants you to verify that the server you’re logging in to is who it says it is. It is unlikely, but possible, that someone could be eavesdropping on your connection and posing as your Linode. To verify the server, compare the key fingerprint shown in the PuTTY warning - the string of numbers and letters starting with ssh-rsa in the image above - with your Linode’s public key fingerprint. To get your Linode’s fingerprint, log in to your Linode via the Lish console (see the Console tab in the Linode Manager) and executing the following command:
ssh-keygen -l -f /etc/ssh/ssh_host_rsa_key.pub
The key fingerprints should match. Once you click Yes, you won’t receive further warnings unless the key presented to PuTTY changes for some reason; typically, this should only happen if you reinstall the remote server’s operating system. If you receive this warning again for the same Linode after the key has already been cached, you should not trust the connection and investigate matters further.

1. Direct your local MySQL client to localhost:3306. Your connection to the remote MySQL server will be encrypted through SSH, allowing you to access your databases without running MySQL on a public IP.
Create an SSH Tunnel on Mac OS X or LinuxPermalink
This section will show you how to create an SSH tunnel to MySQL on Mac OS X or Linux.
1. Install a MySQL client. Installing MySQL server comes prepackaged with an installation of the client. To install the client only:
MacOS
brew install caskroom/cask/mysql-shell
Ubuntu/Debian
sudo apt install mysql-client
2. Open a command prompt and run the following command to open the SSH tunnel.
3. ssh user@example.com -L 3306:127.0.0.1:3306 -N
Replace <user@example.com> with your SSH username and your server’s hostname or IP address. The long string of numbers in the command lists the local IP, the local port, the remote IP, and the remote port, separated by colons (:).
-L - binds a local port to the remote host post. -N - means forwarding ports.
Note
If you’re already running a local MySQL server on your workstation, use a different local port (3307 is a common choice). Your new command would look like this:
ssh user@example.com -L 3307:127.0.0.1:3306 -N
4. Open a new terminal window. Direct your local MySQL client to 127.0.0.1:3306 with the MySQL server username and password.
MacOS
mysqlsh --host=127.0.0.1 --port=3306 -u user -p
Ubuntu/Debian
mysql --host=127.0.0.1 --port=3306 -u user -p
Your connection to the remote MySQL server will be encrypted through SSH, allowing you to access your databases without running MySQL on a public IP.
5. When you’re ready to close the connection, issue a CTRL-C command or close the command prompt window. This will close the SSH tunnel.







Comments

Post a Comment

Popular posts from this blog

About MySQL - Database history Support and Versioning

About MySQL: MySQL was created by a Swedish company. David Axmark (left) and Michael "Monty" Widenius,  MySQL AB, founded by David Axmark, Allan Larsson and Michael "Monty" Widenius. Original development of MySQL by Widenius and Axmark began in 1994. The first version of MySQL appeared on 23 May 1995. It was initially created for personal usage from mSQL based on the low-level language ISAM, which the creators considered too slow and inflexible. They created a new SQL interface, while keeping the same API as mSQL. By keeping the API consistent with the mSQL system, many developers were able to use MySQL instead of the (proprietarily licensed) mSQL antecedent. Support: The MySQL server software itself and the client libraries use dual-licensing distribution. They are offered under GPL version 2, or a proprietary license. MySQL AB owns the copyright to the MySQL source code. This means that MySQL AB can distribute MySQL under several different l...
Post a Comment
Read more

MySQL thread error code of 1032- Translate binlog completely to avoid error

Could not execute Update_rows event on table db.table; Can't find record in 'table', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's   master log mysql-bin.262297, end_log_pos 1983208 As of my knowledge the causing stop of slave SQL thread error code of 1032, due to lack of sync between Master - Slave. In our case, slave got an Update event form master, but that particular record was not available in the slave. (Of course, that was deleted on Master n Slave separately) You would get this error only if the binlog_format is set to ROW_BASED or MIXED mode. So, now check that particular binlog at that position where replication stopped with this error. When you convert the binlog with mysqlbinlog command, you may see some junk characters   where you were expecting some DMLs which caused the error(In our case its an Update statement). So you can use below command to translate binlog completely: mysqlbinlog --base64-output=DECO...
Post a Comment
Read more

MySQL Architecture -Client Server Architecture

Client/Server Overview The MySQL database system operates using a client/server architecture. The server is a central program that manages database contents, and client programs connect to the server to retrieve or modify the data. MySQL also includes non-client utility programs and scripts. MySQL Server:   This is the mysqld program that manages database and tables. Most users choose binary MySQL distribution that includes a server ready to run with the capabilities they need, but it's also possible to compile MySQL from source. Client Programs:  These are programs that communicate with the server by sending requests to it over a network connection. The server acts on each request and returns a response to the client. For example you can use the mysql client to send queries to the server, and the server returns the query results. A client program can connect locally to a server running on the same machine or remotely to a server running on a different mac...
Post a Comment
Read more