My MySQL notes

April 12, 2013

I use MySQL just seldom enough to forget the important details, so this is my little tutorial about how to do the basics.

I do have some version of the MySQL manual online here from when I installed this years ago.

I also must recommend my two favorite MySQL books. The first is a fat one named MySQL written by Paul DuBois (I have the second edition). It has served me well. The other is the MySQL Tutorial by Luke Welling and Laura Thomson. The biggest virtue of this book is that is is succinct. Also it is "official" in the sense of being published by the MySQL press (for what that is worth). Both books have been worthwhile and useful.

The MySQL Monitor (command line)

Before launching into this, I should point out that there is another approach to doing this stuff, namely using phpMyAdmin, or better yet, the MySQL workbench.

mysql will run the command line mysql "monitor". This is what I use for all my administrative tasks. Note that a semicolon ; must be used at the end of all mysql monitor commands.

The command \q quits the monitor.
The command quit; also seems to work.

show databases; will show you what databases exist on your system. The database mysql is special. It should always be there and will hold general and global mysql stuff. The user table holds all mysql users and passwords.

use xxx; Tells the mysql monitor to use database xxx as the database to resolve all subsequent commands.

show tables; shows the tables within the selected database.

select * from table shows all the records in the specified table.

For some reason everyone skips the two most basic parts of administering MySQL and expect to just dive in and start using creating databases and tables. The two basic things are creating users and databases.

MySQL users

This was a big shock to me: MySQL has its own world of users and passwords. MySQL has its own root user, with its own password. Typically new users are created for each project, and each user can be given greater or lesser privileges, as is appropriate. This is a good thing, despite the complications it causes, because a project may have a general read-only user for the part exposed to the general web, and a special administrative user for parts of the project that should be allowed to modify the database.

To do any of the following, you will need to be running the mysql monitor as the mysql root user. Do this:

mysql -u root -p

Users are stored in the user table of the mysql database, so to list all your users:

select * from mysql.user;
select user,host from mysql.user;

To poke around and inspect the mysql database, do the usual:

use mysql;
show tables;
select * from user;

New users are created using (of all things) the "create user" command:

create user 'joe'@'host' identified by 'password';
delete from mysql.user where user='joe'; (old and bad)
flush privileges; (harmless, but not necessary if you use the following)
drop user joe@host
It seems best to specify the host name as the fully qualified domain name (i.e., not just "spam"), but sometimes I create both users with identical privileges since all of this is so confusing and chaotic.

Here is an important note on the above (and all of this). The details have changed a lot with different mysql versions (and have changed significantly over the time since I first wrote all of this). The prefered way to get rid of a user these days is the drop user command, it gets rid of the user and their privileges.

There is some suggestion that you can create a user and grant the user privileges in one command like the following. I don't know if this is a shortcut or a historical artifact that may no longer work this way.

grant select,insert,update,delete on zonkdb.* to "zonk_admin" identified by "uranium";
grant select on zonkdb.* to "zonk_user" identified by "borax";
grant all on zonkdb.* to "zonk_root" identified by "super";

The more usual case (at least in the way I do things) is to user the create user command to create the user, then the grant command to specify what the user can do.

grant select,insert,update,delete on zonkdb.* to "zonk_admin";
grant select on zonkdb.* to "zonk_user";
grant all on zonkdb.* to "zonk_root";
Note that you can remove privileges using revoke.

To see what a user can do:

show grants;
show grants for current_user;
show grants for 'joe'@'host';

Creating a database

Do this:
create database zonkdb;
use zonkdb;
create table posts (
    title VARCHAR(50),
    body TEXT,

insert into posts (title,body,created)
    values ( 'The title', 'This is the post body.', NOW());
insert into posts (title,body,created)
    VALUES ('A title once again', 'And the post body follows.', NOW());
insert into posts (title,body,created)
    VALUES ('Title strikes back', 'This is really exciting! Not.', NOW());


This will create a database with one table, and adds 3 somewhat bogus records to it.

Deleting a row

Once in a while my clever web application creates a row I don't like and there is no way to delete it (via the web interface). Do something like this:
mysql --i-am-a-dummy
(or more likely:)
mysql --i-am-a-dummy -u root -p
use database
delete from table where record_id = 325;

Access from other computers

MySQL makes itself available via its own protocol on port 3306. If you don't want this at all (which is often the case), you can edit /etc/my.cnf and add the line skip-networking to the [mysqld] section and do away with this altogether, which could be viewed as a dramatic security enhancement.

If you do want to enable access from other computers, you will want to not add this line, then ensure that your firewall is not blocking port 3306. Then you need to fiddle with the MySQL grant tables to allow access from other (or another) machine. The following is one way to allow one specific machine to have access to one specific table.

mysql -u root -p
This will prompt for your root password (which hopefully you know), it is not necessarily the same as that machines root password. As mentioned above, MySQL lives in its own world of users and passwords.

To allow access to all tables of the database "junk" from machine "nostrum" by user "hank" with password "money" do this:

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER on junk.* to 'hank'@'' identified by 'money';

mysql> GRANT lock tables on junk.* to 'hank'@'' identified by 'money';
Note that fully qualified domain names (as used above) are required.
Feedback? Questions? Drop me a line!

MySQL notes /