10 Chapter.,,.,, MySQL. MySQL mysqld MySQL.,. MySQL. MySQL....,.,..,,.,. UNIX, MySQL. mysqladm mysqlgrp. MySQL 608 MySQL(2/e)
Chapter 10 MySQL. 10.1 (,, ). 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
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=127.0.0.1 --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)
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
PART 3 MySQL,., Makefile.. datadir, localstatedir., MySQL NFS,..,..frm( ). % find / -name "*.frm" -print.frm, MySQL.frm. ( ).,. MySQL DATADIR,. 10.2 MySQL., UNIX Windows.., InnoDB InnoDB.,. InnoDB. 612 MySQL(2/e)
Chapter 10. my.cnf, ID ( windows ).,..,,. 10.2.1 MySQL /, MySQL mysqld..,,,. [. ]., (listen), ( ).,, SQL,, (select),..., (update), ( serialize, ),. ( ),., MySQL(2/e) 613
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,.,.,.,..,.,,.. 614 MySQL(2/e)
Chapter 10 myisamchk isamchk,,.,,..,,. ( CHECK TABLE REPAIR TABLE, ). 10.2.2 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
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. 10.2.3 MySQL. ISAM, MyISAM, MERGE, BDB, InnoDB HEAP. MySQL,.frm.,., (., MySQL SQL ). 616 MySQL(2/e)
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
PART 3.frm,. InnoDB.,.. HEAP. HEAP,.frm. 10.2.4.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)
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. 10.2.5 MySQL.. ( ), ( _ $ ). MySQL.. (, `odd@name`).. MySQL(2/e) 619
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)
Chapter 10, (.).. ( UNIX ), mytbl MYTBL. (Windows Mac OS X HFS+ ), mytbl MYTBL.. lower_case_table_ names,..,,.,., lower_case_ table_names., MySQL...,.,.,.. 10.2.6 MySQL,.. MySQL.. ISAM,.ISD.ISM GB. MySQL(2/e) 621
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)
Chapter 10.,,.,.. ISAM. MySQL GB. GB,.,, GB, MySQL GB. InnoDB InnoDB. InnoDB,., InnoDB. InnoDB.,,, InnoDB (MySQL..,. ). 10.2.7 MySQL.,,.,., ( )..,, MySQL(2/e) 623
PART 3..,.,., MyISAM., MyISAM,,., (Linux ext Solaris ).,., ReiserFS.,..,.,.,.,.,.. ( ), ( ).,.,.,...,. MySQL. 624 MySQL(2/e)
Chapter 10., ( ),. InnoDB. InnoDB.frm, InnoDB InnoDB.,,. InnoDB ( )..frm. 10.2.8, MySQL, [. ]., HOSTNAME. PID ID(PID). PID MySQL(2/e) 625
PART 3., MySQL mysql. server, PID. MySQL..,,,....,,.,. mysql SQL.,..,,...,, (. db ). (create) (insert) (drop). 020727 15: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)
Chapter 10, ( ) ID,,. (, ).. (;), mysql. use test; CREATE TABLE mytbl (val INT); INSERT INTO mytbl VALUES(1); DROP TABLE mytbl;, log long format..,,,.,. # Time: 020727 15:00:17 # User@Host: sampadm[sampadm] @ localhost [] use test; CREATE TABLE mytbl (val INT); # User@Host: sampadm[sampadm] @ localhost [] INSERT INTO mytbl VALUES(1); # User@Host: sampadm[sampadm] @ localhost [] DROP TABLE mytbl; #, mysql..,.,... MySQL(2/e) 627
PART 3,.,.. 020727 15:47:24 4 Query UPDATE user SET Password=PASSWORD('secret') WHERE user='root', MySQL.. 10.3,.,. MySQL...,.., InnoDB,..,.,. 628 MySQL(2/e)
Chapter 10 PID, /var/run. MySQL PID. /var/log, MySQL (,., ).,. 10.3.1.,.,, 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
PART 3 my.cnf ( ).,,, ( ). 10.3.2,. du, df, ls l,.,. /usr/local/mysql df /var /var/mysql ( ). % df /usr /var Filesystem 1K-blocks Used Avail Capacity Mounted on /dev/wd0s3e 396895 292126 73018 80% /usr /dev/wd0s3f 1189359 1111924 162287 15% /var /usr?, du s. % cd /usr/local/mysql/data 630 MySQL(2/e)
Chapter 10 % du -s 133426. MB, /usr.? df. % df /usr/local/mysql/data Filesystem 1K-blocks Used Avail Capacity Mounted on /dev/wd0s3f 1189359 1111924 162287 15% /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 10.3.3, MySQL(2/e) 631
PART 3., datadir. my.cnf,. 10.3.4,. 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)
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
PART 3 10.3.5. MySQL.. realpath(). MyISAM.,.MYD.MYI, (.frm ).. ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE,.,.,., ALTER OPTIMIZE ( ),. 10.3.6 innodb_data_home_dir innodb_data_file_path InnoDB ( ).,,,....... InnoDB. 634 MySQL(2/e)
Chapter 10..,... 10.3.7 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