|
|
▮ mysqldump mysqldump [¿É¼Ç] db_name [table_name] > backup_filename mysqldump [¿É¼Ç] --databases [¿É¼Ç] db_name1, db_name2, .... > backup_filename mysqldump [¿É¼Ç] --all-databases [¿É¼Ç] > backup_filename [¿É¼Ç] -A, --all-databases : ¸ðµç DB¸¦ ´ýÇÁ --add-locks : ´ýÇÁ Àü¿¡ lock ´ýÇÁ ÈÄ¿¡ unlock --add-drop-table : ´ýÇÁ ÀÌÈÄ¿¡ Ãâ·Â¹°ÀÇ ¾Õ¿¡ drop table ¸í·É Ãß°¡ (º¹±¸ À§ÇØ) -B, --databases : ¿©·¯ DB¸¦ µ¿½Ã¿¡ ´ýÇÁÇÒ ¶§ »ç¿ë -f, --force : ¿¡·¯¸¦ ¹«½Ã -h, --host : ÁöÁ¤ÇÑ È£½ºÆ®ÀÇ µ¥ÀÌÅ͸¦ ´ýÇÁ -t : data¸¸ ´ýÇÁ -d : µ¥ÀÌÅ͸¦ Á¦¿ÜÇÏ°í ½ºÅ°¸¶¸¸ ´ýÇÁ -p : »ç¿ëÀÚÀÇ ¾ÏÈ£¸¦ ÁöÁ¤ -P : Æ÷Æ®¹øÈ£ ÁöÁ¤ -u : »ç¿ëÀÚ¸í ÁöÁ¤ ▮ Data¸¸À» ¹é¾÷ : LOAD DATA INFILE¿¡¼ »ç¿ëÇÑ ÆÄÀÏÀÇ °æ¿ì Data¸¸ Æ÷ÇÔÇÏ´Â °Íó·³ select ¸í·ÉÀ» ÀÌ¿ëÇؼ Data¸¸ Æ÷ÇÔµÈ Çü½ÄÀ¸·Î ¹é¾÷ [¹®¹ý] - SELECT column_name, output_type INTO OUTFILE '°æ·Î ¹× ÆÄÀÏ¸í¡® [FIELDS TERMINATED BY 'Á¾´Ü¹®ÀÚ¡®] FROM table_name WHERE Á¶°Ç±¸¹® ; ex) student tableÀÇ ³»¿ëÀ» °¢ Ä÷³À» | ·Î ±¸ºÐÇؼ ¼ºÀÌ ¸¶¾¾ÀÎ Çлý¸¸ ¡®/root/student.dat'·Î ÀúÀå mysql> select * -> into outfile '/root/student.dat' -> fields terminated by '|' -> from student -> where sname like '¸¶%'; Query OK, 3 rows affected (0.00 sec) [root@linux /root]# cat student.dat 012312|¸¶·®|³²|3|¹°¸®|3.50 013213|¸¶ÃÊ|³²|2|ÈÇÐ|2.42 013111|¸¶´ë|³²|1|»ý¹°|1.23 ¡Ø [= ¿ÜºÎ ¸í·É¾î] # mysql -p -e "select * from school.student where sname like '¸¶%';" ▮ º¹±¸ : ¹é¾÷µÈ ³»¿ëÀÇ º¹±¸´Â mysql ¸í·É¾î³ª mysqladmin ¸í·É¾î¸¦ »ç¿ëÇÔ ex) rootÀÇ password°¡ 12345ÀÎ »óÅ¿¡¼ database1À» ¹é¾÷ÇÑ data.db¶ó´Â ¹é¾÷ÆÄÀÏÀ» º¹±¸ $ mysqladmin -u root -p12345 drop database1 // database1 »èÁ¦ $ mysqladmin -u root -p12345 create database1 // database1 »ý¼º $ mysql -u root -p12345 database1 < data.db // data.dbÆÄÀÏ·Î database1 º¹±¸ ====================================================================================== ¹é¾÷ÆÄÀÏÀ» »ý¼ºÇÒ À§Ä¡·Î À̵¿ÇÑ´Ù. MySQL ¹é¾÷ mysqldump -u»ç¿ëÀÚ¸í -pÆнº¿öµå DB¸í > »ý¼ºÇÒ ÆÄÀÏ ¿¹) /usr/local/mysql/bin/mysqldump -uroot -pÆнº¿öµå system > backup.sql °¡²û ÇѱÛÀÌ ±úÁ®¼ ³ª¿Ã ¶§°¡ ÀÖ´Ù. ÀÌ·² ¶§´Â character set À» ÁöÁ¤ÇÏ¿© ¹é¾÷ÆÄÀÏÀ» ¸¸µé¸é µÈ´Ù. ¿¹) /usr/local/mysql/bin/mysqldump -uroot -pÆнº¿öµå system --default-character-set latin1 > backup.sql; ±×·³ DBÀÇ character set Àº ¾î¶»°Ô ¾Ë±î? MySQL¿¡ Á¢¼ÓÇϽŠ´ÙÀ½ status Ä¡¸é µÈ´Ù. ... Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 ... º¹±¸ÇÒ¶§´Â ¿¹) mysqladmin -u root -pÆнº¿öµå create database¸í mysql -u root -pÆнº¿öµå database¸í < backup.sql database ¸íÀ» ÁöÁ¤ÇÏÁö ¾Ê°í Àüü¸¦ ´ÙÇÒ °æ¿ì,, -- Àüü ¹é¾÷ /usr/local/mysql/bin/mysqldump -u¾ÆÀ̵ð -pÆнº¿öµå --all-database > backup.sql -- Àüü º¹¿ø /usr/local/mysql/bin/mysql -u¾ÆÀ̵ð -pÆнº¿öµå < backup.sql ==================================================================================
MySQL DUMP ¹é¾÷ ¹× º¹¿ø
¹é¾÷/º¹±¸ ¸ðµÎ cmd¸ðµå¿¡¼ ÇÒ°Í. ====¹é¾÷==== 1.cmdÁ¢¼Ó -±ÇÇÑÀÌ ÀÖ´Â °èÁ¤À¸·Î dumpÇÒ°Í .(º¸Åë root°èÁ¤) 2.¸í·É¾î -p[ºñ¹Ð¹øÈ£]´Â ÇÑÄ ¶ç¿ìÁö ¸»°í ºÙ¿©¼ ÇÒ°Í. 3.ÀúÀåÀ§Ä¡¸¦ ÆÄÀÏ¸í ¾Õ¿¡ ÀûÁö¾ÊÀ¸¸é ÇöÀç À§Ä¡ÇØ ÀÖ´Â Æú´õ¿¡ ¹é¾÷ÇÑ´Ù. ====º¹±¸==== 1. Mysql 5.0 °èÁ¤»ý¼º, µðºñ»ý¼º ÂüÁ¶. -°èÁ¤ ¹× °èÁ¤¿¡ ±ÇÇÑÀ» ÁÖ°í db±îÁö ¼ÂÆÃ. 2.cmdÁ¢¼Ó -±ÇÇÑÀÌ ÀÖ´Â °èÁ¤À¸·Î dumpÇÒ°Í .(º¸Åë root°èÁ¤) 2.¸í·É¾î(¹Ø¿¡) -u[¾ÆÀ̵ð] -p[ºñ¹Ð¹øÈ£]´Â ÇÑÄ ¶ç¿ìÁö ¸»°í ºÙ¿©¼ ÇÒ°Í. ¸í·É¾î == ¹é¾÷ == DBÀüü ´ýÇÁ mysqldump -u[¾ÆÀ̵ð] -p[ºñ¹Ð¹øÈ£] --all-databases > [ÀúÀåµÉ ÆÄÀϸí] DB¸¸ ´ýÇÁ mysqldump -u[¾ÆÀ̵ð] -p[ºñ¹Ð¹øÈ£] [µðºñ¸í] > [ÀúÀåµÉ ÆÄÀϸí] Å×ÀÌºí ±¸Á¶¸¸ mysqldump -u[¾ÆÀ̵ð] -p[ºñ¹Ð¹øÈ£] --no-data [µðºñ¸í] [Å×À̺í¸í] > [ÀúÀåµÉ ÆÄÀϸí] Å×ÀÌºí±¸Á¶¸¦ Á¦¿ÜÇÑ µ¥ÀÌÅ͸¸ ´ýÇÁ mysqldump -u[¾ÆÀ̵ð] -p[ºñ¹Ð¹øÈ£] --no-create [µðºñ¸í] [Å×À̺í¸í] > [ÀúÀåµÉ ÆÄÀϸí] ==º¹±¸ == ´ýÇÁÆÄÀÏÀ» ÀÌ¿ëÇÑ º¹±¸ mysql -u[¾ÆÀ̵ð] -p[¾ÏÈ£][µðºñ¸í] < [ÆÄÀϸí] Æнº¿öµå µ¿½Ã ÀԷ½à º¹±¸ ¾ÈµÇ¸é mysql -u[¾ÆÀ̵ð] -p[°ø¹é][µðºñ¸í] < [ÆÄÀϸí] |