2. 리눅스 Server 구축및실무운영 2.9. Mysql DB Server Admin 이장에서는 Mysql DBA 에게필요한다양한기술에대해소개하도록하겠습니다. 앞장의 [Apache, Php, Java(Tomcat), Mysql 개발환경웹 Server] 부분에서기본적인 Mysql 설치방법에대해서는다룬적이있습니다. 이장에서는보다구체적인 Mysql 관리방법에대해소개하도록하겠습니다. 2.9.1 Mysql 최적화설치하기 2.9.1.1 소스코드로설치하기 Mysql 을구할수있는곳은 http://www.mysql.com/downloads/mirrors.html 을방문하면자신이있는곳에서가장가까운곳에서소스코드를다운받을수있습니다. 소스로 mysql 이설치하기전에먼저자신의시스템이 rpm 으로이미설치가되어져있는지확인하시고먼저설치된 rpm 을제거해주시길바랍니다. # rpm -qa grep mysql # rpm -e mysql-x.xx.xx(verison) # tar xzvf mysql-version.tar.gz // 압축해제 # cd mysql-version // 압축이풀린 Directory로이동 #./configure --prefix=/usr/local/mysql --with-charset=euc_kr // 옵션과함께 configure 실행 # make // make 실행 # make install // make install 실행 # bin/mysql_install_db // 기본 DB와테이블생성 # chown -R mysql. /usr/local/mysql/data // Mysql DB 권한변경 1/41 페이지
# bin/safe_mysqld -u mysql & // mysql Daemon 실행 2.9.1.2 설치옵션 configure 옵션엔위에설치시사용한 --prefix --with-charset 이외도여러옵션이있다. 확인할려면다음과같이문서를만든뒤살펴보도록하자. #./configure --help > mysql_otion.txt 대표적인기타추가옵션으로는다음이있다. 기타추가옵션 --with-unix-socket-dir=/usr/local/mysql/sock/socket.mysql 소켓정보파일을 /usr/local/mysql/sock/socket.mysql 로지정생성하겠다이부분을기술하지않으면 /tmp/( 디폴트소켓이름 ) 으로사용됩니다. --localstatedir=/mysqldb : 이것은데이타베이스파일을어디에다가생성할것인지지정해주는것입니다. 기술하지않으면 mysql_path/var 에 DB가생성된다. 2.9.1.3 mysql Directory 구조 bin : 클라이언트프로그램, 각종스크립트프로그램 include : 개발에필요한헤더파일 info : 각종문서 lib : 개발에필요한라이브러리 libexec : mysqld (Mysql 서버실행 Daemon) share/mysql : Mysql 운영관리스크립트 sql-bench : 벤치마크프로그램 var : Mysql 의데이터와데이터베이스생성장소, 로그파일 2.9.1.4 시스템부팅시 Mysql 자동실행방법 BSD 계열의시스템에서는 /etc/rc.d/rc.local 파일의제일하단에실행명령어를 2/41 페이지
적어준다. /usr/local/mysql/bin/safe_mysqld & 혹은.. /bin/sh -c `cd /usr/local/mysql ;./bin/fafe_mysqld &` System V 계열에서는 share/mysql 안에 mysql.server 스크립트를이용하면된다. # cd /usr/local/mysql mysql 프로그램생성위치로가서 # bin/mysql_install mysql DB 를생성시켜준다. # cd /usr/local/mysql/share/mysql # vi mysql.server mysql 제어스크립트수정 편집기로열어서 safe_mysql 이실행되는라인에 -- language=korean 옵션을추가 $bindir/safe_mysqld --user=$mysql_daemon_user --pid-file=$pid_file \ --datadir=/usr/local/mysql/var --language=korean & # chmod 700 mysql.server # cp -p mysql.server /usr/bin # cp mysql.server /etc/rc.d/init.d/mysql # ln -s /etc/rc.d/init.d/mysql /etc/rc.d/rc3.d/s95mysqld 이와같이해주면된다. 주의할것은 S95mysqld 에서 95 번호는 85~99 사이에임시로정하되중복되면안된다. 일반적으로 mysql 의시작과중지는그냥.. # mysql.server start # mysql.server stop 이런식으로하면된다. (System V 계열에서만적용 ) 3/41 페이지
2.9.1.5. 바이너리로설치하기 Mysql 에서는소스코드배포이외에각각의플랫폼에맞게소스코드를컴파일하여바이너리형태로 Mysql을배포합니다. 바이너리로설치할경우간편할뿐아니라설치시에도시간을단축할수있습니다. 일반적으로프로그램을설치시는소스로설치하길권장하지만 Mysql 의경우는최적화된 configure 옵션이아니라면바이너리로설치하는것이 20%~30% 정도더빠르다고합니다. 그이유는 Mysql 개발자들이가장최적화된옵션을이용하여바이너리배포본을만들기때문입니다. http://www.mysql.com/downloads/ 에서자신의플랫폼에맞는바이너리를다운로드합니다. # cp Mysql-Version-OS.tar.gz /usr/local // 설치Directory로복사 # tar xzvf Mysql-Version-OS.tar.gz // 압축해제 # mv Mysql-Version-OS mysql // mysql Directory명변경 # cd mysql // mysql Directory 이동 # scripts/mysql_install_db // 기본 DB 생성 # chown -R mysql. /usr/local/mysql/data # bin/safe_mysql -u mysql & // Mysql Daemon 실행 2.9.1.6 Mysql 업그레이드시주의할점 Mysql 을업그레이드하다보면여러가지문제점에부딪히게된다. 이전버젼의데이터와의호환문제와 PHP와 Mysql 혹은 Perl 과 Mysql 연동시기존의 PHP 나 Perl 이문제없이잘실행되는지등의문제가있다. 따라서 Mysql을업그레이드시엔꼭이전버젼의 Mysql Daemon인 mysqld 와라이브러리를백업해두는것이좋다. 문제가발생할땐이전의 mysqld 파일만 libexec/mysqld 로대체해준다. 그럼.. 왠만한거는무난히잘호환될것이다. 이밖에 Perl 프로그램에서문제가생기면새로 DBI 모듈을설치해준다. 기타다른프로그램에서도 4/41 페이지
라이브러리가변경됨으로해서문제가발생할수도있다. 이때는백업해둔라이브러리를 lib 밑에복사함으로해결할수있다. 새로운 Mysql을설치하면서 charset 을변경하였을경우엔기존의테이블들을 # myisamchk -r -q 명령어를이용하여인덱스를변경해주어야한다. 2.9.2 Mysql Log 파일관리하기 Mysql 의로그파일은다음과같이크게 3 종류가있습니다. a. 에러로그 b. 일반적인로그 c. UPDATE 로그 첫번째에러로그는 hostname.err 의이름으로서버실행시에러를기록하는파일입니다. 두번째로그파일은 mysql 에접근하는사용자와그들이파일과관련된쿼리를실행할경우에기록되는로그파일로 /usr/local/mysql/var 밑에 host_name.log 으로저장이되어집니다. Mysql 데이터에파일을기록하므로파일과관련된쿼리는 DB 생성 / 삭제, 테이블생성 / 삭제, 레크드삽입 / 갱신이있습니다. 이로그파일은 Mysql 실행시 --log 옵션을주어활성화시키면된다. # /usr/local/mysql/bin/safe_mysqld --log & 업데이터로그는테이블이변경될때마다해당쿼리가기록됩니다. 기본적으로활성화되지않고 Mysql 실행시 --log-update 옵션으로가능하다. # /usr/local/mysql/bin/safe_mysql --log-update & 업데이터로그는 /usr/local/mysql/var 밑에 host_name.00x 식으로서버가다시실행되거나 mysqladmin reflesh 혹은 mysqladmin flush-logs 명령을내릴때마다뒤의번호가 1씩증가한다. 혹은 --log-update=mysql.log 와같이로그파일명을정해줄수도있다. Update 로그는 update 쿼리만저장하거같지만.. delete, create 등의쿼리도모두저장한다. 5/41 페이지
mysql 의사용량이많은사이트는이런로그파일이쌓이므로해서디스크용량에문제가생길수있다. 관리자는수시로점검하여삭제를해어야한다. 로그파일을관리하는방법으로는두가지가있다. 먼저 /usr/local/mysql/share/mysql/mysql-log-rotate 파일을이용하는방법과간단한스크립트를작성하여 cron 에등록한뒤관리하는방법이있다. /usr/local/mysql/share/mysql/mysql-log-rotate 파일을이용하는방법은.. --log-update=mysqld.log 와같이로그파일을정해서관리할때이용하면된다. # vi /usr/local/mysql/share/mysql/mysql-log-rotate ------------------------------------------------------------------------- # This logname is set in mysql.server.sh that ends up in /etc/rc.d/init.d/mysql # # If the root user has a password you have to create a # /root/.my.cnf configuration file with the following # content: # # [mysqladmin] # password = <secret> # user= root # # where "<secret>" is the password. # # ATTENTION: This /root/.my.cnf should be readable ONLY # for root! /usr/local/mysql/var/mysqld.log { # create 600 mysql mysql notifempty daily rotate 3 missingok 6/41 페이지
} compress postrotate # just if mysqld is really running if test -n "`ps acx grep mysqld`"; then /usr/local/mysql/bin/mysqladmin flush-logs fi endscript ------------------------------------------------------------------------- 위의파일을 /etc/logrotate.d Directory에복사만하면알아서로테이트하게된다. 단.. 로그파일을교체한후 mysqladmin flush-logs 를적용하므로 root 홈Directory에.my.cnf 파일을만든후 MySQL 의 root 사용자의암호와사용자명을적어주어야한다. vi /root/.my.cnf -------------------------------------- [mysqladmin] password = xxxxxxxxx user = root -------------------------------------- 정상적인로그교체의확인은다음과같이하면된다. # logrotate -f mysql-log-rotate 이밖에 --log-update 등의옵션을이용하면수시로로그파일의뒤에 001,002 씩으로번호가증가되면서저장이되므로별도의스크립트를작성하여관리해야한다. 이는각자머리를잘짜면될거같다. #!/bin/sh 7/41 페이지
find /usr/local/mysql/var -name "*.[0-9]*" -type f -mtime +3 -exec rm -f {} \; /usr/local/mysql/bin/mysqladmin flush-logs 위와같은만들면된다. 이는 "3일지난파일은지워라 " 로 cron 에등록한뒤적절한시간마다실행해주면된다. 2.9.3 Mysql 설치시발생문제해결하기 보통솔라리스에서 gcc 를이용하여컴파일하는경우 warning 하나없이설치가가능합니다. 하지만이외의시스템에서는헤더파일이조금씩다르기때문에 warning 메세지가간혹나타나기도한다. 보통컴파일시생기는문제는컴파일단계에서 configure 명령을여러번내릴경우발생합니다. confiure 명령을내리면 configure 의결과를 config.cache파일에저장한다음에다시 configure를실행할때시간을절약하기위하여이전의 config.cache 내용을읽게되는데이때시스템환경이바뀌었을때는이전의 config.cache 내용을읽으므로 error 가나게된다. 이때는 config.cache 파일의유무를확인한후지워버린뒤새로 configure 를실행해준다. 컴파일시생기는다른문제로는 sql_yacc.cc 파일을컴파일하는도중다음과같은에러가나는경우가있다. Internal compiler error: program cclplus got fata signal 11 또는 Out out virtual memory 또는 Virtual memory exhausted. sql_yacc.cc 파일은인라인함수 (Inline Function) 를사용하므로매우많은메모리를필요로합니다. 따라서이문제는대개메모리나스왑영역이부족한경우에생기게됩니다. 이문제를해결하기위해서는 configure 를적용할때다음과같이 --with-low-memory 옵션을주면됩니다. 8/41 페이지
#./configure --with-low-memory GNU make version 이낮을경우생기는에러내용이다. making all in mit-pthreads make: Fatal error in reader: Makefile, line 18: Badly formed macro assignment 또는 make: file 'Makefile' line 18: Must be separator 또는 pthread.h: No such file or directory 이와같은메세지와함께에러가발생하면최신 make 를설치하길바란다. 이밖에 GNU gcc 버젼문제로생기는경우도있다. client/libmysql.c:273 parse error before ' attribute ' GNU gcc version 이 2.8.1 이하인경우는반드시업그레이드하길바란다. 이밖에컴파일시 'C++ compiler connot create executables' 라는메세지가발생하는경우도있는데이는 Mysql 이 C++ 을컴파일러로사용하는데 Mysql 설치하려는시스템에서는 gcc 를 C++ 컴파일러로사용하는경우에발생하는에러입니다. 이문제를해결하기위해서는 g++,libg++,libstdc++ 을설치해야한다. gcc 를 C++ 의컴파일러로그대로사용하는방법도있는데이는 configure 시다음과같이 gcc 를컴파일러로사용하겠다는옵션을적어주면된다. # CXX="gcc -O3"./configure Mysql Daemon 을실행시발생하는대표적인에러는다음과같다. mysqld: Can't find file: 'host.frm' 9/41 페이지
이는 mysql 설치후 DB 를생성을하지않았을때발생한다. mysql 설치후반드시초기 DB 를생성해주어야한다. 다음과같이.. # /usr/local/mysq/bin/mysql_install_db Can't start server: Bind on TCP/IP port: Address already in use 위의메세지는 Mysql 이사용하는 port 를이미다른곳에사용중에있을때발생하는메세지이다. mysql 는기본적으로 3306 포트를사용하는데이포트를다른서비스가사용중이라면다음과같이다른포트를사용하여포트충돌을피해야한다. # /usr/local/mysql/bin/safe_mysqld -P3333 & 이밖에도여러가지에러와문제가발생할수있다. 이렇땐 /usr/local/mysql/var/hostname.err 파일을참조하여문제를해결하면된다. 2.9.4 Mysql 옵션 my.cnf 사용방법 옵션파일은다양한옵션을프로그램실행시지정하지않고파일에저장하여좀더편리하게 Mysql 을사용하도록합니다. 옵션파일에는크게두가지가있는데 ' 서버에관련된옵션 ' 과 ' 개인사용자에게관련된옵션 ' 이렇게두개가있습니다. 옵션파일의위치로는다음과같다. /etc/my.cnf : Mysql 에관련된모든옵션을저장하는옵션파일 DATADIR/my.cnf : Mysql 서버에관련된옵션만지정할수있는옵션 $HOMEDIR/.my.cnf : 개인사용자의옵션을지정할수있는옵션파일 옵션파일사용법 # : 주석을의미합니다. [group] : 옵션을지정할프로그램또는프로그램그룹을지정합니다. 10/41 페이지
[client] : 클라이언트프로그램에해당하는옵션적용 option=value : 커맨드모드에서 mysql --option=value 와동일하게적용 set-variable = variable=value : 커멘트모드에서 --set-variable variable=value 와동일하고 Mysql 서버의각종변수옵션을줄때사용됩니다. 간략한예제입니다. /etc/my.cnf --------------------------------------------------- [client] port=3306 socket=/tmp/mysql.sock [mysqld] port=3306 socket=/tmp/mysql.sock set-variable = key_buffer_size=16m set-variable = max_allowed_packet=1m [mysqldump] quick -------------------------------------------------- $HOMEDIR/.my.cnf -------------------------------------------------- [client] user=user_name password=my_password --------------------------------------------------.my.cnf 는위의예제와같이자신의계정의 Mysql 접속정보가저장된다. 그러므로다른사람에게공개되면안된다. 파일의퍼미션을 600 으로주어서정보유출을막도록한다. 11/41 페이지
$ chmod 600 $HOMEDIR/.my.cnf 2.9.5 Mysql 관리자패스워드관리 MySQL 초기설치시관리자암호는설정되어져있지않다. 실질적으로서비스할경우엔반드시관리자 (root) 암호를설정해야한다. root 암호설정하는방법에는 3 가지가있다. a. UPDATE 문이용하기 b. SET PASSWORD 이용하기 c. mysqladmin 이용하기 UPDATE 문이용하기 $ mysql -u root mysql mysql> update user set password=password('new-passwd') where user='root'; mysql> flush privileges; update 문이용하여암호를변경할땐꼭 flush privileges; 를실행하여변경된내용을적용해야한다. MYSQL 에서사용자권한에관한내용은 MYSQL 실행시메모리에불러놓고이용되는데이에관해변경된내용이있을땐반드시서버에변경된내용을갱신하라는명령을전달해야한다. SET PASSWORD 이용하기 mysql> set password for root=password('new-passwd'); 이방법은 flush privileges 가필요없다. 12/41 페이지
mysqladmin 이용하기 root 암호초기설정시 : $ mysqladmin -u root password new-passwd root 암호변경시 : $ mysqladmin -u root -p password new-passwd Enter password: 2.9.6 Mysql 사용자추가하기 Mysql 를작업하다보면 root 로만작업하진않을것이다. 여러사람에게각각의 DB 를제공하기위해서는반드시각 DB별로사용자를생성해야한다. Mysql 에사용자를추가하는방법에는 GRANT 를이용하는방법과 INSERT 를이용하는방법이있다. $ mysql -u root -p mysql ( 먼저 mysql DB 에접속하자.) Enter password: ******** mysql> grant all privileges on *.* to alang@localhost -> identified by 'password' with grant option; 위의내용은 localhost 의 alang 에게 (to alang@localhost ) 모든테이블에 ( *.* ) 모든권한을 ( all privleges ) 부여하는것을의미한다. 참고로 grant 를이용하여사용자추가할때 flush privileges 를할필요없음. 위의권한을 INSERT 구문으로하면.. mysql> insert into user values('localhost','alang',password 13/41 페이지
('password'), -> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'); mysql> flush privileges; 위와같이한사용자가모든 DB 에접근해선곤란하다. 보통한사용자에게하나의 DB 에만접근가능하게하는것이표준이다. mysql> grant all privileges on test.* to alang@localhost -> identified by 'password'; 웹호스팅업체와같은곳에서사용자와 DB 를추가하는예를들어보다. # mysql -u root -p mysql ( root MYSQL 에접속 ) mysql> create database DB ( 먼저추가될사용자가사용할 DB 생성 ) mysql> grant all privileges on DB.* to USER@localhost -> identified by 'password'; ( 생성된 DB 에 USER 사용자권한주기 ) 이와같은방법으로사용자를추가할수있다. 2.9.7 Mysql 기본 SQL 문사용방법 - 데이타베이스생성과삭제 데이터베이스생성및삭제에대해간단히알아보자. 데이터베이스생성과삭제에관련된 SQL 문은 create 와 drop 다. create database 를통해생성하고, drop database 통해삭제한다. mysql> show databases; +-----------+ Database 14/41 페이지
+-----------+ board shopdb sysmng test test1 +-----------+ 5 rows in set (0.00 sec) 먼저어떤 DB 가있는지살펴본다. mysql> create database test2; Query OK, 1 row affected (0.01 sec) create database 를이용하여 test2 란 DB 를생성했다. 다시 show databases 를이용해확인하자. mysql> show databases; +-----------+ Database +-----------+ board shopdb sysmng test test1 test2 +-----------+ 6 rows in set (0.01 sec) mysql> drop database test2; Query OK, 0 rows affected (0.00 sec) drop database 를통해 test2 DB 를삭제하였다. 15/41 페이지
mysql> show databases; +-----------+ Database +-----------+ board shopdb sysmng test test1 +-----------+ 5 rows in set (0.01 sec) 이밖에데이타베이스의생성과삭제는 mysqladmin 이란명령어를통해서도가능하다. $ mysqladmin -u root -p create test2 (test2 DB 생성 ) $ mysqladmin -u root -p drop test2 (test2 DB 삭제 ) 이와같이데이터베이스를생성하면 /usr/local/mysql/var 에생성된데이터베이스명의 Directory가생성되어진다. 그리고생성된데이터베이스에서테이블을생성하면생성된테이블이름의파일이 3개가만들어지는데다음과같다. 테이블이름.frm : 테이블정의에대한정보테이블이름.ISM : 인덱스관련정보테이블이름.ISD : 데이터내용 이와같이 SQL 문으로인해만들어진데이터베이스는파일로만들어져저장되어진다. - Mysql SQL 기본관리 지금까지다루어온내용을기본으로하여나만의데이터베이스를만들어 16/41 페이지
보도록하겠다. 먼저앞으로사용할 DB 를생성한다. mysql> create database test1; Query OK, 1 row affected (0.00 sec) (test1 이란 DB 를생성하였다. ) 이와같이 DB 를생성하였으면사용하기전에이 DB 에대한사용권한을정해주어야한다. 즉어떤사용자가어떠한권한을이 DB 에서행할수있는지를정해주어야한다. mysql> grant all on test1.* to alang; Query OK, 0 rows affected (0.01 sec) test1 이란 DB 의모든사용권한을 alang 이란사용자에게준다는뜻이다. 데이터베이스를생성하면앞에서배운바와같이 /usr/local/mysql/var 에새로생성한 DB 명과같은 Directory가생길것이다. 확인한후정상적으로생성이되어져있으면이제 DB 내에데이타를입력할준비가완료되어진것이다. 먼저 DB 에데이터를입력하기전에꼭짚고넘어가야할사항이있다. 데이터베이스에서데이터를입력하는데는열과행의형식막? 데이터가입력되어진다. 열을 ' 필드 ' 라고하고행을 ' 레코드 ' 라고부른다. NO NAME EMAIL SEX 1 서진우 alang@clunix.com m 2 박창현 macs911@clunix.com m 3 신상철 scsinn@clunix.com m 4 김승진 jinee@clunix.com m 5 김수경 invoice@clunix.com w 이와같은데이터를입력할려고한다. NO,1,2,3,4,5 와같은열을필드라고한다. 17/41 페이지
1, 서진우,alang@clunix.com,m 와같은행을레코드라고한다. 레코드가많아질수록데이터의중복이발생할우려가생긴다. 만일레코드중고유식별데이터가없다면검색시정확한검색을할수가없다. 그렇기때문에데이터베이스를처음에설계할때반드시고유식별을해줄수있는필드를만들어놓아야한다. 이를데이터베이스에서는 Key(Primary Key) 라고한다. Key 와같은필드엔반드시데이터가들어있어야한다. 반대로필드중정보를공개할수없거나비워두어도되는특성을가진필드엔 NULL 값을가지게된다. 이제이론은그만하고진짜로테이블을생성해보도록한다. 테이블생성은아주쉽다. 각필드에들어가는데이터의자료형과함께 create table 문을적어주면된다. 테이블생성형식 : ------------------------------------- create table 테이블명 ( 필드이름1 (Data형) NOT NULL 필드이름2 (Data형)... PRIMARY KEY ( 필드이름 ); ------------------------------------- NOT NULL 은반드시존재해야할데이터의경우에지정한다. 즉 PRIMARY KEY로사용될필드에대해서는 Date type 가반드시 NOT NULL 이되어야겠다. 진짜로만들어보자. mysql> create table member ( -> NO INT NOT NULL, -> NAME VARCHAR(30), -> EMAIL VARCHAR(50), 18/41 페이지
-> SEX VARCHAR(2), -> PRIMARY KEY (NO)); Query OK, 0 rows affected (0.00 sec) 에러없이처리가무사히되었다면 show table 과 desc 를이용하여테이블이정상적으로생성이되었는지를확인하자. mysql> show tables; +-----------------+ Tables_in_test1 +-----------------+ member +-----------------+ 1 row in set (0.00 sec) mysql> desc member; +-------+-------------+------+-----+---------+-------+ Field Type Null Key Default Extra +-------+-------------+------+-----+---------+-------+ NO int(11) PRI 0 NAME varchar(30) YES NULL EMAIL varchar(50) YES NULL SEX varchar(2) YES NULL +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) 생성이완료되었다. 생성된테이블을삭제할때는아래와같은방식으로삭제할수있다. mysql> drop table member; Query OK, 0 rows affected (0.00 sec) mysql> show tables; Empty set (0.00 sec) 19/41 페이지
Member 테이블이삭제된것을확인할수있을것이다. 하지만앞으로의데이터입력구문을배우기위해서는테이블이꼭필요하다. 테이블생성시컬럼에해당하는데이터형 ( 속성 ) 을지정해야한다. 데이터형으로너무나많은종류가있는데시스템관리시에는대표적인몇가지만알아두면된다. INT : 4byte 정수 CHAR (M) : M개의문자 VARCHAR (M) : 최대 M개를넘지않는문자 TEXT : 최대 65535 개의문자 그럼다음엔데이터입력에대해살펴보도록하겠다. - 데이터입력 테이블에데이터를입력하는방법엔 insert 문과 load data 문을사용할수있다. 먼저 insert 문을이용하여데이터를입력해보도록한다. 형식은아래와같다. 형식 ) insert into 테이블명 ( 필드명1, 필드명2,...) values (" 데이터1"," 데이터2",..); 실제로입력해보자. mysql> insert into member (NO,NAME,EMAIL,SEX) values -> ("1"," 서진우 ","alang@clunix.com","m"); Query OK, 1 row affected (0.00 sec) 별다른에러없이처리가되었으면재대로입력이되었는지살펴보자. 테이블의내용을검색하는데는앞에언급한바와같이 select 구문을이용한다. 형식 ) select 검색필드명.. from 테이블명 20/41 페이지
mysql> select NO,NAME,EMAIL,SEX from member; +----+--------+-----------------+------+ NO NAME EMAIL SEX +----+--------+-----------------+------+ 1 서진우 alang@clunix.com m +----+--------+-----------------+------+ 1 row in set (0.00 sec) select 구문에서모든필드값을검색할때아스키문자를사용할수있다. mysql> select * from member; +----+--------+-----------------+------+ NO NAME EMAIL SEX +----+--------+-----------------+------+ 1 서진우 alang@clunix.com m +----+--------+-----------------+------+ 1 row in set (0.00 sec) load data 는데이터베이스테이블을초기화할때주로사용되어진다. 먼저입력할데이터를필드와레코드에맞주어정렬하여 text 파일로만들어놓는다. member.txt ---------------------------------------------- 0 서진우 alang@clunix.com m 1 박창현 macs911@clunix.com m 2 신상철 scsinn@clunix.com m 3 김승진 jinee@clunix.com m 4 김수경 invoice@clunix.com w ---------------------------------------------- 텍스트파일을만들때반드시 " 방향키 (->)" 를사용하여데이터를구분해야한다. space bar 를사용해선절대안된다. 명심해야한다. 그리고마지막행에공백이있어서도안된다. 이점만명심하고데이터파일을만들면된 21/41 페이지
다. 리눅스의 vi 편집기로작성할때는 tab 을사용하여구분하면됩니다. mysql> load data local infile "member.txt" into table member; mysql> select * from member; +----+--------+-------------------+------+ NO NAME EMAIL SEX +----+--------+-------------------+------+ 0 서진우 alang@clunix.com m 1 박창현 macs911@clunix.com m 2 신상철 scsinn@clunix.com m 3 김승진 jinee@clunix.com m 4 김수경 invoice@clunix.com w +----+--------+-------------------+------+ 이와같이많은데이터를한번에입력할수가있다. - 데이터수정하기 저장된데이터에잘못된부분이있어서수정할경우에도두가지방법이있다. delete 구문을이용하여테이블에입력된모든데이터를지워버리고 load data 문을이용하여다시입력하는방법과 update 문을이용하는방법이있다. 먼저 delete 와 load data 를이용하는방법에대해알아보자. mysql> delete from member; mysql> load data local infile "member.txt" into table member; 형식 ) delete from 테이블명 ; 위와같이명령을하면테이블내의모든데이터가삭제된다. where 구문을이용하여부분적으로삭제도가능하나, where 구문은나중에다루도록하겠다. 이제 update 문을이용하는방법을알아보자. 22/41 페이지
형식 ) update 테이블명 set 해당필드명 =" 수정데이터 " where 조건문 mysql> update member set EMAIL="alang at sysmng.com" -> where NO="0"; Query OK, 1 row affected (0.00 sec) 일치하는 Rows : 1개변경됨 : 1개경고 : 0개 위의구문은 NO 가 0 인필드에서 EMAIL 값을 alang at sysmng.com 으로변경한다는의미를갖는다. 재대로변경이되었는지확인을해보도록하자. mysql> select * from member; +----+--------+-------------------+------+ NO NAME EMAIL SEX +----+--------+-------------------+------+ 0 서진우 alang@sysmng.com m 1 박창현 macs911@clunix.com m 2 신상철 scsinn@clunix.com m 3 김승진 jinee@clunix.com m 4 김수경 invoice@clunix.com w +----+--------+-------------------+------+ 5 rows in set (0.00 sec) 이로써데이터변경에대해서마치도록하겠습니다. 다음편엔 select 구문에대해서보다자세하게알아보도록하겠습니다. - SELECT 문사용하기 데이터양이많이지면.. 막연히 [ select * from table_name ] 와같이검색 23/41 페이지
하면.. 눈과기억력이무척좋아야겠죠..!! 그렇기때문에대량의 DB 를검색할땐자신이원하는내용만을검색할수있는 select 구문을이용하여야합니다. 이제 select 구문과같이사용할수있는조건문에대해알아보도록하겠습니다. a. 원하는행만검색하기 원하는행검색이란.. 검색하고자하는컬럼값을포함하는행만을검색하도록하는방법이다. 일단 member 테이블의모든내용을검색합니다. mysql> select * from member; NO NAME EMAIL SEX AREA HOB 0 서진우 alang@clunix.com m 서울 운동 1 박창현 macs911@clunix.com m 서울 운동 2 김수경 invoice@clunix.com w 서울 컴퓨터 3 신상철 ttt@clunix.com m 경기 컴퓨터 4 김승진 bbb@clunix.com m 서울 컴퓨터 5 이필유 ccc@clunix.com w 경기 요리 6 우서준 ddd@clunix.com m 서울 운동 7 rows in set (0.00 sec) 이번엔회원중여자분만을검색하도록하겠습니다. mysql> select * from member where sex='w'; NO NAME EMAIL SEX AREA HOB 2 김수경 invoice@clunix.com w 서울 컴퓨터 5 이필유 ccc@clunix.com w 경기 요리 24/41 페이지
2 rows in set (0.00 sec) 여기서 where 구문을이용하여원하는행만을검색할수있는예제이다. 위의구문을풀이하면 member 란테이블에서 sex 컬럼값이 w 인행만검색하란의미이다. b. 원하는열만검색하기 이번엔원하는열만검색하는방법이다. 원하는열검색이란.. 검색하고자하는컬럼값만을출력하도록하는것이다. 즉만일회원들의이름만검색하고자한다. 혹은회원들의 email 만검색하고자한다.. 이럴경우사용하는방법이다. 컬럼중 NAME 컬럼열만검색 mysql> select name from member; +--------+ name +--------+ 서진우 박창현 김수경 신상철 김승진 이필유 우서준 +--------+ 7 rows in set (0.00 sec) 컬럼중 EMAIL 컬럼열만검색 mysql> select email from member; 25/41 페이지
+-------------------+ email +-------------------+ alang@clunix.com macs911@clunix.com invoice@clunix.com ttt@clunix.com bbb@clunix.com ccc@clunix.com ddd@clunix.com +-------------------+ 7 rows in set (0.00 sec) 컬럼중 NAME 과 EMAIL 만검색 mysql> select name,email from member; +--------+-------------------+ name email +--------+-------------------+ 서진우 alang@clunix.com 박창현 macs911@clunix.com 김수경 invoice@clunix.com 신상철 ttt@clunix.com 김승진 bbb@clunix.com 이필유 ccc@clunix.com 우서준 ddd@clunix.com +--------+-------------------+ 위와같이 [ select 검색컬럼명 from 테이블명 ] 형식으로원하는열만검색할수있다. 원하는컬럼가여러개일경우는 (,) 로구분하면된다. - 고급 SELECT 문사용하기.. 26/41 페이지
a. 중복행제거 각컬럼에서중복된행을없애기위하여 select 구문의 " 검색컬럼명 " 앞에 distinct 를넣어주면된다. mysql> select distinct sex from member; +------+ sex +------+ m w +------+ sex 컬럼값에중복된행을제거하고대표대는값만을출력하게된다. 수학의교집합에해당하는것이다. mysql> select distinct sex,name from member; +------+--------+ sex name +------+--------+ m 서진우 m 박창현 w 김수경 m 신상철 m 김승진 w 이필유 m 우서준 +------+--------+ 위의예문은 distinct 를이용하여컬럼값을그룹별로묶어서출력하는예이다. b. where 구문에서논리연산과관련연산사용하기 만일 where 구문사용에서여러가지연산구문을이용하여다양한검색을행할수 27/41 페이지
있다. 사용되는기호엔 or, and, =,!=, <, > 등이있다. no 컬럼값이 1 혹은 3 인행을검색하는구문예이다. mysql> select * from member where no='1' or no='3'; +----+--------+-------------------+------+ NO NAME EMAIL SEX +----+--------+-------------------+------+ 1 박창현 macs911@clunix.com m 3 김승진 jinee@clunix.com m +----+--------+-------------------+------+ no 컬럼값이 1 보다크고 4 보다작은행은검색하는구문예이다. mysql> select * from member where no < 4 and no > 1 ; +----+--------+------------------+------+ NO NAME EMAIL SEX +----+--------+------------------+------+ 2 신상철 scsinn@clunix.com m 3 김승진 jinee@clunix.com m +----+--------+------------------+------+ 2 rows in set (0.00 sec) no 컬럼값이 1 인필드를제외한나머지필드를검색한다. mysql> select * from member where no!= '1'; NO NAME EMAIL SEX AREA HOB 0 서진우 alang@clunix.com m 서울 운동 2 김수경 invoice@clunix.com w 서울 컴퓨터 3 신상철 ttt@clunix.com m 경기 컴퓨터 4 김승진 bbb@clunix.com m 서울 컴퓨터 5 이필유 ccc@clunix.com w 경기 요리 6 우서준 ddd@clunix.com m 서울 운동 28/41 페이지
6 rows in set (0.00 sec) c. 내림차순, 오름차순정렬검색 DB 를검색하다보면테이블에저장된값들을컬럼별로정렬해서출력해야할경우가생긴다. 이때사용되는것이 [ ORDER BY ] 이다. ORDER BY 컬럼명 ASC : 내림차순 ( 정 ) ORDER BY 컬럼명 DESC : 오름차순 ( 역 ) 내림차순에서 ASC 는생략가능하다. 차순은영문은 abc... 한글은ㄱ, ㄴ, ㄷ.. 숫자는 1,2,3.. 순으로정해진다. mysql> select * from member order by no asc; NO NAME EMAIL SEX AREA HOB 0 서진우 alang@clunix.com m 서울 운동 1 박창현 macs911@clunix.com m 서울 운동 2 김수경 invoice@clunix.com w 서울 컴퓨터 3 신상철 ttt@clunix.com m 경기 컴퓨터 4 김승진 bbb@clunix.com m 서울 컴퓨터 5 이필유 ccc@clunix.com w 경기 요리 6 우서준 ddd@clunix.com m 서울 운동 7 rows in set (0.00 sec) mysql> select * from member order by sex asc; mysql> select * from member order by sex asc; NO NAME EMAIL SEX AREA HOB 29/41 페이지
0 서진우 alang@clunix.com m 서울 운동 1 박창현 macs911@clunix.com m 서울 운동 3 신상철 ttt@clunix.com m 경기 컴퓨터 4 김승진 bbb@clunix.com m 서울 컴퓨터 6 우서준 ddd@clunix.com m 서울 운동 2 김수경 invoice@clunix.com w 서울 컴퓨터 5 이필유 ccc@clunix.com w 경기 요리 7 rows in set (0.00 sec) mysql> select * from member order by name; NO NAME EMAIL SEX AREA HOB 2 김수경 invoice@clunix.com w 서울 컴퓨터 4 김승진 bbb@clunix.com m 서울 컴퓨터 1 박창현 macs911@clunix.com m 서울 운동 0 서진우 alang@clunix.com m 서울 운동 3 신상철 ttt@clunix.com m 경기 컴퓨터 6 우서준 ddd@clunix.com m 서울 운동 5 이필유 ccc@clunix.com w 경기 요리 7 rows in set (0.00 sec) 위의예제들은각각 no,sex,name 컬럼들을내림차순으로정렬한것이다. 다음예는오름차순으로정렬을해보도록하자. mysql> select * from member order by no desc; NO NAME EMAIL SEX AREA HOB 6 우서준 ddd@clunix.com m 서울 운동 5 이필유 ccc@clunix.com w 경기 요리 4 김승진 bbb@clunix.com m 서울 컴퓨터 3 신상철 ttt@clunix.com m 경기 컴퓨터 30/41 페이지
2 김수경 invoice@clunix.com w 서울 컴퓨터 1 박창현 macs911@clunix.com m 서울 운동 0 서진우 alang@clunix.com m 서울 운동 7 rows in set (0.00 sec) mysql> select * from member order by sex desc; NO NAME EMAIL SEX AREA HOB 2 김수경 invoice@clunix.com w 서울 컴퓨터 5 이필유 ccc@clunix.com w 경기 요리 0 서진우 alang@clunix.com m 서울 운동 1 박창현 macs911@clunix.com m 서울 운동 3 신상철 ttt@clunix.com m 경기 컴퓨터 4 김승진 bbb@clunix.com m 서울 컴퓨터 6 우서준 ddd@clunix.com m 서울 운동 7 rows in set (0.01 sec) mysql> select * from member order by name desc; NO NAME EMAIL SEX AREA HOB 5 이필유 ccc@clunix.com w 경기 요리 6 우서준 ddd@clunix.com m 서울 운동 3 신상철 ttt@clunix.com m 경기 컴퓨터 0 서진우 alang@clunix.com m 서울 운동 1 박창현 macs911@clunix.com m 서울 운동 4 김승진 bbb@clunix.com m 서울 컴퓨터 2 김수경 invoice@clunix.com w 서울 7 rows in set (0.00 sec) - ALTER 구문사용하기 31/41 페이지
a. 필드추가하기 더많은데이터가증가되면서필드의개수를추가해야할경우가종종발생하게된다. 이때사용되는구문이 alter 구문이다. mysql> select * from member; NO NAME EMAIL SEX AREA HOB 0 서진우 alang@clunix.com m 서울 운동 1 박창현 macs911@clunix.com m 서울 운동 2 김수경 invoice@clunix.com w 서울 컴퓨터 3 신상철 ttt@clunix.com m 경기 컴퓨터 4 김승진 bbb@clunix.com m 서울 컴퓨터 5 이필유 ccc@clunix.com w 경기 요리 6 우서준 ddd@clunix.com m 서울 운동 7 rows in set (0.00 sec) 위의출력문과같이현재 no,name,email,sex,area,bob 의필드로구성된테이블이 member 이다. 여기에나이에관련된 age 필드를추가한다고가정하면.. mysql> alter table member add age varchar(3); Query OK, 7 rows affected (0.00 sec) 레코드 : 7개중복 : 0개경고 : 0개 mysql> select * from member; ------+ NO NAME EMAIL SEX AREA HOB age ------+ 0 서진우 alang@clunix.com m 서울 운동 NULL 1 박창현 macs911@clunix.com m 서울 운동 NULL 2 김수경 invoice@clunix.com w 서울 컴퓨터 NULL 32/41 페이지
3 신상철 ttt@clunix.com m 경기 컴퓨터 NULL 4 김승진 bbb@clunix.com m 서울 컴퓨터 NULL 5 이필유 ccc@clunix.com w 경기 요리 NULL 6 우서준 ddd@clunix.com m 서울 운동 NULL ------+ 7 rows in set (0.00 sec) 위와같이 [ alter table member add age varchar(3) ] 구문을사용하면된다. alter 구문의형식은다음과같다. 형식 ) ALTER TABLE [ 테이블이름 ] ADD [ 추가필드명 ] [ 변수타입 ] 위의예제에서보는바와같이 alter table 필드를추가하면행당필드값은무조건 NULL 로표시된다. b. 필드삭제하기 필요없는필드를제거하는것도 alter 구문으로한다. 형식은다음과같다. 형식 ) ALTER TABLE [ 테이블이름 ] DROP [ 삭제필드명 ] mysql> alter table member drop age; Query OK, 7 rows affected (0.00 sec) 레코드 : 7개중복 : 0개경고 : 0개 mysql> select * from member; NO NAME EMAIL SEX AREA HOB 0 서진우 alang@clunix.com m 서울 운동 33/41 페이지
1 박창현 macs911@clunix.com m 서울 운동 2 김수경 invoice@clunix.com w 서울 컴퓨터 3 신상철 ttt@clunix.com m 경기 컴퓨터 4 김승진 bbb@clunix.com m 서울 컴퓨터 5 이필유 ccc@clunix.com w 경기 요리 6 우서준 ddd@clunix.com m 서울 운동 7 rows in set (0.00 sec) c. 필드정보변경하기 필드속성을변경할때도 alter 구문을이용한다. 형식은다음과같다. 형식 ) ALTER TABLE [ 테이블이름 ] CHANGE [ 현재필드명 ] [ 변경할필드명 ] [ 변수타입 ] 예 ) 필드명변경 먼저 member 테이블정보를확인하자. mysql> desc member; +-------+-------------+------+-----+---------+-------+ Field Type Null Key Default Extra +-------+-------------+------+-----+---------+-------+ NO int(11) PRI 0 NAME varchar(30) YES NULL EMAIL varchar(50) YES NULL SEX char(2) YES NULL AREA varchar(8) YES NULL HOB varchar(10) YES NULL age char(3) YES NULL +-------+-------------+------+-----+---------+-------+ 7 rows in set (0.00 sec) 34/41 페이지
age 필드명을 " 나이 " 로바꾸어보자. mysql> alter table member change age 나이 varchar(3); Query OK, 7 rows affected (0.01 sec) 레코드 : 7개중복 : 0개경고 : 0개 확인하자. mysql> desc member; +-------+-------------+------+-----+---------+-------+ Field Type Null Key Default Extra +-------+-------------+------+-----+---------+-------+ NO int(11) PRI 0 NAME varchar(30) YES NULL EMAIL varchar(50) YES NULL SEX char(2) YES NULL AREA varchar(8) YES NULL HOB varchar(10) YES NULL 나이 char(3) YES NULL +-------+-------------+------+-----+---------+-------+ 7 rows in set (0.00 sec) 예 ) 변수타입변경 mysql> alter table member change 나이나이 varchar(10); Query OK, 7 rows affected (0.00 sec) 레코드 : 7개중복 : 0개경고 : 0개 mysql> desc member; +-------+-------------+------+-----+---------+-------+ Field Type Null Key Default Extra +-------+-------------+------+-----+---------+-------+ NO int(11) PRI 0 NAME varchar(30) YES NULL EMAIL varchar(50) YES NULL 35/41 페이지
SEX char(2) YES NULL AREA varchar(8) YES NULL HOB varchar(10) YES NULL 나이 varchar(10) YES NULL +-------+-------------+------+-----+---------+-------+ 7 rows in set (0.00 sec) 예 ) 필드명과변수타입변경 mysql> alter table member change 나이 age varchar(3); Query OK, 7 rows affected (0.00 sec) 레코드 : 7개중복 : 0개경고 : 0개 mysql> desc member; +-------+-------------+------+-----+---------+-------+ Field Type Null Key Default Extra +-------+-------------+------+-----+---------+-------+ NO int(11) PRI 0 NAME varchar(30) YES NULL EMAIL varchar(50) YES NULL SEX char(2) YES NULL AREA varchar(8) YES NULL HOB varchar(10) YES NULL age char(3) YES NULL +-------+-------------+------+-----+---------+-------+ 7 rows in set (0.00 sec) 이것을 Mysql 관리에필요한기본 SQL 문설명을마치도록하겠습니다. 다음은 Mysql DB 백업및복구에대해알아보겠습니다. 2.9.8 Mysql DB 백업및복구 Database 에서백업과복구는매우중요한부분이다. 시스템불안및외부영향으로데이타에손상이갔을때백업이재대로되어 36/41 페이지
있지않을경우정말막연할것이다. 이밖에서버를이전한다던지기타한꺼번에 DB 전체를재갱신할때백업과복구에관련된기술은필수적이라할수있다. Mysql 에서백업명령어로대표적인것은 mysqldump 를들수있다. 2.9.8.1 백업하기 형식 ) mysqldump -u [ DB user ] -p [ password ] [ DB_name ] > backup_file_name [alang@arhdev alang]$ mysqldump -u alang -p test1 > test.dat Enter password: 위와같이 Mysql 백업을할수가있다. 백업된파일의내용을보도록하자. [alang@arhdev alang]$ vi test.dat --------------------------------------------------------------------- # MySQL dump 8.13 # # Host: localhost Database: test1 #-------------------------------------------------------- # Server version 3.23.37 # # Table structure for table 'member' # CREATE TABLE member ( NO int(11) NOT NULL default '0', NAME varchar(30) default NULL, EMAIL varchar(50) default NULL, SEX char(2) default NULL, AREA varchar(8) default NULL, 37/41 페이지
HOB varchar(10) default NULL, PRIMARY KEY (NO) ) TYPE=MyISAM; # # Dumping data for table 'member' # INSERT INTO member VALUES (5,' 이필유 ','ccc@clunix.com','w',' 경기 ',' 요리 '); INSERT INTO member VALUES (6,' 우서준 ','ddd@clunix.com','m',' 서울 ',' 운동 '); -------------------------------------------------------------------------- 이와같이 DB 의내용이 dump 되어져서 text 파일로만들어져있으면백업이무사히되어진것이다. 만일대용량의 DB 를전체적으로백업하기엔부담스러울수도있다. 그리고필요한부분은일부분인데전체로백업하는것도힘들것이다. 하지만이런문제로크게걱정할필요는없다. mysqldump 는원하는 table 만백업할수도있다. 형식 ) mysqldump -u [DB_user] -p [password] [DB_name] [table_name] > backup_file_name [alang@arhdev alang]$ mysqldump -u alang -p test1 member > member.sql 위구문은 test1 DB 의 member 테이블만을 member.sql 파일로백업하는구문이다. 2.9.8.2. 백업파일복구하기 백업파일을복구하는방법은매우간단하다. 앞에 mysql 접속하는방법과매우유사하다. 형식 ) mysql -u [ DB_ser ] -p [ DB_name ] < backup_file_name 38/41 페이지
예 ) mysql -u alang -p test2 < member.sql 위와같이백업파일을복구할수가있습니다. 2.9.9 Mysql Replication 으로엔터프라이즈 Mysql 구축하기 - svr1, svr2 서버에 mysql 을버전은 3.23 이상으로설치한다. - mysql replication 설정하기 svr1 서버를 master 서버로지정함. svr2 서버를 slaver 서버로지정함. - master 서버에 DB 복제를해주는역할의 User 를만든다. mysql > GRANT FILE ON *.* TO cluster@"%" IDENTIFIED BY '<password>'; 여기서 % 대신에 slave 서버주소를적어주어도상관없음. master 의 /etc/my.cnf ------------------------------------------------------------------- [mysqld] log-bin binlog-do-db=syszone server-id=1 ------------------------------------------------------------------- binlog-do-db='db 이름 ' slaver 의 /etc/my.cnf ------------------------------------------------------------------- 39/41 페이지
[mysqld] server-id=2 master-host=192.168.133.165 master-user=repli master-password=root/// master-port=3306 ------------------------------------------------------------------- master-user=repli 가잘안되면그냥 root 로한다. 단..root 역시원격 DB 접속을가능토록해주어야한다. mysql> select User,Host from user; +---------+-----------+ User Host +---------+-----------+ root % www % localhost root localhost sahak21 localhost www localhost www svr2 +---------+-----------+ svr1, svr2 서버의 mysql deamon 을차례로 start 시킨다. 확인절차 --master server ( svr1 ) mysql > show master status; +--------------+----------+--------------+------------------+ File Position Binlog_do_db Binlog_ignore_db +--------------+----------+--------------+------------------+ svr1-bin.023 79 syszone +--------------+----------+--------------+------------------+ 40/41 페이지
확인절차 --slave server ( svr2 ) Master_Host 192.168.133.165 Master_User root Master_Port 3306 Connect_retry 60 Master_Log_File svr1-bin.023 Read_Master_Log_Pos 79 Relay_Log_File svr2-relay-bin.025 Relay_Log_Pos 271 Relay_Master_Log_File svr1-bin.023 Slave_IO_Running Yes Slave_SQL_Running Yes Replicate_do_db Replicate_ignore_db Last_errno 0 Last_error Skip_counter 0 Exec_master_log_pos 79 Relay_log_space 271 여기서 master pos 에관련된것이 master status 의 position 값과일치해야한다. 이제 Mysql master 서버에 insert 작업을한후 slave 서버에서확인을해보면역시데이터가추가되었을것이다. 41/41 페이지