.. _section-Configuring MySQL Support:

Configuring MySQL Support
=========================

**Sunflower** contains XSB and Flora-2 installations that support integration with MySQL databases. To utilize this capability, XSB and Flora-2 need to be configured to know where your MySQL database installation and configuration exist. The following explains how to do this on a Windows system and on a Mac System.


.. _section-Configuring MySQL Support on Windows:

Configuring MySQL Support on Windows
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Start by downloading and installing MySQL server 5.6.23 from <http://dev.mysql.com/downloads/>. You may use either the installer or the archive versions of MySQL. Install and configure MySQL according to the specific distribution’s instructions.

While most installation settings can be left as their defaults, do select the “Full” configuration, as shown in :numref:`figure-Installation-mysql-full.png`.


.. _figure-Installation-mysql-full.png:

.. figure:: ../figs/Installation-mysql-full.png
   :width: 500
   :height: 500
   :alt: Choose “Full” configuration for MySQL installation

   Choose “Full” configuration for MySQL installation

And also be sure to remember your choice of password for the administrative **root** account.

Once MySQL is installed, you will need to add additional environment variables to inform the **Sunflower** XSB and Flora-2 installations about MySQL.

On Windows, it will require administrator access in order to modify the environment variables. Click to activate the **Start** menu, and choose **Control Panel**. Then click **System and Security** followed by **System** to open the **System** control panel.


.. _figure-windows7-system-control-panel.png:

.. figure:: ../figs/windows7-system-control-panel.png
   :width: 500
   :height: 500
   :alt: From the **System** control panel click on **Advanced System Settings**

   From the **System** control panel click on **Advanced System Settings**

Click the **Advanced System Settings** on the left to open the **System Properties** window, as shown in :numref:`figure-windows7-system-control-panel.png`. You may be prompted to authorize the opening of the **System Properties** windows by the operating system. On the **System Properties** window, click the **Advanced** tab, then the **Environment Variables…** button, as in :numref:`figure-windows7-system-properties.jpg`.


.. _figure-windows7-system-properties.jpg:

.. figure:: ../figs/windows7-system-properties.jpg
   :alt: Click the **Environment Variables…** button.

   Click the **Environment Variables…** button.

In the **Environment Variables** window, beneath **User variables for…** click the **New…** button. This will open the **Add User Variable** dialog box, as in :numref:`figure-windows7-new-user-variable.jpg`.

.. _figure-windows7-new-user-variable.jpg:

.. figure:: ../figs/windows7-new-user-variable.jpg
   :alt: Add User Variable dialog box

   Add User Variable dialog box

For **Variable name**, enter MYSQL\_HOME, and for **Variable value**, enter the path to your MySQL server installation. The default location is C:\\Program Files (x86)\\MySQL\\MySQL Server 5.6. Then click **OK** to save the value. Use MYSQL\_HOME to update the environment variable Path so it contains the MySQL installation.

For a listing and explanation of Windows environment variables and values
mentioned above, see :numref:`b`.

.. _b:

.. table:: Windows Environment Variables for MySQL Support
	   
   +-----------------+---------------------------------------------------+
   | Variable name   | Variable value                                    |
   +=================+===================================================+
   | MYSQL\_HOME     | Absolute path to where MySQL server is installed. |
   +-----------------+---------------------------------------------------+
   |                 | This folder should contain bin, includes, and lib +
   +-----------------+---------------------------------------------------+
   |                 | folders.                                          |
   +-----------------+---------------------------------------------------+
   | Path            | Prepend or add %MYSQL\_HOME%;%MYSQL\_HOME%\\bin   |
   +-----------------+---------------------------------------------------+
	

	
Once the necessary environment variables are in place, start the MySQL server as user root. In Windows, one way to do this is to bring up a Command Prompt window with administrative privileges **Start > Command Prompt > right click menu > Run as administrator**, as shown in :numref:`figure-Installation-command-prompt-for-mysql.png`.


.. _figure-Installation-command-prompt-for-mysql.png:

.. figure:: ../figs/Installation-command-prompt-for-mysql.png
   :width: 300
   :height: 300
   :alt: Use **Start > Command Prompt** then right click menu to bring up a Command Prompt window with administrative privileges

   Use **Start > Command Prompt** then right click menu to bring up a Command Prompt window with administrative privileges

In the Command Prompt window, type:

|    ``mysql -u root -p<root-password>``
|

and hit return.  This will start the MySQL server.  The root-password is the MySQL root user password set previously when you install MySQL.  In the example shown in :numref:`figure-Installation-start-mysql.png`, this password has been previously set to rootpswd.


.. _figure-Installation-start-mysql.png:

.. figure:: ../figs/Installation-start-mysql.png
   :width: 500
   :height: 500
   :alt: MySQL server started in Command Prompt window

   MySQL server started in Command Prompt window

At the MySQL prompt, type the following 2 commands, as shown in :numref:`figure-Installation-florauser-mysql-user.png`.

|    ``CREATE USER 'florauser'@'localhost' IDENTIFIED BY 'florapass';``
|    ``GRANT ALL ON *.* TO 'florauser'@'localhost';``
|

This will create a custom MySQL account called florauser. This account can now be used to make all MySQL database creations and connections from **Sunflower**.


.. _figure-Installation-florauser-mysql-user.png:

.. figure:: ../figs/Installation-florauser-mysql-user.png
   :width: 500
   :height: 500
   :alt: User account florauser added to MySQL

   User account florauser added to MySQL

If you plan to use the MySQL features in your **Sunflower** session, be sure to have the MySQL server running before launching **Sunflower**.

For an example of loading and using a MySQL database with **Sunflower**, see Section :ref:`section-Working-with-a-MySQL-Data-Base`.



.. _section-Configuring MySQL Support on Macintosh OS X:

Configuring MySQL Support on Macintosh OS X
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Start by downloading and installing MySQL server 5.7.18 from <http://dev.mysql.com/downloads/>. Please be sure to use the DMG archive installer.  Once download is complete, double click the installer package to start installation.

While the installer does permit you to customize installation, it is recommended that you use the **Standard Install**, as shown in :numref:`figure-install-mysql-community-osx.png`. 

Make note of the root user password if the installer automatically assigns one for you.  Also note that **Sunflower** assumes and automatically configures the standard installation location on the Mac for MySQL, that is, /usr/local/mysql.


.. _figure-install-mysql-community-osx.png:

.. figure:: ../figs/install-mysql-community-osx.png
   :alt: Use the “Standard Install” for MySQL installation

   Use the “Standard Install” for MySQL installation

Ensure that you have MySQL running before proceeding. To do this, open **System Preferences…** and select the **MySQL** preference pane. See :numref:`figure-osx-mysql-prefs-pane-stopped.png`. 


.. _figure-osx-mysql-prefs-pane-stopped.png:

.. figure:: ../figs/osx-mysql-prefs-pane-stopped.png
   :alt: MySQL preference pane in OS X System Preferences showing MySQL server stopped

   MySQL preference pane in OS X System Preferences showing MySQL server stopped

If MySQL is not running, click the **Start MySQL Server** button. Here you will be prompted to enter your credentials.  Once MySQL has started, you will see its status change to running, as seen in :numref:`figure-osx-mysql-prefs-pane.png`.

.. _figure-osx-mysql-prefs-pane.png:

.. figure:: ../figs/osx-mysql-prefs-pane.png
   :alt: MySQL preference pane in OS X System Preferences showing MySQL started

   MySQL preference pane in OS X System Preferences showing MySQL started

   

.. Once MySQL is installed, you will need to add additional environment variables to inform the **Sunflower** XSB and Flora-2 installations about MySQL.

.. On OS X, you will need to create or edit an existing .bashrc file. Using **Finder**, launch the **Terminal** application, located inside . Since you may not have a .bashrc file, we can conditionally create it if it doesn’t exist, and then edit the existing file. To do this, in the Terminal application window, type:

.. |    touch \` /.bashrc 
.. |    open -a TextEdit \` /.bashrc 


.. .. _figure-mysql-osx-edit-bashrc.png:

.. .. figure:: ../figs/mysql-osx-edit-bashrc.png
..   :alt: Editing the .bashrc with TextEdit.

..   Editing the .bashrc with TextEdit.

.. You may see an empty file or an existing file with contents. Near the bottom of the file add the following:

.. |    export DYLD\_LIBRARY\_PATH=/usr/local/mysql/lib:$DYLD\_LIBRARY\_PATH

.. Then save the file by pressing **Command + S** keys or by using the **File > Save** menu option. You can now exit TextEdit by using **TextEdit > Quit** or **Command + Q** keys.

Next we need to add a database user to MySQL that will be used to connect from **Sunflower**. Return to the **Terminal** application window and type at the prompt:

|    ``/usr/local/mysql/bin/mysql -u root -p``
|

MySQL will ask for the root user's password.  

|    ``Enter password: <root-password>``
|

The root-password is the MySQL root user password set previously when you install MySQL, either automatically by the MySQL installer or by you.  If you did not set a password for the root user, just press the **Return** key when prompted to enter the password, as shown in :numref:`figure-osx-mysql-loggedin.png`.


.. _figure-osx-mysql-loggedin.png:

.. figure:: ../figs/osx-mysql-loggedin.png
   :alt: Logging into MySQL from Terminal

   Logging into MySQL from Terminal

At the mysql> prompt, enter the following command to change the MySQL root user passwrod before proceeding further.  Here we choose rootpw as the new password, but you may use any password you prefer, as long as you remember it for later use.

|    ``ALTER USER 'root'@'localhost' IDENTIFIED BY 'rootpw';``   
|

Again at the mysql> prompt, enter the following 2 commands to add a new user and grant them permissions on the database:


|    ``CREATE USER 'florauser'@'localhost' IDENTIFIED BY 'florapass';``
|    ``GRANT ALL ON *.* TO 'florauser'@'localhost';``
|

This will create a MySQL user account called florauser as seen in :numref:`figure-osx-mysql-add-new-user-and-grant-privs.png`. This account can now be used to make all MySQL database creations and connections from **Sunflower**.


.. _figure-osx-mysql-add-new-user-and-grant-privs.png:

.. figure:: ../figs/osx-mysql-add-new-user-and-grant-privs.png
   :alt: User account florauser added to MySQL

   User account florauser added to MySQL

.. Now logout and login, or restart your Macintosh to capture the new environment variables.

To load and use a MySQL database with **Sunflower**, see Section :ref:`section-Working-with-a-MySQL-Data-Base` for an example.