[MySQL] MySQL 팁 정리

root암호변경설정

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

This is done with:

/usr/bin/mysqladmin -u root -p password 'new-password'

/usr/bin/mysqladmin -u root -h ns.dbakorea.pe.kr -p password 'new-password'

 

쉘에서는 mysql이 되는데 PHP에서 mysql.sock error를 내면서 MySQL이 안되는 경우

mysql.sock은 /tmp 아니면 /var/lib/mysql에 생기게 된다.

나의 경우, /var/lib/mysql에 mysql.sock파일이 있는데 PHP에서는 /tmp에서 찾으려하면서 에러를 발생했다.

/usr/bin/safe_mysqld파일에서 다음과 같이 수정한다.

주석(#)이 달린 것이 원래것이고 그 밑에 있는것이 수정한 것이다.

 

# MYSQL_UNIX_PORT=${MYSQL_UNIX_PORT:-/var/lib/mysql/mysql.sock}

MYSQL_UNIX_PORT=${MYSQL_UNIX_PORT:-/tmp/mysql.sock}

 

위와 같이 하니 /usr/bin/mysql이 /var/lib/mysql/mysql.sock에서 소켓파일을 찾으려 했다.

socket file을 지정하는 --socket이라는 옵션으로 다음과 같이 지정하면 된다.

 

mysql --socket=/tmp/mysql.sock -u dbakorea -p db_test

 

하지만 mysql실행시마다 이렇게 써줘야한다는 것이 상당히 귀찮다. 옵션이 바로 적용되게 설정하자.

mysql은 설정사항을 다음 3가지 파일에서 검색한다.

 

/etc/my.cnf global options(MySQL 전체적으로 사용되는 옵션 정의)

mysql-data-dir/my.cnf 특정 DB에 적용되는 option (/var/lib/mysql/my.cnf)

~/.my.cnf 사용자 각각의 설정('~'문자는 사용자의 홈디렉토리는 의미)

 

/usr/share/mysql디렉토리에 예제가 있으므로 참고한다.

소켓파일의 지정은 다음줄을 넣어주면 된다.

 

socket = /tmp/mysql.sock

 

 

== /etc/my.cnf예 ==

# The following options will be passed to all MySQL clients

[client]

#password = your_password

port = 3306

socket = /tmp/mysql.sock

 

# Here follows entries for some specific programs

 

# The MySQL server

[mysqld]

port = 3306

socket = /tmp/mysql.sock

 

 

# root암호설정

% mysqladmin -u root password '변경암호' root로 로그인하여 해야함

or

% mysqladmin -u root -p기존암호 password '변경암호' root로 로그인하여 해야함

 

DB생성

mysql> create database DB명 ( or % mysqladmin -u root -p create DB명 )

 

DB삭제

mysql> drop database DB명

 

MySQL 연결

mysql -u 사용자 -p DB명 ( or % mysqladmin -u root -p drop DB명 )

 

데이터파일 실행(sql*loader기능)

mysql>load data local infile "데이터파일" into table 테이블명 ;

데이터파일에서 컬럼구분은 탭문자, Null값은 /n로 입력

 

질의 파일 실행

쉘프롬프트상에서

mysql -u 사용자 -p DB명 < 질의파일

or

mysql프롬프트상에서

mysql> source 질의파일

 

쉘프롬프트상에서 질의 실행

dbakorea@lion board]$ mysql mysql -u root -pxxxx -e

> "INSERT INTO db VALUES(

> 'localhost', 'aaa', 'aaa',

> 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y')"

 

 

사용자 생성 & 사용자에게 DB할당

shell> mysql --user=root -p mysql

 

mysql> INSERT INTO user VALUES('localhost','사용자',PASSWORD('비밀번호'),'Y','Y','Y','Y','Y','Y','Y',

'Y','Y','Y','Y','Y','Y','Y');

mysql> INSERT INTO user VALUES('%','사용자',PASSWORD('비밀번호'),'Y','Y','Y','Y','Y','Y','Y','Y','Y',

'Y','Y','Y','Y','Y');

 

mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)

VALUES ('localhost','DB명','사용자','Y','Y','Y','Y','Y','Y');

mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)

VALUES('%','DB명','사용자','Y','Y','Y','Y','Y','Y');

 

mysql> FLUSH PRIVILEGES; (or shell prompt: mysqladmin -u root -pxxxx reload)

 

 

DB, 테이블목록보기

mysql> show databases;

mysql> show tables;

 

사용 DB선택

mysql> use DB명

 

테이블구조

mysql> describe 테이블명

 

인덱스 보기

show index from 테이블명

 

테이블명 변경

mysql> rename table test to test2;

 

접속

mysql {-h 접속호스트} -u 사용자 -p 사용DB

-h로 다른 서버에 존재하는 MySQL접속시 다음과 같이 MySQL DB에 설정해줘야 한다.

mysql> INSERT INTO user VALUES('접근을 허용할 호스트ip','사용자',PASSWORD('비밀번호'),'Y','Y','Y',

'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)

VALUES('접근을 허용할 호스트ip','사용DB','사용자','Y','Y','Y','Y','Y','Y');

mysql> FLUSH PRIVILEGES; or 쉴프롬프트상에서 % mysqladmin -u root -p flush-privileges

 

버전체크

mysql> select version();

 

검색조건(where)

regular expression을 지원하다니 신기하군..

mysql> select * from work where 열명 regexp "정규표현식";

 

컬럼의 추가, 삭제(오라클과 동일)

alter table 테이블명 add 컬럼명 데이터타입;

alter table 테이블명 del 컬럼명;

 

 

백업 & 복구

mysqldump {-h 호스트} -u 사용자 -p DB명 > 백업파일

mysql {-h 호스트} -u 사용자 -p DB명 < 백업파일

 

mysqldump -u root -p --opt db_dbakorea > dbakorea.sql

mysqldump -u root -p --opt db_board | mysql ---host=remote-host -C database (상이한 머쉰)

mysql -u dbakorea -p db_dbakorea < dbakorea.sql

 

 

mysqldump -u root -p --opt db_dbakorea | mysql ---host=ns.dbakorea.pe.kr -C db_dbakorea

 

테이블 검사

isamchk

 

오라클 sysdate와 동일

insert into test values('12', now());

 

유닉스 time()함수 리턴값 사용

FROM_UNIXTIME(954788684)

UNIX_TIMESTAMP("2001-04-04 :04:04:04")

 

MySQL 디폴트 DB&로그파일 위치

/var/lib/mysql

/var/lib디렉토리는 여러 프로세스들이 사용하는 데이터를 저장하는 일종의 파일시스템상의 데이터베이

스라고 볼 수 있다.

 

replace

해당 레코드 존재하면 update하고, 존재하지 않는다면 insert한다.(insert문법과 동일)

replace into test values('maddog','kang myung gyu')'

 

explain

explain 질의문: 지정한 질의문이 어떻게 실행될 건지를 보여줌

mysql> explain select u.uid, u.name, a.name from sm_user u, sm_addr a where u.uid=a.uid;

+-------+------+-----------------+-----------------+---------+-------+------+-------+

| table | type | possible_keys | key | key_len | ref | rows | Extra |

+-------+------+-----------------+-----------------+---------+-------+------+-------+

| u | ALL | PRIMARY | NULL | NULL | NULL | 370 | |

| a | ref | sm_addr_uid_idx | sm_addr_uid_idx | 11 | u.uid | 11 | |

+-------+------+-----------------+-----------------+---------+-------+------+-------+

2 rows in set (0.01 sec)

 

temporary table

크기가 큰 테이블에 있는 subset에 대한 질의라면 subset을 temporary table에 저장한 후

질의하는 것이 더 빠를 경우가 있다.

create temporary table (...);

create temporary table (...) type=heap; 디스크가 아닌 메모리에 테이블 생성

 

==============================================================================

MySQL 특성정리

==============================================================================

primary key지원

index 지원(15개컬럼, 256byte까지)

foreign key, check, references기능 지원하지 않음(형식상 있음)

 

mysql> CREATE TABLE test (

id INT NOT NULL,

last_name CHAR(30) NOT NULL,

first_name CHAR(30) NOT NULL,

PRIMARY KEY (id),

INDEX name (last_name,first_name));

 

MySQL에서의 Stored script개념 => SQL server language

commit-rollback개념 => lock tables

[MySQL 원격접속사용하기]

 

DB서버가 분리되어 운영될 때 원격의 호스트가 해당 디비서버에 접속하기 위한 설정방법이다.

 

+ 원격의 디비에 접속하기위한 Host1, Host2, Host3가 있고 DB서버인 Host4가 있다고 가정했을 때..

원격디비서버에서 User테이블에 원격접속을 허용할 호스트를 입력해주고 DB 테이블에서 사용자에게

지정된 모든 호스트를 지정해주거나 원하는 호스트를 지정해 주면 됩니다.

 

+ DB 서버설정하기.

 

sueni@bbung ...]# mysql -uroot -p mysql

password: *********

 

# manpage라는 하나의 사용자를 등록 후 해당사용자에 대한 정보로 원격에서 접속하도록 설정해

보겠다.

 

먼저 DB를 하나 만들도록 합니다.

 

mysql> create database manpage;

 

mysql> insert into user (host,user,password) values ('localhost','manpage',password('manpage'));

 

mysql> insert into user (host,user,password) values ('Host1','manpage',password('manpage1'));

 

mysql> insert into user (host,user,password) values ('Host2','manpage',password('manpage2'));

 

mysql> insert into user (host,user,password) values ('Host3','manpage',password('manpage3'));

 

# 위에서 패스워드에 1,2,3이 붙은것은 하나의 사용자에 대해 호스트별로 패스워드를 다르게 줄수 있

다는것을 보여주는 것입니다.

 

# DB 테이블 정보입력

mysql>insert into db values

('localhost','manpage','manpage','y','y','y','y','y','y','y','y','y','y');

 

# 해당사용자의 user테이블에 지정된 모든 원격지 호스트에 대한 접속을 허용하도록 설정한다.

 

mysql> update db set host='%' where db='manpage';

 

mysql> flush privileges

 

# manpage 사용자에게 지정된 호스트를 보도록 하자.

mysql> select host,user from user where user='manpage';

+------------------------+---------+

| host | user |

+------------------------+---------+

| localhost | manpage |

| Host1 | manpage |

| Host2 | manpage |

| Host3 | manpage |

+------------------------+---------+

 

# Host1,2,3에 대해 각각의 사용자 홈계정에 다음과 같은 index.php파일을 만들어서 테스트해보자.

 

 

<? $db_conn = mysql_connect('원격DB호스트', 'User', 'Passwd'); mysql_select_db('DB이름', '$db_conn'); if (!$db_conn) { echo "디비연결실패"; } else { echo "디비연결성공"; } ?>

위와 같은 파일을 만들어 테스트해보면 각각의 호스트마다 서로다른 패스워드로 원격접속이 가능함

을 볼수 있다.

 

원격디비접속은 생각외로 쉽다. :)

 

+ 팁 :

 

+ MySQL Root 패스워드 분실시...

 

실행중인 MySQL 데몬을 정지시킨 후 -Sg (Skip GrantTable)옵션을 주고 MySQL을 기동한다.

 

sueni@bbung ...]# /etc/rc.d/init.d/mysqld stop 또는

sueni@bbung ...]# mysqladmin -uroot -p shutdown

 

sueni@bbung ...]# safe_mysqld -Sg --language=korean &

 

sueni@bbung ...]# mysql

 

위와 같이 Sg 옵션을 주면 사용자 권한을 체크하지 않으므로 MySQL에 접속이 가능해진다.

 

mysql> update user set password=password('root') where user='root';

 

위와 같이 Root 패스워드를 업데이트한 후 MySQL데몬을 Sg 옵션없이 정상적으로 기동하도록한다.