Remote MySQL With cPanel

From GoBlueMich Wiki
Jump to navigation Jump to search

Oh noes, you have a customer with two or more load balanced web nodes and they need access to a dedicated remote MySQL server!! What do?

Why, set up a Remote MySQL Profile, of course!

It is strongly recommended to set up remote mysql BEFORE ADDING ACCOUNTS OR DATABASES. This is so that cPanel can add the proper grants right to the remote MySQL server, saving hours of headaches manually creating database users, their grants, and mapping them to cPanel manually.

how's it work?

Setting up a remote MySQL profile changes the entire server to use the external database server as its primary database. When creating databases through cPanel or WHM, they will be created on the server defined in the profile. When adding users, grants will be added to the server defined in the profile.

The setup connects root MySQL to the other server by adding a "host" line to /root/.my.cnf, so any MySQL commands run as root will be run on the database server. It also updates the MySQL socket to access the new server. This makes administration incredibly easy because when PHP connects to "localhost", it actually connects to the socket rather than 127.0.0.1. This means that you can safely use "localhost" in the configuration files instead of updating each of them to point to the database server's IP.

To reiterate, this means that if you set up the MySQL profile before migrating the accounts, you likely won't have to update any configurations files. It will just connect automatically if the configuration files are using "localhost".

At least, you know, usually.

network

Set up the private network. private network or xover cable recommended to avoid using up your public bandwidth and passing secure data over the pub net. If they're Storm servers do this in the Networking Page of manage. If they're dedicated hardware, ensure all hosts have private IP addresses and can ping each other. Note that Storm will reboot the instances when adding IP addresses.

On the cPanel side, for every web node, either add the private network interface to the firewall ignore list (in CSF this is ETH_DEVICE_SKIP), open 3306 outbound, or add the database server private IP to the whitelist.

For the DB side, allow inbound 3306 for the private interface or for the private ip range. Your server is probably core managed, so you will need to use iptables:

iptables -A LW_RULES -p tcp -s 192.168.0.0/24 --dport 3306 -j ACCEPT

for cPanel servers Allow only an IP to access a given port In the allow list (csf.allow) you can add an IP and port to provide access even if it is closed. You would add an entry such as:

tcp:in:d=3306:s=67.227.128.126

This would let IP 67.227.128.126 access port 3306 (mysql) even though the port isn't open in the firewall globally.

Our core-managed CentOS 7+ server are currently running firewalld, so we can/should use firewalld to whitelist. The simplest way to do this is to add source and target servers to each other's "trusted" zones. This whitelists them on all ports (like a CSF whitelist), but that's probably just fine:

firewall-cmd --add-source=192.168.0.0/24 --zone=trusted --permanent
firewall-cmd --reload

Don't forget to make changes permanent.

db server setup

Bind mysql to the local IP. In /etc/my.cnf , [mysqld] section:

bind-address = 192.168.0.X #or whatever IP it is

If you are using mariadb, you might have to add this to /etc/my.cnf.d/server.cnf. Next, add a grant for root at the cPanel server. Get the mysql password on the DB server:

cat /root/.my.cnf

Formulate a grant as such:

mysql -e 'grant all privileges on *.* to root@"192.168.0.X" identified by "password_goes_here" with grant option;'

cpanel setup

Template:Warning

You want do this on the main web/apache cpanel server node. First, make sure you can connect from cli:

mysql -h 192.168.0.X -u root -p

Pass the password from /root/.my.cnf on the DB server. if you get a sql prompt, you good.

Next, add any remote mysql access hosts. Namely, you will want to add the private IP to this list, as well as any secondary web nodes. A good option is:

192.168.0.%

Then, hit up Manage MySQL Profiles. Create a new profile here for the remote server, adding the pre-existing remote mysql root user's credentials. you can switch back and forth between different profiles if needed too. Validate the profile, and then activate it.


Usually this edits /root/.my.cnf with a remote host setting as well, so mysql from cli on this node will also work.

Template:Warning Template:Warning

SSL support

http://thecpaneladmin.com/installing-ssl-certificate-mysql/