mysql Chapter Five (Administration)

Documentation Version: 0.80
mysql Version: 3.20.16

Granting Access to a Database

The mysql database system uses a special database to grant access privileges to it's databases. These privileges can be based on hosts and/or users, and can be granted to a single database or many.

 User accounts are passworded. When connecting to the database the password is encrypted to assure that it can not be intercepted and used by others.

 The mysql database contains three tables. They are defined below.

 Database: mysql Table: db
Field Type Null Key Default Extra
Host char(60) PRI
Db char(32) PRI
User char(16) PRI
Select_priv char(1) N
Insert_priv char(1) N
Update_priv char(1) N
Delete_priv char(1) N
Create_priv char(1) N
Drop_priv char(1) N
File_priv char(1) N

Database: mysql Table: host
Field Type Null Key Default Extra
Host char(60) PRI
Db char(32) PRI
Select_priv char(1) N
Insert_priv char(1) N
Update_priv char(1) N
Delete_priv char(1) N
Create_priv char(1) N
Drop_priv char(1) N

Database: mysql Table: user
Field Type Null Key Default Extra
Host char(60) PRI
User char(16) PRI
Password char(8)
Select_priv char(1) N
Insert_priv char(1) N
Update_priv char(1) N
Delete_priv char(1) N
Create_priv char(1) N
Drop_priv char(1) N
Reload_priv char(1) N
Shutdown_priv char(1) N
Process_priv char(1) N
File_priv char(1) N

The following is an example of adding new users.
 
 
$ mysql mysql

mysql> INSERT INTO user VALUES ('%','monty',password('something'),
    -> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user (host,user,password) values('localhost','dummy','') ;
mysql> INSERT INTO user VALUES ('%','admin','','N','N','N','N','N','N','Y','N','Y','N') ;
mysql> quit

$ mysqladmin reload

This makes three new users:
 
monty:  Full superuser, but must use password when using mysql. 
 
dummy:  Must be granted individual database privileges through table 'db'. 
 
admin:  Doesn't need a password but is only allowed to use 'mysqladmin reload' and 'mysqladmin processlist'. May be granted individual database privileges through the 'db' table. 
 

NOTE: You MUST use the password() function when creating a user that has a password. The mysql database system expects passwords to be stored encrypted.

 Attribute assigned in the user table over-ride attribute assigned to a user in a given DB. If you are paranoid or have a server that provides multiple databases you probably want to create users in the user account with no privileges, and then assign appropriate privileges on a database by database basis.
 
 

How passwords work

The scrambled password is stored in the mysql 'user' table.

Compile Time Options

There are several values that can be "tweaked" at compile time to modify the behavior of mysql.

 (Need to document these -mke) 

Tuning mysqld

When you issue the -O option by itself you will be given a list of all tunable variables and their current values. This should look similar to the following.
$ mysqld -O

Possible variables to option -O are:
keybuffer             current value: 1048568
max_allowed_packet    current value: 65536
net_buffer_length     current value: 16384
max_connections       current value: 128
table_cache           current value: 64
recordbuffer          current value: 524280
sortbuffer            current value: 2097144

The following table explains a bit about each of the above listed values and gives some hints on how you might want to tweak them for better performance.
 
 
keybuffer Buffer to hold all recently used keys. A big buffer gives the best performance. With the -Sl switch only one buffer is allocated.

 

max_allowed_packet The server connection buffer may be resized up to this if the client gives long commands. The initial buffer is 'net_buffer_length'. One buffer is allocated per connection.

 

net_buffer_length Initial size of the connection buffer. One buffer is allocated per connection.

 

max_connections The maximum number of connections that mysqld can have open at the same time. 
table_cache Maximum number of tables kept open on the server. Tables are kept open to give better query speed on frequently used tables.

 

recordbuffer Buffer used to read records sequentially. One buffer will be allocated per connection.

 

sortbuffer Buffer used when sorting. One buffer will be allocated per connection.
 
 

Things to Know

Query Optimization

What optimization is done on the WHERE clause?

Here's a partial list:
 
 
const_table.key = constant


 const_table.key_part_1 = const_table2.field and
const_table.key_part_2 = constant

  • Find the best join combination to join tables (By trying all possibilities :( )
  • For each table use a ranged key if possibly to read records. Each table index is queried and if there exists a key range that spans < 30% of the records then a key is used, else a quick table scan is used.
  • Before each record is output, skip those that match the HAVING clause.
  • Optimizing Table Space Usage

    There are several things you can do to minimize the amount of space your tables take. First of all, use NOT NULL whenever possible. This will speed up queries, and save 1 bit per field.

     You can save a lot of space by using the smaller integer values. For instance, MEDIUMINT is often sufficient.

     In general you should choose the smallest data-type you will need if you are concerned with space.

    The Client/Server Protocol

    Key:
     
     
    < = client 
    > = server
    [] is one byte
    [2: ] is two bytes packed low byte first
    [3: ] is three bytes packed low byte first
    [4: ] is four bytes packed low byte first
    [string: ] is a null byte terminated string
    [length: ] This is a length coded integer. Check
               net_store_length() for this one!
               The main idea is to get most frequent length bytes in 1
               byte but to allow longer data (and NULL)
    The Gory Details

    Every sent and received packet has the following prefix.

     [3: packet length] [packet number] packet data

     The packet reader adds a [0] after each received package (to get easy handling of error strings)

     The packet number starts from 0 and increments for each send/receive packet. Its a char (modulo 255)
     
     

    Packet Types

    :connect

    > [protocol version] [string: password crypt seed]

     < [2: 0] [3: max_allowed_client_packet_length] [string: user name]

     [string: scrambled password]

     > ok packet

     Check the file password.c from the mysql source release for the method used to encrypt a password. Password should be empty if the user has no password.
     
     

    :ok

    [0] [length: affected_rows] [length: unique id]
     
     

    :error

    (May come at any time)

     > [255] [string: error message]
     
     

    :command

    < [command number]

     enum enum_server_command {SLEEP,QUIT,INIT_DB,QUERY,FIELD_LIST, CREATE_DB,DROP_DB,RELOAD,SHUTDOWN,STATISTICS, PROCESS_INFO,CONNECT}
     
     

    :query

    < [QUERY command] [string: query string] (The end null is not sent)

     > [0] [length: affected rows] [length: insert id] (Insert, delete...)

     or

     > [length: column_count] (a query result)

     > column field data packets

     > row data packets
     
     

    :data

    : This is done repeatedly until a packet which only consist of a single [254] is found. Beware there may be packets which starts with 254. Each data packet has 'column count' fields.

     Format for each field:

     [:length] == NULL_LENGTH -> NULL field

     or

     [:length] [length data] (Not null terminated strings!)

     The column data package consists of 5 columns with the following data:

     [:string table name]
    [:string column name]
    [:3 create length of column (may be larger in a few cases)]
    [:1 type (as of enum_field_types)]
    [:1 flag] [:1 decimals] (a 2 byte binary column!)

     When using the command list_fields there is a sixth column:
    [:string default]

     Look at libmysql.c, net.c and password.c for more information.
     
     

    Table Details

    To get a description of a a table use the following command. (You'll have to be cd'd to the directory that contains the table you wish to look at. Generally this will be $DATADIR/[dbname], where dbname is the name of the database that contains the table.)
     
     
    prompt> isamchk -d table_name
    
    ISAM file:     table_name
    Data records:               215  Deleted blocks:              0
    Recordlength:                85
    Record format: Packed
    
    table description:
    Key Start Len Index   Type
    1   37    14  unique  text packed stripped 
    2   1     30  multip. text packed stripped 
    3   51    4   multip. long                 
    4   31    2   multip. short                
        33    2           short                   
        35    2           short                   
    
    For a more verbose description of the table try the following options.  (Explanations 
    follow.)
    
    isamchk -d -v table_name 
    
    ISAM file:     Customer
    Isam-version:  2
    Creation time: 1996-12-17 21:40:04
    Data records:                 0  Deleted blocks:              0
    Datafile: Parts:              0  Deleted data:                0
    Datafilepointer (bytes):      4  Keyfile pointer (bytes):     3
    Recordlength:               374
    Record format: Packed
    
    table description:
    Key Start Len Index   Type                       Root  Blocksize    Rec/key
    1   2     2   unique  short                        -1       1024          1
    2   4     80  multip. text packed stripped         -1       1024          1
    3   2     2   multip. short                        -1       1024          1
    
    
    Explanation: (As the example above shows, you won't always see all of these.)
     
     
    ISAM file  Name of the ISAM file. 
    Isam-version  Version of ISAM format. Currently always 2. 
    Creation time  When was the data file created? 
    Recover time  When was the index/data file last reconstructed? 
    Data records  How many records/rows. 
    Deleted blocks  How many deleted blocks still have reserved space? (See the examples in section on isamchk above for information on getting rid of the unused space.)
    Datafile: Parts  For the dynamic record format this shows how many data blocks there are. For a just optimized table without splits this will be the same as Data records. 
    Deleted data  Total bytes taken up by non reclaimed deleted data. 
    Datafilepointer How many bytes is the datafile pointer? This is generally 2, 3 or 4 bytes. Most tables manage with 2 bytes but this cannot be controlled from mysql yet. For fixed tables this is a record address. For dynamic tables this is a byte address. 
    Keyfile pointer  How many bytes is the datafile pointer? This is usually 1, 2 or 3 bytes. Most tables manage with 2 bytes but this is calculated automatically by mysql. This is always a block address. 
    Max datafile length  How long in bytes can the data file (.ISD) for this table get?
    Max keyfile length  How long in bytes can the key file (.ISM) for this table get?
    Recordlength  How much space does each record/row take? 
    Record format  What format does each record/row have?
    table description  A list of all keys in the table. For each key some low level information is presented. (More information in next table.)
    Key This keys number. 
    Start Where in the record/row does this index-part start. 
    Len How long is this index-part? For packed numbers this should always be the full length of the field. For strings it may be shorter than the full length (not supported from mysql yet). 
    Index  unique or multip.. Can one value exist multiple times in this index> 
    Type  What data-type does this index part have? This is a C data-type with a optional packed or stripped. 
    Root  Address of the root index block. 
    Blocksize  The size of each index block. By default this is 1024, but it may be changed at compile time. 
    Rec/key  This is a statistical value used by the optimizer. It tells how many records there are per value for this key. A unique key always has a value of 1. This may be updated after a table is loaded and somewhat stable by using isamchk -a. If you do not do an update, a default value of 30 is used. 

    The mysql FAQ has more extensive information on this topic.