Quick Guide: A Basic MySQL Tutorial

Introduction

Generally, we use capital letters for mysql statements, but that is not required. The only time case matters is for the name of fields in a table, or database and table names, and when using \G instead of \g to end a statement. SQL statements are terminated with a semicolon, or a \g. You can also terminate a mysql statement with a \G if you wish an expanded/more human readable output to some commands.
If you ever get a prompt "->" instead of "mysql>", that means mysql is waiting for you to finish your statement. That probably means you failed to provide either a semicolon, a \g, or a \G at the end (note that \G and \g produce responses formatted differently, but basically they have the same effect.
To exit mysql, simply use the quit, exit, or \q statement.

The mysql client

Options to the mysql client are optional, but generally you'll need a few. If you forget to specify "-p" then mysql will try logging in without a password-- which probably wont work.

mysql -h remote-host -u userid -p
mysql -h remote-host -u userid --password='password'

If -p is specified, but no password given, then mysql will prompt for a password. Failure to use the -p option for accounts for which passwords are assigned will result in a failure. If -h isn't given, then mysql attempts to connect to localhost.
The mysql server must be running on the local host for mysqladmin to work.
Brackets "[]" are used to indicate optional parameters-- don't actually type the brackets :-P.

mysqladmin, mysqldump and mysql all take the -u -h and -p flags.

Setting Initial mysql root Password

This may have already been done when MySQL was installed on your system. Versions of Ubuntu 9+ and later prompt for the root password on install, earlier versions do not. If a root password has already been assigned, then skip to the following section on how to reset a forgotten MySQL root password.
First step is to start the server.

mysqladmin method (localhost only)

# mysqladmin -u root password "new-password"

mysql client method

# mysql  -u root mysql
mysql> use mysql;
mysql> SET PASSWORD FOR root=PASSWORD('new password');
mysql> flush privileges;
mysql> quit;

You can also set the password for specific remote hosts, by using root@hostname instead of root for the host.

Chainging passwords

mysqladmin -u user-id "old-password" "new-password"

Resetting forgotten mysql root password

Note: The command "sudo" allows for commands to be executed as the super user on Unix and Unix-like operating systems (including Mac OS X, Linux, BSD, Solaris, and many others). If you're already logged in as root, then you do not need to use sudo. If your system does not have sudo, then you will need to log in as the root or use and account with administrator privileges (for both Unix/Unix-like, and non-Unix systems such as Microsoft Windows).
1.First step is to kill the running mysqld server:
Using the init scripts:

# sudo /etc/init.d/mysqld stop

Using the killalll command:

# sudo killall mysqld

Now start a new server that will ignore password checks...
If your have mysql_safe:

# sudo mysql_safe --skip-grant-tables &

If not:

# sudo mysqld --safe-mode --skip-grant-tables &

2.Next, lets change the password(s):

# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.38-Ubuntu_0ubuntu1.1-log Ubuntu 7.04 distribution

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

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set password=PASSWORD("root password")
    -> where User='root';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
# sudo killall mysqld

3.Finally, restart the server

# sudo /etc/init.d/mysql start

Or you can restart it without using init scripts:

# sudo mysqld&

Addining a Database

mysqladmin method

mysqladmin create database_name

mysql client method

# mysql -u root -p [password] [-h hostname]
mysql> CREATE database database_name;
mysql> quit;

Listing databases and tables

If you want to see all your databases:

mysql> SHOW databases;

If you want to take a look at the tables in a database:

mysql> USE database_name;
mysql> SHOW tables;

Removing a Database or Table

The DROP command is used for all these operations.

Dropping a database

mysql> DROP DATABASE database_name; quit;

You can use SHOW DATABASES; to list all databases on the mysql server. After selecting a database with USE database_name, you may list all tables within that database with SHOW TABLES. No, the commands do not need to be upper case.

Dropping a table

To drop a table:

mysql> USE database_name;
mysql> DROP TABLE table_name;

Backing up a Database

Use mysqldump to back up your database.

mysqldump -u root -p databasename >backup-file.sql

That simple!
The sql file may be very large, so piping it throug gzip first is a good idea.

mysqldump -u root -p databasename |gzip -9 >backup-file.sql.gz

Restoring a Database

First, you'll need to create the database you wish to restore as it doesn't exist yet.

# mysql -u root -p
mysql> CREATE DATABASE us_presidents; quit;

Next, restore your database.

mysql -u root -p databasename <backup-file.sql

or

gzip -d <backup-file.sql.gz|mysql -u root -p database

You may use bzip2 if you like, but gzip is far faster, and on modern computers, can compress text data faster than it can be written to the hard drive (PATA/IDE drives, fast SATA/SCSI/SCA drives may be another matter).

User Administration

Adding Users

Adding users is done with the following mysql command:

GRANT  privlages ON database_name.tablename  TO 
  user@[hostname/'%']  IDENTIFIED BY 'password';

Though this may be more useful if you intent to grant a user control over a database (including tables, or if no tables in the database exist):

GRANT ALL ON database.* TO user@'%' IDENTIFIED BY 'password';

You may use wild cards for the database name. To select all databases, use "*.*" for the database name. Using '%' for the hostname means the user may log in from any host (the '%' wildcard doesn't work on all distributions). There are several privileges that may be set per user. If you're creating a database for your use, you should use ALL for the privileges (optionally, you may add "privileges" after the ALL statement). See the permission list below.
If we wanted to create a user zippy from da-host.com with the password zipzap, with all privileges on the boo database, we would use:

GRANT ALL ON boo.* TO zippy@da-host.com IDENTIFIED BY 'zipzap';

We could also do this:

GRANT ALL PRIVILEGES ON boo.* TO zippy@da-host.com IDENTIFIED BY 'zipzap';

To create a user with ALL, SUPER, and CREATE USER privileges on all database that can log in from any host computer, using the ID anewroot with password r00tyt00ty, we would use:

GRANT ALL, SUPER, CREATE USER ON *.* TO anewroot@'%' 
  IDENTIFIED BY 'r00tyt00ty';

We could also use *.
Here is a partial list of permissions:
CREATE USER || allows adding/deleting of users and changing user permissions for the selected databases (CREATE USER, DROP USER, RENAME USER, REVOKE ALL PRIVILEGES)

ALL [PRIVILEGES]	sets all simple permissions except granting of permissions
ALTER	allows altering of tables (ALTER TABLE)
CREATE	allows creation of new tables (CREATE TABLE)
CREATE TEMPORARY TABLES	allows creation of temporary tables (CREATE TEMPORARY TABLE)
DROP	allows dropping of tables (DROP TABLE)
DELETE	allows use of DELETE (DELETE)
INSERT	allows use of INSERT (INSERT)
SELECT	allows use of SELECT, see note below (SELECT, SHOW CREATE TABLE)
FILE	allows use of file commands (SELECT ___ INTO OUTFILE, LOAD DATA INFILE)
INDEX	allows creating and dropping indexes (DROP INDEX, CREATE INDEX)
RELOAD	allow use of FLUSH (FLUSH)
UPDATE	allows use of UPDATE (UPDATE)
PROCESS	allows listing of processes, useful for troubleshooting (SHOW PROCESSLIST)
SUPER	special privileges, including making one connection to the server even if max_connections is reached. (CHANGE MASTER, KILL, PURGE MASTER LOGS, SET GLOBAL)
SHUTDOWN	allows shutdown of mysql server via mysqladmin
USAGE	same as NO PRIVILEGES
GRANT OPTION	allows for graning of privlages.
EXECUTE	Allows for executing of stored routines on mysql 5+
ALTER ROUTINE	allows for altering of stored routines on mysql 5+ (ALTER ROUTINE)
CREATE ROUTINE	allows for creating stored routines.
REPLICATION CLIENT	allows client get location of master and slaves
REPLICATION SLAVE	needed by replication slaves to allow access to binary log file.

It has been reported that granting SELECT access can allow access to passwords if FEDERATED storage engine is used. This matter is made even worse if the passwords are stored in plain text.
More can be found at http://dev.mysql.com/doc/refman/5.0/en/grant.html

Revoking user privileges

The format of the REVOKE command is similar to GRANT:

REVOKE privilege ON database FROM user@host;

Droppign a user

This is a little different.. use the DELETE command instead.

DELETE FROM user WHERE (user="username");
DELETE FROM user WHERE (user='user1' OR user='user2' OR user='user3' ...);

Getting a list of users

You'll want to save these commands as a script to make it easier to send the output of mysql to a file. This is because the output stream can be quite long when using \G instead of a semicolon or \g to terminate a command.
You could use a semicolon to terminate teh select command, but the output is very hard to read without using the most pager.
To get a list of all users:

use mysql;
select * from user\G
quit;

To get a list of all users by database:

use mysql;
select * from db\G
quit;

To get a list of all users for just one database:

SELECT * FROM mysql.db WHERE (Db='database_name')\G
quit;

then run mysql with (assuming you saved the script as script.sql):

mysql -u root -p <script.sql >outfile

You could also use grep just to list the user names:

mysql -u root -p <script.sql|grep "User: "