Enable Remote MySQL Connections in cPanel

Remote MySQL connections are disabled by default in cPanel servers because they are considered a potential security threat. Using the tools in the Web Host Manager (WHM) and the domain-level cPanel interface (usually http://domainname.com/cpanel) remote hosts can be added which the server allows to connect to the MySQL service.

Before using either of the following techniques, you will need to to open up port 3306 in your server’s firewall.

Enabling Remote MySQL in the WHM Interface

Log in to the server’s WHM interface and find the section in the left-side navigation bar labeled SQL Services. You can sort the list by typing ‘sql’ in the search box. Click on the link marked Additional MySQL Access Hosts:

WHM - Remote MySQL List

On the following page, enter one or more hosts or IP addresses in the text box (1) and click the Save button (2). If you wish to activate these settings on all user accounts see (3).

WHM - Remote MySQL page

Now that the remote connection has been activated in the WHM each domain account that wants to use the remote connection will need to activate it in their own cPanel interface.

Enabling Remote MySQL in the Domain cPanel Interface

Log in to the domain’s cPanel interface and find the section on the main page labeled Databases.

In the Databases section find the link/button labeled Remote MySQL and click on it.

cPanel - Remote MySQL list

The following page will appear in your browser. Add a hostname or IP address that you want to grant remote MySQL access to (1) and then click the Save button (2).

If a host or IP address needs to be removed from this list you can click the ‘Delete’ button next to the entry in the list.

cPanel-page-fxt

Once you have made your changes, additions, or removals to the list you can return the main page of the cPanel interface, or log out if you have no other tasks to take care of.

===

Liquid Web’s Heroic Support is always available to assist customers with this or any other issue. If you need our assistance please contact us:
Toll Free 1.800.580.4985
International 517.322.0434
support@liquidweb.com
https://manage.liquidweb.com/

How to Disable MySQL Strict Mode

MySQL’s, and MariaDB’s, strict mode controls how invalid or missing values in data changing queries are handled; this includes INSERT, UPDATE, and CREATE TABLE statements. With MySQL strict mode enabled, which is the default state, invalid or missing data may cause warnings or errors when attempting to process the query.

When strict mode is disabled the same query would have its invalid, or missing, values adjusted and would produce a simple warning. This may seem like the preferred result, however with strict mode disabled certain actions may cause unexpected results; for instance, when the value being inserted exceeds the maximum character limit it will be truncated to fit the limit.

There are various reasons why MySQL’s strict mode may need to be disabled, however the most common is when a server is running WHMCS — this is a requirement of that tool.

Pre-Flight Check

  • These instructions are intended specifically for disabling MySQL strict mode on a managed Liquid Web server with cPanel.
  • The server should be running either MySQL 5.6/5.7 or MariaDB 10.x
  • Command line and root level access via SSH will be necessary to follow this tutorial.

Step #1: Make Backups, Always!

Whenever modifying files on a server it’s always best practice to take some form of a backup beforehand. This ensures you have a way to revert changes if something goes awry; it’s also beneficial because it helps track when and what changes were made.

While logged into SSH with the root user, do the following:

cp -a /usr/my.cnf{,.strict.bak}
cp -a /etc/my.cnf{,.strict.bak}

The above command uses ‘BASH brace expansion’ in order to make a backup copy of the file in its original directory.

Step #2: Disable MySQL Strict Mode

Depending on the server and the current configurations you may need to edit one, or both, of the following files on the server. Generally, the relevant configuration lines are only in one of them, however, it could be in either one without causing issues; so generally it’s best to check both.

To edit the files, you will open the file with your favorite command line editor. In this example, we use ‘vim’.

vim /usr/my.cnf
vim /etc/my.cnf

In vim, you can press “a” or “i” to enter text insertion mode; pressing the escape key (Esc) on your keyboard returns you to command mode. For a refresher on editing files with vim, see our New User Tutorial: Overview of the Vim Text Editor.

Within each file above you will be looking for a line with the following content:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

If you find a line similar to the above that is setting the `sql_mode` variable then you will need to replace it with the following line to disable MySQL strict mode.

sql_mode=""

Once this adjustment has been made, or you’ve confirmed the file does not need to be adjusted you will then save and close the file.

Step #3: Restart the MySQL Service

Finally, to make these changes effective you will need to restart the MySQL service as it will only read the configuration files when it initially loads up. In order to force MySQL to use the new configuration files you will do the following:

For CentOS 7 servers:
systemctl restart mysql

For CentOS 6 and prior:
/etc/init.d/mysql restart

After issuing this command on the server the MySQL service will be restarted and will load the changes made. If all the directions were followed and completed, then MySQL strict mode should now be disabled.

To verify that the process was completed properly you can run the following:

mysql -e "SELECT @@sql_mode;"

The output may look similar to the following:

+--------------------------------------------+
| @@sql_mode
+--------------------------------------------+
| NO_AUTO_CREATE_USER
+--------------------------------------------+

If you have any questions or are not comfortable making these changes yourself, please feel free to contact Heroic Support®.

How To Manage a MySQL Database with phpMyAdmin in cPanel

  1. This tutorial assumes you’ve already logged in to cPanel, and are starting on the home screen.
  2. Now let’s learn how to manage a database with phpMyAdmin.
  3. Click the "phpMyAdmin" icon.

    cpanel-pl-mysql-11-phpmyadmin-02

  4. This is the phpMyAdmin main page. It is from here that you can manage all MySQL databases that exist in your hosting account.

    cpanel-pl-mysql-11-phpmyadmin-03

  5. In the top left corner, you’ll see an entry with your account user name. Click the "plus sign" to its left.

    cpanel-pl-mysql-11-phpmyadmin-04

  6. You’ll now see a list of databases in your account. Click the one you want to manage.

    cpanel-pl-mysql-11-phpmyadmin-05

  7. You can now start managing your database. You can manage tables, add or delete entries, and perform queries among other things.

    cpanel-pl-mysql-11-phpmyadmin-06

 

How To Delete a MySQL Database User in cPanel

  1. This tutorial assumes you’ve already logged in to cPanel, and are starting on the home screen.
  2. Now let’s learn how to delete a database user.
  3. Click the "MySQL Databases" icon.

    cpanel-pl-mysql-8-deleteuser-02

  4. Locate the "Current Users" section at the bottom of the page, and click the "Delete" icon next to the user you want to delete.

    cpanel-pl-mysql-8-deleteuser-03

  5. Then click "Delete User" to confirm.

    cpanel-pl-mysql-8-deleteuser-04

  6. That’s it! The database user has been deleted.

    cpanel-pl-mysql-8-deleteuser-05

 

How To Check a MySQL Database for Errors in cPanel

  1. This tutorial assumes you’ve already logged in to cPanel, and are starting on the home screen.
  2. Now let’s learn how to check a database for errors.
  3. Click the "MySQL Databases" icon.

    cpanel-pl-mysql-9-check-02

  4. Under "Modify Databases", and in the "Check Database" drop down box, select the database you want to check.

    cpanel-pl-mysql-9-check-03

  5. Then click "Check Database".

    cpanel-pl-mysql-9-check-04

  6. That’s it! The check has been completed, and no errors were found.

    cpanel-pl-mysql-9-check-05

 

How To Remove a User from a MySQL Database in cPanel

  1. This tutorial assumes you’ve already logged in to cPanel, and are starting on the home screen.
  2. Now let’s learn how to remove a user from a database.
  3. Click the "MySQL Databases" icon.

    cpanel-pl-mysql-7-removeuser-02

  4. In the table of databases, locate the database you wish to remove a user from, locate the specific user assigned to that database that you want to remove and click the delete icon.

    cpanel-pl-mysql-7-removeuser-04

  5. Click "Revoke User Privileges from Database" to confirm.

    cpanel-pl-mysql-7-removeuser-05

  6. That’s it! The user has been removed from the database.

    cpanel-pl-mysql-7-removeuser-06

  7. The user hasn’t been deleted … you can see it’s still listed in the list of current users. It has simply been removed from that database.

    cpanel-pl-mysql-7-removeuser-07

 

How To Change a MySQL Database User’s Password in cPanel

  1. This tutorial assumes you’ve already logged in to cPanel, and are starting on the home screen.
  2. Now let’s learn how to change a database users password.
  3. Click the "MySQL Databases" icon.

    cpanel-pl-mysql-6-chguserpass-02

  4. Locate the user whose password you want to change, and click "Set Password".

    cpanel-pl-mysql-6-chguserpass-03

  5. Enter and confirm a new password, then click "Change Password".

    cpanel-pl-mysql-6-chguserpass-04

  6. That’s it! The database user’s password has been changed.

    cpanel-pl-mysql-6-chguserpass-05

 

How To Assign a User to a MySQL Database in cPanel

  1. This tutorial assumes you’ve already logged in to cPanel, and are starting on the home screen.
  2. Now let’s learn how to assign a user to a database.
  3. Click the "MySQL Databases" icon.

    cpanel-pl-mysql-5-assignuser-02

  4. Under "Add User To Database", select the user you want to add, and then select the database you want to add it to.

    cpanel-pl-mysql-5-assignuser-03

  5. When ready, click "Add".

    cpanel-pl-mysql-5-assignuser-04

  6. Select the privileges you want to grant the user… in this case, let’s select "All Privileges".

    cpanel-pl-mysql-5-assignuser-05

  7. Then click "Make Changes".

    cpanel-pl-mysql-5-assignuser-06

  8. That’s it! The user has been assigned to the database.

    cpanel-pl-mysql-5-assignuser-07

  9. You can see the user assigned to the database in the table of databases.

    cpanel-pl-mysql-5-assignuser-08

 

How To Create a MySQL Database User in cPanel

  1. This tutorial assumes you’ve already logged in to cPanel, and are starting on the home screen.
  2. Now let’s learn how to create a MySQL database user.
  3. Click the "MySQL Databases" icon.

    cpanel-pl-mysql-4-createuser-02

  4. Under "Add New User", enter a username for the user you want to create, along with a password.

    cpanel-pl-mysql-4-createuser-03

  5. Then click "Create User".

    cpanel-pl-mysql-4-createuser-04

  6. That’s it! The new database user has been created.

    cpanel-pl-mysql-4-createuser-05

  7. You can see the new database user listed at the bottom of the page.

    cpanel-pl-mysql-4-createuser-06

 

How To Delete a MySQL Database in cPanel

  1. This tutorial assumes you’ve already logged in to cPanel, and are starting on the home screen.
  2. Now let’s learn how to delete a MySQL database.
  3. Click the "MySQL Databases" icon.

    cpanel-pl-mysql-3-deletedb-02

  4. Locate the database name you want to delete, and click the "Delete Database" link next to it.

    cpanel-pl-mysql-3-deletedb-03

  5. Then click "Delete Database" to confirm.

    cpanel-pl-mysql-3-deletedb-04

  6. That’s it! The MySQL database has been deleted.

    cpanel-pl-mysql-3-deletedb-05