Peter's MySQL Notes

In the following examples, there is a database named foo with a table named bar.

Case Sensitivity

• Not case insensitive: Keywords
• Case insensitive: Database names, table names, data

Administration and Monitor Commands

Log in to monitor	mysql -h hostname -u user -p
Create database	create database foo;
List databases	show databases;
Switch databases	use foo;
List all tables in foo	show tables;
Describe table fields	describe bar;
Describe table fields	show columns from bar;
Delete a database	drop database foo;
Delete a table	drop table bar;

SQL

Show all data in table	SELECT * FROM bar;
Show selected rows	SELECT * FROM bar WHERE field='blah';
Show selected rows with two conditions	SELECT * FROM bar WHERE field1='blah1' AND field2='blah2';

Examples

Listing All Databases

   mysql> show databases;
   +----------+
   | Database |
   +----------+
   | mysql    |    <--- The administrative database.  Comes with MySQL.
   | slash    |
   | test     |    <--- Test database.  Comes with MySQL.
   +----------+

Selecting A Database

   mysql< use test
   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

Create A Database

   mysql> CREATE DATABASE foo;
   Query OK, 1 row affected (0.07 sec)

Listing Tables

You must first select a database with the use command.

   mysql> use mysql
   Database changed
   mysql> show tables;
   +-----------------+
   | Tables_in_mysql |
   +-----------------+
   | columns_priv    |
   | db              |
   | func            |
   | host            |
   | tables_priv     |
   | user            |
   +-----------------+

Accounts

A MySQL account is comprised of 3 parts: user name, password, and what host the account can log in from. MySQL comes with three factory default accounts:

• User "root" has no password, but must log in from "localhost".
• Two anonymous accounts with a null login and no password. Can do anything with db's whose name starts with "test" or "test_" from "localhost".

MySQL user/passwords are distinct from system login/passwords. When you run "mysql" without -u and -h, your MySQL login is the same as your user name, and your MySQL host is the same as your system hostname. This is only done as a convenience. Here's how to find out your current MySQL user and current date:

   mysql> select user(), current_date();
   +----------------+----------------+
   | user()         | current_date() |
   +----------------+----------------+
   | p@localhost    | 2004-04-09     |
   +----------------+----------------+

Confusingly, it's possible to be logged in to MySQL as "p@localhost" even when "p@localhost" is not a valid MySQL username/host combo.

You can list all users by selecting from the mysql database:

    mysql> select user,host from user;
    +------------------+-----------+
    | user             | host      |
    +------------------+-----------+
    | root             | gabriel   |
    | debian-sys-maint | localhost |
    | root             | localhost |
    | slash            | localhost |
    +------------------+-----------+

Logging In

You can log in as user "jdoe", password "secret", and use database "foo": between -p and password). Note there is no space between -p and the password:

   mysql -u jdoe -psecret menagerie
   mysql --user=jdoe --password=secret foo

Or like this to be prompted for the password:

   mysql -u jdoe -p foo
   mysql --user=jdoe --password foo

Setting A Password

The "PASSWORD()" function encrypts a password. When you change passwords or permissions, you should always use "FLUSH PRIVILEGES" to re-read the grant tables. There are three ways to set a password:

1. Using mysqladmin:

        $ mysqladmin -u jdoe -h myhost -p "secret"
   	
   

2. Using "SET PASSWORD":

        $ mysql -u root mysql
        mysql> SET PASSWORD FOR 'monty'@'%' = PASSWORD('mypassword');
        mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd');
   	

3. Modifying mysql's "user" table table directly:

        $ mysql -u root mysql
        mysql> UPDATE user SET Password = PASSWORD('mypassword')
        > WHERE User = 'monty';
        > FLUSH PRIVILEGES;
   	

Creating New Accounts

You create MySQL users by simply granting permissions to a user which doesn't exist yet, so see "Permissions" on how to create a new user.

Deleting An Account

Delete accounts by deleting that user's record in the mysql.user table:

	$ mysql -u root mysql
	mysql> DELETE FROM user WHERE User = 'jdoe';
	mysql> FLUSH PRIVILEGES;

Permissions

You can grant all permissions to jdoe for a given database by:

	GRANT ALL ON foo.* TO 'jdoe'@'localost';

This makes jdoe@localhost a valid MySQL username/host combo, if it's not currently a defined combo. Here's another example that uses specific types of grants:

	mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX
		-> ON foo.*
		-> TO jdoe@localhost;

Lost Root Password

You want to stop the daemon, run it in some kind of safe mode that appears to do away with permissions and then alter mysql's user table directly:

     # /etc/init.d/mysql stop
     # mysqld_safe --skip-grant-tables & 
     # mysql -uroot mysql
     mysql> UPDATE user SET password=PASSWORD("abcd") WHERE user="root";
     mysql> FLUSH PRIVILEGES;

Tips

• phpMyAdmin is a very highly recommended way of doing admin things with MySQL.