Peter's MySQL Notes

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

Case Sensitivity

Administration and Monitor Commands

Log in to monitormysql -h hostname -u user -p
Create database create database foo;
List databases show databases;
Switch databases use foo;
List all tables in fooshow 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:

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