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