MySQL
"It is difficult to know yourself if you do not know others"
-Miyamoto Musashi, A Book of Five Rings: Ground Book 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
installation:
set up grant tables with "mysql_install_db", shouldn't be needed if mysql was installed via RPMs.
running mysql_install_db again won't hurt anything. when run initially it sets up the data directory, privileges database, and test database

building from source:
groupadd mysql
useradd -g mysql mysql

compiling
CLFAGS="-O3"
CXX=gcc
CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti"

./configure --prefix=/usr/local/mysql --enable-assembler --with-mysqld-ldflags=-all-static
make
make install

in /usr/local/mysql/
chown -R root .
chown -R mysql var
chgrp -R mysql .

cleaning out previous configs: make distclean

my.ini on windows
my.cnf on linux

my.small.cnf my.medium.cnf....

if upgrading mysql...update the grant tables by: ./scripts/mysql_fix_privilege_tables
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
directories:
/usr/bin - most mysql programs and scripts
/usr/sbin - location of mysqld
/var/lib/mysql - databases, logs


mysql run options:
-V = display version of mysql

starting mysql:
mysqld_safe &, mysqld_safe will auto-restart the daemon if a failure occurs.

mysqladmin command:
mysqladmin -u root drop test, remove test database (untested)
-u root password <new password>, change password for root user (or -p password)
-p ping, test to see if mysqld is up.
-p shutdown, shutdown mysqld
-p version, display mysql version

mysqlshow command:
+----------------------------+
|     Databases              |
+--------------------------- +
| information_schema |
| eric                               |
| mysql                           |
| test                               |
+---------------------------+

permissions:
select - read data in tables
insert - add data to ables
update - modify data in tables
delete - remove data in tables
show databases - list databases
index - index modification
alter - modifying tables
create - create tables/databases
create temporary tables - as name implies
drop - delete tables/databases
create view - as name implies
show view - allows use of a view
alter routine - modify stored procedure
create routine - create stored procedure
execute - run stored procedure
lock tables - as name implies
replication client - show replication status
replication slave - perform replication

reload - reload grant tables (update permissions)
shutdown - shutdown mysql server
file - import data via files
grant option - create new user with same permissions
create user - as name implies
revoke - remove permissions
process - show current processes
super - terminate processes

design:
name primary key/foreign key columns .._id

mysql command line (mysql -p <optional database>):
-h = hostname

storage engines:
show engines; - displays storage engines available

character sets:
show character set; - displays available character sets, default is latin1
show collation like 'latin1%'; - displays possible collations for specified character set

database creation:
show databases;
or: show create database <database>; - displays information regarding database creation

create database <database>;
or: create database <database> character set <set> collate <collation>;
use <database>;

delete database:
drop database <database>;

importing (as root):
mysql existing_database < filename.sql

show tables;

create table mytable
(
        fname char(15) ,
        lname char(15) not null
);

insert into mytable
(mycolumn1, mycolumn2)
values ("somevalue1", "somevalue2");

show columns from mytable;

drop table mytable;

passwords:
set password for 'username'@'hostname' = password('password')
or
set password = password('password')

permissions/user mgt:
grant <privileges - SELECT, DELETE, etc> on database.* to 'username'@'hostname' identified by 'password'
revoke is the opposite of grant
database.table or *.* = all
host wildcard = %, 192.168.5.%, eric.net)
identified by password can be left off

show grants for 'username'@'hostname'; - show permissions for user
show user, host, password from mysql.user; - lists current users
drop user username - delete user
drop user ''@'hostname'; - delete anonymous user

indexes:
index when theres lots of use of where, joib, order by

create index myindex
on mytable (mycolumn);

drop index myindex
on mytable;

queries:
select mycolumn1, mycolumn2  from mytable;
select mycolumn from mytable order by mycolumn;
select * from mytable;

data types:
types of characters
date - YYYY-MM-DD
datetime - YYYY-MM-DD HH:MM:SS
timestamp - YYYYMMDDHHMMSS
time - HH:MM:SS
year - YYYY
tinytext, 255 characters
text, 65535 characters
mediumtext, 16 million characters
char(length), known length
varchar, up to 255 chars
binary, binary data
varbinary
tinyblob - binary data up to 255 bytes
blob - binary data up to 65K
mediumblob - binary data up to 16MB
longblob - binary data up to 4GB
tinyint, 255
smallint, 65535
int, whole numbers
float(length, decimals)
double
decimal - double w/ fixed decimal point
numeric (precision, scale), precision = # of digits, scale = # of digits behind decimal point
enum('1', '2') default '1'
 
not null - blank values will not be accepted
auto-increment - as name implies
unsigned - non-negative numbers
default
zerofill - extra space will be padded w/ zeroes

troubleshooting:
"can't create database 'name' (errno: 28)"
no disk space available to create new database
in this case logs had filled up /var

"too many connections"
go to a mysql prompt and enter:
show processlist;
processes can be killed by kill PID;
show variables;

in my.cnf:
under [mysqld]
max_connections=500 (default is 100)
interactive_timeout=7200
wait_timeout=7200
log_error=/var/lib/mysql/mysql.err

security:
disable remote root access
disable anonymous access

mysql on windows:
mysql subfolders
bin = apps
data = databases and logs
docs = docs
examples = samples
include = header files
lib = libraries
scripts = scripts
share = error msgs
sql-bench = benchmark utils

config: my.ini in windows