Mysql

packages:
	mysql-server

daemons:
	mysqld

configs:
	/etc/my.cnf - main config file
	/var/lib/mysql/mysql.sock - socket
	/var/lib/mysql/ - data/log directory (centos)
	~/.mysql_history - command history log

ports:
	tcp 3306

other:
	\ - escape char
	* - wild
	% - wild in "like" string matching
	0x'1234' - hex literal
	0b'1010' - bit literal

commands:
	mysql - opens a mysql shell
	mysql -u <user> -p - open shell as user and prompt for password
		-h <host> - specify host
	mysql <database> < script.sql - execute script
	/usr/bin/mysql_secure_installation v- wizard for implementing secure mysql (say yes to all for most secure)

backing up:
	mysqldump -p --all-databases - dumps sql scripts to stdout
	mysqldump -p <database> - dumps a specific database
	mysqldump -p --databases <database> <database> - dumps multiple databases
	mysqldump -p <database> <table> <table> - dumps specific tables in a database
		--opt - optimize for reading data later
		--no-data - only dump table structures

	mysqlhotcopy -u <user> -p <password> <database> /path/to/dir/ - most efficient backup method. username/password required. directory must exist.

remote administration:
	- on server, edit /etc/my.cnf and add line 'bind-address=<local-server-addr>'
	- in /etc/my.cnf make sure line 'skip-networking' is commented out with #
	- restart mysqld daemon
	- enter the mysql shell and the main database with 'mysql -p mysql'
	- type: update db set Host='<remote-client-addr>' where Db='<database>';
	- type: update user set Host='<remote-client-addr>' where user='<user>';
	- open up tcp port 3306 on the firewall
	- connect using 'mysql -u <user> -h <server-addr> -p

mysql shell commands:
	create user <user>@<host>; - create new user
	set password for 'root'@'localhost' = md5('<password>'); - sets password for root
	drop user <user>@<host>; - delete user
	grant <privilege> on <component> to <user>; - grant privileges to user
		privileges: all privileges, file, reload, alter, index, select, create, insert, shutdown, delete, process, update, drop, references, usage
		components: table_name, *, *.*, db_name.*
		users: username, username@host
	revoke <privilege> on <component> from <user>; - revokes priveleges
	show grants for <user>@<host>; - shows user's priveleges
	select user(); - show current user

	create database <database>;
	use <database>; - work with specified database
	show databases;
	show tables from <database>;
	show columns from <table>;
	select version();
	select now(); - current date and time
	select database(); - show current database

	delete from <table> where <table>.<column> = '<string>'; - delete a record
	delete from <table> where 1=1; - clear a table
	drop table <table>; - delete a table
	drop database <database>; - delete a database

	show global variables;
	show global variables like "max\_%"; - shows global variables starting with "max_"
	set @<variable> = <value>; - define variables for shell session. vars start with '@'. can use '=' or ':='.
	select @<variable> - cannot use '=' in select statement, must use ':='.

default tables:
	mysql - contains privilege tables
	information_schema - contains environment variables

tips:
	to cancel a multi-line command, type \c

constraints:
	zerofill - pads int types with 0s. zerofill must be the first constraint. zerofill automatically implies "unsigned"
	auto_increment - used for numbers.
	default 'value' - specifies a default value to be inserted if nothing else is inserted.
	primary key
	unique
	not null

references:
	primary key (id)
	foreign key (name1, name2) references table(nameA, nameB) on update cascade on delete restrict

example scripts:

create table web_user(
id		int(6)		not null primary key auto_increment,
username	varchar(20)	not null unique,
password	varchar(100)	not null,
email		varchar(100)	null unique,
phone		numeric(10)	null
);

insert into web_user(username,password,email,phone) values('bob',password('password'),'bob@project.com','3605551234');
insert into web_user(username,password,email,phone) values('tim',password('herp'),'tim@project.com','3605552345');




CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
                      price DECIMAL,
                      PRIMARY KEY(category, id)) ENGINE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
                       PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
                            product_category INT NOT NULL,
                            product_id INT NOT NULL,
                            customer_id INT NOT NULL,
                            PRIMARY KEY(no),
                            INDEX (product_category, product_id),
                            FOREIGN KEY (product_category, product_id)
                              REFERENCES product(category, id)
                              ON UPDATE CASCADE ON DELETE RESTRICT,
                            INDEX (customer_id),
                            FOREIGN KEY (customer_id)
                              REFERENCES customer(id)) ENGINE=INNODB;