Keep your dev servers separate from your workstation

Setting up a development server on your local network separate from the machine you write and crunch code is a great way to keep the machine you rely on to operate as fast as possible.  I never try to install any virtual machine on the same machine I need to write code and design databases on, even a quad core with 8 GBs of RAM will slow down pretty quickly.

Virtualbox, Virtualmin and MySQL database server

So, after installing Virtualbox on another PC, usually the most recently replaced PC that used to be my main computer, I prefer instead of selling it to use it as another dev server on my network.  I prefer to install Virtualmin and use that to help speed up the process of deploying another dev server in minutes.  Obviously, the “M” in L.A.M.P. is short for MySQL, and without a local database to interact with, you are sure to get frustrated with the time lags involved in working with a remote database server.  This is a “dev” environment, after all, so you need every advantage to keep things running fast.  Dev is usually 80% of all the work you put into a new site and system.

Virtualmin allows you to install a new database server, Git repositories, and a myriad of other modules that will help you duplicate your existing host environment.  Of course, you can also open this server to the outside world if you have static IP as well.  Virtualmin allows easy deployment of new servers quickly and reliably.

Failure to connect to MySQL database

Once you set everything up in Virtualmin and can access your site, you will be tempted to go and establish a connection to the site’s database and start devving.  You will likely run into an error message saying your connection isn’t allowed.  This is what happens when I try to connect to the site’s MySQL server directly to its IP through MySQL Workbench:

Failure to connect through MySQL Workbench to local Virtualmin database

You did everything right, you set up your database and user privileges, so what gives?!  Well, Virtualmin can get a little confusing, so go back to your Virtualmin console then click “Edit Database” then go to the “Remote hosts” tab.  Here, you need to put in your IP address, if you’re not DHCP on your local network and that’s where you need to access your database from, it’s best to type in a wildcard of your network, which for me and my local network IPs are in the range from 192.168.1.1 – 192.168.1.255.  By entering the wildcard “192.168.1.%” this will allow any connection within the 192.168.1  IP range to access your database.

Virtualmin - open local IP to connect to your dev server databaseYou won’t have to restart MySQL, just go back to MySQL Workbench or whatever tool you’re using to access your database and see if it works now:

MySQL Workbench Virtualmin Database success screenshotSo, when you think you know where the proper settings are, in Virtualmin, this is something that often gets overlooked.