Mysql server on vagrant, virtualbox

Vagrant is a popular tool used by development teams to quickly install and deploy one or several clusters of virtualized server environments on which to test and develop software and distributed systems. In the era of micro services, it is extremely important to be able to quickly setup and tear down a test environment that would closely mimic it’s cloud environment once a system is deployed in production.

There are countless possible ways of using vagrant to automate and configure development environments, but for this discussion we will focus on mysql server and ubuntu, and the steps on how to get it up and running quickly. This includes virtualizing and automating the download, setup of ubuntu where mysql server will be running on, as well as the installation of the necessary dependencies, configurations required for mysql server to operate properly.

Ubuntu is a linux based operating system and is one of the most popular among the open source operating systems. We are going to use ubuntu 16.04 in this demonstration.

mysql is also one of the most popular and important open source DB systems today. For this demo, we are going to use mysql 5.7

For vagrant to work, we also need a virtualization provider, for this we are going to use Oracle’s VirtualBox.

We also need a GUI Admin interface to manage and configure mysql databases and its users. For this we are going to use the web based phpmyadmin, as well as the corresponding web tools apache2, and php7.

The platform

Vagrant and virtual box needs to be installed in a host system, for this demo, I am doing it on ubuntu 16.04. You may also do the same steps on Windows, or Mac, only the tools that will be used are different.

Let us begin

On your host system, open a terminal. All succeeding steps will be done on the terminal command line interface, so for those of you who are not familiar with linux commands, you may search google and learn about each of the commands used here as we go along.

1.) Install vagrant

user$> sudo apt-get install virtualbox
user$> sudo apt-get install vagrant

Thats its! Once the download completes both tools should now be available. To check for proper installation of vagrant, type the command

user$> vagrant version

It should return with the following lines

Installed Version: 2.0.3
Latest Version: 2.0.3

2.) Setup vagrant workspace

To use vagrant, create a directory where you want to configure a vagrant work environment. This step is important as vagrant will install a hidden folder name .vagrant as well as file named Vagrantfile. Vagrantfile is where we define the environment to setup and deploy our mysql server.

For this example, I am going to create a directory named mysql-env,

user$> mkdir mysql-env
user$> cd mysql-env

The last command should now take you inside ./mysql-env. Type the command below to initialize the workspace.

user:mysql-env$> vagrant init

3.) Configure Vagrantfile

The file Vagrantfile will be created. Open Vagrantfile using your favorite editor, In my case I will use vim. (To install: sudo apt-get install vim).

The initial content of the file will look like,

Vagrant.configure(2) do |config|
    config.vm.box = “base”
end

config.vm.box specifies the type of operating system that will be used as guest operating system running in virtualbox.
Now change “base” to “xenial64”, this will install ubuntu16.04 the 64bit version. The OS will be downloaded from Vagrant’s cloud OS box site.

Add the additional entries as shown below,

Vagrant.configure(2) do |config|

    config.vm.box = "ubuntu/xenial64"

    config.vm.define "db-server" do |db|
        db.vm.network "public_network", :bridge => "eth0", ip: "192.168.1.237"
        db.vm.network "forwarded_port", guest: 3306, host: 3306
        db.vm.network "forwarded_port", guest: 80, host: 8306
        db.vm.provision "shell", path: “bootstrap.sh"
    end
end   

What each line is about,

config.vm.define, allows us to define a named virtual environment. We can define several virtual environments in a single Vagrantfile, as long as we provide a unique name for each. In this particular example, we named our environment to “db-server”.

db.vm.network “public_network”, defines the network interface to use. In this case we are using the host’s dhcp provider (as specified by “public_network”) to assign the ip via a bridge, using the shown IP. Note: the IP value may be changed to suit your network environment. Also make sure the IP is configured in the local network provider/router to lease the IP semi-permanently to this virtual machine as this may change on your next restart of the guest machine, and may have to be reconfigured to a new one.

db.vm.network “forwarded_port”, this will allow us to open a listening port in the host and guest OS. The host OS has the actual connection to the outside world, so it forwards all received packets to the port we specify for the guest OS.

For mysql, we can set 3306 for the guest and host OS.
For the web application phpmyadmin, guest is set to 80, for the host we set to 8306. NOTE: vagrant will complain about port 80 being a well known port and can not be used. Choose any number higher than 1024 and you should be ok.

db.vm.provision “shell”, this allows us to execute a shell script inside the guest OS right after it boots up.
bootstrap.sh, This is where we add additional commands to install and configure the guest OS to run mysql server and phpmyadmin. bootstrap.sh may be renamed to anything you fancy.

4.) Bootstrap.sh.

The contents below should include all we need to configure and install mysql and phpmyadmin.
Change the database name, user, password variables as needed.

#!/usr/bin/env bash

DBHOST=localhost
DBNAME=dbname
DBUSER=dbuser
DBPASSWD=userpass

apt-get update
apt-get install vim curl build-essential python-software-properties git

debconf-set-selections <<< "mysql-server mysql-server/root_password password $DBPASSWD"
debconf-set-selections <<< "mysql-server mysql-server/root_password_again password $DBPASSWD"
debconf-set-selections <<< "phpmyadmin phpmyadmin/dbconfig-install boolean true"
debconf-set-selections <<< "phpmyadmin phpmyadmin/app-password-confirm password $DBPASSWD"
debconf-set-selections <<< "phpmyadmin phpmyadmin/mysql/admin-pass password $DBPASSWD"
debconf-set-selections <<< "phpmyadmin phpmyadmin/mysql/app-pass password $DBPASSWD"
debconf-set-selections <<< "phpmyadmin phpmyadmin/reconfigure-webserver multiselect none"

# install mysql and admin interface

apt-get -y install mysql-server phpmyadmin

mysql -uroot -p$DBPASSWD -e "CREATE DATABASE $DBNAME"
mysql -uroot -p$DBPASSWD -e "grant all privileges on $DBNAME.* to '$DBUSER'@'%' identified by '$DBPASSWD'"

cd /vagrant

# update mysql conf file to allow remote access to the db

sudo sed -i "s/.*bind-address.*/bind-address = 0.0.0.0/" /etc/mysql/mysql.conf.d/mysqld.cnf

sudo service mysql restart

# setup phpmyadmin

apt-get -y install php apache2 libapache2-mod-php php-curl php-gd php-mysql php-gettext a2enmod rewrite

sed -i "s/AllowOverride None/AllowOverride All/g" /etc/apache2/apache2.conf

rm -rf /var/www/html
ln -fs /vagrant/public /var/www/html

sed -i "s/error_reporting = .*/error_reporting = E_ALL/" /etc/php/7.0/apache2/php.ini
sed -i "s/display_errors = .*/display_errors = On/" /etc/php/7.0/apache2/php.ini

service apache2 restart

5.) Boot up db-server

In step 2, We named the virtual machine “db-server”, we use this name to refer to the guest server when sending commands to it.

To start db-server

user:mysql-env$> vagrant up db-server

To check status

user:mysql-env$> vagrant status

To ssh to guest

user:mysql-env$> vagrant ssh db-server

The last command should now bring you inside the db-server. Type cd /vagrant, this will bring you to the base path of the guest machine, which is the same path as your host machine, in mysql-env.

6.) Test connections

Load mysqladmin. Use any browser in the host, or any system in the same network. Login with the username and password you assigned in step-4, bootstrap.sh

On your browser, go to: http://192.168.1.237:8306/phpmyadmin

Connect to mysql server in the host terminal, or any system in the same network. Login with the username and password you assigned in step-4, bootstrap.sh

user$> mysql -u dbuser -p userpass -h 192.168.1.237

That’s it! If all went well, you are on your way to create some more guest services for other purposes, like a spring boot micro service application, or a LAMP/LEMP server, or a Jenkins CI system, the possibilities are endless.

Hope this helps to jumpstart your understanding of vagrant and see for yourself how easy it is to get started. Enjoy and message me if you find any issues with the steps or the scripts.