¸¶½ºÅÍÆ÷À¯(Master4U)
Monday, 17 of January
 

·Î±×ÀÎ Category
MySQL °ü¸®¹®¼­
°ü¸®ÀÚ  2011-01-01 16:08:45 Hit:4510
¸µÅ© #1: http://blog.naver.com/kaiserjh1/20118199509


http://blog.naver.com/kaiserjh1/20118199509


mysql ÀÇ ¾ÈÁ¤ÀûÀÎ Á¾·á
mysqladmin -u root -p shutdown



mysql ÀÚµ¿È­ Á¢¼Ó
./mysql -uroot -p'passwd' mysql


mysql¿ø°ÝÁ¢¼Ó
default·Î local¼­¹ö¿¡¼­¸¸ Á¢¼Ó°¡´É.
mysql db¿Í userÅ×À̺íÀÇ È£½ºÆ®Çʵ带 %¶Ç´Â Á¢¼ÓÀ» Çã¿ëÇÒ IPÁÖ¼Ò¼³Á¤.
./mysql -u tempuser -p temp_db2 -h 192.168.0.111 -P 3306

ÆÐ½º¿öµå Àç¼³Á¤
mysqladmin -u °èÁ¤ -p password ÆÐ½º¿öµå
mysql>update user set password=password('12345) where user='root';
mysql>set password for root=password('12345');
mysql>flush privileges;

ÆÄÀÏ·Î µÈ Äõ¸®¹® ½ÇÇà
./mysql -u root -p mysql < ./temp3.sql

À¯Àúµî·Ï°ú dbµî·Ï ½ºÅ©¸³Æ®
create database mare_db;
insert into db
values('localhost','mare_db','mare','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
insert into user
values('localhost','mare',password('12345'),'N','N','N','N','N','N','N','N','N','N',
'N','N','N','N','N','N','N','N','N','N','N',1,'','','','','','');

¼ÒÄÏÆÄÀÏÀ§Ä¡ ÁöÁ¤ÇÏ¿© Á¢¼ÓÇϱâ
./mysql -u root -p mysql -S /var/lib/mysql/mysql.sock

mysql passwod º¹±¸
mysqlÀÇ table ±ÇÇÑÀ» skipÇÏ°í ½ÇÇà½ÃŲÈÄ º¹±¸.
./mysqld_safe --skip-grant-table

update¹®À¸·Î º¹±¸.
flush privileges;

mysql ÇÁ·Î¼¼½º¸®½ºÆ® ¹× ȯ°æ°ª º¸±â
./mysqladmin -u root -p processlist
./mysqladmin -u root -p variables
./mysqladmin -u root -p variables |grep max_connections



too many connections ¿¡·¯½Ã Á¶Ä¡
./mysqld_safe -O max_connections=500 -O table_cache=256 -O wait_timeout=57600
(ȯ°æº¯¼ö°ªÀ» Á¶Á¤ÇÔ)

mysqlÀ» mysql user·Î ½ÇÇà½Ã۱â
chown -R mysql:mysql /var/lib/mysql

mysql erro Äڵ庸±â
perror -v 101

mysqladmin Ȱ¿ë
mysqladmin -u root -p create µ¥ÀÌÅͺ£À̽º
mysqladmin -u root -p drop µ¥ÀÌÅͺ£À̽º
mysqladmin -u root -p reload or flush-privieges
mysqladmin -u root -p processlist
mysqladmin -u root -p kill 8(threads)
mysqladmin -u root -p status
mysqladmin -u root -p extended-status
mysqladmin -u root -p variables
mysqladmin -u root -p version
mysqladmin -u root -p ping

mysql logºÐ¼®
mysql Áַα×(¿¡·¯·Î±×)ÆÄÀÏ
db ÀúÀå¼Ò¿¡ hostname.err·Î ÀúÀåµÊ.

mysql Äõ¸®·Î±× ³²±â±â
mysqld_safe --log=mysql_query_log &
(Äõ¸®ÀÇ ±â·ÏÀ̹ǷΠ¼ø°£¿¡ Âû¼ö ÀÖ´Ù,Á¶½É)

mysql ¹ÙÀ̳ʸ®(binary)·Î±× ³²±â±â
mysqld_safe --log-bin=mysql_binary_log
(insert,update,deleteÄõ¸®¿¡ µ¥ÀÌÅͰ¡ º¯°æ½Ã ±× ³»¿ªÀ» ±â·Ï, ÁÖ ¸ñÀûÀº slave¿ÍÀÇ replication)

mysql Æ©´×
- slow log¸¦ Ȱ¿ëÇÑ ÁöÁ¤µÈ ½Ã°£ÀÌ»ó ÁúÀǸ¦ °è¼ÓÇÏ´Â ÁúÀǹ® Àâ¾Æ³»±â.
mysqld_safe --log-slow-queries=mysql_slow_log
(ȯ°æ°ªÀÇ long_query_time ¿¡ ÁöÁ¤µÈ °ªº¸´Ù ³ôÀ»½Ã ±â·Ï)

¿ø°ÝÁö¼­¹ö¿¡¼­ÀÇ Á¢¼Ó
mysql DBÀÇ user , db Å×ÀÌºí¿¡ host¸¦ %·Î ¼³Á¤ or 192.168.1.%
mysql>grant all on mysql.* to comansun@192.168.1.20;

my.cnf ÆÄÀÏÀ» ÀÌ¿ëÇÑ ¼º´ÉÆ©´×
share µð·ºÅ丮¿¡ ¸Þ¸ð¸®¿ë·®¿¡ µû¸¥ *.cnf ÆÄÀÏÀÌ Á¸Àç
my-innodb-heavy-4G.cnf (4GÀÌ»ó)
my-huge.cnf (1G~2GÀÌ»ó)
my-large.cnf (512MÀÌ»ó)
my-medium.cnf (32M~64MÀÌ»ó)
my-small.cnf (64MÀÌÇÏ)

ÅØ½ºÆ®ÆÄÀÏÀ» ƯÁ¤Å×ÀÌÅͺ£À̽º·Î ÀÔ·Â
mysqlimport -u root -p µ¥ÀÌÅͺ£À̽º¸í ÅØ½ºÆ®ÆÄÀϸí
´Ü, ÅØ½ºÆ®ÆÄÀϸíÀº DBÀÇ Å×À̺í¸í°ú µ¿ÀÏÇÏ¿©¾ßÇÔ.
(Å×À̺íÀº ÀÌ¹Ì ½ºÅ°¸¶°¡ Çü¼ºµÈ»óÅÂ)



½©»ó¿¡¼­ mysql ½ºÅ°¸¶ È®ÀÎ
mysqlshow -u root -p DB table column

mysql ¹é¾÷°ú º¹±¸
¹æ¹ý 1. µð·ºÅ丮 Àüü¾ÐÃà
¹æ¹ý 2. mysqldump -u root -p db¸í > ÀúÀåÆÄÀÏ¸í  
          mysql -u root -p db¸í < ÀúÀåÆÄÀϸí

ƯÁ¤Å×ÀÌºí ¹é¾÷ ¹× º¹±¸
mysqldump -u root -p db¸í table¸í > ÀúÀåÆÄÀϸí
mysql -u root -p db¸í < ÀúÀåÆÄÀϸí

¿©·¯°³ÀÇ Å×ÀÌÅͺ£À̽º Çѹø¿¡ ¹é¾÷ ¹× º¹±¸
mysqldump -u root -p --database db1, db2, db3 > ÀúÀåÆÄÀϸí
mysql -u root -p < ÀúÀåÆÄÀϸí
(--database ¿É¼Ç »ç¿ë½Ã create ¸í·ÉÀ¸·Î db¸¦ »ý¼ºÇÑ´Ù.)



Àüüµ¥ÀÌÅͺ£À̽º Çѹø¿¡ ¹é¾÷
mysqldump -u root -p --all-database > ÀúÀåÆÄÀϸí

Å×ÀÌºí½ºÅ°¸¶¸¸ ¹é¾÷Çϱâ
mysqldump -u root -p -d mysql > mysql.sql

±âÁ¸Å×ÀÌºí »èÁ¦ÇÏ´Â ¿É¼ÇÃß°¡ ¹é¾÷
mysqldump -u root -p --add-drop-table temp_db > temp_db.sql

¿ø°ÝÁö¼­¹ö mysql¹é¾÷
mysqldump -u root -p -h 192.168.0.111 sspark > sspark.sql
(sspark.sqlÀÌ ·ÎÄÿ¡ »ý¼ºµÈ´Ù)

Æ÷Æ®°¡ »óÀÌÇÒ½Ã
mysqldump -u root -p -h 192.168.0.111 -p 3307 sspark > sspark.sql

±úÁøÅ×ÀÌºí º¹±¸Çϱâ.
*mysqlÀ» ³»¸°ÈÄ ÀÛ¾÷ÇÑ´Ù.
myisamchk -> *.MYI¿¡¸¸ ÇØ´çµÈ´Ù.
-c ¿É¼Ç¾øÀ»¶§¿Í µ¿ÀÏÇÑÁ¡°Ë(-_-)
-s ¿¡·¯¸Þ¼¼Áö¸¸ Ãâ·Â
-v »ó¼¼ÇÏ°Ô Ãâ·Â
-i °á°ú¸¦ »ó¼¼È÷ Á¾ÇÕ
-e ´Ü¼øÇÑ Á¡°Ë¸¸À» Çҽà °¡Àå Á¤¹ÐÇÏ°Ô Áø´Ü. -ev
-r ±úÁø Å×À̺íÆÄÀÏ º¹±¸ -rv
-o -r·Î º¹±¸ÇÏÁö ¸øÇßÀ»½Ã »ç¿ë -rº¸´Ù ¾ÈÁ¤¼ºÀÌ ³ôÁö¸¸ ¼Óµµ°¡ ¶³¾îÁø´Ù.
-e -r or -o ·Î º¹±¸°¡ µÇÁö ¾Ê¾ÒÀ»½Ã »ç¿ë. µ¥ÀÌÅÍ ¹é¾÷ÈÄ »ç¿ë. º¹±¸À²ÀÌ °¡Àå ³ôÀ¸¸é¼­ °¡Àå ÀüÇÏÁö ¸øÇÏ´Ù. -re -oe

*.FRM(Å×ÀÌºí±¸Á¶ÆÄÀÏ)·Î ÀÎÇØ º¹±¸°¡ ºÒ°¡´ÉÇÒ½Ã
±âÁ¸ ¹é¾÷º»À» º¹»ç , ¹é¾÷º» ¾øÀ»½Ã ÀÏ´Ü *.MYI³ª *.MYD¸¦ ¹é¾÷ÈÄ mysql¿¡ Á¢¼ÓÇÏ¿© create table·Î Àç»ý¼º.myisamchk »ç¿ë

*.MYI(À妽ºÆÄÀÏ)·Î ÀÎÇØ º¹±¸°¡ ºÒ°¡´ÉÇÒ½Ã
¹é¾÷º» º¹»ç, ¹é¾÷º» ¾øÀ»½Ã *.MYD,*.FRMÀ» ¹é¾÷ÈÄ delete from table¸íÀ» »ç¿ë ÇØ´ç Å×À̺íÀÇ ¸ðµç ·¹ÄÚµå(µ¥ÀÌÅÍ)µéÀ» »èÁ¦.
MYD¿Í FRMÀ» ´Ù½Ã º¹»çÈÄ myisamchk·Î º¹±¸½Ãµµ.
º»¹®Àμ⺻¹®¸ÞÀϹ߼Û
¡â phpMyAdmin ¼³Ä¡ ÇØº¸±â
¡ä MySQLÀÇ ¿©·¯°¡Áö ºñ¹Ð¹øÈ£ ¼³Á¤ ¹æ¹ý
Copyright 1999-2025 Zeroboard / skin by ChanBi