using_mysql.md 7.61 KB
Newer Older
Adam Caprez's avatar
Adam Caprez committed
1
+++
2
3
title = "Using MySQL instances"
description = "How to connect to and use MySQL-based instances"
Adam Caprez's avatar
Adam Caprez committed
4
5
6
7
8
9
10
+++

{{% 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 %}}

Adam Caprez's avatar
Adam Caprez committed
11
The MySQL images HCC provides are CentOS 7 based with MySQL/MariaDB installed
Adam Caprez's avatar
Adam Caprez committed
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
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 >}}

144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
### Using a volume to store the database

By default, the database is stored on the instance's virtual disk. The amount of space
available may not be sufficient for larger databases, so a _Volume_ may be used instead.

All of the following setup commands need to be run as `root` in the instance after
connecting via SSH from either [Windows]({{< relref "connecting_to_linux_instances_from_windows" >}})
or [Mac]({{< relref "connecting_to_linux_instances_from_mac" >}}).

{{% panel theme="danger" header="**Running commands as root**" %}}**Extreme care should be taken when running commands as `root.`** It is very easy to permanently delete data or cause irreparable damage to your instance.{{% /panel %}}

In order to use a volume, first follow the instructions to
[create and attach a volume]({{< relref "creating_and_attaching_a_volume" >}}) to your
instance MySQL instance. Next, proceed with [formatting and mounting the volume]({{< relref "formatting_and_mounting_a_volume_in_linux" >}})
in your instance. If all the steps complete without errors, you should have your volume mounted at `/mnt/myvolume`.

Next, the MySQL server needs to be stopped and the existing contents of its storage directory copied to the volume:

{{< highlight bash >}}
service mysql stop
rsync -av /var/lib/mysql/ /mnt/myvolume/
{{< /highlight >}}

Now the volume can be unmounted from `/mnt/myvolume` and remounted over the existing `/var/lib/mysql` location:

{{< highlight bash >}}
umount /mnt/myvolume
mount /dev/vdb1 /var/lib/mysql
{{< /highlight >}}

Running the `df -h` command should show the `/var/lib/mysql` location as mounted from `/dev/vdb1` with the
increased storage amount:

{{< highlight bash >}}
/dev/vdb1        99G  183M   94G   1% /var/lib/mysql
{{< /highlight >}}

In this example the volume size is 100GB. In general, it should be approximately the same size as your volume.

Finally, restart the MySQL server:

{{< highlight bash >}}
service mysql start
{{< /highlight >}}

Assuming the `service` command doesn't return an error, the increased space is now available for use.

The last step is to ensure the volume gets mounted automatically after the instance is rebooted.  To
enable this, add the following line to the `/etc/fstab` file:

{{< highlight bash >}}
/dev/vdb1	/var/lib/mysql	ext4	defaults 0 0
{{< /highlight >}}