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)

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

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

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";

To see what a user can do:

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

Creating a database

mysql
create database zonkdb;
use zonkdb;
create table posts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(50),
    body TEXT,
    created DATETIME DEFAULT NULL,
    modified DATETIME DEFAULT NULL
);

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());

\q

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

Deleting a row

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;
\q

Access from other computers

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

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'@'nostrum.mmto.arizona.edu' identified by 'money';

mysql> GRANT lock tables on junk.* to 'hank'@'nostrum.mmto.arizona.edu' identified by 'money';