{"id":280,"date":"2023-03-17T10:47:46","date_gmt":"2023-03-17T15:47:46","guid":{"rendered":"https:\/\/client35.dev\/code\/?p=280"},"modified":"2023-03-18T13:27:11","modified_gmt":"2023-03-18T18:27:11","slug":"remote-database-connection-ssh-tunnel","status":"publish","type":"post","link":"https:\/\/client35.dev\/code\/remote-database-connection-ssh-tunnel\/","title":{"rendered":"Connecting to a Remote Database through an SSH Tunnel on WP Engine"},"content":{"rendered":"Reading Time: <\/span> 3<\/span> minutes<\/span><\/span>\n

In this guide, we’ll explore what it’s like to connect to a remote database using MySQL, through an SSH tunnel. WP Engine recently stopped allowing direct remote SQL connections to their servers for security reasons, and switched to allowing customers remote database connections through an SSH tunnel. <\/p>\n\n\n\n

Connecting through an SSH tunnel can be tricky, especially if you’re not used to it. I had my own issues as I fumbled my way through figuring out how to connect for the first time, but after I understood how SSH tunneling works, it allowed me to have a deeper understanding of how remote database connections work through SSH tunneling. I’m writing this guide in hopes that it will help demystify the set up process. <\/p>\n\n\n\n

What is SSH tunnel?<\/h2>\n\n\n\n

Let’s start off by giving a brief understanding of SSH tunneling. If you’re already familiar with this, and just need to know how to connect on WP Engine, see the Connecting to the MySQL Database<\/strong><\/a> section below.<\/p>\n\n\n\n

\n\n\n\n

Start the SSH Connection<\/h2>\n\n\n\n

You can read WP Engine’s official guide on remote database connections by clicking this link<\/a>. However, I think it’s important to know the pieces to connect.<\/p>\n\n\n\n

ssh -L 3307:127.0.0.1:3306 env@env.ssh.wpengine.net -oStrictHostKeyChecking=no<\/pre><\/div>\n\n\n\n
The aforementioned command creates an SSH connection, forwarding port 3307 on the local machine to port 3306 on the remote server; meanwhile, -oStrictHostKeyChecking=no<\/code> flag tells SSH to ignore the HostKeyCheck (the part you’re usually asked to accept or deny the connection; i.e removes unnecessary pop-ups, instructions or dialogue). Once successfully connected, you should see a message or prompt like what I have below: <\/p>\n\n\n\n
___       _________       __________              _____\n__ |     \/ \/__  __ \\      ___  ____\/_____________ ___(_)___________\n__ | \/| \/ \/__  \/_\/ \/      __  __\/  __  __ \\_  __ `\/_  \/__  __ \\  _ \\\n__ |\/ |\/ \/ _  ____\/       _  \/___  _  \/ \/ \/  \/_\/ \/_  \/ _  \/ \/ \/  __\/\n____\/|__\/  \/_\/            \/_____\/  \/_\/ \/_\/_\\__, \/ \/_\/  \/_\/ \/_\/\\___\/\n                                          \/____\/\nWP Engine Shell - PHP 8.0\n\n* WP-CLI Commands:   https:\/\/developer.wordpress.org\/cli\/commands\/\n\n*** NOTE ***\nThis is a sandboxed environment that interacts with the production server for\nyour website. Most resource usage commands (i.e. top, vmstat, free, etc.) do not\nreflect the usage of your production server environment.\nwpe-user@env.ssh.wpengine.net:~$<\/pre><\/div>\n\n\n\n
Connecting to the MySQL Database<\/h2>\n\n\n\n
Once the SSH tunnel is established, the next step is to connect to the MySQL database using the local port. Your SSH connection will need to run in the background. This can be done using any MySQL client software or terminal program. This guide covers connecting via terminal. Below are what you’ll need for your connection settings. The information for Username<\/code>, Password<\/code> and Database<\/code> can all be found in your wp-config.php<\/code> file:<\/p>\n\n\n\n
    \n
  • Hostname:<\/strong> 127.0.0.1<\/li>\n\n\n\n
  • Port:<\/strong> 3307<\/li>\n\n\n\n
  • Username:<\/strong> WordPress database user<\/li>\n\n\n\n
  • Password:<\/strong> WordPress database user password<\/li>\n\n\n\n
  • Database:<\/strong> WordPress database user<\/li>\n<\/ul>\n\n\n\n
    With the above settings, the MySQL client will connect to the database through the SSH tunnel, providing a secure remote connection. Once successful, you should get something like I have below. <\/p>\n\n\n\n
    mysql -h 127.0.0.1 -P 3307 -u username -p wp_database_name\nEnter password:\nReading table information for completion of table and column names\nYou can turn off this feature to get a quicker startup with -A\n\nWelcome to the MySQL monitor.  Commands end with ; or \\g.\nYour MySQL connection id is 12856769\nServer version: 5.7.40-43-log Percona Server (GPL), Release '43', Revision 'c1b94a6cfd7'\n\nCopyright (c) 2000, 2022, Oracle and\/or its affiliates.\n\nOracle is a registered trademark of Oracle Corporation and\/or its\naffiliates. Other names may be trademarks of their respective\nowners.\n\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\n\nmysql><\/pre><\/div>\n\n\n\n
    Closing the SSH Tunnel<\/h2>\n\n\n\n
    After the MySQL connection is no longer needed, the SSH tunnel can be closed. This can be done by pressing Ctrl + D<\/code> in the terminal window where the SSH tunnel was created.<\/p>\n\n\n\n
    Conclusion<\/h2>\n\n\n\n
    Creating a secure remote MySQL connection through an SSH tunnel is an effective way to protect the database from unauthorized access. By following the steps outlined in this blog post, remote access to a MySQL database can be established securely.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Reading Time: <\/span>  3<\/span> minutes<\/span><\/span>In this guide, we’ll explore how to connect to a remote database using MySQL, through an SSH tunnel. WP Engine recently stopped allowing direct remote SQL connections to their servers for security reasons, and switched to allowing customers remote database connections through an SSH tunnel. Connecting through an SSH tunnel can be tricky, especially if you’re not used to it. However, by following the steps outlined in this blog post, remote access to a MySQL database can be established securely.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_monsterinsights_skip_tracking":false,"footnotes":""},"categories":[2],"tags":[30,29,26,24,28,20],"class_list":["post-280","post","type-post","status-publish","format-standard","hentry","category-command-line-tutorials","tag-mysql-cli","tag-remote-connection","tag-remote-database","tag-ssh","tag-ssh-tunnel","tag-wp-engine"],"acf":[],"_links":{"self":[{"href":"https:\/\/client35.dev\/code\/wp-json\/wp\/v2\/posts\/280","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/client35.dev\/code\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/client35.dev\/code\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/client35.dev\/code\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/client35.dev\/code\/wp-json\/wp\/v2\/comments?post=280"}],"version-history":[{"count":0,"href":"https:\/\/client35.dev\/code\/wp-json\/wp\/v2\/posts\/280\/revisions"}],"wp:attachment":[{"href":"https:\/\/client35.dev\/code\/wp-json\/wp\/v2\/media?parent=280"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/client35.dev\/code\/wp-json\/wp\/v2\/categories?post=280"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/client35.dev\/code\/wp-json\/wp\/v2\/tags?post=280"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}