[DB] mysql 관련 팁

mysql 관련된 질문을 조금 정리 했습니다.

Ⅱ. MYSQL 관련 FAQ

Warning: 0 is not a MySQL result index in file_name on line 8…

쿼리에 대한 결과로 리턴된 결과값이 0 이면 쿼리가 어떤 이유로 건 실패

했음을 의미합니다.

따라서 쿼리를 확인해 보세요.

적절한 방법은 다음과 같은 코드를 사용하는 것입니다.

$result = mysql_query(“select * from table_priv”);

if(!$result) {

echo mysql_error();

exit;

}

또는

$result = mysql_query(“select * from table_priv”) or die(“Bad

query: “.mysql_error());

Can’t connect to [local] MySQL server error

1. mysql 데몬이 안 떠 있다.

2. 잘못된 socket file이나 TCP/IP를 사용하고 있다.

mysqld이 안 떠 있으면 mysqld을 뛰운다.

shell> cd mysql_installation_directory

shell> bin/safe_mysqld &

만약 데몬이 떠 있으면 서버를 검사해 봐야 한다.

shell> mysqladmin version

shell> mysqladmin -h `hostname` version

shell> mysqladmin -h `hostname` –port=3306 version

shell> mysqladmin -h ‘ip for your host’ version

shell> mysqladmin –socket=/tmp/mysql.sock version

5. access 자료를 MySQL로 변환하는 방법

먼저 access에서 mdb파일을 불러 온다. 파일(F) / <다른 이름으로 저장/내보내기>

를 선택 한다.

이때 텍스트 파일(Text Files)을 선택하고 <내보내기>를 누른다.

그다음 <고급>을 누르고 <필드구분자>와 <문자열 묶음기호 >를 선택한다.

<확인>을 누르면 텍스트 파일이 만들어진다.

그다음 LOAD DATA INFILE 명령을 쓰면 된다.

일반 사용자가 LOAD DATA INFILE 명령을 쓸 수 있게 하려면

mysql DB의 사용자 테이블에서 File_priv를 ‘Y’로 만들어야 한다.

mysql> use mysql

Database changed

mysql> update user set File_priv=’Y’ where User=’user_name’

mysql을 다시 로드한다.

#./bin/mysqladmin reload

위와 같이 하면 user에게 외부 파일을 테이블로 읽어들일 수 있는 권한이 부여된다.

LOAD DATA INFILE 명령을 사용하여 외부 파일을 읽어 오면 된다.

파일의 절대 경로: /tmp/test.txt

파일의 내용: ( 각 필드는 ‘,’으로 구분되어 있다. )

1,2,a,b,c

2,3,b,c,d

…

mysql>LOAD DATA INFILE ＇/tmp/test.txt’ into TABLE table_name

FIELDS  TERMINATED BY ＇,＇ (col_1,col_2,col_3,col_4,col_5);

6. MySQL 에 remote 호스트에서 접속할 수 없습니다.

Host … is not allowed to connect to this MySQL server

user 테이블에 연결을 하려고 하는 호스트 이름이 없다는 것을 말한다.

mysql DB의 user 테이블에 연결하고자 하는 사용자/호스트 이름을 추가하

여 해결할 수 있다.

mysql 3.22 를 사용하고 있지 않고 연결하고자 하는 시스템의 IP 숫자나

호스트 이름을 모른다면,  user 테이블에 Host 컬럼 값으로 ‘%’ 목록을 입력하고

서버 시스템에서 –log 옵션을 사용해 mysqld 를 재시작하자.

클라이언트 시스템에서 연결을 시도한 후 mysql 로그에는 어떻게 실제로 연결을

했는지에 대한 정보가 들어있다.

(그러고나서 ‘%’를 로그에 나온 실제 호스트 이름으로 바꾼다. 그렇지 앟

으면 보안에 문제가 생길 수 있다.)

7. MySQL 사용자 등록하기

1. mysql mysql로 mysql DB에 연결한다.

# mysql mysql

2. db table에 user_db를 user_name이란 사용자가 사용할 수 있게 정보를

입력한다.

mysql> insert into db values(‘%’,’user_db’,’user_name’,

-> ‘Y’, ‘Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’ );

3. user table에 사용자에 대한 정보를 입력한다.

mysql> insert into user (Host, User, Password)

-> values ( ‘localhost’,’user_name’,passwd(‘user_passwd’) );

4. mysql reload

mysql> bye

# mysqladmin reload

8. MySQL 의 root 암호를 잃어 버렸어요 . 다시 깔아야 하나요?

1. 우선 다음처럼 MySQL 서버데몬을 모조리 죽여 버립니다.

[root@linuxer mysql]# ps -aux | grep my

root 7646 0.0 0.6 1668 808 pts/4 S 09:42 0:00 sh ./bin/safe_mys

root 7740 0.0 0.7 10784 980 pts/4 SN 09:45 0:00 /usr/local/mysql/

root 7742 0.0 0.7 10784 980 pts/4 SN 09:45 0:00 /usr/local/mysql/

root 7743 0.0 0.7 10784 980 pts/4 SN 09:45 0:00 /usr/local/mysql/

root 7747 0.0 0.3 1148 392 pts/4 R 09:45 0:00 grep my

[root@linuxer mysql]# kill -9 7646 7740 7742 7743

[1]+ Killed ./bin/safe_mysqld

2. 그다음 –skip-grant 옵션을 이용

권한테이블을 사용하지 않는 옵션으로 데몬을 띄웁니다.

[root@linuxer mysql]# ./bin/safe_mysqld –skip-grant &

[1] 7759

[root@linuxer mysql]# kill 7743: No such process

Starting mysqld daemon with databases from /usr/local/mysql/data

[root@linuxer mysql]#

여기서 kill 7743: No such process 이런 메시지는 safe_mysqld 스크립트

가 가장 마지막에 실행중이던 데몬의 프로세스를 죽이고 다시 시작하려다 못찾아

나오는 에러 메시지 이지만 그냥 데몬이 떠 버렸으니 신경 쓰지 않아도 됩니다.

프로세스를 확인해 보면….

[root@linuxer mysql]# ps -aux | grep my

root 7759 0.0 0.6 1668 812 pts/4 S 09:49 0:00 sh ./bin/safe_mys

root 7775 0.0 0.5 2520 696 pts/4 SN 09:49 0:00 /usr/local/mysql/

root 7777 0.0 0.5 2520 696 pts/4 SN 09:49 0:00 /usr/local/mysql/

root 7778 0.0 0.5 2520 696 pts/4 SN 09:49 0:00 /usr/local/mysql/

이 상태에서는 권한테이블을 사용하지 않으므로 어떤 호스트에서도 아무

사용자로 모든 DB에 접속이 가능합니다.

얼른 작업을 끝내고 권한을 설정해 DB를 다시 띄워야 합니다. ^,^

3. 자 이제 마음놓고 root 로 들어갑니다.

권한을 설정하는 데이타 베이스 mysql 을 이용하겠습니다.

[root@linuxer mysql]# ./bin/mysql -u root mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \\g.

Your MySQL connection id is 1 to server version: 3.22.22

Type ‘help’ for help.

mysql>

4. 다음처럼 새로운 root 의 암호로 바꾸고

mysql> UPDATE user SET Password=PASSWORD(‘newpasswd’) WHERE

user=’root’;

Query OK, 1 row affected (0.02 sec)

Rows matched: 2 Changed: 1 Warnings: 0

5. 그다음 권한 테이블을 다시 읽고

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

빠져 나갑니다.

mysql> \\q

Bye

[root@linuxer mysql]#

6. MySQL 서버 데몬을 죽여보도록 하겠습니다.

그런데 이미 FLUSH PRIVILEGES 옵션으로 권한이 재 설정 된 상태이므로

아까 바꾼 암호를 입력해야 합니다.

[root@linuxer mysql]# ./bin/mysqladmin -uroot -p***** shutdown

[root@linuxer mysql]# mysqld daemon ended

[1]+ Done ./bin/safe_mysqld –skip-grant

잘 죽었군요(^,^)

7. 이제 권한 테이블을 이용한 원래의 스크립트를 이용 데몬을 다시 띄웁

니다.

[root@linuxer mysql]# ./bin/safe_mysqld &

[1] 7859

[root@linuxer mysql]# Starting mysqld daemon with databases

from /usr/local/mysql/data

[root@linuxer mysql]

8. 자 이제 드디어 아까 바꾼 암호로 들어 갈 수 있습니다.

다음의 방법으로 하면 됩니다.

a. # ./bin/mysql -uroot -p**** mysql 를 써도 되고

b. # ./bin/mysql -uroot -p mysql 를 쓰면 암호를 물어 봅니다.

보안상 b 방법을 권해 드립니다. (누가 뒤에서 보면 안되니까요!)

[root@linuxer mysql]# ./bin/mysql -uroot -p mysql

Enter password:

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \\g.

Your MySQL connection id is 1 to server version: 3.22.22

Type ‘help’ for help.

mysql> select Host ,User from user ;

+——————-+——-+

| Host | User |

+——————-+——-+

| localhost | root |

| php.pe.kr | root |

| localhost | |

| php.pe.kr | |

| localhost | mysql |

+——————-+——-+

5 rows in set (0.00 sec)

mysql> \\q

10. MySQL 의 암호화된 파일의 내용을 알아 낼 수 없나요 ( 생략 )

13. mysql.sock 파일이 없다는데….. ( mysql_err_2002 파일에 있음 )

error 2002 : Can’t connect to local Mysql server through

socket ‘/tmp/mysql.sock'(111)

은 서법 데몬이 떠 있지 않을 때 생기는 에러입니다.

서버 데몬을 먼저 띄우세요.

#cd /usr/local/mysql

#./bin/safe_mysqld &

14. 파일로 존재하는 SQL스크립트 실행하기

SQL 명령문을 적은 파일을 하나 만들고,( text_file ) 다음 명령을 실행한

다.

shell> mysql database < text_file

28. db테이블 백업하기

mysqldump 명령을 쓰면 됩니다.

일단 A 라는 계정으로 로긴합니다.

shell> mysqldump A_db_name > /tmp/temp.dump

그리고 B라는 계정으로 바꿔서….

shell> mysql B_db_name < /tmp/temp.dump

이렇게 하면 모든게 끝납니다.

( temp.dump 라는 파일을 한번 보세요. 그러면 위의 과정을 이해 하실수

있을 것입니다. )

29 pconnect(영구적인 접속)이 무슨 의미인가요?

( mysql FAQ 6.1을 참조 )

영구적인 접속은 여러분의 스크립트의 실행이 종료될 때 까지 close되지

않는 SQL link입니다. 영구적인 접

속이 요청되었을 때, PHP는 이미 동일한(이미 열려져 있는) 영구적인 접속

이 존재하는가를 검사하고, 만약

동일한 것이 있다면 link를 새로 만들지 않고 기존에 존재하는 것을 사용

한다. ‘동일한’ 접속은 동일한 host

에 같은 username과 같은 password를 사용해서 열려져 있는 접속을 의미한

다.

웹서버의 동작 방식과 부하를 나누는 방식에 대해 완벽하게 이해하지 못하

는 사람들은 영구적인 접속에 대

해 착각하는 경우가 많다. 특히 PHP3는 동일한 SQL link에 여러개

의 ‘user session’을 여는 것을 허용하지

않는다. 이것은 transaction을 효율적으로 만들어 내지 못하게 하고, 기

타 다른 여러 가지 작업들을 효율적

으로 처리하지 못하게 한다. 사실, 이 주제에 대해 분명한 것은, 비영구적

인 접속들로 불가능한 기능은 영구

적인 접속에서도 제공되지 않는다는 것이다.

왜?

PHP는 웹서버 하에서 동작한다. 여러분의 웹서버에서 PHP를 이용하여 웹페

이지를 만들어내는 방법은 세

가지가 있다.

첫 번째 방법은 PHP를 CGI “wrapper”처럼 사용하는 것이다. 이 방법을 사

용하면, 웹서버에서 모든 PHP페이

지의 요구가 있을 때마다 PHP 해석기의 instance가 만들어지고 제거된다.

이 방식은 매 페이지를 만들고 난

후에 SQL 서버와의 연결같은 사용한 모든 자원을 반환해야 하기 때문에,

여러분은 영구적인 접속을 사용하

여도 어떤 이득도 얻을 수 없다.(한마디로 영구적이 아니다.)

두 번째 방법은, 가장 많이 사용되는 것으로, PHP를 Apache같은

multiprocess 웹서버에서 module로 사용

하는 방법이다. multiprocess 서버는 전형적으로 한 개의 부모가 되는

process를 가지고, 이 부모 process

로 실제적인 웹서버의 기능을 제공하는 자식 프로세스들을 조정하고 통합

한다. 클라이언트에서 각각의 요

청이 도착하면, 놀고 있는 자식 프로세스에게 이 요청을 넘겨준다. 이것

은 같은 클라이언트에서 두 번째 요

청을 했을 때, 이 요청이 첫 번째 요청과는 다른 자식 프로세스에서에서

처리된다는 것이다. 이 경우 첫 번

째 요청에서 SQL 서버에 영구적인 접속을 만들었다면, 두 번째 요청에서

사용하는 SQL 서버의 영구적인

접속은 첫 번째 요청에서 이매 만들어 놓은 영구적인 접속을 재사용한다.

마지막 방법은 PHP를 a multithreaded 웹서버의 plug-in으로 사용하는 것

이다. 이것은 현재 이론적으로만

있는 방법이고, 실제적으로 multithreaded 웹서버에서 plug-in으로 작동하

는 PHP는 완성되지 않았다.

Netscape FastTrack, Microsoft’s Internet Information Server (IIS),

O’Reilly’s WebSite Pro같은 웹서버들

에서 ISAPI, WSAPI, NSAPI 같은 plug-in 방법들을 사용하는 PHP를 제작중

에 있다. 이것들이 완료되면 아마

도 multiprocess 방식과 같은 방식으로 동작할 것이다.

만약 영구적인 접속이 어떤 추가적인 기능도 제공하지 않는다면, 무엇이

좋다는 것입니까?

이것에 대한 간단한 답은 효율성이다. 영구적인 접속은 SQL 서버와의 link

를 만드는 것에 따른 overhead가

클 경우에 유용하다. 이 overhead는 많은 요인에 따라 커지기도 하고 작아

지기도 한다. 어떤 종류의 데이

터베이스인가? 웹서버와 같은 컴퓨터에 데이터베이스 서버가 있는가? SQL

서버가 어떻게 사용되고 있는

가? 등에 따라 크게 달라진다. 접속에 따른 overhead가 클 경우에 영구적

인 접속은 여러분에게 적지 않은

도움을 줄 것이다. 이것은 자식 process에서 SQL 서버에 접속을 요청할

때 마다 접속을 만드는 대신, 이 프

로세스가 종료될 때 까지 살아있는 한 개의 접속 만을 사용한다. 이것은

모든 영구적인 접속을 사용한 프로

세스는, 그에 해당하는 한 대의 영구적인 접속을 가지고 있다는 것을 의미

한다. 예를 들어 SQL 서버에 대하

여 영구적인 접속을 사용하는 스크립트를 실행하였던 20개의 다른 자식 프

로세스가 있다면, 아마도 각각의

자식 프로세스에대해 1개씩, 20개의 SQL 접속이 있을 것이다.

중요한 요약 한가지. 영구적인 접속은 일반적인 접속에 1대1로 대응되도

록 설계되었다. 이것은 스크립트의

기능은 그대로 두고, 언제라도 비영구적인 연결을 영구적인 연결로 대치

할 수 있다는 것을 의미한다. 이것

으로 여러분은 아마도 스크립트의 효율을 향상시킬 수 있을 것이다.

33. mysql에서 like 문제 ( 한글 )

기본적으로 rpm으로 설치를 하시면 한글에 대한 지원이 되지않습니다.

그래서 대부분의 정확한 한글의 지원을 위해서 컴파일을 합니다.

컴파일하실때 다음의 옵션을 사용해서 컴파일을 하시면 됩니다.

–with-charset=euc_kr