Beard: Created page with "

 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) 	~..."

2016-02-29T21:23:13Z

Created page with "<pre> 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) ~..."

New page

<pre>
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;

</pre>

Mysql - Revision history

Beard: Created page with "
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) ~..."