Monday, April 6, 2015

Quickly Spinning up a MYSQL DB with Vagrant

Since we often have to carry out these steps in a variety of my guides, I've decided to isolate them, here. By the end of this article, you'll have a Vagrant VM running MySQL.

Prerequisites: Reading up on Ansible Vagrant is recommended. This article was written for Mac OSX users.

Preparing Our MySQL Database

# spin up the vm and ssh in
git clone https://github.com/roblayton/vagrant-ansible-mysql-python
cd vagrant-ansible-mysql-python

# optionally, take a look at the vagrantfile
cat Vagrantfile

Notice we're setting the private network to 192.168.33.10. You're welcome to set this IP address to whatever you'd like, especially if you're spinning up multiple VMs. We're also leveraging Ansible to further customize the VM with MySQL. See playbook.yml to understand the specific configuration.

# -*- mode: ruby -*-
# vi: set ft=ruby :

# Vagrantfile API/syntax version. Don't touch unless you know what you're doing!
VAGRANTFILE_API_VERSION = "2"

Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  config.vm.box = "ubuntu/trusty64"
  config.vm.network "forwarded_port", guest: 3306, host: 3306
  config.vm.network "private_network", ip: "192.168.33.10"

  config.vm.provider "virtualbox" do |vb|
     vb.customize ["modifyvm", :id, "--memory", "2048"]
  end

  config.vm.provision "ansible" do |ansible|
    ansible.playbook = "playbook.yml"
    ansible.verbose = "vv"
    ansible.sudo = true
  end
end
# spin up the instance
vagrant up
vagrant ssh

# create a user with privileges to access the db
mysql -uroot

CREATE USER 'test'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost' WITH GRANT OPTION;
CREATE USER 'test'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' WITH GRANT OPTION;

# exit out of mysql
exit

# turn on networking
sudo nano /etc/mysql/my.cnf

# change this line from 127.0.0.1 to 0.0.0.0
bind-address            = 0.0.0.0

# restart mysql
sudo service mysql restart

# exit out of the VM
exit

# verify that you can connect from your Mac
# enter "password" when prompted
mysql -utest -h192.168.33.10 -p

By setting the bind-address to 0.0.0.0, the VM will accept TCP/IP connections on all server host IPv4 interfaces. See theMySQL documentation:

  • If the address is 0.0.0.0, the server accepts TCP/IP connections on all server host IPv4 interfaces.
  • If the address is ::, the server accepts TCP/IP connections on all server host IPv4 and IPv6 interfaces. Use this address to permit both IPv4 and IPv6 connections on all server interfaces.
  • If the address is an IPv4-mapped address, the server accepts TCP/IP connections for that address, in either IPv4 or IPv6 format. For example, if the server is bound to ::ffff:127.0.0.1, clients can connect using --host=127.0.0.1 or --host=::ffff:127.0.0.1.
  • If the address is a “regular” IPv4 or IPv6 address (such as 127.0.0.1 or ::1), the server accepts TCP/IP connections only for that IPv4 or IPv6 address.

Warning: It isn't a good idea to expose MySQL or any database server to the world, but for local development on a private network, it's perfectly acceptable. When running mysql in production, I strongly recommend running sudo mysql_secure_installation.

No comments:

Post a Comment