Month: December 2014

Connecting Windows Azure Pack to MySQL

Samir Farhat Windows Azure Pack , ,

Hello readers,

This is a fast how-to to connect your Windows Azure Pack to a MySQL platform in order to add MySQL services to a Windows Azure Pack platform.

1- Install the MySQL server extension (provider)

Connect to the server where you want to install the MySQL server WAP extension, this operation is mandatory to add the MySQL server support to your Windows Azure Pack environment. It’s recommended to install the extension on the WAP Admin servers.

Run the Microsoft Web platform installer, go to Products, Windows Azure, and choose to install Windows Azure Pack : MySQL Server extension. Follow the instructions to complete the installation, and to configure the Windows Azure Pack platform.

SNAG-0001

2- Install the MySQL server

In order to use MySQL, you need a MySQL server platform. Until the writing time of this blog, the last supported MySQL server version is 5.5. You can use your existent MySQL sever platform, or choose to install a new one. If you choose to use your existent platform, skip this step and go to step 3. Otherwise, connect to the machine where you want to install MySQL server. Run the Microsoft Web platform installer, Products, All, and search for MySQL

SNAG-0002

Choose the latest version of MySQL, click Add and then Install. Follow the instructions until you complete the MySQL installation.

3- MySQL configuration

Now you need to configure your MySQL configuration. I’m not a guru of MySQL, but i will explain here two steps that may be useful for you.

3.1- Configure the default MySQL databases location

By default, MySQL will create new provisioned databases in a default folder. You should/can change this behavior by choosing another location.

  • First, stop the MySQL service.

SNAG-0004

  • Go to the MySQL installation folder (By default: C:\Program Files\MySQL\MySQL Server 5.5) and edit the my.ini configuration file with a text editor (Notepad…). It’s recommended to make a backup of this file before proceeding in case you miss-configure it.

SNAG-0005

  • Locate the default database folder, browse it and copy all the content to the new location where you decided to store the new provisioned databases.

SNAG-0006

To

SNAG-0007

  • Change the datadir value to the new path (In our example E:\MySQLDatabases)

SNAG-0008

  • Start the MySQL service

SNAG-0009

3.2- Configure the Windows Azure Pack / MySQL connection user

Windows Azure Pack needs a MySQL user to connect the MySQL server and provision new databases. I will detail here how to create the user in MySQL and grant it the needed rights.

  • Start the MySQL command line client, tape the root password to access the MySQL server

SNAG-0010

  • To create a new user, you must provide 3 values :
    • Name : The name of the user (in this example : wap)
    • The server name: The server from which you will connect remotely to mysql (in this example: wap-admin01). If your Windows Azure Pack platform is highly available (more than one mySQL provide°, use the % symbol to mean Any Server)
    • Password: the new user password

CREATE USER ‘wap’@’wap-admin01’ IDENTIFIED BY ‘P@ssw0rd’;

OR

CREATE USER ‘wap’@’%’ IDENTIFIED BY ‘P@ssw0rd’;

  • Provide the user the root privileges then apply the rights

GRANT ALL PRIVILEGES ON * . * TO ‘wap’@’wap-admin01’ WITH GRANT OPTION;
FLUSH PRIVILEGES;

4- Connect Windows Azure Pack to MySQL

Finally, log into the Windows Azure Pack admin portal. Go to MySQL Servers and click Add a New MySQL Server

SNAG-0011

Fill the needed information:

MySQL Server Group : Choose a group to include this MySQL server connection to

MySQL Server Name : The name or FQDN of the MySQL server

Username : The name of the user

Password : The password of the user

Size of the hosting server: The maximum allowed databases size (The full storage size)

SNAG-0012

and you got this

SNAG-0013

Congratulations!!!!

—-NB—-

When adding the MySQL server connection to Windows Azure Pack you may encounter the following error if the user does not have root rights with Grant rights. Don’t forget to use the “with Grant Options”

  • The server encountered an internal error. Please retry the request.
  • And the following error is logged on the Microsoft-WindowsAzurePack-MgmtSvc-MySQL/Operational event log, on the WAP Admin server:
    • Source: MgmtSvc-MySQL
    • EventID: 160
    • Level : Error
    • Content: Unexpected exception for operation ”, version ”, client request Id ”, server request Id ”, exception ‘MySql.Data.MySqlClient.MySqlException (0x80004005): Access denied for user ‘wap’@’%’ (using password: YES)

Bonus : MySQL commands

  • How to list the MySQL users: select host, user, password from mysql.user;
  • How to delete a MySQL user: DROP USER “username”