Configuring mysql server on Ubuntu in Azure

We recently had to create a mysql server on an Azure VM and give remote access to one database.

I followed the very good documentation here. The steps I had to follow were connecting via ssh

ssh -i myprivatekey.key instanceusername@instancename.cloudapp.net

Then, to install mysql server

sudo apt-get install mysql-server

This automatically creates mysql service which makes sure that mysql is restarted when VM is rebooted

And then we have to secure this mysql installation. Running this command helps you set the values for common settings by asking you questions and recommending good options

mysql_secure_installation

After this log into mysql as root

mysql -u root -p

To create a new MySQL user, run the following at the mysql> prompt

CREATE USER 'mysqluser'@'localhost' IDENTIFIED BY 'password';

Create a database and give all privileges to mysqluser

CREATE DATABASE testdatabase;

GRANT ALL ON testdatabase.* TO 'mysqluser'@'localhost' IDENTIFIED BY 'password';

Other options in this command are

# give permission on all databases to mysqluser from localhost
GRANT ALL ON *.* TO 'mysqluser'@'localhost' IDENTIFIED BY 'password';

# give permission on testdatabase to mysqluser connecting 
# from the particular ip 123.234.132.412 
GRANT ALL ON testdatabase.* TO 'mysqluser'@'123.234.132.412' IDENTIFIED BY 'password';

For a complete list of options available you should consult MySQL Docs

To permit the user mysqluser to connect from any ip,

# permitting mysqluser from any ip to access students table 
# on testdatabase
GRANT ALL ON testdatabase.students TO 'mysqluser'@'%' IDENTIFIED BY 'password';

Notice that we used ‘%’ as a wild card for ip while ‘*’ for database/table names wildcard. For a remote user to connect with the correct privileges you need to have that user created in both the localhost and ‘%’ as in.

# creating mysqluser at locahost to access students table 
# on testdatabase
GRANT ALL ON testdatabase.students TO 'mysqluser'@'locahost' IDENTIFIED BY 'password';

Exit the mysql shell

quit

This is not all. We have to add the port where mysql is running (default is 3306) as an endpoint in azure management portal. We have to change the bind-address in mysql config that is located at /etc/mysql/my.cnf. As per this SO answer , you should edit that file and put this line for bind-address

bind-address = 0.0.0.0

Then mysql needs to be restarted. As per this answer

sudo service mysql stop
sudo service mysql start

You should now be able to connect and access students table in testdatabase as mysqluser from anywhere in the world by providing the following params.

host url : instancename.cloudapp.net
port no. : 3306 [optional if default]
username : mysqluser
password : password