by, for, and about women in science, technology, engineering, & math

Introduction

With every new project comes a new database. If this is your first time setting up a database outside of phpMyAdmin or one of its ugly cousins, relax! In no time you will be a graduate of the command-line interface.

Logging In

First thing’s first. We need to log in to our server and connect to MySQL.

amanda@ubuntu:~$ mysql -u root -p

Enter password:

Assuming you remember your password you’ll be greeted with the following ‘hello’ from your MySQL server. Note the part about ending commands.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

Creating a Database

We can tell that we are connected successfully and the world is happy by the all-important mysql> waiting for our command. Let’s give it something to work with.

mysql> create database awesomenewdb

->

Note the →. When we don’t end our commands with a semi-colon MySQL assumes there is more to come. To let her know we’re done, just show her some love. We can even attach another command on the same line.

Introducing the Database to a Friend

 -> ; grant ALL on awesomenewdb.* to awesomenewuser@localhost;

Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Cool, so now we have a database and a user. Now what? We need to set awesomenewuser up with a password.

mysql> set password for awesomenewuser = password('awesomenewpassword');

Query OK, 0 rows affected (0.00 sec)

Using Protection

Now we have our awesomenewdb and our awesomenewuser. Our awesome user now has access to our entire database, which works out because, well, they’re awesome. But what if they were less awesome? Like, what if they were kind of mean? And might delete stuff we like? Or what if we just need them to add data and not snoop around? Thankfully MySQL totally understands. Let’s set up a second user as well.

mysql> grant SELECT on awesomenewdb.* to readonlyuser@localhost
identified by "somepassword";

We can now give the above username and password to anyone who only needs to look at the content. We could also grant them CREATE, INSERT, DELETE and UPDATE privileges as well. Also note that we don’t have to do two seperate commands just to set up a new user. We can use the idenfitied by to specify a password on the same line.

Cleaning Everything Up

Yes, they’re called privileges. And we need to flush them just like a toilet. MySQL actually just wrote a couple entries to a database as we added users. Flushing the privileges asks MySQL to reload that database to make sure everything actually works. The exit politely closes out of the server so we can go back to our daily lives. Pretty nice.

mysql> flush privileges;

mysql> exit;

It’s pretty likely that you will want to add a table to this new database. Sorry, kids. But that’s another story all together. Stay tuned.