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·Î º¹±¸½Ãµµ.
|