MySQL DBA II Revision

This article contains some rather random notes/information recorded while revising for the MySQL DBA II exam, these notes do not cover all topics in the exam.

Procedures

I used the world database to learn/practice procedures. I created a user ‘lame’@’localhost’ that had no access to the database, so that I could grant selective access via procedures.

  1. Create procedure
    mysql> create procedure citycount() select count(*) from world.City;
  2. Check default security type, in this case it is definer, so the lame user will only need to be granted privileges for this function, not underlying databases and tables.
    mysql> select routine_name, routine_definition, security_type from information_schema.routines\G
    *************************** 1. row ***************************
     routine_name: citycount
    routine_definition: select count(*) from world.City
     security_type: DEFINER
    1 row in set (0.00 sec)
  3. Show that lame user can’t access table directly:
    mysql> select count(*) from world.City;
    ERROR 1142 (42000): SELECT command denied to user 'lame'@'localhost' for table 'City'
  4. Grant Access to procedure
    mysql> grant execute on procedure world.citycount to 'lame'@'localhost';
    Query OK, 0 rows affected (0.00 sec)
  5. Check Privilege
    mysql> select * from mysql.procs_priv where user='lame'\G
    *************************** 1. row ***************************
     Host: localhost
     Db: world
     User: lame
    Routine_name: citycount
    Routine_type: PROCEDURE
     Grantor: [email protected]
     Proc_priv: Execute
     Timestamp: 2012-09-15 13:07:55
    1 row in set (0.00 sec)
  6. Test as lame user:
    mysql> call world.citycount;
    +----------+
    | count(*) |
    +----------+
    | 4079     |
    +----------+
    1 row in set (0.00 sec)
    Query OK, 0 rows affected (0.00 sec)
  7. Alter function to use invoker security context
    mysql> alter procedure citycount sql security invoker;
    Query OK, 0 rows affected (0.00 sec)
  8. Test as lame user
    mysql> call world.citycount;
    ERROR 1142 (42000): SELECT command denied to user 'lame'@'localhost' for table 'City'

    Access is deined to the underlying table, not the function.

Privileges in MySQL

PrivilegeOperations Allowed
create temporary tablecreate temporary tables
create usercreate/modify users
fileUse "into outfile" and "load data infile"
lock tablesallow tables to be explicitly locked
processview running threads - show process list
reloaduse flush and reset
replication clientget information about replication
replication slaveact as a slave
show databaseslist all databases
shutdownshutdown the server daemon
superchange master to, kill, purge binary logs, set global, modify logging, update when read_only is set.
altermodify table structure
alter routinemodify/delete stored proc/function
createcreate databases and tables
create routingcreate stored routine
create viewcreate view
deleteremove rows from tables
dropdelete databases and tables
executecall stored functions/procedures
grant optiongrant held privileges to other accounts
indexcreate/delete indexes
insertadd rows to tables
selectretrieve rows from tables
show viewuse show create on views
updatemodify records
usageno privilege - except for being able to connect to the server.
allalias for all privileges except grant option

Grant Tables (in the “mysql” database):

  • user – stores usernames, credentials and global privs
  • db – db level privs
  • tables_priv – table level privs
  • columns_priv – column level privs
  • procs_priv – stored procedure privs.

Granting access to a column

  1. Grant privilege
    mysql> grant select (Name) on City to 'lame'@'localhost';
    Query OK, 0 rows affected (0.01 sec)
  2. Check the privilege just created
    mysql> select * from mysql.columns_priv where user='lame'\G
    *************************** 1. row ***************************
     Host: localhost
     Db: world
     User: lame
     Table_name: City
    Column_name: Name
     Timestamp: 2012-09-15 14:29:40
    Column_priv: Select
    1 row in set (0.00 sec)
  3. The lame user can now “see” the database world, and one table within it (although it actually contains 3 tables).
    mysql> show tables from world;
    +-----------------+
    | Tables_in_world |
    +-----------------+
    | City            |
    +-----------------+
    1 row in set (0.00 sec)
  4. The lame user sees the City table as having one column
    mysql> describe world.City\G
    *************************** 1. row ***************************
     Field: Name
     Type: char(35)
     Null: NO
     Key: 
    Default: 
     Extra: 
    1 row in set (0.00 sec)
  5. Counting the column to which access was granted works, but count(*) returns 1, not sure why that is.
    mysql> select count(Name) from City;
    +-------------+
    | count(Name) |
    +-------------+
    |        4079 |
    +-------------+
    mysql> select count(*) City;
    +------+
    | City |
    +------+
    |    1 |
    +------+
    1 row in set (0.00 sec)

Modifying the mysql.users table directly

It is important to modify users via create, drop, grant, revoke, rename instead of updating the users table. This is because the user may have priviliges in the other grant tables, that won’t be updated if you rename/modify a user directly in the mysql.users table. For example:

The lame user has access to the Name column from world.City:

mysql> select * from mysql.columns_priv where user='lame'\G
*************************** 1. row ***************************
 Host: localhost
 Db: world
 User: lame
 Table_name: City
Column_name: Name
 Timestamp: 2012-09-15 14:29:40
Column_priv: Select
1 row in set (0.00 sec)

If we rename the lame user via mysql.user, this privilege is not updated, so no longer applies.

mysql> update mysql.user set user='lame2' where user='lame';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from mysql.columns_priv where user='lame'\G
*************************** 1. row ***************************
 Host: localhost
 Db: world
 User: lame
 Table_name: City
Column_name: Name
 Timestamp: 2012-09-15 14:53:08
Column_priv: Select
1 row in set (0.00 sec)

However if we rename the user via rename, all relevant grant tables are updated:

mysql> rename user 'lame'@'localhost' to 'lame2'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mysql.columns_priv where user like 'lame%'\G
*************************** 1. row ***************************
       Host: localhost
         Db: world
       User: lame2
 Table_name: City
Column_name: Name
  Timestamp: 2012-09-15 14:55:05
Column_priv: Select
1 row in set (0.00 sec)

Change a user’s password

mysql> set password for [email protected] = password('lame');
Query OK, 0 rows affected (0.00 sec)

This is the proper way of doing it, but is the same as:

mysql> update mysql.user set password=password('lame') where user='lame';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Setting resource limits

To set a limit for a user, grant usage on all with a modifier:

mysql> grant usage on *.* to [email protected] with max_queries_per_hour 2;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for [email protected]\G
*************************** 1. row ***************************
Grants for [email protected]: GRANT USAGE ON *.* TO 'lame'@'localhost' IDENTIFIED BY PASSWORD '*C8D3D3868B8E328F1CB52AE5D1A094B7B29084DB' WITH MAX_QUERIES_PER_HOUR 2
*************************** 2. row ***************************
Grants for [email protected]: GRANT CREATE TEMPORARY TABLES ON `world`.* TO 'lame'@'localhost'
*************************** 3. row ***************************
Grants for [email protected]: GRANT SELECT (Name) ON `world`.`City` TO 'lame'@'localhost'
*************************** 4. row ***************************
Grants for [email protected]: GRANT EXECUTE ON PROCEDURE `world`.`citycount` TO 'lame'@'localhost'
4 rows in set (0.00 sec)

This limit can be revoked by setting it to 0, the user will then have the default limits, not no limit.

mysql> grant usage on *.* to [email protected] with max_queries_per_hour 0;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for [email protected]\G
*************************** 1. row ***************************
Grants for [email protected]: GRANT USAGE ON *.* TO 'lame'@'localhost' IDENTIFIED BY PASSWORD '*C8D3D3868B8E328F1CB52AE5D1A094B7B29084DB'
*************************** 2. row ***************************
Grants for [email protected]: GRANT CREATE TEMPORARY TABLES ON `world`.* TO 'lame'@'localhost'
*************************** 3. row ***************************
Grants for [email protected]: GRANT SELECT (Name) ON `world`.`City` TO 'lame'@'localhost'
*************************** 4. row ***************************
Grants for [email protected]: GRANT EXECUTE ON PROCEDURE `world`.`citycount` TO 'lame'@'localhost'
4 rows in set (0.00 sec)

Note that all limits are reset when limits are modified.

Types of resource limit that are available:

  • max_queries_per_hour
  • max_updates_per_hour
  • max_connections_per_hour
  • max_user_connections (simultaneous)

Functions for determining the current user

  • user() – returns the user specified when connecting to the server
  • current_user() – returns the user that the server is using for this session

For example, if the anonymous user is enabled, you can end up with this situation:

[[email protected] ~]$ mysql -u nonexistentuser -e 'select user(), current_user();'
+---------------------------+----------------+
| user()                    | current_user() |
+---------------------------+----------------+
| [email protected] | @localhost     |
+---------------------------+----------------+

Implicit user creation by GRANT

Grant will create a user if the user specified doesn’t exist. Therefore mistakes in granting privileges can lead to users without passwords being created in error. This behaviour can be disabled using the NO_AUTO_CREATE_USER sql mode.

mysql> set global sql_mode='NO_AUTO_CREATE_USER';
Query OK, 0 rows affected (0.00 sec)

Disconnect/Reconnect, then..

mysql> grant all on *.* to [email protected];
ERROR 1133 (42000): Can't find any matching row in the user table

Yes MySQL thats how you should behave.

Creating an index

mysql> create index CountryCode on world.City (CountryCode);

-OR-

mysql> alter table world.City add index CountryCode (CountryCode);
Query OK, 4079 rows affected (0.02 sec)
Records: 4079 Duplicates: 0 Warnings: 0
Check the index
mysql> show indexes from world.City where Column_name='CountryCode'\G
*************************** 1. row ***************************
 Table: City
 Non_unique: 1
 Key_name: CountryCode
Seq_in_index: 1
 Column_name: CountryCode
 Collation: A
 Cardinality: 226
 Sub_part: NULL
 Packed: NULL
 Null: 
 Index_type: BTREE
 Comment: 
1 row in set (0.00 sec)

Drop the index

mysql> drop index CountryCode on world.City;
Query OK, 4079 rows affected (0.01 sec)
Records: 4079 Duplicates: 0 Warnings: 0

Creating a table with the same structure as an existing table

Using a select to create a new table doesn’t copy keys/indexes.

Table to copy:

mysql> describe log\G
*************************** 1. row ***************************
 Field: id
 Type: int(11)
 Null: NO
 Key: PRI
Default: NULL
 Extra: auto_increment
*************************** 2. row ***************************
 Field: ts
 Type: timestamp
 Null: NO
 Key: 
Default: CURRENT_TIMESTAMP
 Extra: on update CURRENT_TIMESTAMP
*************************** 3. row ***************************
 Field: message
 Type: text
 Null: YES
 Key: 
Default: NULL
 Extra: 
3 rows in set (0.00 sec)

Create copy:

mysql> create table log_3 as select * from log;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

New structure doesn’t match:

mysql> describe log_3\G
*************************** 1. row ***************************
 Field: id
 Type: int(11)
 Null: NO
 Key: 
Default: 0
 Extra: 
*************************** 2. row ***************************
 Field: ts
 Type: timestamp
 Null: NO
 Key: 
Default: 0000-00-00 00:00:00
 Extra: 
*************************** 3. row ***************************
 Field: message
 Type: text
 Null: YES
 Key: 
Default: NULL
 Extra: 
3 rows in set (0.00 sec)

A better way is to create using like, then copy the data:

mysql> create table log_3 like log;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into log_3 select * from log;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
Now the structure matches:
mysql> describe log_3 \G
*************************** 1. row ***************************
 Field: id
 Type: int(11)
 Null: NO
 Key: PRI
Default: NULL
 Extra: auto_increment
*************************** 2. row ***************************
 Field: ts
 Type: timestamp
 Null: NO
 Key: 
Default: CURRENT_TIMESTAMP
 Extra: on update CURRENT_TIMESTAMP
*************************** 3. row ***************************
 Field: message
 Type: text
 Null: YES
 Key: 
Default: NULL
 Extra: 
3 rows in set (0.00 sec)