Skip to content
Snippets Groups Projects

Add MySQL image instructions.

Merged Add MySQL image instructions.
anvil-mysql into master
Merged Adam Caprez requested to merge anvil-mysql into master
Compare
and
13 files
+ 144
− 1
Compare changes
  • Side-by-side
  • Inline
Files
13
content/anvil/connecting_to_mysql.md 0 → 100644
+ 143
− 0
 
+++
 
title = "Connecting to MySQL instances"
 
description = "How to connect to MySQL-based instances"
 
+++
 
 
{{% notice info %}}
 
This guide assumes you associated your SSH Key Pair with the instance
 
when it was created, and that you are connected to the [Anvil VPN]({{< relref "connecting_to_the_anvil_vpn" >}}).
 
{{% /notice %}}
 
 
The MySQL image HCC provides is a CentOS 7 image with MySQL (MariaDB) installed
 
and configured to allow remote access for a MySQL user named `root`.
 
Access to MySQL is provided via this `root` user and a random password. When
 
the instance is created, the password is set randomly using your
 
SSH Key Pair. This password can be retrieved via the Dashboard web
 
interface, and is then used to login to MySQL.
 
 
{{% notice info %}}
 
The `root` user is for connecting to MySQL only. Access via SSH is the same
 
as other CentOS-based images, using the `centos` user and SSH keys. Refer to
 
the instructions for [Windows]({{< relref "connecting_to_linux_instances_from_windows" >}})
 
or [Mac]({{< relref "connecting_to_linux_instances_from_mac" >}}) as needed.
 
{{% /notice %}}
 
 
This guide assumes you have already created an instance using the `MySQL Server` image.
 
In order to access the database, two additional steps are required. A
 
_Security Group_ must be created in OpenStack to allow access to the MySQL port,
 
and the randomly generated password must be recovered.
 
 
### Create and attach a security group
 
 
Log into the Anvil web dashboard at [anvil.unl.edu](https://anvil.unl.edu) using
 
your HCC credentials. On the left-hand side navigation menu,
 
click *Access & Security*.
 
 
{{< figure src="/images/13599031.png" >}}
 
 
The *Security Groups* tab should already be selected. Click the *Create Security Group* button
 
in the upper right.
 
 
{{< figure src="/images/security_groups_1.png" width="900" >}}
 
 
A new window will open. Type in "MySQL" for the *Name* and then click *Create Security Group* to
 
save and close the window.
 
 
{{< figure src="/images/security_groups_2.png" width="600" >}}
 
 
Next, click *Manage Rules* for the newly created *MySQL* group.
 
 
{{< figure src="/images/security_groups_3.png" width="900" >}}
 
 
The details page for the group will open. Click the *Add Rule* button in the upper right.
 
 
{{< figure src="/images/security_groups_4.png" width="900" >}}
 
 
In the new window, open the *Rule* drop-down menu and select *MYSQL*. Click *Add* to finalize
 
the selection and close the window.
 
 
{{< figure src="/images/security_groups_5.png" width="600" >}}
 
 
You should now be returned to the details page for the security group. Verify that the
 
MYSQL rule is listed.
 
 
{{< figure src="/images/security_groups_6.png" width="900" >}}
 
 
Click the *Instances* button on the left, and locate your MySQL Server instance in the
 
list. Open the drop-down menu on the far right, and choose *Edit Security Groups*.
 
 
{{< figure src="/images/security_groups_7.png" width="150" >}}
 
 
A new window will open with the available security groups on the left, and the active ones
 
for the instance on the right. Click the `+` sign next to the *MySQL* group to add it to
 
your instance.
 
 
{{< figure src="/images/security_groups_8.png" width="600" >}}
 
 
It will move from the left side to the right.
 
Now click *Save* to apply the changes and close the window.
 
 
{{< figure src="/images/security_groups_9.png" width="600" >}}
 
 
You can now connect to the MySQL server in your instance.
 
 
### Recover the MySQL password
 
Once your instance is running, login to the Anvil web dashboard
 
at [anvil.unl.edu](https://anvil.unl.edu) and click the *Instances* menu option on left-hand side.
 
You should see an entry for your instance similar to the following:
 
 
{{< figure src="/images/sql_server_1.png" width="900" >}}
 
 
Click the down arrow next to *Create Snapshot* to open the drop-down
 
menu and select *Retrieve Password*:
 
 
{{< figure src="/images/13042846.png" height="400" >}}
 
 
This will open a new pop-up window where you will need to select
 
your **private** SSH key file. Click the *Choose File* button to open a
 
file explorer window.
 
 
{{< figure src="/images/13042857.png" width="600">}}
 
 
Navigate to your private key file and choose to open the file. The
 
large text box should now have the contents of your private key. Click
 
the *Decrypt Password* button:
 
 
{{< figure src="/images/13042860.png" width="600" >}}
 
 
The randomly generated password should appear in the *Password* field.
 
 
{{< figure src="/images/13042862.png" width="600" >}}
 
 
Copy and paste this password into a convenient text editor.
 
 
### Connecting to MySQL
 
 
Determine the IP address of your instance by looking at the fourth
 
column entry on the *Instances* page:
 
 
{{< figure src="/images/sql_server_2.png" width="900" >}}
 
 
Using the username `root` along with the recovered password and the IP
 
address, you can now connect to MySQL using the program of your choice.
 
 
For example, using the command line client:
 
 
{{< highlight bash >}}
 
[demo20@login ~]$ mysql -u root -h 10.71.104.142 -p
 
Enter password:
 
Welcome to the MySQL monitor. Commands end with ; or \g.
 
Your MySQL connection id is 6
 
Server version: 5.5.65-MariaDB MariaDB Server
 
 
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
 
 
Oracle is a registered trademark of Oracle Corporation and/or its
 
affiliates. Other names may be trademarks of their respective
 
owners.
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
mysql>
 
{{< /highlight >}}