Sometimes the combination of a web server and database server on the same machine can be so much that it causes the server to lag. For this reason, many system administrators will run MySQL on a separate dedicated machine. When doing so, it is necessary to configure MySQL for remote access. You can setup remote MySQL access within cPanel’s Web Host Manager (WHM). (Note: These instructions describe how to setup remote MySQL access for cPanel servers. For manual remote MySQL connections, see our ServerSchool.com instructions).
You can find the remote MySQL server setup at: Home >> SQL Services >> Setup Remote MySQL Server.
In order to properly setup remote access, you must have the package openssh-clients installed:
# yum install openssh-clients
You will also need to make sure local server’s hostname resolves to your IP address. If your IP address were 192.168.1.2, with reverse DNS setup, running the “hostname 192.168.1.2” command should produce the output:
220.127.116.11.in-addr.arpa domain name pointer your.hostname.com
If it does not, you may need to manually edit /etc/hosts and add the line:
Assuming you have all of the above configured and ready to go, you can enter in the appropriate information into the remote MySQL server form:
- Remote server address – This should be the IP address or domain for the remote server
- Remote SSH port – It will be a secure SSH connection. The remote server’s SSH port should be 22 by default
- Login as – This should be the root account username on the remote server
- Select authentication method – You can use an SSH key or password to login
- Select an installed SSH Key – If you already have an SSH key created, you can use it to login to the remote server. This is considered more secure than a password connection.
- SSH Key password – This is the password for the SSH key that you originally setup.
- Root Password – If you are using password login, you will need to supply the root password
Finally, click “Setup”, and the remote connection will be established. You will now be able to connect one cPanel server to another running MySQL remotely.