MySQL-Ch10

Size: px

Start display at page:

Download "MySQL-Ch10"

지완 도
6 years ago
Views:

1 10 Chapter.,,.,, MySQL. MySQL mysqld MySQL.,. MySQL. MySQL....,.,..,,.,. UNIX, MySQL. mysqladm mysqlgrp. MySQL 608 MySQL(2/e)

2 Chapter 10 MySQL (,, ). UNIX MySQL, /usr/local/mysql/var, /usr/local/mysql/data, RPM /var/lib/mysql. Windows c:\mysql\data. datadir=dir_name..... MySQL ( : mysqld,. ). ( : ),. ( ),..,. datadir, mysqladmin variables SHOW VARIABLES. ( : ) mysqladmin. UNIX. % mysqladmin variables Variable_name Value datadir /usr/local/mysql/var/... MySQL(2/e) 609

3 PART 3 Windows. C:\> mysqladmin variables Variable_name Value datadir c:\mysql\data\... mysql. mysql> SHOW VARIABLES LIKE 'datadir'; Variable_nam Value datadir /usr/local/mysql/var/ , TCP/IP,, (listening). port socket.... mysqladmin TCP/IP % mysqladmin --host= port=port_num variables UNIX localhost UNIX., socket. % mysqladmin --host=localhost --socket=/path/to/socket variables Windows NT., socket. C:\> mysqladmin --host=. --socket=pipe_name variables, TCP/IP, host. 610 MySQL(2/e)

4 Chapter 10 % mysqladmin --host=host_name variables port. UNIX ps mysqld. datadir,. BSD ps. % ps axww grep mysqld System V ps. % ps -ef grep mysqld ps,., mysqld datadir (, ps ).., UNIX /etc/my.cnf Windows C:\my.cnf [mysqld] datadir. [mysqld] datadir=/path/to/data/directory..,,.. % mysqld --help... datadir /usr/local/mysql/var/... MySQL(2/e) 611

5 PART 3 MySQL,., Makefile.. datadir, localstatedir., MySQL NFS,..,..frm( ). % find / -name "*.frm" -print.frm, MySQL.frm. ( ).,. MySQL DATADIR, MySQL., UNIX Windows.., InnoDB InnoDB.,. InnoDB. 612 MySQL(2/e)

6 Chapter 10. my.cnf, ID ( windows ).,..,, MySQL /, MySQL mysqld..,,,. [. ]., (listen), ( ).,, SQL,, (select),..., (update), ( serialize, ),. ( ),., MySQL(2/e) 613

7 PART 3 (listen).,,,. Client 1 Client 2 Client 3 UNIX domain socket (UNIX) TCP/IP port (UNIX, NT) Named pipe (NT) Network Interface MySQL server Data directory Database 1 Database 2... Database n Table 1 Table 2 Table 3 Table 1 Table 2 Table 3 Table 1 Table 2 Table 3,.,.,.,..,.,, MySQL(2/e)

8 Chapter 10 myisamchk isamchk,,.,,..,,. ( CHECK TABLE REPAIR TABLE, ) MySQL.,., mydb DATADIR/mydb. (SQL ). SHOW DATABASES., MySQL.,,. CREATE DATABASE db_name db_name. UNIX. CREATE DATABASE,. % cd DATADIR % mkdir db_name % chmod u=rwx,go-rwx db_name MySQL(2/e) 615

9 PART 3. DROP DATABASE. DROP DATABASE db_name db_name. UNIX. % cd DATADIR % rm -rf db_name Windows. C:\> cd DATADIR C:\> del /S db_name DROP DATABASE. DROP DATABASE,.,, ( SHOW DATABASES ). InnoDB, InnoDB. InnoDB, InnoDB DROP DATABASE., rm del MySQL. ISAM, MyISAM, MERGE, BDB, InnoDB HEAP. MySQL,.frm.,., (., MySQL SQL ). 616 MySQL(2/e)

10 Chapter 10 MySQL ISAM. MySQL ISAM,.,., mytbl. mytbl.frm. mytbl.isd ISAM. mytbl.ism. MySQL. MyISAM ISAM, ISAM. ISAM, MyISAM,.frm,.MYD,.MYI, (description),,. MERGE. MyISAM,., MERGE.frm.MRG,.MRG MyISAM,., FLUSH TABLES.MRG MyISAM, MERGE ( ). BDB,.frm.db.. InnoDB. InnoDB MySQL(2/e) 617

11 PART 3.frm,. InnoDB.,.. HEAP. HEAP,.frm frm, SHOW TABLES db_name db_name.frm.. MySQL,.. MySQL, CREATE TABLE.,.frm,.., MyISAM.MYD.MYI, BDB.db /. InnoDB InnoDB. UNIX. ALTER TABLE,.frm,. CREATE INDEX DROP INDEX, ALTER TABLE. 618 MySQL(2/e)

12 Chapter 10 InnoDB (alter) InnoDB. DROP TABLE., InnoDB InnoDB.,., mydb mytbl ISAM, MyISAM, BDB MERGE, DROP TABLE mytbl, UNIX. % cd DATADIR % rm -f mydb/mytbl.*, Windows. C:\> cd DATADIR C:\> del mydb\mytbl.* InnoDB HEAP, DROP TABLE.,.frm InnoDB. InnoDB MySQL.. ( ), ( _ $ ). MySQL.. (, `odd@name`).. MySQL(2/e) 619

13 PART 3,,.,...frm,., $ MySQL,., UNIX Windows.., $ UNIX. $mydb,. % ls $mydb mydb: Undefined variable. $. % ls \$mydb % ls '$mydb'..,., UNIX Windows, / \,., (, / Windows, UNIX ). MySQL,., UNIX System V.,. 620 MySQL(2/e)

14 Chapter 10, (.).. ( UNIX ), mytbl MYTBL. (Windows Mac OS X HFS+ ), mytbl MYTBL.. lower_case_table_ names,..,,.,., lower_case_ table_names., MySQL...,.,., MySQL,.. MySQL.. ISAM,.ISD.ISM GB. MySQL(2/e) 621

15 PART 3 MyISAM,.MYD.MYI GB., AVG_ROW_LENGTH MAX_ROWS ( D, SQL CREATE TABLE ). MERGE MyISAM. BDB.db. ( ), ( ),. InnoDB, InnoDB, KB (MySQL InnoDB KB KB ). InnoDB.., GB., MyISAM.MYD.MYI., InnoDB. InnoDB.,,. InnoDB, MySQL.... (ISAM MyISAM )..,.,. AUTO_INCREMENT., TINYINT UNSIGNED,. 622 MySQL(2/e)

16 Chapter 10.,,.,.. ISAM. MySQL GB. GB,.,, GB, MySQL GB. InnoDB InnoDB. InnoDB,., InnoDB. InnoDB.,,, InnoDB (MySQL..,. ) MySQL.,,.,., ( )..,, MySQL(2/e) 623

17 PART 3..,.,., MyISAM., MyISAM,,., (Linux ext Solaris ).,., ReiserFS.,..,.,.,.,.,.. ( ), ( ).,.,.,...,. MySQL. 624 MySQL(2/e)

18 Chapter 10., ( ),. InnoDB. InnoDB.frm, InnoDB InnoDB.,,. InnoDB ( )..frm , MySQL, [. ]., HOSTNAME. PID ID(PID). PID MySQL(2/e) 625

19 PART 3., MySQL mysql. server, PID. MySQL..,,,....,,.,. mysql SQL.,..,,...,, (. db ). (create) (insert) (drop) :00:17 1 Connect sampadm@localhost on test 2 Query CREATE TABLE mytbl (val INT) 2 Query INSERT INTO mytbl VALUES(1) 2 Query DROP TABLE mytbl 2 Quit 626 MySQL(2/e)

20 Chapter 10, ( ) ID,,. (, ).. (;), mysql. use test; CREATE TABLE mytbl (val INT); INSERT INTO mytbl VALUES(1); DROP TABLE mytbl;, log long format..,,,.,. # Time: :00:17 # User@Host: localhost [] use test; CREATE TABLE mytbl (val INT); # User@Host: localhost [] INSERT INTO mytbl VALUES(1); # User@Host: localhost [] DROP TABLE mytbl; #, mysql..,.,... MySQL(2/e) 627

21 PART 3,., :47:24 4 Query UPDATE user SET Password=PASSWORD('secret') WHERE user='root', MySQL ,.,. MySQL...,.., InnoDB,..,.,. 628 MySQL(2/e)

22 Chapter 10 PID, /var/run. MySQL PID. /var/log, MySQL (,., )., ,.,, datadir= dir_name,. [mysqld] datadir=dir_name, [mysqld].,., [embedded]., mysqld_multi [mysqldn], n.,.,.. [. ],., (UNIX /etc/my. cnf Windows c:\my.cnf my.ini ). MySQL(2/e) 629

23 PART 3 my.cnf ( ).,,, ( ) ,. du, df, ls l,.,. /usr/local/mysql df /var /var/mysql ( ). % df /usr /var Filesystem 1K-blocks Used Avail Capacity Mounted on /dev/wd0s3e % /usr /dev/wd0s3f % /var /usr?, du s. % cd /usr/local/mysql/data 630 MySQL(2/e)

24 Chapter 10 % du -s MB, /usr.? df. % df /usr/local/mysql/data Filesystem 1K-blocks Used Avail Capacity Mounted on /dev/wd0s3f % /var. (, /usr), df /var? ls l. % ls -l /usr/local/mysql/data... lrwxrwxr-x 1 mysqladm mysqlgrp 10 Dec 11 23:46 data -> /var/mysql... /usr/local/mysql/data /var/mysql., /var,. /var /usr!.,,. > TIP , MySQL(2/e) 631

25 PART 3., datadir. my.cnf, ,. UNIX bigdb. % mysqladmin -p -u root shutdown Enter password: ****** % cd DATADIR % tar cf - bigdb (cd /var/db; tar xf -) % mv bigdb bigdb.orig % ln -s /var/db/bigdb. % mysqld_safe & MySQL. bigdb.orig.,. % rm -rf bigdb.orig 632 MySQL(2/e)

26 Chapter 10 Windows sym MySQL,., sampdb C:\mysql\data\sampdb E:\mysql book\sampdb, sampdb.sym C:\mysql\data. E:\mysql-book\sampdb\.sym MySQL..,. use symbolic links. [mysqld] use-symbolic-links Windows, MySQL.. max (mysqld max mysqld max nt)., InnoDB InnoDB InnoDB. InnoDB,. > TIP MySQL(2/e) 633

27 PART MySQL.. realpath(). MyISAM.,.MYD.MYI, (.frm ).. ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE,.,.,., ALTER OPTIMIZE ( ), innodb_data_home_dir innodb_data_file_path InnoDB ( ).,,, InnoDB. 634 MySQL(2/e)

28 Chapter 10.., PID,., /tmp/mysql.pid PID, pid file=/tmp/mysql.pid. [mysqld] pid-file=/tmp/mysql.pid,.,., pid file= mysqld.pid, PID mysqld.pid.,.. MySQL(2/e) 635

8 장데이터베이스 8.1 기본개념 - 데이터베이스 : 데이터를조직적으로구조화한집합 (cf. 엑셀파일 ) - 테이블 : 데이터의기록형식 (cf. 엑셀시트의첫줄 ) - 필드 : 같은종류의데이터 (cf. 엑셀시트의각칸 ) - 레코드 : 데이터내용 (cf. 엑셀시트의한줄 )

8 장데이터베이스 8.1 기본개념 - 데이터베이스 : 데이터를조직적으로구조화한집합 (cf. 엑셀파일 ) - 테이블 : 데이터의기록형식 (cf. 엑셀시트의첫줄 ) - 필드 : 같은종류의데이터 (cf. 엑셀시트의각칸 ) - 레코드 : 데이터내용 (cf. 엑셀시트의한줄 ) - DDL(Data Definition Language) : show, create, drop