devops: mysql server on azure ubuntu vm

Full disclosure, there are a lot of tutorials for this on the azure website. I just found that every one of them had either enough errors, or enough missing pieces, that it made it impossible to get setup correctly unless you knew what you were doing, so hacked quite a bit of them together.

Go to manage.windowsazure.com
Click Virtual Machines > New > From Gallery
(I’m using Ubuntu server 14.04 LTS)

Virtual Machine Name: yourvm
(This will become the address in yourvm.cloudapp.net)

New User Name: yourvmusername
(this will be used anytime you need to access the vm)

UNCHECK: UPLOAD COMPATIBLE SSH KEY FOR AUTHENTICATION
(you are welcome to handle keys yourself, I’m just going for passwords)

CHECK: PROVIDE A PASSWORD: yourvmPassword!
(this will be the password to access the vm)

SET: create new cloud service

CHECK: VM agent

COMPLETE

Open Terminal. Connect Via SSH:
ssh yourvmusername@yourvm.cloudapp.net -o ServerAliveInterval=180
//You will see the following, enter yes, then your vm password:
The authenticity of host 'yourvm.cloudapp.net (255.255.255.255)' can't be established.
Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added 'yourvm.cloudapp.net,255.255.255.255' (RSA) to the list of known hosts.
yourvmusername@yourvm.cloudapp.net's password:
yourvmPassword!

Welcome to Ubuntu 14.04.2 LTS (GNU/Linux 3.16.0-43-generic x86_64)
//a bunch of text
//then you'll be at the prompt: yourvmusername@yourvm:~$

Update the VM:
sudo apt-get update
(could take a couple of minutes, bunch of text)
//back to prompt

Install MySQL Server on the VM:
sudo apt-get install mysql-server
//bunch of text
Do you want to continue? [Y/n] Y

UI popup for password for root user on mysql server: yourSQLpw
(this will be how you initially access the mysql server, via un: root, pw: yourSQLpw)
//Confirm it, then back to terminal, lots of text spam, even some things that look like errors or warnings “sent invalidate” and “using unique option prefix” just move on.
//back to prompt

Install MySQL Client on the VM:
sudo apt-get install mysql-client
//bunch of text
//back to prompt

Test if the Service is Running:
sudo service mysql status
//should see: mysql start/running, process etc

Now Open Port 3306 on the VM:
sudo iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT

Test that the Port is Open:
sudo netstat -anltp|grep :3306
//you should see something like:
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 3259/mysqld
(3306 in red, that doesn’t mean bad, just that it found that number)

SSH Tunnel from 3307 to 3306:
(Now we create a tunnel on the VM that takes public connections from 3307 and locally sends them to 3306, which is the mysql server)
sudo ssh -fNg -L 3307:127.0.0.1:3306 yourvmusername@yourvm.cloudapp.net
//warnings about authenticity will pop up:
yes
//then you’ll see yourvmusername@yourvm.cloudapp.net’s password:
(enter your vm password, NOT your mysql password)
yourvmPassword!
//back to prompt

Go Back to manage.windowsazure.com

Setup End Point on the VM:
(this will be the public port 3307 that we add to the VM, which will then hit the SSH Tunnel we made, and route all traffic to the mySql server)
Click on yourvm in the VM tab
Click Endpoints
Click ADD+ button at the bottom of the screen
Name it anything, I named mine 'MySQL Tunnel'
Use TCP
Public Port 3307
Private Port 3307
Don't check any boxes

DONE WITH SETUP!

Now to Connect to the database from Node or PHP, etc, You Use:
URL: yourvm.cloudapp.net
Port: 3307
User: root
Password: yourSQLpw

(obviously you can create other user accounts and not use root if you want the edit trail)

To Connect to the VM from CLI:
ssh yourvmusername@yourvm.cloudapp.net -o ServerAliveInterval=180
password: yourvmPassword!

Access MySQL Directly from the VM CLI:
mysql -u root -p
password: yourSQLpw