• RawNotes
• mysql

Author

Christopher Marshall (christopherlmarshall@yahoo.com)

Raw Notes on MySQL

# first time db setup
# you have to run mysql_install_db as user mysql or certain tables will be created as 
# owned by root and then the mysql demon won't be able to write to them.  You then have 
# to delete the /var/lib/mysql/mysql directory and run mysql_install_db script again 
# as user mysql to regenerate them.
su
su mysql
mysql_install_db
exit

# here is how you normally start mysqld
mysqld_safe --user=mysql &
#The command used to be safe_mysqld but that was deprecated

# shutdown
mysqladmin -p shutdown

# set the root password
mysqladmin -u root password 'newpassword'

# this runs the client and connects to mysqld as user 'user'
# initially only the root user will be allowed
mysql -h host -u user -p

# some mysql commands, run from within mysql
show databases;
# this creates database names test2 
create database test2;
# this tells us to open it
use test2
# you can also open a database from the mysql command line
mysql -u root -p test2

# table creation
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
   species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
CREATE TABLE t2 (x VARCHAR(20), y VARCHAR(20));
show tables;
# get a list of columns and types
describe pet;
# delete a table
drop table pet;
# here's how to load data manually
insert t2 set x="hi",y="there";
# here's how you could do it by selecting from another table
insert into t2 (x,y) select ...
# here's how you delete rows
delete from t2 where x="hi";

# here we load data into the table from the tab separated file pet.txt.
# note that this won't work unless mysql is invoked with 
# the option --local-infile=1.  There is a security issue involved 
# with mysql where a malicious server could exploit a client to 
# read any file the client machine.
LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;
LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet FIELDS TERMINATED BY ',' ;
# here is how it would look in batch form
mysql -u someuser --password=somepass --local-infile=1 
   --exec "load data local infile \"pet.txt\" into table pet fields terminated by ',';"


# here we ask for a dump of the entire contents of the table
select * from pet;
# the general form is "select <what> from <table> where <condition>"
# create the database names "d1" and give chris@localhost access to it

# examples of user creation, and the granting and revoking of access.
create database d1;

grant select,insert,update,delete,create,drop,index 
on d1.* 
to chris@localhost 
identified by 'somepassword';

grant select,insert,update,delete,create,drop 
on d1.* 
to chris@analysispc.norcom.net
identified by 'somepassword';

revoke select,insert,update,delete,create,drop 
on d1.* 
from chris@somehost;

# table creation example:
# this is perfect for sucking in mdlp_cdr's !
create table t3 (day DATE, x bigint, index index1 (day) );
load data local infile "tmp.txt"  into table t3 fields terminated by ',';
# tmp.txt could look like this:
# 2001-1-1,5
# 2001-1-2,10
# 2001-1-2,15

# indexes
# 
create index ind1 on sometable (x,y,z);
drop index ind1;

# batch use of mysql
mysql -u sniff --password=some_password --exec="use sniff_db; show_tables;"
# this shows how to override the local-infile being turned off from the mysql client in batch mode
mysql -u sniff --local-infile=1 --password=sniff --exec="use sniff_db; load data local infile \"t1.txt\" into table t1 fields terminated by \",\";"

# SQL examples
   # making a copy of a table
   create table t1 select count(*) from cdr_full ;

   # grouping and summing
   select beam,count(bytes) from cdr_full group by beam ;

   # creating a table to hold the results of an analysis of another table
   create table t1 (beam int, cg int, bytes int);
   insert into t1 (beam,cg,bytes) select beam,cg,sum(bytes) from cdr_full group by beam,cg;
   
   # creating new tables through joins
   insert into t1 select t2.x,t3.y from t2,t3 where t2.w==t3.w;
   # using a table alias (t3 is alias for t4)
   insert into t1 select t2.x,t3.y from t2, t3 as t4 where t2.w==t3.w;

   # suppose t1 (x,y) has rows
   1,a
   2,b
   3,c
   # and t2(x,y) has rows
   4,d
   5,e
   # and you want to copy the y column from t2 into t1 where the x's match
   # here is what the update looks like for that:
   update t1,t2 set t1.y=t2.y where t1.x=t2.x ;
   # in Oracle, that statement would be
   update t1 set t1.y=(select y from t2 where t1.x=t2.x) where t1.x in (select x from t2);