Using a central virtual MySQL server

For all my projects I’ve been using dedicated virtual machines which I manage and configure using Vagrant. In this manner it was easy to manage a dedicated environment where you won’t have conflicting settings or libraries that was easily recreated on the fly. Every project with it’s own virtual machine get’s all the components installed it needs. With at least 5 or 6 virtual machines running on my personal iMac (which is an older model from 2013) it was getting a bit busy. One common component installed on all my machines was MySQL, which is still my go to database for simple projects. So I’ve been toying with the idea of creating a single virtual machine that only runs MySQL for all my projects. I could even host this virtual database server on an even older Mac Mini (from 2010) which I still keep around. It used to be my generic media machine untill an Apple TV took over it’s role.

At first setup everything looked great, it all went well when running on the same host (the iMac). But when I hosted the virtual database server on the Mac Mini things started to go wrong and I couldn’t make a connection to the database. While locally everything went well, going over the network was the problem. Several things to check: Was my virtual machine accepting remote connections. Yes, I’ve enabled the option: config.vm.network "public_network"

Next was connectivity to MqSQL. I learned that the skip-networking option which one usually used to secure your connectivity to the outside world has been deprecated. Instead the network connectivity is linked to the network interface of your (virtual) server. It’s got three options:

• Only acces from the local host
• Access from all networks
• Access only from one network

Only acces from the local host
Here, the bind-address takes a value of 127.0.0.1, the loopback IP address. MySQL can only be accessed by applications running on the same host.

Access from all networks
MySQL listening to all networks then the bind-address configuration is IP as 0.0.0.0. With this setting MySQL listens from all networks. Furthermore, to permit both IPv4 and IPv6 connections on all server interfaces,

Access only from one network
MySQL is allowed to listen only to a specific network interface. The value, in this case, will be the ip-address of the specific network interface for instance: 192.168.1.1

So when I adjusted the settings for mysqld in /etc/mysql/mysql.conf.d/mysqld.cnf and changed bind-address=127.0.0.1 into bind-address=0.0.0.0 and restarted mysqld everything connected and started working properly!

Next step is migrating all active projects to the virtual central MySQL server and see if there are any performance benefits.

Sidenote: I’ve learned that to make sure you can rebuild your database server on the fly you’ll have to make a backup of your data before you halt or destroy the virtual server. I’ve done this via a trigger configuration in my Vagrantfile that makes a dump of the database on demand to a shared folder. Just add the following lines to your Vagrantfile:

config.trigger.before [:halt, :destroy] do |trigger|   
   trigger.warn = "Dumping database to /vagrant/Code/dbserveroutfile.sql"    
   trigger.run_remote = {inline: "mysqldump -u<username> -p<password> --all-databases --single-transaction --events > /vagrant/Code/dbserveroutfile.sql; "}   
end