'mysql'에 해당되는 글 57건

  1. 2008.10.17 인커밍(incoming) MySQL 커넥션을 테이블에…
  2. 2008.10.17 MySQL과 SQLGate for MySQL 연결방법
  3. 2008.10.16 SSH 터널링을 활용한 MySQL Replication 구축
2008.10.17 11:01

인커밍(incoming) MySQL 커넥션을 테이블에…

- 원문은 http://www.futhark.ch/mysql/143.html 에 있음

MySQL 5.1에서는 일반적인 쿼리 로그를 테이블에 저장할 수가 있다. 하지만 만약에 모든 단일 명령문을 로그하는 대신에 단지 로깅을 하는 사용자 및 접속을 하는 호스트만 로그 하고자 할 경우에는 어떻게 해야 하는가? 이번 컬럼에서는 위의 문제를 간단하게 해결할 수 있는 방법을 설명하기로 한다. 이 해법은 MySQL 5.0에서도 실행할 수 있으며, 약간의 변경만 하면 이전 버전에서도 실행 시킬 수가 있다.

이에 대한 해결책은 바로 로깅 루틴을 설치하는 도구로 init_connect 서버 변수를 사용하는 것이다. init_connect 변수는 커넥션되는 각 클라이언트(SUPER 사용자는 제외)를 위해 실행되는 SQL 명령문을 가지고 있다.

우선, 커넥션 로그(connecting log)를 보관하기 위한 데이터 베이스와 테이블을 하나씩 생성한다. 새로운 데이터 베이스 sys를 아래와 같이 생성한다.

CREATE DATABASE IF NOT EXISTS sys;

CREATE TABLE sys.connection_log (
log_id INT UNSIGNED NOT NULL auto_increment,
login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
connection_id INT(10) NOT NULL,
user_id VARCHAR(255) NOT NULL,
PRIMARY KEY (log_id),
KEY (user_id)
);

로그 엔트리를 주문(order)하기 위해서는 auto_increment 컬럼이 필요한데, 그 이유는 TIMESTAMP만으로는 불충분하고 CONNECTION_ID()는 서버가 재 시작(restart)되면 리셋(reset)되어 버리기 때문이다.

다음으로는, 커넥션 정보를 로그에 기록하는 간단한 루틴을 설치한다. CREATE 명령어는 루트(root)로 실행하거나 또는 sys.connection_log 테이블에 INSERT 권한을 가진 사용자로 실행하는 것이 좋다.

delimiter //

CREATE PROCEDURE sys.init_connect()
SQL SECURITY DEFINER
MODIFIES SQL DATA DETERMINISTIC
BEGIN
INSERT INTO connection_log
(connection_id, user_id)
VALUES (CONNECTION_ID(), USER());
END //

delimiter ;

SQL SECURITY를 DEFINER에 설정함으로써, 일반 사용자로부터 오는 로깅 시스템의 모든 내부 정보는 숨길 수가 있게 된다. 우리가 원하는 것은 단지 모든 사용자에게 새로운 데이터 베이스 sys상에서의 글로벌 실행 권한만을 주는 것이다.

REPLACE INTO mysql.db
(Host, Db, User, Execute_priv)
VALUES ('%', 'sys', '', 'Y');

FLUSH PRIVILEGES;

로깅을 활성화 시키기 위해서는, 우리가 사용한 루틴을 init_connect 변수에 연결(hook)하기만 하면 된다:

SET GLOBAL init_connect = 'CALL sys.init_connect()';

만일 서버가 리부팅(rebooting)될 때에도 커넥션 로깅 정보가 남아 있도록 하고자 한다면, 이 설정 내용을 my.cnf 파일에 추가하면 된다:

[mysqld]
init_connect = 'CALL sys.init_connect()'

이와 같이 하면, 모든 커넥션(connection)은 로그가 된다.
실무에 많은 도움이 되시길……….

Trackback 0 Comment 0
2008.10.17 10:57

MySQL과 SQLGate for MySQL 연결방법

SQLGate for MySQL 을 MySQL 과 연결을 못하시는 분들이 많다는걸 알게 되었습니다.
그래서 이렇게 연결을 못해서 사용을 못하시는 분들을 위해 아래와 같은 문서를 작성하였습니다.
아직 연결을 못하신 분들을 아래의 절차를 통해서 연결하시기 바랍니다.

1

telnet 명령어를 통해서 mysql 이 실제 서비스 중인지 확인합니다.

c:\>telnet 아이피주소 3306
3.23.38-nt3?;ft4B3to

만약 telnet 명령어로 위와 같은 답을 못얻었을 경우는 서비스 중이 아니거나 또는 방화벽 또는
포트를 죽였는지 확인해 보시기 바랍니다.

위에 3.23XXX 쪽은 자신의 MySQL 버전에 따라 다른 문자로 표현될 수 있습니다.

2

Window => 도스프로그램에서 root 권한으로 MySQL에 접속합니다.
Linux => Telnet 접속후 root 권한으로 MySQL에 접속합니다.

mysql>use mysql;

mysql>create database test_db;

mysql>insert into db values('%','test_db','test_user','y','y','y','y','y','y','y','y','y','y');

mysql>insert into user (host,user,password) values('%','test_user',password('1234'));

mysql>flush privileges; or shell환경에서 mysqladmin reload -p

Host 에서 %는 와일드(wild) 문자로 '모든'을 의미합니다. 이 뜻은 모든곳에서 접속이 가능하다는뜻이죠.
Host 에서 localhost 라고 되어 있다면 이것은 결국 서버에서만 접속이 가능하다라는 뜻으로 해석됩니다.
Host 가 localhost 일 경우 대부분 접근 거부 에러를 발생하게 됩니다.

*)다른 방법은 계정접속후에 % 을 지원하도록 하는겁니다.

mysql> GRANT ALL PRIVILEGES ON *.* TO root@%
IDENTIFIED BY '123456' WITH GRANT OPTION;

위에 방법은 root 유저에게는 어디서든지 접속이 가능하게 설정해준 결과가 됩니다. 

3 SQLGate for MySQL 접속합니다.
 

만약 그래도 접속이 잘 안되실 경우는
http://www.sqlgate.com/bbs/zboard.php?id=bug_mysql
에 문의하시기 바랍니다.

다음과 같은 에러가 발생시

  1. I get an error when connecting: 2003 - Can't connect to MySQL server on 'some_host_name' (10060)

    위에 경우에는 물리적으로 MySQL Server 에 접속할 수 없는 경우입니다. 그 이유는 몇가지 있습니다.

    1. 네트워크 연결 상태가 올바르지 않습니다.
    - 네트워크 연결상태를 체크합니다.

    2. MySQL Server 가 올바르게 작동하고 있지 않습니다
    - 시스템 담당자에서 MySQL Service를 Restart 하도록 요청합니다.

    3. 방화벽 또는 포트가 막혀 있습니다.
    - 시스템 담당자에서 포트를 개방해 달라고 요청합니다.

    4.서버셋업시 "--skip-networking" 옵션을 설정한 경우입니다. 이경우에는 로컬호스트 이외에는 접근할 수가 없습니다.
    - Config 설정시 위에 옵션을 제거합니다.

    More - http://www.mysql.com/doc/C/a/Can_not_connect_to_server.html

    아래의 글은 SQLGate for MySQL 사용자의 체험담입니다.
    Linux7.x 버전을 설치하신분은 이런일을 겪지 않을실까 걱정되어 글을 올리게 되네요.
    아무설정도 하지않고 그냥 설치했습니다.
    리눅스 설치할때 보안수준(Firewall)은 미디엄으로 설치했고요. 중간에 하두 안되서 Firewall 수준을 최저로 낮춰봐도 소용이 없더라구요. 그래도 ipchains 의 값은 변경되지 않나봐요. ㅡ.ㅡ;
    암튼 어느 게시판엔가 ipchains -L 해서 몬가 의심스러운(?)게 나온다면 ipchain 을 공부하라고 써있더군요.
    그래서 ipchains -L 해보니 모든 패킷의 input이 다 막혀있고 딱 세가지 telnet, http,ftp 이 세가지만 허용이 되어있더군요.
    이게 리눅스 설치할때 Firewall 수준을 미디엄으로 하면 이렇게 설정되나바요.
    (다른 수준으로 설치해보진 않았음 ^^;)

    결국 그래서 아래 문서보면서 해결하고 있습니다. 일단 깝깝한게 해결되어 기뿐맘으로 글 올립니다. 그럼 고생하세요~. 좋은 작품 기대하겠습니다. 고생스런 답변도 감사하고요. ^^ 즐프~

    http://kldp.org/Translations/IPCHAINS-HOWTO

  2. I get an error when connecting: 1130 - Host "some_client_name_or_ip" is not allowed to connect to this MySQL server

    More -http://www.mysql.com/doc/A/c/Access_denied.html
  3. I get an error when connecting: Error 1045 - Access Denied for User 'XYZ@HOSTXYZ' (Using password: NO)

    More - http://www.mysql.com/doc/A/c/Access_denied.html

Trackback 1 Comment 0
2008.10.16 14:59

SSH 터널링을 활용한 MySQL Replication 구축

Mysql 서버의 데이터를 미러링 할 목적으로 replication 을 셋팅하던 도중에...
갑자기 생각이 나서 한번 해봤습니다.
딴거는 다 포트가 막혀있거나 또는 SSH 터널링을 이용하는데 replication 을 위해..
mysql 포트를 열어야 하는것과 mysql 권한에 원격 로그인을 허용한다는게 좀 맘에 안들어서... ^^

replication 을 구축하기 위해 최소 2개의 mysql 서버가 필요하겠죠.
master 서버 : insert, update, delete 등이 일어나는서버..
slave 서버 : select 를 주로 하는 서버...
우선 두 서버가 리눅스라는 가정하에 설명하겠습니다.
한쪽이 윈도우거나 두쪽다 윈도우인 경우는 별도의 툴들이 필요하니...
대체적으로 replication 을 사용하시는 분들은 둘다 리눅스를 많이 사용할듯 싶으니...

설치 환경
master : fedora4 mysql.4.1.16
slave : centos5 mysql.5.0.41

SSH 터널링
우선 ssh 터널링을 만들어야 합니다.
ssh 터널링은 slave 서버쪽에 셋팅합니다.

# ssh -CNf -L3307:127.0.0.1:3306 ssh계정@master서버IP
패스워드를 입력하자.
이제 slave 서버와 master 서버간에 터널링이 되었다.

# netstats -an | grep LISTEN
하면 3307 포트가 열려있는것을 확인할수 있다.

잠깐 ssh 터널링을 설명하자면 slave 서버에서 로컬(-L)의 3307번 포트로 접속하면 slave의 ssh를 통해..
master 의 ssh 에 접속하여 127.0.0.1의 3306번 포트로 접속한다는것이다.

주의 : 여기서 127.0.0.1 은 slave의 client ssh로 master 서버의 ssh 서버에 접속한 다음에 IP를
의미하는것이므로 127.0.0.1 은 master 서버 자신을 의미한다.
mysql 계정들의 host 권한을 모두 localhost 로 만들어둔 상태라..
127.0.0.1 이 아닌 master 서버의 도메인으로 셋팅한 경우에 접속이 되질 않았다.
이것때문에 삽질을 좀했다.

-f 는 백그라운드로 돌린다는 말이고..
-C는 압축한다는 의미이다.
-N 은 명령어 실해없이 시작한다는 ...

그럼.. 실제로 접속을 해보자.

/usr/local/mysql/bin/mysql -u root -p -P 3307 -h 127.0.0.1
db 리스트 및 내용을 확인해보면 master 로 접속된걸 확인할수 있을것이다.

Tip
위에서 언급한 ssh 터널링은 재부팅되면 초기화 되므로 재부팅시 자동으로활성화 되도록 만들어보자
/root/sshlogin 이란 화일을 만든다음
===========================================================================================
#!/usr/bin/expect
spawn bash -c "ssh -CNf -L3307:127.0.0.1:3306 ssh계정@master서버IP"
expect -re "Password:"
sleep 0.2
send "master서버의ssh계정패스워드\r"
interact
===========================================================================================
위와 같이 입력해준다.
만약 expect 가 없다면 expect 를 yum install expect 해서 설치하거나..
http://rpmseek.com 또는 http://rpmfind.net 에서 찾아서 설치하시길....

이제 해당화일에 chmod 700 권한을 주고... ==> chmod 700 /root/sshlogin
/etc/rc.local 에
/root/sshlogin <== 이부분을 추가..
그럼 리눅스 시스템이 부팅하면서 해당 화일을 실행하고 ssh 터널링이 열린다.

Replication
이제 replication 이 남았다.
일반적인 replication 과 설정이 다른것은 하나밖에 없을것이다.
Slave Server 에서...

Master Server
my.cnf
===========================================================================================
[mysqld]
log-bin = mysql-bin
server-id   = 1
binlog-do-db = db_name1
#binlog-do-db = db_name2
===========================================================================================

Slave Server
my.cnf
===========================================================================================
[mysqld]
server-id       = 2
master-host     = 127.0.0.1
master-user     = 리플리케이션아이디
master-password = 패스워드
master-port     = 3307
# DB 별
replicate-do-db = db_name1
#replicate-do-db = db_name2
# 테이블 별
# replicate-do-table=db_name.tbl_name
===========================================================================================
이제 mysql 서버를 각각 실행시키면 ssh 터널링을 이용해 replication 이 된다.
이문서는 mysql 설치 초기에 replication 환경을 만들기 위한것이다..
mysql 이 서비스 되는 상태에서 replication을 셋팅하는것은 인터넷에 자료가 많으니...

출처 => http://www.phpschool.com/gnuboard4/bbs/board.php?bo_table=tipntech&wr_id=55304

 

sftp를 배제한 ssh 터널링을 이용한 ftp 사용하기

FTP는 암호화되지 않은 형태로 인증과정과 파일이 전송이 이뤄진다.이는 전송되는 중간에
스니핑이 이뤄진다면 고스란히 해커(?)의 손에 ID/PW, 전송 파일이 유출될 수도 있음을 의
미한다. 이글에서는 보다 안전하게 ssh 터널링을 통한 FTP 전송과 vsftpd 설정에 대해 소개
한다.

ssh 터널링 만들기

- FTP 서버에서는 ssh 서버가 동작중이어야 한다. (서버명을 free, ID는 truefeel 이라고 가정)
- client에서는 ssh 클라이언트가 있어야 한다.

연결과정을 그러보면.
로컬의 FTP클라언트 -> ssh 클라이언트 -> 네트워크(암호화 전송) -> ssh 서버 -> FTP 서버

터널링을 만들어보자!

# ssh -P10000 -CNf -L10021:free:21 truefeel@free
-C : 압축해서 전송한다.
-N : 명령어 실행없이 시작한다.
-f : 백그라운드로 실행한다.
-L : 원격서버의 포트를 로컬로 포워딩한다. (즉, 터널링을 만들어줌)
     free서버의 FTP(21번 포트)를 로컬의 10021번 포트로 포워딩한다.
     즉 로컬의 10021번 포트를 통해 free의 FTP서버에 접속할 수 있다. 이 때 원격끼리는 암호화되어 전송된다.
    
만약 원격의 ssh서버가 다른 포트를 사용한다면 -p [포트] 옵셥까지 뒤에 붙여주면 된다.
ps aux 하면 ssh가 백그라운드로 떠있는 것을 확인할 수 있다.

ftp명령으로 로컬의 10021번으로 접속을 하면 원격의 free FTP서버로 접속하게 된다.

$ ftp -p localhost 10021
Connected to localhost (127.0.0.1)
220 Secure FTP 서버
Name (localhost:truefeel): truefeel
331 Please specify the password.
Password:
230 Login successful.
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> ls
227 Entering Passive Mode (xxx,xxx,xxx,xxx,80,250)
Security: Bad IP connecting.
ftp>

접속은 정상적으로 이뤄졌는데, 명령어를 입력했더니 'Security: Bad IP connecting.'에러가
발생을 했다. /etc/vsftpd.conf에 다음 한 줄을 추가하면 쉽게 해결할 수 있다.

pasv_promiscuous=YES


Trackback 0 Comment 0