find who is trying to connet some port

http://xubo8118.blog.163.com/blog/static/185552332201182611440897/

Too many connection errors in Mysql

linux prompt : mysqladmin flush-hosts

Mysql Install

1. yum 방식을 이용한 설치
[의존성 패키지]
mysql / mysql-server / mysql-connector-odbc / mysql-devel
yum -y install mysql mysql-server mysql-connector-odbc mysql-devel
-y 옵션은 [yes/no] 선택시 자동으로 yes를 처리하게해주는 옵션
2. 설정파일 복사
# cp /usr/share/mysql/my-huge.cnf /etc/my.cnf
설치되는 서버의 메모리용량에 따라 최적화된 설정파일을 복사해준다. 보통 2G이상의 메모리를 설치하므로 my-huge.cnf를 복사한다.
my-huge.cnf 1~2G
my-large.cnf 512M
my-medium.cnf 128M~ 256M
my-small.cnf 64M 이하
** UTF8 인코딩 셋을 사용하기 위한 설정파일 내용 변경
# vi /etc/my.cnf
[client]
default-character-set = utf8
[mysqld]
init_connect = SET collation_connection = utf8_general_ci
init_connect = SET NAMES utf8
default-character-set = utf8
character-set-server = utf8
collation-server = utf8_general_ci
[mysqldump]
default-character-set=utf8
[mysql]
default-character-set=utf8
3. DB파일 설치경로
/var/lib/mysql/mysql/ 경로아래에 DB파일이 위치한다.
4. 기본 mysql DB 인스톨 및 권한 변경
mysql_install_db && chown -R mysql:mysql /var/lib/mysql/
5. mysql 데몬 실행
ㄱ) 직접 실행
/etc/rc.d/init.d/mysqld start
/etc/rc.d/init.d/mysqld stop
/etc/rc.d/init.d/mysqld restart
ㄴ) 서비스 등록
chkconfig — add mysqld
chkconfig –level 2345 mysqld on
chmod 755 /etc/rc.d/init.d/mysqld
ㄷ) 자동서비스 선택
[root@localhost test]# ntsysv
[*] mysqld
7. 패스워드 변경 및 등록
ㄱ) sql 에서 실행
mysql > update user set password = password(‘비밀번호’) where user = ‘사용자’;
mysql > flush privileges; (* 적용하기)
ㄴ) 명령어 실행
#mysqladmin -u 사용자 password 비밀번호
8. sql script 실행
#mysql -u 사용자 -p 비밀번호 mysql < 파일명.sql

make sequences in mysql

create table mysql_sequences(
seq_name varchar(10) not null,
seq_currval Bigint unsigned not null,
primary key (seq_name)
)engine=MyISAM;

DELIMITER ;;

CREATE FUNCTION nextval()
returns bigint unsigned
modifies sql data
sql security invoker
begin
insert into mysql_sequences
set seq_name='default', seq_currval=(@v_current_value:=1)
on duplicate key
update seq_currval=(@v_current_value:=seq_currval+1);

return @v_current_value;
end ;;

Create Scheduler in Mysql

Repeactable Scheduler

CREATE EVENT daily_ranking
on SCHEDULE EVERY 1 DAY STARTS '2011-05-16 01:00:00' ENDS '2013-04-01 12:59:59'
DO
INSERT INTO SCHEDULER_TEST VALUES (NOW(), 'DONE');


every day on 01:00:00 doing insert in period

Only one time Scheduler 


CREATE EVENT daily_ranking
on SCHEDULE at CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
INSERT INTO SCHEDULER_TEST VALUES (NOW(), 'DONE');




blow line is test and Query



CREATE EVENT daily_ranking
on SCHEDULE EVERY 1 DAY STARTS '2011-05-16 01:00:00' ENDS '2013-04-01 12:59:59'
DO
INSERT INTO SCHEDULER_TEST VALUES (NOW(), 'DONE');


CREATE EVENT daily_ranking_AT4
on SCHEDULE at CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
DO
INSERT INTO SCHEDULER_TEST VALUES (NOW(), 'Test2');


SELECT * FROM INFORMATION_SCHEMA.EVENTS;

SELECT * FROM SCHEDULER_TEST;

SELECT NOW();

SELECT db, name, interval_value, interval_field, status, on_completion, time_zone, execute_at, starts, ends, last_executed, created, modified
from mysql.event;

SELECT event_schema, event_name, interval_value, interval_field, status, on_completion, time_zone, execute_at, starts, ends, last_executed, created, last_altered
from information_schema.events;


show global variables like '%sche%';

set global event_scheduler = 1 ;

process kill in mysql

kill only query from thread
mysql>kill query 1000;

kill query with thread
mysql> kill 1000;

FullText in mysql

Engine = myisam

index = fulltext key fx_article (acolumn, bcolumn)

ex)
select doc_id doc_title, doc_body
from ft_article where match(doc_title, doc_body) against('list' in boolean mode);


ft_min_word_len : minimum length of searching word
ft_stopword_file : engine consider it not word, Skip
ft_max_word_len : maximum length of searching word



in Natural Language Mode
ex) searching by word

select doc_id, doc_title, doc_body
from ft_article
where match(doc_title, doc_body) against('hash key' in natural language mode);


in Boolean Mode

+ : AND
-: Not
nothing : OR

Query Profile in mysql

show variables like 'profiling';

set profiling = 1;

show variables like 'profiling';

select * from employees where emp_no =10001;


show profiles;

show profile cpu for query [query number];

use row_number in Mysql

select emp_no , @i:=@i +1 as result  from employees, (select @i:=0) temp order by emp_no;

ibatis get pk key after insert in mysql

<insert id="user.insertUserAndGetId" parameterClass="user">
    <![CDATA[
        INSERT INTO t
        (
            name
        )
        VALUES
        (
            #name#
        )
    ]]>
    <selectKey keyProperty="id" resultClass="Integer">
        SELECT LAST_INSERT_ID()
    </selectKey>
</insert>



UserVO user = new UserVO();
user.setName("Bob");

System.out.println("Index of user " + user.getName() + " is " + user.getId() + " (before insert)");
int id = ((Integer) sqlMap.insert("user.insertUserAndGetId")).intValue();
System.out.println("Index of user " + user.getName() + " is " + user.getId() + " (after insert)");

Anti join in mysql

create table tab_tset1 (id INT, PRIMARY KEY(id));
create table tab_test2(id INT);
insert into tab_test1 values (1), (2), (3), (4);
insert into tab_test2 values (1), (2);

select t1.id from tab_test1 t1
where t1.id not in (select t2.id from tab_test2 t2);

select t1.id from tab_Test1 t1
where not exists
(select 1 from tab_tetst2 t2 where t2.id=t1.id);

select t1.id
from tab_test1 t1
left outer join tab_test2 t2 on t1.id=t2.id
where t2.id is null;


choose one thing that you want

if there are a lot of datas i prefer to use third way

DATE_FORMAT, STR_TO_DATE

-- datetime, date to str

SELECT DATE_FORMAT(REG_DATE, '%Y-%m-%d %H:%i:%s') FROM TB_FILE;

SELECT DATE_FORMAT(REG_DATE, '%Y-%m-%d %H:%i:%s') FROM TB_FILE;

-- str to datetime

select STR_TO_DATE('2011-04-30 15:13:25', '%Y-%m-%d %H:%i:%s');

--

SELECT NOW();

SELECT DATE_ADD(NOW(), INTERVAL -1 DAY);

string to datetime in mysql

http://www.geeksengine.com/database/single-row-functions/conversion-functions.php

Index list depending on Engine in mysql

MyISAM : B-Tree, R-tree, fulltext-index
InnoDB :  B-Tree
Memory : B-Tree, Hash
TokuDB : Fractal-Tree

'cascade on delete' in mysql

what mean cascade on delete ??

this means if parent record deleted, the child records referencing parent pk also deleted


create table tb_parent (
 id int not null,
fd varchar(100) not null,
primary key (id)
)ENGINE = INNODB;

create table tb_child(
id int not null,
pid int default null,
fd varchar(100) default null,
primary key (id),
key ix_parentid(pid),
constraint child_ibfk_1 foreign key (pid) references tb_parent (id) on delete cascade
) ENGINE = INNODB;

insert into tb_parent values (1, 'parent-1'), (2, 'parent-2');

insert into tb_child values(100, 1, 'childe-100');

MYSQL EXPLAIN QUERY

Explain 정보보는법

인덱스가 적절히 사용되고 있는지 검토
나열된 순서는 MYSQL 이 쿼리처리에 사용하는 순서대로 출력

EXPLAIN 의 각 행 설명

1. id : SELECT 번호, 쿼리내의 SELECT 의 구분번호

2. select_type : SELECT 의 타입

• SIMPLE: 단순 SELECT (UNION 이나 서브쿼리를 사용하지 않음)
• PRIMARY: 가장 외곽의 SELECT
• UNION: UNION 에서의 두번째 혹은 나중에 따라오는 SELECT
• DEPENDENT UNION: UNION 에서의 두번째 혹은 나중에 따라오는 SELECT, 외곽쿼리에 의존적
• UNION RESULT: UNION 의 결과물
• SUBQUERY: 서브쿼리의 첫번째 SELECT
• DEPENDENT SUBQUERY: 서브쿼리의 첫번째 SELECT, 외곽쿼리에 의존적
• DERIVED: SELECT 로 추출된 테이블 (FROM 절 내부의 서브쿼리)

3. table : table명

4. type : 조인타입, 우수한 순서대로… 뒤로갈수록 나쁜 조인형태

• system
  테이블에 단 하나의 행만 존재(시스템 테이블). const join 의 특수한 경우
• const
  많아야 하나의 매치되는 행만 존재할 때
  PRIMARY KEY 나 UNIQUE index 를 상수와 비교할 때
  각 컬럼값은 나머지 연산에서 상수로 간주, 처음 한번만 읽어들이면 되므로 매우 빠름
• eq_ref
  조인수행을 위해 각 테이블에서 하나씩의 행만이 읽히는 경우
  조인연산에 PRIMARY KEY 나 UNIQUE index 인덱스가 사용되는 경우
  인덱스된 컬럼이 = 연산에 사용되는 경우
• ref
  이전 테이블과의 조인에 사용될 매치되는 인덱스의 모든행이 이 테이블에서 읽혀질 때
  leftmost prefix 키만을 사용하거나 사용된 키가 PRIMARY KEY 나 UNIQUE 가 아닐때
  (즉 키값으로 단일행을 추출할수 없을때)
  사용된 키가 적은수의 행과 매치되면 이것은 적절한 조인 타입
  ref 는 인덱스된 컬럼과 = 연산에서 사용됨
• ref_or_null
  ref 와 같지만 NULL 값을 포함하는 행에대한 검색이 수반될 때
  서브쿼리 처리에서 대개 사용됨
• index_merge
  인덱스 병합 최적화가 적용되는 조인 타입
  이 경우, key 컬럼은 사용된 인덱스의 리스트를 나타내며
  key_len 컬럼은 사용된 인덱스중 가장 긴 key 명을 나타냄
• unique_subquery
  몇몇 IN 서브쿼리 처리에서 ref 타입대신 사용됨
  unique_subquery 는 성능향상을 위해 서브쿼리를 단순 index 검색 함수로 대체함
• index_subquery
  unique_subquery 와 마찬가지로 IN 서브쿼리를 대체
  단, 서브쿼리에서 non-unique 인덱스가 사용될때 동작 함
• range
  인덱스를 사용하여 주어진 범위 내의 행들만 추출
  key 컬럼: 사용된 인덱스
  key_len: 사용된 가장 긴 key 부분
  ref 컬럼: 이 타입의 조인에서 NULL
  키 컬럼이 상수와 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN 또는 IN 연산에 사용될때 적용됨
• index
  인덱스가 스캔된다는걸 제외하면 ALL 과 같음
  일반적으로 인덱스 파일이 데이타파일보다 작기 때문에 ALL 보다는 빠름
  MySQL 은 쿼리에서 단일 인덱스의 일부분인 컬럼을 사용할때 이 조인타입을 적용함
• ALL
  이전 테이블과의 조인을 위해 풀스캔
  (조인에 쓰인) 첫번째 테이블이 고정이 아니라면 비효율적
  대부분의 경우에 아주 느린 성능

정리:
system – const – eq_ref – ref – ref_or_null – index_mergy – unique_subquery – index_subquery – range – index – ALL

5. possible_keys : MySQL 이 해당 테이블의 검색에 사용할수 있는 인덱스들
possible_keys 에 나타난 인덱스들이 결과에 나타난 테이블 순서에서 실제 사용할 수 없을수도 있음

6. key :  MySQL 이 실제 사용한 key(index)

7. key_len : MySQL 이 사용한 인덱스의 길이, key 컬럼값이 NULL 이면 이값도 NULL
key_len 값으로 MySQL 이 실제 복수컬럼 키중 얼마나 많은 부분을 사용할 것인지 알 수 있음

8. ref : 행을 추출하는데 키와 함께 사용된 컬럼이나 상수값

9. rows : 쿼리 수행에서 MySQL 이 예상하는 검색해야할 행수

10. Extra : MySQL 이 쿼리를 해석한 추가적인 정보를 나타냄

• Distinct: MySQL 이 매치되는 첫행을 찾는 즉시 검색을 중단한다는 의미
• Not exists: MySQL 이 LEFT JOIN 을 수행함에 매치되는 한 행을 찾으면 더이상 매치되는 행을 검색x
• range checked for each record (index map: #): MySQL 이 사용할 좋은 인덱스가 없음 의미
• Using filesort: MySQL 이 정렬을 위해 추가적인 과정을 필요로 함
• Using index: 컬럼정보가 실제 테이블이 아닌 인덱스트리에서 추출, 쿼리에서 단일 인덱스된 컬럼들만을 사용하는 경우
• Using temporary: MySQL 이 결과의 재사용을 위해 임시테이블을 사용, 쿼리 내에 GROUP BY 와 ORDER BY 절이 각기 다른 컬럼을 사용할때 발생
• Using where: WHERE 절이 다음 조인에 사용될 행이나 클라이언트에게 돌려질 행을 제한하는 경우 테이블의 모든 행을 검사할 의도가 아니면 ALL 이나 index 라면 쿼리사용이 잘못된 것임
• Using sort_union(…) , Using union(…) , Using intersect(…)
• Using index for group-by: Using index 와 접근방식이 같으며, 추가적인 디스크 접근 없이 GROUP BY 나 DICTINCT 쿼리에 사용된 모든 컬럼에 대한 인덱스를 찾았음을 의미

정리:
쿼리를 가능한 한 빠르게 하려면, Extra 값의 Using filesort 나 Using temporary 에 주의해야 함
EXPLAIN 의 출력내용중 rows 컬럼값들을 곱해봄으로써 얼마나 효과적인 join 을 실행하고 있는지 알 수 있다

[출처] MySQL 실행계획보기 (explain)|작성자 eagle5945

Mysql make user and password

First of all make database

mysql> create database DBName;

See all database list

mysql> show databases

mysql> use mysql

Making general user

mysql> grant usage on *.* to amarokuser@localhost identified by 'amarokpasswd'; Query OK, 0 rows affected (0.00 sec)

Making the user having all authorities

mysql> grant all privileges on amarokdb.* to amarokuser@localhost ; Query OK, 0 rows affected (0.00 sec)

commit all changes

mysql> FLUSH PRIVILEGES;

see allowed ip adresses

SELECT Host FROM mysql.user WHERE user='root';

mysql unique setting

ALTER TABLE [table] ADD UNIQUE (USER_ID)

First of all, you can make some column unique when creating table

CREATE TABLE Persons
(
seq int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
UNIQUE (seq)
)

Second, when you alter table.

ALTER TABLE TB_USER ADD UNIQUE (USER_ID);

MYSQL Authority~!

$ mysql -u root -p
Enter password:

mysql> use mysql

mysql> GRANT ALL ON *.* to root@'192.168.1.4' IDENTIFIED BY 'your-root-password'; 

mysql> FLUSH PRIVILEGES;

if i want all ipaddress can access to DB Server

mysql> GRANT ALL ON *.* to root@'%' IDENTIFIED BY 'your-root-password'

Change Database Characters

Change Character set

There are many way to change character set
this way is to change database character set

ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

belows sql query shows that what kind of character set mysql has

mysql> show character set;