mysqladmin -u root -p variables
mysqladmin -u root -p processlist
#
# º¹±¸
#
ERROR 1030 (HY000): Got error 127 from storage engine
mysql> analyze table mj_default; +------------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------------------+---------+----------+----------+ | houseinfodb.mj_default | analyze | status | OK | +------------------------+---------+----------+----------+ 1 row in set (0.43 sec)
mysql> repair table mj_default; +------------------------+--------+----------+--------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +------------------------+--------+----------+--------------------------------------------------------------------+ | houseinfodb.mj_default | repair | info | Found link that points at 15739244 (outside data file) at 13826076 | | houseinfodb.mj_default | repair | warning | Number of rows changed from 56134 to 56133 | | houseinfodb.mj_default | repair | status | OK | +------------------------+--------+----------+--------------------------------------------------------------------+ 3 rows in set (3.25 sec)
#
# mysql ¿¬°á Á¤º¸
#
show variables;
show variables like 'connect_timeout'; show variables like 'max_connect_errors'; show variables like 'max_connections'; show variables like 'max_user_connections' ;
show processlist
1.ƯÁ¤ µ¥ÀÌÅͺ£À̽ºÀÇ DEFAULT CHARACTER SET È®ÀÎ Çϱâ
# SHOW CREATE DATABASE db_name;
2.ƯÁ¤ Å×À̺íÀÇ DEFAULT CHARACTER SET È®ÀÎ Çϱâ
# SHOW CREATE TABLE table_name;
3.ƯÁ¤ µ¥ÀÌÅͺ£À̽ºÀÇ DEFAULT CHARACTER SET ¼öÁ¤ÇÏ´Â ¹æ¹ý ALTER DATABASE db_name [DEFAULT] CHARACTER SET = charset_name; - ±âÁ¸ Å×À̺íµéÀÇ Ä³¸¯ÅÍ ¼ÂÀ» ¼öÁ¤Çϱ⠺¸´Ù ¾ÕÀ¸·Î ¸¸µé¾îÁú Å×À̺íµéÀÇ ±âº» Äɸ¯ÅÍ ¼ÂÀ» ÁöÁ¤ÇÏ´Â °ÍÀÔ´Ï´Ù.^^
4.ƯÁ¤ µ¥ÀÌÅͺ£À̽ºÀÇ DEFAULT COLLATION ¼öÁ¤ÇÏ´Â ¹æ¹ý ALTER DATABASE db_name [DEFAULT] COLLATE = collation_name; - ±âÁ¸ Å×À̺íµéÀÇ Ä³¸¯ÅÍ ¼ÂÀ» ¼öÁ¤Çϱ⠺¸´Ù ¾ÕÀ¸·Î ¸¸µé¾îÁú Å×À̺íµéÀÇ ±âº» Äɸ¯ÅÍ ¼ÂÀ» ÁöÁ¤ÇÏ´Â °ÍÀÔ´Ï´Ù.^^
5.ƯÁ¤ Å×À̺íÀÇ DEFAULT CHARACTER SET ¼öÁ¤ÇÏ´Â ¹æ¹ý ALTER TABLE table_name [DEFAULT] CHARACTER SET = charset_name; - ÇâÈÄ ¸¸µé¾îÁö°ÔµÉ Ä®·³µéÀÇ ±âº» Äɸ¯ÅͼÂÀ» ÁöÁ¤ Çϴ°ÍÀÔ´Ï´Ù.
ALTER TABLE table_name [DEFAULT] CHARACTER SET = charset_name COLLATE = collation_name; - ÇöÀç Æ÷ÇԵǾî ÀÖ´Â Å×À̺íÀÇ Äɸ¯ÅÍ ¼ÂÀ̳ª collationÀ» ¼öÁ¤ÇÒ¶§ À§ÀÇ ¸í·É¾î¸¦ ¾¹´Ï´Ù.
ALTER TABLE table_name CONVERT TO CHARACTER SET = charset_name [COLLATE =collation_name];
- ÇöÀç Æ÷ÇԵǾî ÀÖ´ø °¢ Ä÷³µé¿¡ ÁöÁ¤µÈ ¹®ÀÚ¼ÂÀÌ µ¿ÀÏÇÏ°Ô »õ·Î¿î ¹®ÀÚ¼Â/collation À¸·Î º¯°æ µË´Ï´Ù.
¡Ø CONVERT TO'¸¦ ¹®ÀÚ¼ÂÀÌ ±âÁ¸ÀÇ ¹®Àڼº¸´Ù Max_len ÀÌ Å« °æ¿ì °¢ Ä÷³µé¿¡ ÁöÁ¤µÇ¾îÀÖ´ø µ¥ÀÌÅÍ Å¸ÀÔÀÇ Å©±â ¿ª½Ã ÇÔ²² º¯°æÀÌ µË´Ï´Ù. µ¥ÀÌÅÍ Å¸ÀÔÀÇ Å©±â³ª '°ª'ÀÇ º¯È¸¦ ¿øÇÏÁö ¾Ê´Â °æ¿ì¿¡´Â 'CONVERT TO'¸¦ »ç¿ëÇÏ¸é ¾ÈµÇ°í °¢ Ä÷³¸¶´Ù 'MODIFY' ¸í·É¾î¸¦ ÅëÇØ °¢°¢ ¼öÁ¤ÇØÁÖ¾î¾ß ÇÔ.
6. ÇöÀç ½Ã½ºÅÛ¿¡¼ Áö¿øÇÏ´Â ¹®ÀڼµéÀÇ ¸ñ·ÏÀ» º¸°í ½ÍÀ» ¶§ SHOW CHARACTER SET; ¸¸¾à, Àüü ¸ñ·ÏÀÌ ¾Æ´Ï¶ó ÇÊÅ͸¦ »ç¿ëÇؼ ÀϺΠ¸ñ·Ï¸¸ º¸°í ½ÍÀ» ¶§´Â ´ÙÀ½°ú °°ÀÌ ÇÏ¸é µÈ´Ù. SHOW CHARACTER SET LIKE '%euc%';
ÇöÀç ½Ã½ºÅÛ¿¡¼ Áö¿øÇÏ´Â collation ¸ñ·ÏÀ» º¸°í ½ÍÀ» ¶§ SHOW COLLATION;
MySQL¿¡¼ ¹®ÀÚ¼Â(Character Set)°ú CollationÀÇ Â÷ÀÌ
- ¹®ÀÚ¼Â(Character Set)
- ¸» ±×´ë·Î ƯÁ¤ '¹®ÀÚ(Á» ´õ Á¤È®È÷´Â symbol)'°¡ ÄÄÇ»ÅÍ¿¡ ÀúÀåµÉ ¶§ ¾î¶°ÇÑ 'ÄÚµå'·Î ÀÎÄÚµùµÇ¾î ÀúÀåµÉ °ÍÀÎÁö¿¡ ´ëÇÑ ±ÔÄ¢ÀÌ Á¤ÀǵǾîÀÖ´Â ÁýÇÕÀ» ¸»ÇÔ. (A character set is a set of symbols and encodings.)
- ´ëÇ¥ÀûÀÎ °ÍÀ¸·Î´Â ASCII, ÇѱÛÀ» Áö¿øÇÏ´Â EUC-KR, À¯´ÏÄÚµå UTF-8, UTF-16 µîÀÌ ÀÖÀ½.
- ¹®ÀÚ¼ÂÀº ÀÚ½ÅÀÌ Ã³¸®ÇÒ ¹®ÀÚ ÁýÇÕÀÌ ¾ó¸¶³ª Å«°¡¿¡ µû¶ó ÇϳªÀÇ '¹®ÀÚ'¸¦ ÀúÀåÇϴµ¥ ÇÊ¿äÇÑ ÀúÀå°ø°£ÀÇ Å©±â°¡ ´Þ¶óÁö°Ô µÈ´Ù. ¶ÇÇÑ Ç×»ó ¸ðµç ¹®ÀÚµéÀ» µ¿ÀÏÇÑ Å©±â·Î ÀÎÄÚµùÇÏ´Â °íÁ¤Æø ¹æ½ÄÀÇ ¹®ÀÚ¼ÂÀÌ Àִ°¡Çϸé, EUC-KR(1~2¹ÙÀÌÆ®)À̳ª UTF-8(1~3¹ÙÀÌÆ®)°ú °°ÀÌ °¡º¯Æø ¹æ½ÄÀÇ ÀÎÄÚµùÀ» »ç¿ëÇÏ´Â ¹®Àڼµµ ÀÖ´Ù.
- ÄÝ·¹À̼Ç(Collation)
- ÀÌ°ÍÀº ƯÁ¤ ¹®Àڼ¿¡ ÀÇÇØ µ¥ÀÌÅͺ£À̽º¿¡ ÀúÀåµÈ °ªµéÀ» °Ë»öÇϰųª Á¤·Ä µîÀÇ ÀÛ¾÷À» À§ÇØ ¹®Àڵ鰣¿¡ ¼·Î 'ºñ±³'ÇÒ¶§ »ç¿ëÇÏ´Â ±ÔÄ¢µéÀÇ ÁýÇÕÀÌ´Ù. (A collation is a set of rules for comparing characters in a character set.) µû¶ó¼, ÀϹÝÀûÀÎ Data Type¿¡´Â Àû¿ëµÇÁö ¾Ê°í, CHAR, VARCHAR, TEXT¿Í °°Àº µ¥ÀÌÅÍ Å¸ÀÔÀ» °¡Áö´Â Ä®·³µé¿¡ ´ëÇؼ¸¸ Àû¿ëµÈ´Ù.
- ÁÖ·Î ´ë¼Ò¹®ÀÚ¸¦ ±¸º°ÇÏ´Â binary Çü½ÄÀ» »ç¿ëÇÒ °ÍÀÎÁö, ´ë¼Ò¹®ÀÚ¸¦ ±¸º°ÇÏÁö ¾Ê´Â Case Insensitive Çü½ÄÀ» »ç¿ëÇÒ °ÍÀÎÁö·Î ³ª´©¾îÁø´Ù. Case Insensitive Çü½ÄÀ» »ç¿ëÇÏ´Â collationµéÀº ¸ðµÎ collation À̸§ÀÇ ³¡¿¡ 'ci'¶ó´Â ¹®ÀÚ°¡ ºÙ¾îÀÖ´Ù.
[Âü°í]
- Ưº°È÷, MySQL¿¡¼ÀÇ ¹®Àڼ°ú collation ¿¡ ´ëÇÑ ¹®Á¦´Â ´Ü¼øÈ÷ µ¥ÀÌÅ͸¦ ÀúÀåÇÏ´Â °ø°£¿¡ ´ëÇÑ °üÁ¡¸¸ÀÌ ¾Æ´Ï¶ó, MySQL ¼¹ö¿Í MySQL ¼¹ö¿¡°Ô ¿äûÀ» Çϴ Ŭ¶óÀ̾ðÆ® »çÀÌ¿¡ ÁÖ°í¹Þ´Â »óÈ£ÀÛ¿ë¿¡ ´ëÇÑ °üÁ¡±îÁöµµ Æ÷ÇÔÇÑ´Ù´Â °ÍÀ» ²À ±â¾ïÇÒ ÇÊ¿ä°¡ ÀÖ´Ù.
- µ¥ÀÌÅ͸¦ ÀúÀåÇÏ´Â °üÁ¡ : MySQLÀº Server ·¹º§, Database ·¹º§, Table ·¹º§, Column ·¹º§±îÁö ÃÑ 4°³ÀÇ ·¹º§À» Áö¿øÇϴ Ư¡À» °¡Áö°í ÀÖÀ½.
- ¼¹ö¿Í Ŭ¶óÀ̾ðÆ®°£ÀÇ »óÈ£ÀÛ¿ë °üÁ¡ : MySQLÀº ÀÌ °üÁ¡¿¡¼ ¹®Àڼ°ú °ü·ÃÇÑ ÃÑ 3°³ÀÇ ¼·Î ´Ù¸¥ ŸÀÔÀ» Á¦°øÇÑ´Ù.
- character_set_client : Ŭ¶óÀ̾ðÆ®¿¡¼ ¹®ÀåÀÌ ¶°³¯ ¶§ ÇØ´ç ¹®ÀåÀº ¾î¶² ¹®ÀÚ¼ÂÀ¸·Î ÀÌ·ç¾îÁ®Àִ°¡ÀÇ ¹®Á¦
- character_set_connection : Ŭ¶óÀ̾ðÆ®¿¡¼ ³Ñ¾î¿Â ¹®ÀåÀ» ¼¹ö¿¡¼´Â ¾î¶² ¹®ÀÚ¼ÂÀ» ±âÁØÀ¸·Î Çؼ®ÇؾßÇϴ°¡ÀÇ ¹®Á¦.
- character_set_results : ¼¹ö¿¡¼ Ŭ¶óÀ̾ðÆ®·Î ÀÀ´äÀ» ³Ñ°ÜÁÙ¶§ ¾î¶² ¹®ÀÚ¼ÂÀ¸·Î ³Ñ°Ü¾ßÇϴ°¡ÀÇ ¹®Á¦.
- µû¶ó¼, µ¥ÀÌÅͺ£À̽º ·¹º§¿¡¼ euckr ¹®ÀÚ¼ÂÀÌ ±âº»°ªÀ¸·Î ÁöÁ¤µÇ¾îÀÖ´Â µ¥ÀÌÅͺ£À̽º¸¦ »ý¼ºÇÑ ÈÄ ÇØ´ç µ¥ÀÌÅͺ£À̽º¿¡ µ¥ÀÌÅ͸¦ Àß ÀÔ·ÂÇÏ¿´´Âµ¥ ÀÌ»óÇÏ°Ô ºê¶ó¿ìÀú·Î ³Ñ±â¸é µ¥ÀÌÅͺ£À̽º¿¡¼ Àоî¿Â ¹®ÀÚ¸¸ ±ÛÀÚ°¡ ±úÁö´Â »óȲÀÌ ¹ú¾îÁø´Ù¸é(ºê¶ó¿ìÀúÀÇ ÀÎÄÚµù ¼³Á¤ µîÀº Á¤»óÀûÀ̶ó°í °¡Á¤) ´ÙÀ½°ú °°ÀÌ Ã¼Å©ÇغÁ¾ß ÇÑ´Ù.
- Database ·¹º§, Table ·¹º§, Column ·¹º§¿¡¼ ¿øÇÏ´Â ¹®ÀÚ¼ÂÀ¸·Î Àß ÁöÁ¤µÇ¾îÀÖ´ÂÁö¸¦ È®ÀÎ
- client, connection, results ¿¡¼ÀÇ ¹®Àڼµµ ÀûÀýÇÏ°Ô ÁöÁ¤µÇ¾îÀÖ´ÂÁö¸¦ È®ÀÎ. (´ë°³ ±ÛÀÚ°¡ ±úÁö´Â °æ¿ì¶ó¸é, ¾Æ¸¶µµ ÀÌÂÊÀÌ À߸ø ÁöÁ¤µÇ¾îÀÖÀ» °¡´É¼ºÀÌ ³ô´Ù)
- SQL ¹®Àå Áß¿¡ 'SET NAMES euckr [COLLATE collation_name]'À» »ç¿ëÇÏ¸é ¾Õ¿¡¼ »ìÆ캻 client, connection, results 3°¡Áö¿¡ ´ëÇÑ ¹®ÀÚ¼ÂÀ» ÇѲ¨¹ø¿¡ ÁöÁ¤ÇØÁÙ ¼ö ÀÖÀ½. (¼¹ö ½ÇÇàÁß¿¡µµ º¯°æÀÌ °¡´É. ´Ü, ¼¹ö ½ÇÇàÁß¿¡ SQL ¹®ÀåÀ¸·Î º¯°æÇÏ´Â °ÍÀº ¿µ±¸ º¯°æÀÌ ¾Æ´Ï¶ó ÇöÀç ¿¬°á¿¡ ÇÑÇؼ¸¸ Àӽ÷Πº¯°æÇÏ´Â °ÍÀÓ)
- Àӽà º¯°æÀÌ ¾Æ´Ñ ¿µ±¸ º¯°æÀ» ÇÏ·Á¸é, MySQL ¼¹ö°¡ óÀ½ ½ÃÀÛÇÒ ¶§ Âü°íÇÏ´Â my.cnf ȯ°æ¼³Á¤ ÆÄÀϳ»¿¡ 'init_connect=SET NAMES euckr' ÀÌ·±½ÄÀ¸·Î ÁöÁ¤ÇØµÎ¸é µÊ.
- ÀÌ·¸°Ô ¿µ±¸ º¯°æ°ú Àӽà º¯°æ ¹æ¹ýÀ» È°¿ëÇϸé Á» ´õ ´Ù¾çÇÑ ÀÀ¿ëÀÌ °¡´ÉÇØÁø´Ù.
- ¼¹ö°¡ ½ÃÀÛÇÏ´Â ´Ü°è¿¡¼ ÁöÁ¤ÇÏ´Â °æ¿ì : ´ë°³ ȯ°æ¼³Á¤ ÆÄÀÏÀÎ my.cnf ÆÄÀÏ¿¡¼ 'init_connect=SET NAMES euckr' ¸í·É¾î·Î ÁöÁ¤. ÀÌ·¸°Ô ÇßÀ»¶§´Â ÇØ´ç ¼¹ö¸¦ »ç¿ëÇÏ´Â ¸ðµç ¾ÖÇø®ÄÉÀ̼ǵ鿡°Ô ÀÌ ¼³Á¤ÀÌ ±âº»°ªÀ¸·Î¼ÀÇ ¿µÇâÀ» ¹ÌÄ£´Ù.
- ½ÃÀÛ ´Ü°è°¡ ¾Æ´Ï¶ó ¼¹ö°¡ ½ÇÇàÁß¿¡ µ¥ÀÌÅͺ£À̽º º°·Î ´Ù¸¥ ¹®ÀÚ¼ÂÀ» »ç¿ëÇϵµ·Ï ÁöÁ¤ÇÏ´Â °æ¿ì : ÀÏ´Ü Å¬¶óÀ̾ðÆ®°¡ ¼¹ö·Î ¿¬°áÀ» ÇÑ ÀÌÈÄ¿¡ °ð¹Ù·Î 'SET NAMES euckr' ¸í·É¾î¸¦ ½ÇÇà½ÃÄѼ ÇöÀç ¿¬°á¿¡ ´ëÇÑ ¿¬°á °ü·Ã ¹®ÀڼµéÀ» ¼öÁ¤Çسõ°í ½ÃÀÛÇÑ´Ù. ÀÌ·¸°Ô Çϸé, °¢°¢ÀÇ µ¥ÀÌÅͺ£À̽ºº°·Î ¼·Î ´Ù¸¥ ¿¬°á °ü·Ã ¹®ÀÚ¼ÂÀ» ÁöÁ¤ÇÒ ¼ö ÀÖ°Ô µÊ(!!!)
- Áï, µ¿ÀÏÇÑ ¼¹ö³»¿¡¼ ´ëºÎºÐÀÇ ¾ÖÇø®ÄÉÀ̼ÇÀº utf-8 ¹®ÀÚ¼ÂÀ» »ç¿ëÇϴµ¥ ƯÁ¤ ¾ÖÇø®ÄÉÀ̼Ǹ¸ euckr ¹®ÀÚ¼ÂÀ» »ç¿ëÇÏ°íÀÚ ÇÒ °æ¿ì¿¡ À§¿Í °°Àº ¹æ¹ýÀ» »ç¿ëÇÏ¸é °¡´É.
- Âü°í·Î, PHP¿¡¼ »ç¿ëÇÏ´Â mysqli È®Àå¿¡¼´Â 'SET NAMES ...'¸¦ query ¸Þ¼Òµå·Î ½ÇÇà½ÃÅ°´Â ¹æ¹ý ÀÌ¿Ü¿¡ 'set_charset' À̶ó´Â ´õ ³ªÀº ¸Þ¼Òµå¸¦ ÅëÇؼ µ¿ÀÏÇÑ È¿°ú¸¦ ¾òÀ» ¼öµµ ÀÖÀ½.
- (Áú¹®) PHPMyAdmin¿¡¼ »õ·Î¿î µ¥ÀÌÅͺ£À̽º¸¦ »ý¼ºÇÒ ¶§, ÇØ´ç µ¥ÀÌÅͺ£À̽º¿¡¼ »ç¿ëÇÒ Default collation ¸¸À» ÁöÁ¤ÇÏ°Ô µÇ´Âµ¥... ±×·¸´Ù¸é, ÇØ´ç µ¥ÀÌÅͺ£À̽ºÀÌ Default Character SetÀº ¾î¶»°Ô µÇ´Â °ÍÀϱî?
- (´ë´ä) ¹¹... ´ç¿¬ÇÑ À̾߱â°ÚÁö¸¸, Default collation ¸¸À» ÁöÁ¤ÇÏ°Ô µÇ¸é ÇØ´ç collationÀÌ ¼ÓÇØÀÖ´Â Character SetÀÌ ÀÚµ¿À¸·Î ±× µ¥ÀÌÅͺ£À̽ºÀÇ Default Character SetÀ¸·Î ÁöÁ¤µÊ. ¹°·Ð, PHPMyAdminÀÇ GUI ¹æ½ÄÀ» »ç¿ëÇÏÁö ¾Ê°í, Á÷Á¢ DDL ¹®ÀåÀ» »ç¿ëÇϸé Çϳª Çϳª º°µµ·Î ÁöÁ¤ÇØÁÙ ¼öµµ ÀÖÀ½. ¶ÇÇÑ, PHPMyAdmin¿¡¼ µ¥ÀÌÅͺ£À̽º¸¦ »ý¼ºÇÒ ¶§, Default collation Á¶Â÷µµ ÁöÁ¤ÇÏÁö ¾Ê°Ô µÇ¸é, ´ç¿¬È÷ MySQLÀÇ È¯°æ¼³Á¤ ÆÄÀÏÀÎ my.cnf(¶Ç´Â my.ini)¿¡¼ ÁöÁ¤µÇ¾îÀÖ´Â ½Ã½ºÅÛÀÇ ±âº»ÀûÀÎ Character SetÀÌ Àû¿ëµÇ°Ô µÇ¸ç CollationÀº ÇØ´ç Character SetÀÇ Default collationÀÌ Àû¿ëµÇ°Ô µÊ.
create database mydnsdb;
grant all on mydnsdb.* to mydnsid@localhost IDENTIFIED BY 'mydnspw'; grant all on mydnsdb.* to mydnsid@"%"IDENTIFIED BY 'mydnspw';
flush privileges;
# # ±ÇÇÑ # GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON µ¥ÀÌŸº£À̽ºÀ̸§.Å×À̺íÀ̸§ TO »ç¿ëÀÚID@Á¢¼ÓÈ£½ºÆ® GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON zboard.tableÀ̸§ TO zboard@localhostGRANT ALL PRIVILEGES ON *.* TO zboard@% : ¸ðµç±ÇÇÑÁÖ¸é¼ ¿ÜºÎÁ¢¼Ó ƯÁ¤Å×À̺íÀÇ Æ¯Á¤ÇÊ´õ¿¡°Ô ƯÁ¤±ÇÇѸ¸ ÁÖ±â GRANT select (id, name,.....) on dbname.login to id@'111.1.....' IDENTIFIED by 'ºñºô¹øÈ£' with grant option; 1. GRANT GRANT ±ÇÇÑ1(Ä÷³1[,Ä÷³2,Ä÷³3...])[,±ÇÇÑ2(Ä÷³1[,Ä÷³2,Ä÷³3...]), ±ÇÇÑ3...] ON [ ¿ÀºêÁ§Æ®¸í| Å×À̺í¸í | * ].[µðºñ¸í] TO À¯Àú¸í [IDENTIFIED BY 'ºñ¹Ð¹øÈ£'] [WITH À§µå¿É¼Ç[À§µå¿É¼Ç2,...]] ex) grant all on *.* to 'test0'@'localhost' identified by 'test0'; grant select on *.* to 'test1'@'localhost' identified by 'test1'; grant select on *.* to 'test2'@'localhost' identified by 'test2' with grant option; - ±ÇÇÑ Á¾·ù mysql DBÀÇ user Å×À̺íÀ» describeÇÏ¸é ±ÇÇÑ_Privs Çü½ÄÀÇ Ä÷³ ¸íÀ» ÅëÇØ ±ÇÇÑ ºÎ¿© °¡´É. - ±ÇÇÑ Á¾·ù µÚ¿¡ Ä÷³¸íÀ» ÁöÁ¤Çؼ ƯÁ¤ Ä÷³¿¡ ´ëÇÑ ±ÇÇѸ¸ ºÎ¿©ÇÒ ¼öµµ ÀÖ´Ù. - À§µå ¿É¼Ç WITH GRANT OPTIONÀ» ÅëÇØ ±ÇÇÑÀ» ºÎ¿©¹ÞÀº À¯Àú´Â ´Ù¸¥ À¯Àú¿¡°Ô ÀÚ½ÅÀÌ Çà»çÇÏ´Â ±ÇÇÑÀ» ºÎ¿©ÇÒ ¼ö ÀÖ´Ù. - ±ÇÇÑ ºÎ¿©ÀÚ¿¡°Ô CREATE USER ±ÇÇÑÀÌ ÀÖ°í À¯Àú°¡ Á¸ÀçÇÏÁö ¾ÊÀ» °æ¿ì À¯Àúµµ »ý¼ºÇØÁØ´Ù. 2. REVOKE REVOKE ±ÇÇÑ1(Ä÷³1[,Ä÷³2,Ä÷³3...])[,±ÇÇÑ2(Ä÷³1[,Ä÷³2,Ä÷³3...]), ±ÇÇÑ3...] ON [ ¿ÀºêÁ§Æ®¸í| Å×À̺í¸í | * ].[µðºñ¸í] FROM À¯Àú¸í [IDENTIFIED BY 'ºñ¹Ð¹øÈ£'] ex) revoke select on *.* from 'test2'@'localhost'; - ºÎ¿©Çß´ø ±ÇÇÑÀ» ȸ¼öÇÑ´Ù. - WITH GRANT OPTION À¸·Î ±ÇÇÑÀ» ºÎ¿©Çß´ø À¯ÀúÀÇ ±ÇÇÑÀÌ È¸¼öµÇ¾îµµ ÇÏÀ§ À¯ÀúÀÇ ±ÇÇÑÀº »ç¶óÁöÁö ¾Ê´Â´Ù. grant insert (macSerial, phoneNum, subject, movieDir, movieFileName, photo1, photo2, photo3, photo4, insertDate, insertIP) on ustar.userMovie to 'udbinsert'@'%' identified by '12345'
#
# Å×ÀÌºí »ý¼º
#
drop table tb_cyboard_$id_$code ; create table tb_cyboard_$id_$code ( seq int auto_increment, -- °Ô½ÃÆÇ °íÀ¯ NO no int not null, -- ¼ø¹ø re_no varchar(100) not null, -- ·¹º§/¼ø¼ notice_no int default null, -- °øÁö»çÇ× ¼ø¼ title varchar(100), -- Á¦¸ñ content longtext , -- ³»¿ë input_date datetime , -- ÀÔ·ÂÀÏ cnt int(10) , -- Á¶È¸¼ö email varchar(100) , -- Email homepage varchar(100) , -- HomePage ip_address varchar(20) , -- IP Address agent varchar(50) , -- Agent id varchar(50) , -- ¾ÆÀ̵ð name varchar(50) , -- ¼º¸í password varchar(12) , -- ¾ÏÈ£ html_kind int(1) default 0, -- HTML »ç¿ë ( 0:¹Ì»ç¿ë, 1:»ç¿ë ) secret_kind int(1) default 0, -- ºñ¹Ð ¹®¼ ±¸ºÐ ( 0:°ø°³¹®¼, 1:ºñ¹Ð¹®¼ ) mail_kind int(1) default 0, -- ¸ÞÀÏ ±¸ºÐ ( 0:¹Ì¹ß¼Û, 1:¹ß¼Û ) file_name varchar(50), -- File Name file_rename varchar(50), -- File ReName file_type varchar(50), -- File Type file_size int(10), -- File Size file_route varchar(100), -- File °æ·Î primary key ( seq, no, re_no ),
index idxId (id) );
# »ç¿ëÀÚ¿¡°Ô select, file ±ÇÇÑ ºÎ¿© GRANT SELECT, FILE ON * . * TO "[whomever]"@ "localhost";
revoke insert on table from dalgalim@localhost;
# # mysql reload # flush privileges
alter table tb_cyboard_life_notice change file_name file1_name varchar(50); alter table tb_cyboard_list add board_width varchar(10) default '100%' after board_tail; alter table tb_cyboard_list modify name varchar(50);
alter table mj_deonggi add seq int first; alter table mj_deonggi drop primary key; alter table mj_deonggi add primary key(seq,bub_cd,gae_code,serial,mul_num,deong_date,deong_name); alter table mj_deonggi modify seq int auto_increment;
# # Index # show keys from mj_mulgun;
create index idx_mj_file on mj_file ( bub_cd, serial, main desc, file_type desc, file_url desc );
alter table mj_file add index idx_mj_file ( bub_cd, serial, main desc, file_type desc, file_url desc );
alter table tb_pay drop index idx_tb_pay_state_userid_edate;
# # mysqladmin # mysqladmin -u root -p create DB¸í
# # mysqldump # mysqldump -uID -p (TABLE¸í ȤÀº DB¸í) > ¹é¾÷ÇÒ ÆÄÀÏÀ̸§.sql // local È£½ºÆ®°¡ ¾Æ´Ñ°æ¿ì È£½ºÆ®¸¦ ÁöÁ¤ mysqldump -h192.168.11.10 -uID -p (TABLE¸í ȤÀº DB¸í) > ¹é¾÷ÇÒ ÆÄÀÏÀ̸§.sql // ÀÏ¹Ý port°¡ ¾Æ´Ñ Æ÷Æ®¸¦ ÁöÁ¤ÇÑ °æ¿ì socket ¿É¼Ç mysqldump --socket=(¼ÒÄϸðµâ) -uID -p (TABLE¸í ȤÀº DB¸í) > ¹é¾÷ÇÒ ÆÄÀÏÀ̸§.sql // Å×ÀÌºí ±¸Á¶¸¸ ¹é¾÷¹Þ±â mysqldump -h host -u user -ppassword -d database > script.sql
mysqldump -u root -p lifedb tb_cyhuman_relation_pri --default-character-set=euckr --where="man_userid='moonchoi'" > tb_cyhuman_relation_pri.sql
--skip-opt Disable --opt. Disables --add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, and --disable-keys.
mysqldump -u root -p testdb --default-character-set=euckr > testdb.sql
mysqldump -u root -p --no-data --default-character-set=euckr --compatible=mysql40 nig0412db tb_cyboard_user tb_cyboard_list > cyboard.sql
mysqldump -w "date > 2013-01-01" -u root -p db table > cyboard.sql
mysql -u root -p db -e "source backup.sql"
# µ¥ÀÌŸ¸¸
mysqldump -u root -p wikidb -t > wiki.sql
mysqldump -u root -p wikidb --no-create-info > wiki.sql
# µ¥ÀÌŸ¸¦ Á¦¿ÜÇÑ
mysqldump -u root -p wikidb -d > wiki.sql
mysqldump -u root -p wikidb --no-data > wiki.sql
# ¿¡·¯¹«½ÃÇÏ°í -f
# insert Çϳª¾¿ --complete-insert
# xml·Î ¹Þ±â --xml ȤÀº --x # # restore # mysql -hDB¼¹ö¸í -uÀ¯Àú¸í -pÆнº¿öµå [º¹±¸ÇÒ Å×ÀÌÅͺ£À̽º¸í] < [¿ÜºÎ·Î ÀúÀåµÈ ÆÄÀϸí]
# # ¹é¾÷ & º¹±¸ # mysqldump {-h È£½ºÆ®} -u »ç¿ëÀÚ -p DB¸í > ¹é¾÷ÆÄÀÏ mysql {-h È£½ºÆ®} -u »ç¿ëÀÚ -p DB¸í < ¹é¾÷ÆÄÀÏ mysqldump -u root -p --opt db_moyiza > moyiza.sql mysqldump -u root -p --opt db_board | mysql ---host=remote-host -C database (»óÀÌÇÑ ¸Ó½®) mysql -u moyiza -p db_moyiza < moyiza.sql mysqldump -u root -p --opt db_moyiza | mysql ---host=ns.moyiza.net -C db_moyiza Å×ÀÌºí »ý¼º±¸¹®¸¸À» ȸ鿡¼ º¸·Á¸é ´ÙÀ½°ú °°ÀÌ --no-data¸¦ »ç¿ëÇÑ´Ù. Å×À̺í¸íÀ» »ý·«ÇÏ¸é ¸ðµç Å×À̺í Ãâ·Â mysqldump -u À¯Àú¸í -p --no-data db¸í Å×À̺í¸í # # show # mysql> Show Databases; mysql> Show Tables; mysql> show variables; ¼¹öÀÇ variables(¼³Á¤»çÇ×)Ãâ·Â mysql> show variables like 'have_inno%' Á¶°Ç¿¡ ¸Â´Â variables¸¸ Ãâ·Â mysql> show databases; database¸ñ·Ï mysql> show tables; ÇöÀçDBÀÇ Å×À̺í¸ñ·Ï(temporary tableÀº Ãâ·ÂÇÏÁö ¾ÊÀ½) mysql> show tables from db¸í; ÁöÁ¤µÈ db¸íÀÌ ¼ÒÀ¯ÇÑ Å×À̺í¸ñ·Ï mysql> show tables like 'mem%'; Á¶°Ç¿¡ ¸Â´Â Å×À̺í¸ñ·Ï¸¸ Ãâ·Â mysql> show index from Å×À̺í¸í; À妽º º¸±â mysql> show columns from Å×À̺í¸í; Å×ÀÌºí±¸Á¶(describe Å×À̺í¸í, explain Å×À̺í¸í) mysql> show table status; ÇöÀç DBÀÇ Å×À̺íµéÀÇ »óÅÂ(row¼ö,table type,row±æÀÌ,..) mysql> show table status from db¸í; ÁöÁ¤µÈ DBÀÇ Å×À̺íµéÀÇ »óÅÂ(row¼ö,table type,row±æÀÌ,..) mysql> show create table Å×À̺í¸í; ÇØ´ç Å×ÀÌºí »ý¼º SQL¹® Ãâ·Â
# # status # mysql> status
# # use # use DB¸í mysql> Use Test; # # rename # mysql> rename table Å×À̺í1 to Å×À̺í2; //Å×À̺í¸í º¯°æ(ALTER TABLE Å×À̺í1 RENAME TO Å×À̺í2) mysql> rename table Å×À̺í1 to Å×À̺í2, //Å×À̺í3 to Å×À̺í4; rename multiple tables mysql> rename table db1¸í.Å×À̺í¸í to db2¸í.Å×À̺í¸í; Å×À̺íÀ» ´Ù¸¥ DB·Î À̵¿ # # create # mysql> create database DB¸í mysql>create database tradementor; mysql> create table Å×À̺í2 as select * from Å×À̺í1; Å×À̺í1°ú µ¿ÀÏÇÑ Å×ÀÌºí »ý¼º(with µ¥ÀÌÅÍ, as´Â »ý·«°¡´É) mysql> create table Å×À̺í2 as select * from Å×À̺í1 where 1=2; Å×À̺í1°ú µ¿ÀÏÇÑ ±¸Á¶ÀÇ Å×ÀÌºí »ý¼º(without µ¥ÀÌÅÍ, 1=2´Â 0À¸·Î ÇÒ¼öµµ ÀÖ´Ù.) mysql> create table Å×À̺í¸í(..) type=heap min_rows=10000; 10000row¸¦ ¼ö¿ëÇÒ ¼ö ÀÖÀ» ¸¸Å ¸Þ¸ð¸®ÇÒ´ç(heap typeÀ̹ǷÎ) mysql> CREATE TABLE Å×À̺í¸í (...); # # select # select * from signal_jongmok where date_format(outdate,'%Y%m%d')='00000000' select if(outdate='00000000',unix_timestamp(),unix_timestamp(outdate)) from signal_jongmok mysql> select version(); MySQL¼¹ö¹öÀü Ãâ·Â mysql> select * from work where ¿¸í regexp "Á¤±ÔÇ¥Çö½Ä"; mysql> select substring(subject from 9) from zetyx_board_database where substring(subject, 1, 8) = '[ORACLE]'; # # alter # mysql> alter database db¸í DEFAULT CHARACTER SET charset mysql> alter table Å×À̺í¸í add Ä÷³¸í µ¥ÀÌÅÍŸÀÔ; Ä÷³Ãß°¡ mysql> alter table Å×À̺í¸í del Ä÷³¸í; Ä÷³Á¦°Å mysql> alter table Å×À̺í¸í modify Ä÷³¸í Ä÷³Å¸ÀÔ; Ä÷³¸í¿¡ ÁöÁ¤µÈ Ä÷³Å¸ÀÔÀÇ º¯°æ mysql> alter table Å×À̺í¸í change oldÄ÷³¸í newÄ÷³¸í Ä÷³Å¸ÀÔ; Ä÷³¸í º¯°æ mysql> alter table Å×À̺í¸í type=innodb; Å×À̺ítypeº¯°æ # # drop # mysql> drop database DB¸í mysql> drop table testtable1; mysql> DROP TABLE IF EXISTS Å×À̺í¸í; # # insert # mysql> insert into user (Host, User, Pasword) values('localhost', 'trade', PASSWORD('mentor')); mysql> insert into db values('%', 'tradementor', 'trade', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y'); 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> insert into Å×À̺í2 select * from Å×À̺í1; Å×À̺í1ÀÇ µ¥ÀÌÅ͸¦ Å×À̺í2¿¡ insert 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'); # # replace # mysql> replace into test values('maddog','kang myung gyu')' # # explain # mysql> explain select u.uid, u.name, a.name from sm_user u, sm_addr a where u.uid=a.uid; # # load # mysql>load data infile "µ¥ÀÌÅÍÆÄÀÏ" into table Å×À̺í¸í ; # # flush # mysql> flush privileges; # # grant # mysql> grant all on *.* to 'trade'@'localhost' identified by 'mentor'; mysql> grant all on tradementor.* to 'trade'@'localhost' identified by 'mentor'; mysql> grant select on tradementor.* to 'trade'@'localhost' identified by 'mentor'; mysql> grant update on tradementor.* to 'trade'@'localhost' identified by 'mentor'; mysql> grant select,update on tradementor.* to 'trade'@'localhost' identified by 'mentor'; mysql> grant all privileges on DB¸í.* to ¾ÆÀ̵ð@Á¢¼Ó¾ÆÀÌÇÇ identified by 'Æнº¿öµå'; mysql> GRANT ALL PRIVILEGES ON dbname.* TO username@'%' IDENTIFIED BY 'password';
# # mysql rownum # select *, @rowCount := @rowCount + 1 as rowCount from cyBuyer ta, (select @rowCount := 0) tb where buyerNat = '$buyerNat' order by buyerListSeq desc
#
# mysql group_concat
#
SELECT B.id, group_concat(B.value ORDER BY B.value DESC SEPARATOR '-' ) FROM A, B WHERE B.id = A.id GROUP BY B.id
#
# ³¯Â¥ ºñ±³
#
dateÄ÷³¿¡ Á¤º¸°¡ ¹®ÀÚ¿ ŸÀÔÀÌ¸é¼ Çü½Ä¸¸ ³¯Â¥ÇüÀ̶ó¸é
´ÙÀ½Ã³·³ÇÏ½Ã¸é µÇ°Ú¾î¿ä
SELECT *
FROM BOARD
WHERE date <= date_format(now(),'%Y-%m-%d')
;
¹Ý´ë·Î dataÄ÷³ Á¤º¸°¡ ³¯Â¥ÇüÀÌ°í Çü½ÄÀÌ %Y-%m-%d À̶ó¸é
´ÙÀ½Ã³·³ÇÏ½Ã¸é µÇ°Ú¾î¿ä
SELECT *
FROM BOARD
WHERE date <= str_to_date(now(),'%Y-%m-%d')
;
if( noticeYN = 1, if((noticeSdate <= '$nowDate' and noticeEdate >= '$nowDate' ), 1, 0), 0 ) noticeYNChk
|