Running MySQL on a Remote System


Running MySQL on a Remote System

Because the MySQL database server can consume a large amount of CPU time and RAM on a busy web host, you may want to offload it to a separate system. This is fully suppported in Webmin and Virtualmin, as documented here.

This is best done on a fresh system with no domains yet - or at least, no domains with databases. The steps below will not migrate any databases or MySQL users to the new system.

The only down-sides to moving MySQL to a remote system are :

  • Database access will be slightly slower, due to network overhead.
  • Disk used by MySQL databases will no longer count towards domain's quotas, as it will be on a separate system and thus outside of the Unix quotas setup by Virtualmin.
  • You will not be able to shut down or startup MySQL from Virtualmin. This must be done on the remote system.

Installing MySQL on the Remote System

The easiest way to do this is entirely within Webmin, as follows :

  1. Install Webmin on the MySQL system, using the appropriate package for your OS from . For Fedora, CentOS or Redhat, you can just run :
    rpm -U

    On Debian or Ubuntu, you can run :

    dpkg -i webmin-current.deb
  2. Login to Webmin, and go to ServersMySQL Database Server
  3. If it tells you that MySQL is not installed and offers a link to install it automatically, use it. Otherwise, you will need to install MySQL manually.
  4. Return to the MySQL module, and make sure it is properly configured to login to MySQL. If there is no root password set yet, use the Change Administration Password page to set one.
  5. Grant access to your system running Virtualmin by going to the User Permissions.
  6. Click on Create a new user, enter root as the Username, whatever password you choose above, your Virtualmin system's IP address for Hosts.
  7. Select all the entries in the Permissions list, then click Save.

To verify that everything worked, login to the system running Virtualmin and run a command like :

mysql -u root -ppassword -h dbserver

Where password is the password you selected, and dbserver is the IP address of the database system.

Configuring Webmin to Use a Remote MySQL Server

Now that MySQL is running on a separate system and can be accessed remotely, you can configure Webmin (and thus Virtualmin) to use it as follows :

  1. Login to Virtualmin, and go to WebminServersMySQL Database Server
  2. Click on the Module Config link, and in the System Configuration section enter the IP address of the system you setup in the MySQL host to connect to field.
  3. Click Save, and make sure that no errors are reported by Webmin connecting to MySQL.
  4. Go back to the Virtualmin tab on the left, and try creating a test domain with the MySQL feature enabled.

Assuming the last step worked, you are done!