³ë¹«Çö ´ëÅë·É ¹è³Ê
  ±è¼ºÅÂÀÇ Tech Tips(Linux, PHP, Apache, DBMS, Mobile)
  http://www.supersky.pe.kr  
¾È³çÇϽʴϱî? ±è¼ºÅÂÀÔ´Ï´Ù.
Linux, Apache, PHP, Mysql, Mobile °ü·Ã Tech Tips Á¤º¸¸¦ Á¦°øÇÕ´Ï´Ù.
 
<<   2005 Dec   >>
S M T W T F S
27282930123
45678910
11121314151617
18192021222324
25262728293031
1832076 12
  
DNS Powered by DNSEver.com
  ++ [Æß] MySQL ±âº»ÀûÀÎ ¹®¹ý¿ä¾à  -  2005/12/07 12:15
MySQL °ü·Ã


¡ßroot¾ÏÈ£º¯°æ¼³Á¤

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
This is done with:
/usr/bin/mysqladmin -u root -p password 'new-password'
/usr/bin/mysqladmin -u root -h ns.dbakorea.pe.kr -p password 'new-password'


¡ß½©¿¡¼­´Â mysqlÀÌ µÇ´Âµ¥ PHP¿¡¼­ mysql.sock error¸¦ ³»¸é¼­ MySQLÀÌ ¾ÈµÇ´Â °æ¿ì

mysql.sockÀº /tmp ¾Æ´Ï¸é /var/lib/mysql¿¡ »ý±â°Ô µÈ´Ù.
³ªÀÇ °æ¿ì, /var/lib/mysql¿¡ mysql.sockÆÄÀÏÀÌ Àִµ¥ PHP¿¡¼­´Â /tmp¿¡¼­ ãÀ¸·ÁÇϸ鼭 ¿¡·¯¸¦ ¹ß»ýÇß´Ù.
/usr/bin/safe_mysqldÆÄÀÏ¿¡¼­ ´ÙÀ½°ú °°ÀÌ ¼öÁ¤ÇÑ´Ù.
ÁÖ¼®(#)ÀÌ ´Þ¸° °ÍÀÌ ¿ø·¡°ÍÀÌ°í ±× ¹Ø¿¡ Àִ°ÍÀÌ ¼öÁ¤ÇÑ °ÍÀÌ´Ù.

# MYSQL_UNIX_PORT=${MYSQL_UNIX_PORT:-/var/lib/mysql/mysql.sock}
MYSQL_UNIX_PORT=${MYSQL_UNIX_PORT:-/tmp/mysql.sock}

À§¿Í °°ÀÌ ÇÏ´Ï /usr/bin/mysqlÀÌ /var/lib/mysql/mysql.sock¿¡¼­ ¼ÒÄÏÆÄÀÏÀ» ãÀ¸·Á Çß´Ù.
socket fileÀ» ÁöÁ¤ÇÏ´Â --socketÀ̶ó´Â ¿É¼ÇÀ¸·Î ´ÙÀ½°ú °°ÀÌ ÁöÁ¤ÇÏ¸é µÈ´Ù.

mysql --socket=/tmp/mysql.sock -u dbakorea -p db_test

ÇÏÁö¸¸ mysql½ÇÇà½Ã¸¶´Ù ÀÌ·¸°Ô ½áÁà¾ßÇÑ´Ù´Â °ÍÀÌ »ó´çÈ÷ ±ÍÂú´Ù. ¿É¼ÇÀÌ ¹Ù·Î Àû¿ëµÇ°Ô ¼³Á¤ÇÏÀÚ.
mysqlÀº ¼³Á¤»çÇ×À» ´ÙÀ½ 3°¡Áö ÆÄÀÏ¿¡¼­ °Ë»öÇÑ´Ù.

/etc/my.cnf global options(MySQL ÀüüÀûÀ¸·Î »ç¿ëµÇ´Â ¿É¼Ç Á¤ÀÇ)
mysql-data-dir/my.cnf ƯÁ¤ DB¿¡ Àû¿ëµÇ´Â option (/var/lib/mysql/my.cnf)
~/.my.cnf »ç¿ëÀÚ °¢°¢ÀÇ ¼³Á¤('~'¹®ÀÚ´Â »ç¿ëÀÚÀÇ È¨µð·ºÅ丮´Â ÀǹÌ)

/usr/share/mysqlµð·ºÅ丮¿¡ ¿¹Á¦°¡ ÀÖÀ¸¹Ç·Î Âü°íÇÑ´Ù.
¼ÒÄÏÆÄÀÏÀÇ ÁöÁ¤Àº ´ÙÀ½ÁÙÀ» ³Ö¾îÁÖ¸é µÈ´Ù.

socket = /tmp/mysql.sock


¡ß== /etc/my.cnf¿¹ ==
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock


¡ß# root¾ÏÈ£¼³Á¤
% mysqladmin -u root password 'º¯°æ¾ÏÈ£' root·Î ·Î±×ÀÎÇÏ¿© ÇؾßÇÔ
or
% mysqladmin -u root -p±âÁ¸¾ÏÈ£ password 'º¯°æ¾ÏÈ£' root·Î ·Î±×ÀÎÇÏ¿© ÇؾßÇÔ


¡ßDB»ý¼º
mysql> create database DB¸í ( or % mysqladmin -u root -p create DB¸í )


¡ßDB»èÁ¦
mysql> drop database DB¸í


¡ßMySQL ¿¬°á
mysql -u »ç¿ëÀÚ -p DB¸í ( or % mysqladmin -u root -p drop DB¸í )


¡ßµ¥ÀÌÅÍÆÄÀÏ ½ÇÇà(sql*loader±â´É)
mysql>load data local infile "µ¥ÀÌÅÍÆÄÀÏ" into table Å×À̺í¸í ;
µ¥ÀÌÅÍÆÄÀÏ¿¡¼­ Ä÷³±¸ºÐÀº Åǹ®ÀÚ, Null°ªÀº /n·Î ÀÔ·Â


¡ßÁúÀÇ ÆÄÀÏ ½ÇÇà
½©ÇÁ·ÒÇÁÆ®»ó¿¡¼­
mysql -u »ç¿ëÀÚ -p DB¸í < ÁúÀÇÆÄÀÏ
or
mysqlÇÁ·ÒÇÁÆ®»ó¿¡¼­
mysql> source ÁúÀÇÆÄÀÏ


¡ß½©ÇÁ·ÒÇÁÆ®»ó¿¡¼­ ÁúÀÇ ½ÇÇà
dbakorea@lion board]$ mysql mysql -u root -pxxxx -e \
> "INSERT INTO db VALUES(
> 'localhost', 'aaa', 'aaa',
> 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y')"


¡ß»ç¿ëÀÚ »ý¼º & »ç¿ëÀÚ¿¡°Ô DBÇÒ´ç
shell> mysql --user=root -p mysql

mysql> INSERT INTO user VALUES('localhost','»ç¿ëÀÚ',PASSWORD('ºñ¹Ð¹øÈ£'),'Y','Y','Y','Y','Y','Y','Y',
'Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user VALUES('%','»ç¿ëÀÚ',PASSWORD('ºñ¹Ð¹øÈ£'),'Y','Y','Y','Y','Y','Y','Y','Y','Y',
'Y','Y','Y','Y','Y');

mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)
VALUES ('localhost','DB¸í','»ç¿ëÀÚ','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)
VALUES('%','DB¸í','»ç¿ëÀÚ','Y','Y','Y','Y','Y','Y');

mysql> FLUSH PRIVILEGES; (or shell prompt: mysqladmin -u root -pxxxx reload)


¡ßDB, Å×À̺í¸ñ·Ïº¸±â
mysql> show databases;
mysql> show tables;


¡ß»ç¿ë DB¼±ÅÃ
mysql> use DB¸í


¡ßÅ×ÀÌºí±¸Á¶
mysql> describe Å×À̺í¸í


¡ßÀ妽º º¸±â
show index from Å×À̺í¸í


¡ßÅ×À̺í¸í º¯°æ
mysql> rename table test to test2;


¡ßÁ¢¼Ó
mysql {-h Á¢¼ÓÈ£½ºÆ®} -u »ç¿ëÀÚ -p »ç¿ëDB
-h·Î ´Ù¸¥ ¼­¹ö¿¡ Á¸ÀçÇÏ´Â MySQLÁ¢¼Ó½Ã ´ÙÀ½°ú °°ÀÌ MySQL DB¿¡ ¼³Á¤ÇØÁà¾ß ÇÑ´Ù.
mysql> INSERT INTO user VALUES('Á¢±ÙÀ» Çã¿ëÇÒ È£½ºÆ®ip','»ç¿ëÀÚ',PASSWORD('ºñ¹Ð¹øÈ£'),'Y','Y','Y',
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)
VALUES('Á¢±ÙÀ» Çã¿ëÇÒ È£½ºÆ®ip','»ç¿ëDB','»ç¿ëÀÚ','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES; or ½¯ÇÁ·ÒÇÁÆ®»ó¿¡¼­ % mysqladmin -u root -p flush-privileges


¡ß¹öÀüüũ
mysql> select version();


¡ß°Ë»öÁ¶°Ç(where)
regular expressionÀ» Áö¿øÇÏ´Ù´Ï ½Å±âÇϱº..
mysql> select * from work where ¿­¸í regexp "Á¤±ÔÇ¥Çö½Ä";


¡ßÄ÷³ÀÇ Ãß°¡, »èÁ¦(¿À¶óŬ°ú µ¿ÀÏ)
alter table Å×À̺í¸í add Ä÷³¸í µ¥ÀÌÅÍŸÀÔ;
alter table Å×À̺í¸í del Ä÷³¸í;


¡ß¹é¾÷ & º¹±¸
mysqldump {-h È£½ºÆ®} -u »ç¿ëÀÚ -p DB¸í > ¹é¾÷ÆÄÀÏ
mysql {-h È£½ºÆ®} -u »ç¿ëÀÚ -p DB¸í < ¹é¾÷ÆÄÀÏ

mysqldump -u root -p --opt db_dbakorea > dbakorea.sql
mysqldump -u root -p --opt db_board | mysql ---host=remote-host -C database (»óÀÌÇÑ ¸Ó½®)
mysql -u dbakorea -p db_dbakorea < dbakorea.sql


mysqldump -u root -p --opt db_dbakorea | mysql ---host=ns.dbakorea.pe.kr -C db_dbakorea


¡ßÅ×ÀÌºí °Ë»ç
isamchk


¡ß¿À¶óŬ sysdate¿Í µ¿ÀÏ
insert into test values('12', now());


¡ßÀ¯´Ð½º time()ÇÔ¼ö ¸®ÅÏ°ª »ç¿ë
FROM_UNIXTIME(954788684)
UNIX_TIMESTAMP("2001-04-04 :04:04:04")


¡ßMySQL µðÆúÆ® DB&·Î±×ÆÄÀÏ À§Ä¡
/var/lib/mysql
/var/libµð·ºÅ丮´Â ¿©·¯ ÇÁ·Î¼¼½ºµéÀÌ »ç¿ëÇÏ´Â µ¥ÀÌÅ͸¦ ÀúÀåÇÏ´Â ÀÏÁ¾ÀÇ ÆÄÀϽýºÅÛ»óÀÇ µ¥ÀÌÅͺ£ÀÌ
½º¶ó°í º¼ ¼ö ÀÖ´Ù.


¡ßreplace
ÇØ´ç ·¹ÄÚµå Á¸ÀçÇϸé updateÇÏ°í, Á¸ÀçÇÏÁö ¾Ê´Â´Ù¸é insertÇÑ´Ù.(insert¹®¹ý°ú µ¿ÀÏ)
replace into test values('maddog','kang myung gyu')'


¡ßexplain
explain ÁúÀǹ®: ÁöÁ¤ÇÑ ÁúÀǹ®ÀÌ ¾î¶»°Ô ½ÇÇàµÉ °ÇÁö¸¦ º¸¿©ÁÜ
mysql> explain select u.uid, u.name, a.name from sm_user u, sm_addr a where u.uid=a.uid;
+-------+------+-----------------+-----------------+---------
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+-----------------+-----------------+---------
| u | ALL | PRIMARY | NULL | NULL | NULL | 370 | |
| a | ref | sm_addr_uid_idx | sm_addr_uid_idx | 11 | u.uid | 11 | |
+-------+------+-----------------+-----------------+---------
2 rows in set (0.01 sec)


¡ßtemporary table
Å©±â°¡ Å« Å×ÀÌºí¿¡ ÀÖ´Â subset¿¡ ´ëÇÑ ÁúÀǶó¸é subsetÀ» temporary table¿¡ ÀúÀåÇÑ ÈÄ
ÁúÀÇÇÏ´Â °ÍÀÌ ´õ ºü¸¦ °æ¿ì°¡ ÀÖ´Ù.
create temporary table (...);
create temporary table (...) type=heap; µð½ºÅ©°¡ ¾Æ´Ñ ¸Þ¸ð¸®¿¡ Å×ÀÌºí »ý¼º

=============================================================
MySQL Ư¼ºÁ¤¸®
=============================================================
primary keyÁö¿ø
index Áö¿ø(15°³Ä÷³, 256byte±îÁö)
foreign key, check, references±â´É Áö¿øÇÏÁö ¾ÊÀ½(Çü½Ä»ó ÀÖÀ½)

mysql> CREATE TABLE test (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (last_name,first_name));

MySQL¿¡¼­ÀÇ Stored Script°³³ä => SQL server language
commit-rollback°³³ä => lock tables






      << prev     1  2  3  4  5     next >>