Remote MySQL Access for cPanel

By right, MySQL is one of the engine that used up lots of I/O and Memory resource from a server. Somehow you may use this scenario to setup so-called Clustering Web and MySQL server to reduce the I/O usage.

To setup remote MySQL server, you need 3 main steps as below.
1. Grant remote access to cPanel root user.
2. Add remote host information to /root/.my.cnf file.
3. Configure PHPMyAdmin configuration.

1. Grant remote access to cPanel root user,
To grant remote access to cPanel root user, you will need to amend the MySQL privilege as line below.
mysql> GRANT ALL PRIVILEGES ON *.* TO root@`192.168.1.1` IDENTIFIED BY ‘password’ WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO root@`mysql.mickgenie.com` IDENTIFIED BY ‘password’ WITH GRANT OPTION;

2. Add remote host information to /root/.my.cnf file,
Next, you will need to change the host of the MySQL location to the slave server.
[client]
user=”root”
pass=”password”
host=”mysql.mickgenie.com”

3. Configure PHPMyAdmin information,
Open file /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php, configure the setting as below.
$i++;
$cfg[‘Servers’][$i][‘host’] = ‘mysql.mickgenie.com’;
$cfg[‘Servers’][$i][‘port’] = ‘3306’;
$cfg[‘Servers’][$i][‘socket’] = ”;
$cfg[‘Servers’][$i][‘connect_type’] = ‘tcp’;
$cfg[‘Servers’][$i][‘extension’] = ‘mysql’;
$cfg[‘Servers’][$i][‘compress’] = FALSE;

You have done the setting.

Published by

Mick Genie

Mick Genie is the founder of MickGenie.com and working at ExaBytes Network Sdn Bhd and WPWebHost web host company. He is expertise in Windows and Linux environment especially web hosting related information, tips and trick as well as the IT Information.

2 thoughts on “Remote MySQL Access for cPanel”

  1. Thank you, this is useful information. I’m busy setting this up myself, and found out to my horror that on an existing cpanel server, the databases aren’t copied to the remote mysql server!

    You have to mysqldump and copy them over, and then grant privileges to teh database user, and then edit the config scripts on the cpanel server to use the mysql server’s IP address (or hostname) instead of “localhost” for the mysql server.

    I am sure that cpanel could have made it a lot easier than this! 🙁

  2. Exactly, WHM do provide such setting but not for all and it is to allow you to setup the connection through the primary server to MySQL server.

    Check out “Setup Remote MySQL Connection” from WHM.

Comments are closed.