====== How to connect mysql client over SSH tunnel to Mariadb server ====== For setting up your working Mariadb server, [[howtos:databases:install_mariadb_on_slackware|read this howto]]. You may want to connect clients to the database server over network securely. Instead of using TLS (which is [[https://mariadb.com/kb/en/library/data-in-transit-encryption/ |available natively]]) for this, the same can also be achieved by tunneling over SSH by following the below steps. __**On the database server**__ - Ensure there is no blocking by firewall for the ports/protocols/hosts.. The default Mariadb port is TCP 3306. - Ensure that the SKIP=”..skip-networking…” line in /etc/rc.d/rc.mysqld is commented out. This will allow external client connections to the server. - Identify a user who will be the SSH tunnel user. This refers to the Linux user. You may use the command ‘adduser ’ to create a new user. - Edit /etc/ssh/sshd_config, if necessary, to ensure that key based authentication is allowed for this user. The /etc/rc.sshd service will of course have to be restarted if any edits had been made. __**On the client machine**__ Generate keys for authentication using ssh-keygen without a passphrase as follows. You may use RSA also. The below command will create a private and public key after giving you file name option. Just hit enter to avoid using a passphrase. Create and login as a similar tunnel user when doing this. ssh-keygen -t ecdsa -b 521 -C my-comments The public key with filename extension .pub should be installed in the database server user’s authorized_keys. The contents of the .pub file can be copy pasted or you can use a command line utility like ssh-copy-id as shown below. Never copy the private key to other server. ssh-copy-id -i ~/.ssh/mykey user@database-server-host It is recommended to use autossh, which is available in [[https://www.slackbuilds.org/|slackbuilds]], to make persistent connections. Once autossh is installed, you can now create the tunnel with a command like below. The port 334 on the client machine can be any unassigned port you choose. autossh -M 10984 -o "PubkeyAuthentication=yes" -o "PasswordAuthentication=no" -o "ServerAliveInterval 60" -o "ServerAliveCountMax 3" -i ~/.ssh/mykey -L 334:127.0.0.1:3306 user@database-server-host Connect the client mysql -u root -p --host=127.0.0.1 --port=334 You thus have a secure ssh tunneling for mysql client now. ====== Sources ====== Adapted from original source: [[https://mariadb.com/resources/blog/connecting-to-mariadb-through-an-ssh-tunnel/]] Originally written by [[wiki:user:mahafyi | mahafyi]] {{tag>howtos mariadb mysql tunneling ssh tunnel}}