Editing CommunityData:MySQL

If you need to use a MySQL database for research, you'll need to create a database on [[CommunityData:Kibo|Kibo]]. Kibo has MySQL already installed. You'll also need to create a user with access to that database. Doing these two steps will require access to MySQL using the MySQL "root" user.

== Root Credentials on Kibo ==

First, you'll need an account on Kibo. [[CommunityData:Kibo]] has details on how to do this but it will require a NU NetID and a bunch of other steps. Alas. Once you have this account, you'll need to use the following steps:

* To find username and password credentials to access MySQL, you'll need to get a shell as root. Type <code>sudo -s</code> to get a root shell first.
* Change to the <code>root</code> directory by typing <code>cd ~root</code>.
* You'll find the file labeled <code>mysql_root_credentials</code> that contains the username and password to access MySQL on kibo.

== Making a new MySQL database and user ==

Before you start, you'll need to decide on a name for your database (<code><DATABASENAME></code>), a name for your user (<code><USERNAME></code>), and a password (<code><PASSWORD></code>). The password should be secure. I generate mine with a shell command like <code>pwgen -y 20</code> or some other secure password generator. I typically make the database name and username the same.

Run the following shell commands (replacing <code><DATABASENAME></code> with the name of the database you want to create):

:<code>$ mysqladmin create <DATABASENAME></code>
:<code>$ mysql -uroot -p mysql</code>

Once you have done that and entered the password, you will put into the MySQL shell. Youll need to run these two commands (replacing things as appropriate):

:<code>CREATE USER '<USERNAME>'@'%' IDENTIFIED BY '<PASSWORD>';</code>
:<code>GRANT ALL ON <DATABASENAME>.* TO '<USERNAME>'@'%';</code>

Now exit the MySQL shell with Ctrl-D or similar. You should be good to go! You'll need to enter your your username and password into any scripts you write that will be reading from and/or writing from the database.

It is also possible to create users with only read access to the database, and similar. Details on how to do that are available in MySQL documentation on the web.