12 단원 MySQL 클라이언트사용법 1. 접속과종료 2. 사용자추가 3. 테이블생성과관리 4. 백업과복구 1/17
1. 접속과종료 1) MySQL 접속 MySQL 의모든실행파일은 MySQL 이설치된디렉토리의밑에 bin 디렉토리안에모여있습니다. bin 폴더안의 mysql.exe 파일로우리는 MySQL 에접속이가능합니다. mysql u root p 라고명령어를치고엔터를누릅니다. 그러면 Enter password: 라는문구가보입니다. 정확한패스워드를입력하고엔터키를누릅니다. 그러면화면과같은문구와커서가생기면 MySQL 에정상적으로접속하신겁니다. 방금실행한 MySQL 접속명령어의의미를살펴보겠습니다. -u root 는 mysql 에접속하기위한계정이 root 라고알려주는것입니다. -u 는접속할계정을알려주는것이고, -p 는패스워드를의미하는것입니다. 계정은누구나봐도상관없으므로그냥입력하지만, 패스워드는비밀을보장하기위해다음단계에서쓰는것입니다. 그렇지않으면패스워드가있는그대로찍혀서다볼수있을테니깐말입니다. -p 뒤에사용자전용데이터베이스를써줄수가있습니다. 뒤에안쓰게되면접속후에반드시데이터베이스를선택하셔합니다. 2/17
정상적인접속후에 mysql> 프롬프트가나타나면명령을받아들일준비가되었다는뜻입니다. 프롬프트의모양을보고어느형태의값을입력중인지알수있습니다. 다음은 MySQL 의 bin 디렉토리에존재하는실행파일들중중요하다고판단되는것들과이들의역할에대한설명입니다. 실행파일 역할 mysql MySQl 서버에접속하여 SQL 을이용해 DB 에질의를합니다. mysqladmin MySQL 서버를관리하는프로그램, 데이터베이스생성, 삭제, 권한 설정, 테이블을읽어들이고각종변경사항을반영시킵니다. mysqld MySQL 데몬입니다. mysqlshow MySQL 안에있는데이터베이스와테이블, 컬럼을표시해줍니다. isamchk 테이블이상발생시이를수정하고, 테이블을최적화합니다. (2) MySQL 종료 MySQL 클라이언트사용을마친후끝내려면 exit 나 quit 또는 q 를입력합니다. (3) 기본명령어이번에는 MySQL에있는중요정보를조회할수있는몇가지기본명령에를살펴보겠습니다. - show databases : 데이터베이스의모든목록을보여줍니다. 3/17
그러나, 일반유저로접속하시면접속한사용자의 DB 만보여지게됩니다. - use database : 사용자가특정한 DB 를사용한다는것을알려주는것입니다. 접속을하실때, DB 를선택하지않으셨다면, 접속후에반드시선택을하셔야테이블을만들수있습니다. - show tables : 사용자가선택한 DB 에들어있는 Table 들의리스트를볼수있습니다. 4/17
- desc table_name : 특정테이블에대한 table schema 를볼수있습니다. 5/17
2. 사용자추가 데이터베이스를설치하시면 root 만이사용할수있도록되어있습니다. 그러나사이트작업을하실때에는 root 계정을이용하면좋지못합니다. 왜냐하면, root 계정이아무에게나알려지는것은많은데이터베이스에문제가될것이며, 환경설정이못되면복구가불가능해질수도있습니다. 이제부터사용자추가를해보기로하겠습니다. 권한관리는 root( 수퍼유저 ) 만가능하며, 이계정만접속가능한 mysql 데이터베이스에접속하여 user 와 db 라는테이블에필요한값을등록하면됩니다. 먼저, telnet 으로서버에접속합니다. 우린접속방식을 ssh 로하고있습니다여기서 telnet 과 ssh 에대해간단하게설명을들이자면, 서버에접속하기위해정보를전달하는방식이다릅니다. telnet 은비암호화해서계정이나비밀번호를전달하게되구요, ssh 는암호화해서정보를서버에전달하게됩니다. 즉, ssh 가 telnet 보다보안성이있다고보시면됩니다. 6/17
여기서는 putty 라는접속툴을이용해서접속해보도록하겠습니다. 그래서지금 Host Name 에 IP 를적어주시구요, Protocal 선택을 ssh 로해주세요. 그리고앞으로자주사용할것이기때문에, Saved Sessions 에이름을정하시고오른쪽중간에 save 버튼으로저장을해주세요. 그리고하단오른쪽에 open 을하시면로그인을하라는문구가생깁니다. 리눅스서버에등록된 root 계정으로들어가시구요, 리눅스서버에접속을하시게되면 MySQL 의 root 계정으로접속해주십시오. 사용자는크게 MySQL server 에관한권한과 Database 에관한권한으로나뉩니다. Server 에관한권한은 'user' table 에있고, Database 에관한권한은 'db' table 에있습니다. 일반사용자는 login 만가능하도록 user table 에서권한을모두 'N' 으로설정합니다. 그리고 db table 에서각 Database 에대해서접근권한을설정합니다. mysql> insert into user(host,user,password) values ( 'localhost', 'myuser', password('mypasswd')); user 에는여러가지필드가있으나 3 가지만등록시키면됩니다. 이유는나머지들은권한설정부분인데 default 값으로 N 라고설정되어있으므로따로주시지않아도무관합니다. 7/17
mysql> insert into db values ( 'localhost', 'myuser', 'myuser', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y'); 조금전에등록시킨사용자에대한데이터베이스에권한을설정해주는것입니다. 자신의디비에대해서는수퍼유저와동일하므로권한을모두 'Y' 로주고있습니다. 이제가상적으로계정에게데이터베이스를주었으니, 실제적으로 MYUSER 라는데이터베이스를만들어주어야합니다. create database myuser; ( 엔터 ) 하시면데이터베이스가만들어집니다. 권한을설정하는또다른방법으로 grant 명령을이용하는방법이있습니다. GRANT 형식은다음과같습니다. $mysql>grant 주고자하는권한 PRIVILEGES ON DB 명. 테이블명 TO 계정명 @ 호스트명 IDENTIFIED BY 비밀번호 WITH GRANTOPTION; 위에서주고자하는권한이란, select, insert, delete 등과같은권한을의미합니다. DB 명은해당계정이사용할수있는데이터베이스명을등록하는것입니다. 정의된 db 의모든테이블에대해정의된권한을주고자할경우엔 * 로모든테이블을표현합니다. 예를들어 myuser 계정에게 myuserdb 데이터베이스와 myuserdb 에포함된모든테이블에대해모든권한을주고, myuser 계정이 1234 라는비밀번호를가지고있는경우엔다음과같이적용할수있습니다. mysql> GRANT ALL PRIVILEGES ON myuserdb.* TO myuser@localhost IDENTIFIED BY "1234" WITH GRANTIOPTN. 그러나직접 root 접속해서사용자를추가시켰다고하더라도주의해야할사항중에하나는권한테이블이변경되었다하더라도그변경사항이바로적용되는것이아니라는것입니다. 그래서 MySQL 서버를재기동하던지, FLUSH PRIVILEGES 라는명령을실행해야합니다. 대부분의경우 FLUSH PRIVILEGES 명령을실행하는방법을사용합니다. 8/17
그런다음마지막으로변경된정보에대한적용이필요합니다. msyql>flush privileges; ( 엔터 ) 혹은다음과같이명령을주어도가능합니다. $mysqladmin -u root reload 이렇게하시고접속을끊으신다음에이번엔 root 가아니라계정으로로그인해보세요. mysql u myuser p ( 엔터 ) mysql> show databases; ( 엔터 ) 하셨을때, myuser 라는 DB 가보이시면정상적으로사용자가추가가된것입니다. 3. 테이블만들기및수정하기, 삭제하기 이번시간에는 MySQL 에서테이블을만들고수정, 삭제하는방법을알아보겠습니다. 1) 테이블만들기 대부분의 datebase 들은 SQL 이란명령어를써서작업을하게되는데기본적인명령어들은모두같습니다. 테이블을만드는명령어는 create table 이라고하시면됩니다. 형식은아래와같습니다. CREATE TABLE 테이블이름 ( 이름자료형 ( 크기 ) 이름자료형 ( 크기 )... ); 9/17
예를들어게시판에대한테이블을만들어봅시다. CREATE TABLE bbs ( idx int(4) auto_increment, name varchar(30), title varchar(150), content text, wdate varchar(30), hit int(4), primary key(idx)); 위와같이각항목은,( 쉼표 ) 로구분해주시면됩니다. 위에형식에서보았듯이, 현재게시판을위한테이블에각각의자료형과크기가들어가있습니다. 자료형이나크기란입력할값이문자이냐숫자이냐를묻는것입니다. 크기는몇글자짜리문자이냐몇자리숫자이냐를써주시면됩니다. ======================= < 참고하기 자료형 > - 정수형 TINYINT: 정수형 (-128~127) [UNSIGNED]-정수형(0~255) 10/17
SMALLINT: 정수형 (-32768~32767) [UNSIGNED]-정수형(0~65535) MEDIUMINT: 정수형 (-8388606~8388607)[UNSIGNED]-정수형(0~16777215) INT: 정수형 (-2147483648~2147483647)[UNSIGNED]-정수형(0~4294967295) BIGINT 정수형 (-9223372036854775808~9223372036854775807) [UNSIGNED]- 정수형 (0~18446744073709551615) - 실수형 FLOAT( 정밀도 ) : 부동소수점실수 DOUBLE 배정도부동소수점실수 (-1.7976931348623157E+308 ~ -2.2250738585072014E- 308,0,2.2250738585072014E-308 ~ 1.7976931348623157E+308) - 날짜형 DATE 날짜형 (1000-01-01 ~ 9999-12-31) DATETIME 날짜와시간형 (1000-01-01 00:00:00 ~ 9999-12-31 23:59:59) TIME 시간형 (-838:59:59 ~ 838:59:59) - 문자열 CHAR : 고정폭문자열 VARCHAR : 가변폭문자열 TINYBLOB / TINYTEXT : BOLB, TEXT 형, 최대길이 255 문자 BLOB / TEXT : BOLB, TEXT 형, 최대길이 65535 문자 MEDIUMBLOB / MEDIUMTEXT : BOLB, TEXT 형, 최대길이 16777215 문자 LONGBLOB / LONGTEXT : BOLB, TEXT 형, 최대길이 4294967295 문자 - ENUM 문자열목록형, 최대 65535 개, 저장된문자열목록중에오직한가지만얻을수있습니다. - SET : 문자열목록형, 최대 64 개, 저장된문자열목록중에 0, 1 개이상을얻을수있습니다 =================== 일반적으로많이사용하는자료형으로는 int 와 varchar, 그리고 text 가있습니다. int 는정수형으로최대크기는 11 입니다. 11/17
int(11) 이라고하면 11 자리정수를뜻하며, 숫자를넣을때사용합니다. varchar 는문자형으로최대크기는 255 입니다. varchar(255) 라고하면 255 자를뜻하며, 문자열을넣을때사용합니다. char 도문자열을담을수있는자료형이기는하나 char 은고정폭으로한번길이를잡아놓으면쓰던안쓰던공간을차지합니다. 그러나 varchar 는길이를잡아놓아도사용된길이만잡혀서용량면에서 varchar 를사용하는것이좋습니다. 그러나속도나안정성면에서는 char 좀더좋습니다. 적절하게잘사용하시기바랍니다. text 는크기를정해주지않고그냥 text 라고하며긴문장을입력받을때사용하시면됩니다. 테이블만드는예제에서보면 primary key 와 auto_increment 가있습니다. 이명령의뜻을알아보겠습니다. Primary key 는중복을허용하지않는키를등록시키는것입니다. 만일 primary key 로정의된필드에중복된값을등록하라고요청이들어오면데이터베이스에서등록시킬수없다는메시지를주고해당자료를등록하지않습니다. 그리고 auto_increment 는정수값을자동으로 1 씩증가시키는것을의미합니다. idx 값을 auto_increment 로설정해주었으니, 자연히중복될상황이없으므로 idx 값을 primary key 로설정해두었습니다. 예문을실행시켜보면테이블이만들어짐을확인해볼수있습니다. mysql>desc bbs;( 엔터 ) 명령을실행시키면방금전에생성한테이블의필드명과타입을보실수있습니다. 2) 테이블수정하기아무리심사숙고하여테이블을생성했다하더라도데이터베이스를운영하다보면테이블구조를변경하는경우가발생합니다. 테이블구조를변경하고자한다면 alter table문을사용하시면됩니다. alter table 문을사용하면칼럼을추가하거나혹은수정, 삭제를할수있습니다. 칼럼명뿐만아니라테이블명도변경이가능합니다. 칼럼삽입 : alter table 테이블명 add 칼럼명칼럼타입 ; 12/17
칼럼수정 : alter table 테이블명 change 현재칼럼명바꿀칼럼명바꿀칼럼타입 ; 13/17
칼럼삭제 : alter table 테이블명 drop 칼럼명 ; 14/17
테이블명 : alter table 테이블명 rename as 새테이블명 ; 15/17
3) 테이블삭제하기테이블을삭제하는명령은 drop table 문입니다. 기본형식은다음과같습니다. drop table 테이블명 ; 이렇게쓰시면모든데이터와테이블이삭제됩니다. 삭제는간단하고빠르기때문에항상주의해서사용하셔야합니다. 이상으로우리는테이블조작을간단하게알아보았습니다. 4. 백업과복구 데이터베이스백업에대한습관을가지셔야합니다. 데이터베이스안에는우리가필요한정보들을체계적으로저장을합니다. 그러나데이터베이스도하나의시스템이기때문에언제어느순간에문제가생겨서자료들이사라질지모릅니다. 그냥데이터베이스가잘운영되겠거니믿고있다간낭패를보기쉽습니다. 또한서버를이전하거나재설치할때기존자료를버리는것이아니라가지고가셔야합니다. 그러기위해서사용되는명령이데이터베이스백업과복구작업입니다. database 전체백업 $ mysqldump u 계정 -p database 명 > 'filename' 계정이가지고있는디비안에있는모든테이블과정보들을 filename, 즉, 파일로저장이됩니다. 즉, 디비전체를백업하는것이므로데이터베이스접속전에하셔야합니다. database 전체복구 16/17
$ mysql u 계정 -p database 명 < 'filename' 파일로복구시켜놓은정보들을원하는계정의디비로복구를시키는것을의미합니다. 즉, 디비전체를복구하는것이므로데이터베이스접속전에하셔야합니다. table 백업 mysql> BACKUP TABLE 테이블명 TO '/temp' 디비안에테이블단위로백업하는것을의미합니다. 그리고백업을할폴더 (/temp) 를지정하시는겁니다. table 복구 mysql> RESTORE TABLE 테이블명 FROM '/temp'; 백업한위치거나파일이있는위치로부터원하는테이블을복구하는것을의미합니다. 17/17