MySQL

1. Syntax

On linux the table and column names are case sensitive. This can cause issues with migrating a MySQL database between different servers.

alter table Airline add unique key code (code);
alter table Airline drop key code;

create database dbtest
  DEFAULT CHARACTER SET utf8
  DEFAULT COLLATE utf8_general_ci;
      

mysql> grant all on db_test.* to test_user@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
      

2. innodb_file_per_table

Each table will have its own tablespace instead of using a common tablespace. This is useful if you are low on space - you can drop at table and disk space will be reclaimed. If you are using a common tablespace then its not possible. Usually database servers are allocated space beforehand, so this is more of a developer setup. If you were to do this on production I'm unsure of the performance impacts.

Add "innodb_file_per_table" to my.ini on a line by itself under the mysqld section.

To reclaim space you can run "optimize table table_name". This will rebuild the corresponding ibd file. The table will be locked for the duration of the command. While the command is running, a new temporary table is being created with name prefixed with "#sql-" (e.g. "#sql-50e7_5.ibd"). You can see the size growing on disk, so you can compare this to the original table to get an idea of progress.

+--------------------+----------+----------+-------------------------------------------------------------------+
| Table              | Op       | Msg_type | Msg_text                                                          |
+--------------------+----------+----------+-------------------------------------------------------------------+
| Example.example    | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| Example.example    | optimize | status   | OK                                                                |
+--------------------+----------+----------+-------------------------------------------------------------------+
    

3. Scripting

mysql client password can be communicated by exporting the MYSQL_PWD environment variable.

4. The MySQL Query Cache

Docs at The MySQL Query Cache.

The command "show global status" will give you a snapshot of the server. http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html

Variables to do with the query cache are

Qcache_lowmem_prunes may indicate that your cache is too small.

If a query result is returned from query cache, the server increments the Qcache_hits status variable, not Com_select. If it misses the cache and has to evaluate the select statement, it will increment Com_select.

I calculate the hit percentage as Qcache_hits / (Qcache_hits + Com_select)