ȸ¿ø°¡ÀԡžÆÀ̵ð/ºñ¹øã±â
ȨÀ¸·Î

[MySQL - µ¥ÀÌÅÍ ¹é¾÷°ú º¹±¸]MySQLÀÇ mysqldump ¹é¾÷°ú ¹ÙÀ̳ʸ® ¹é¾÷ ¹× º¹±¸ ¹æ¹ý
13³â Àü
MySQLÀÇ µ¥ÀÌÅ͸¦ ¹é¾÷ÇÏ´Â ¹æ¹ýÀº Å©°Ô µÎ°¡Áö·Î ºÐ·ù ÇÒ ¼ö ÀÖ´Ù.

1. mysqldump¸¦ ÀÌ¿ëÇÑ textŸÀÔÀÇ ¹é¾÷ ¹æ¹ý.
2. µ¥ÀÌÅÍÆÄÀÏ ÀÚü¸¦ º¹»çÇÏ´Â ¹é¾÷ ¹æ¹ý.

µÎ°¡Áö ¹æ¹ý¸ðµÎ Àå´ÜÁ¡ÀÌ ÀÖÀ¸¹Ç·Î ¿ëµµ¿Í ÇÊ¿ä¿¡ ¸Â°Ô »ç¿ëÇÏ´Â °ÍÀÌ ÁÁ´Ù.



1. mysqldump¸¦ ÀÌ¿ëÇÑ ¹é¾÷(Ãâó : http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html)

 mysqldump´Â Igor Romanenko¶ó´Â »ç¶÷ÀÌ ¸¸µç ¹é¾÷ ÇÁ·Î±×·¥ÀÌ´Ù. ÀÌ´Â database³ª databaseÀÇ ÀϺθ¦ ¹é¾÷ÇÏ´Â ¸ñÀûÀ̳ª ´Ù¸¥ ½Ã½ºÅÛÀ¸·Î ¿Å±æ¶§ »ç¿ëµÈ´Ù. ±×¸®°í ¿øÇÏ´Â ºÐ¸® ¹®ÀÚ¸¦ ³ÖÀ» ¼ö ÀÖ¾î CSVÆÄÀÏ µîÀ¸·Î ¸¸µé ¼ö ÀÖÀ¸¸ç, XMLÆ÷¸ËÀ¸·Î ¸¸µé ¼öµµ ÀÖ´Ù.
 ±²ÀåÈ÷ ¸¹Àº ¿É¼ÇµéÀ» Á¦°øÇÏ°í ÀÖ´Â ¸¸Å­ »ç¿ëÇÏ´Â ¹æ¹ý°ú ¿ëµµ ¶ÇÇÑ ¿©·¯°¡ÁöÀÌ´Ù. ¸ÕÀú °¡Àå ±âº»ÀûÀÎ »ç¿ë¹ý ºÎÅÍ ¾Ë¾Æ º¸ÀÚ. ¾Æ·¡¿¡ ¿É¼ÇÇ¥¸¦ ÷ºÎÇÒ °ÍÀÌ´Ù.

1) database¹é¾÷
shell> mysqldump -u user -p ¹é¾÷ÇÒ db_name > ÆÄÀϸí
shell> mysqldump -u comnic -p database1 > database1_20090321000000.sql
¡¡

À§´Â comnicÀ̶ó´Â À¯Àú·Î database1À̶ó´Â database¸¦ database1_20090321000000.sqlÆÄÀÏ·Î ¹é¾÷Ç϶ó´Â ³»¿ëÀÌ´Ù.


2) table¹é¾÷
shell> mysqldump -u user -p ¹é¾÷ÇÒ db_name table_name > ÆÄÀϸí
shell> mysqldump -u comnic -p database1 table1 > table1_20090321000000.sql
¡¡

À§´Â comnicÀ̶ó´Â À¯Àú·Î database1ÀÇ table1À̶ó´Â tableÀ» table1_20090321000000.sqlÆÄÀÏ·Î ¹é¾÷Ç϶ó´Â ³»¿ëÀÌ´Ù.


3) Table ±¸Á¶¸¸ ¹é¾÷
shell> mysqldump --no-data -u user -p ¹é¾÷ÇÒ db_name > ÆÄÀϸí
shell> mysqldump --no-data -u comnic -p database1 > 20090321000000.sql
¡¡

À§´Â comnicÀ̶ó´Â À¯Àú·Î database1ÀÇ table±¸Á¶¸¦ 20090321000000.sqlÆÄÀÏ·Î ¹é¾÷Ç϶ó´Â ³»¿ëÀÌ´Ù.
--no-data ¿É¼ÇÀº create table¹®À常À» ¹é¾÷Çϱ⸦ ¿øÇÒ¶§ À¯¿ëÇÑ´Ù.


4) Tableº°·Î ¹é¾÷ ÆÄÀÏ »ý¼º
shell> mysqldump -u user -p --tab=¹é¾÷Path ¹é¾÷ÇÒ db_name
shell> mysqldump -u comnic -p --tab=/backup database1
¡¡

/backupÀ̶ó´Â Æú´õ¿¡ table¸¶´Ù °¢°¢ tbl_name.sqlÆÄÀÏ°ú tbl_name.txtÆÄÀÏÀ» »ý¼ºÇÑ´Ù. ÀÌ´Â TableÀÇ ±¸Á¶¿Í µ¥ÀÌÅ͸¦ ºÐ¸® ÇÏ¿© ¹é¾÷ÇÏ´Â °ÍÀ» ÀÇ¹Ì ÇÑ´Ù. .sqlÆÄÀÏÀº ±¸Á¶¸¦ .txtÆÄÀÏÀº µ¥ÀÌÅ͸¦ ÀúÀåÇÑ´Ù. À̶§ ºÐ¸® ¹®ÀÚ°¡ »ç¿ëµÇ´Âµ¥ ±âº»ÀûÀ» tabÀÌ »ç¿ëµÈ´Ù. ÀÌ ºÐ¸®¹®ÀÚ¸¦ º¯°æÇÏ¸é ¿øÇÏ´Â ºÐ¸® ¹®ÀÚ·Î ±¸¼ºµÈ ÆÄÀÏÀ» ¸¸µé ¼ö ÀÖ´Ù.


5) CSVÆÄÀÏ·Î ¹é¾÷ ¹× ´Ù¸¥ ºÐ¸® ¹®ÀÚ·Î ¹é¾÷
shell> mysqldump -u user -p --tab=¹é¾÷Path  --fields-terminated-by='ºÐ¸®¹®ÀÚ' ¹é¾÷ÇÒ db_name
shell> mysqldump -u comnic -p --tab=/backup --fields-terminated-by=',' database1
¡¡

±âº»ÀûÀÎ ºÐ¸®¹®ÀÚÀÎ tab´ë½Å ,¸¦ »ç¿ëÇØ ¹é¾÷À» ÇÑ´Ù. Áï, CSVÆÄÀÏ·Î »ý¼ºµÈ´Ù.


6) XMLÆ÷¸ËÀ¸·Î ¹é¾÷
shell> mysqldump -u user -p --xml ¹é¾÷ÇÒ db_name > ÆÄÀϸí
shell> mysqldump -u comnic -p --xml database1 > 20090321000000.xml
¡¡

 

2. º¹±¸Çϱâ

 À§¿Í °°Àº ¹æ¹ýµé·Î ¹é¾÷µÈ ÆÄÀÏÀ» º¹±¸ÇÏ´Â ¹æ¹ýÀº ¾Æ·¡¿Í °°´Ù.

¡¡
shell> mysql -u user -p db_name < ÆÄÀϸí
shell> mysql -u comnic -p database1 < 20090321000000.sql
¡¡

º¹±¸½Ã ÇѱÛÀÌ ±úÁö´Â °æ¿ì°¡ Á¾Á¾ ÀÖ´Ù. ±×·²¶§´Â ¾Æ·¡¿Í °°ÀÌ --default-character-set ¿É¼ÇÀ» »ç¿ëÇØ º¹±¸ÇÑ´Ù.
 
¡¡
shell> mysql -u user -p --default-character-set=euckr db_name < ÆÄÀϸí
¡¡



3. ¹ÙÀ̳ʸ® ¹é¾÷(ÆÄÀÏ º¹»ç ȤÀº ¾ÐÃàÀ» ÀÌ¿ë)

 MySQLÀ» »ç¿ëÇÏ´Â »ç¿ëÀÚµéÀº ÈçÈ÷ dataÆÄÀÏÀ» º¹»çÇÏ´Â ¹æ½ÄÀÇ ¹é¾÷ÀÌ ½±°í ºü¸£´Ù°í ¾ê±âµéÀ» ÇÑ´Ù. ³ª ¶ÇÇÑ ±×·±½ÄÀ¸·Î ¹é¾÷À» ¸¹ÀÌ ÇÏ°í ÀÖ´Ù. shell ÇÁ·Î±×·¥À¸·Î ÀÚµ¿ ¹é¾÷ ÈÄ ´Ù¸¥ ½Ã½ºÅÛÀ¸·Î ftp¸¦ ÀÌ¿ëÇØ ¿Å±â´Â ¹æ¹ýÀ¸·Î ÀÚµ¿È­¸¦ ¸¹ÀÌ »ç¿ë ÇÏ°í ÀÖ´Ù. shell¸¦ ÀÌ¿ëÇÑ ÀÚµ¿È­ ºÎºÐÀº ´ÙÀ½¿¡ ´Ù·çµµ·Ï ÇÏ°Ú´Ù.

1) º¹»ç ¹× ¾ÐÃàÀ» ÀÌ¿ëÇÑ ¹é¾÷
 cp¿Í tar¸¦ ÀÌ¿ëÇÏ¸é ½±°Ô ¹é¾÷À» ÇÒ ¼ö ÀÖ´Ù. º¹»çÀü¿¡ lockÀ» °É¾îÁÖ°í º¹»ç°¡ ³¡³­ÈÄ unlock¸¦ ²À ÇØ ÁÖ¾î¾ß ÇÑ´Ù.
º¹±¸´Â ÆÄÀÏ ±×³É ´Ù½Ã º¹»ç ÇØ ÁÖ¸é µÈ´Ù. µ¥ÀÌÅ͸¦ ¿Å±â´Â °úÁ¤À̶ó¸é º¹»ç ÀÌÈÄ Ãß°¡µÈ µ¥ÀÌÅ͸¦ ´Ù½Ã Ãß°¡ ÇØ ÁÖ¾î¾ß ÇÑ´Ù.

2) mysqlhotcopy¸¦  ÀÌ¿ëÇÑ ¹é¾÷(Ãâó:http://dev.mysql.com/doc/refman/5.1/en/mysqlhotcopy.html)
 DBÀüü¸¦ ¹é¾÷ÇÒ¶§´Â mysqldumpº¸´Ù À̸¦ ÀÌ¿ëÇÏ¸é ´õ¿í ºü¸£°Ô ¹é¾÷À» ÇÒ ¼ö ÀÖ´Ù°í ÇÑ´Ù.
mysqlhotcopy´Â LOCK TABLES, FLUSH TABLES ±×¸®°í cp³ª scp¸¦ ÀÌ¿ëÇØ ÆÄÀÏÀ» »ý¼ºÇÑ´Ù.
ÁÖÀÇ ÇÒÁ¡Àº  MyISAM°ú ARCHIVE table¸¸ °¡´ÉÇÏ´Ù´Â °ÍÀÌ´Ù.
»ç¿ë¹æ¹ýÀº ´ÙÀº°ú °°´Ù.

¡¡
shell> mysqlhotcopy db_name [/path/to/new_directory]
shell> mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory
¡¡

Á¤±Ô½ÄÀ» ÀÌ¿ëÇÏ´Â ¹æ¹ýµµ ÀÖ´Ù.

¡¡
shell> mysqlhotcopy db_name./regex/
shell> mysqlhotcopy db_name./~regex/
¡¡




÷ºÎ.1

¡¡
[mysqldump ¿É¼ÇÇ¥ - Ãâó:http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html]
¡¡
Format Config File Description Introduction
--add-drop-database add-drop-database Add a DROP DATABASE statement before each CREATE DATABASE statement ¡¡
--add-drop-table add-drop-table Add a DROP TABLE statement before each CREATE TABLE statement ¡¡
--add-locks add-locks Surround each table dump with LOCK TABLES and UNLOCK TABLES statements ¡¡
--all-databases all-databases Dump all tables in all databases ¡¡
--all-tablespaces all-tablespaces Adds to a table dump all SQL statements needed to create any tablespaces used by an NDB Cluster table 5.1.6
--allow-keywords allow-keywords Allow creation of column names that are keywords ¡¡
--comments comments Add comments to the dump file ¡¡
--compact compact Produce less verbose output ¡¡
--compatible=name[,name,...] compatible Produce output that is more compatible with other database systems or with older MySQL servers ¡¡
--complete-insert complete-insert Use complete INSERT statements that include column names ¡¡
--create-options create-options Include all MySQL-specific table options in the CREATE TABLE statements ¡¡
--databases databases Dump several databases ¡¡
--debug[=debug_options] debug Write a debugging log ¡¡
--debug-check debug-check Print debugging information when the program exits 5.1.21
--debug-info debug-info Print debugging information, memory and CPU statistics when the program exits 5.1.14
--delayed-insert delayed-insert Write INSERT DELAYED statements rather than INSERT statements ¡¡
--delete-master-logs delete-master-logs On a master replication server, delete the binary logs after performing the dump operation ¡¡
--disable-keys disable-keys For each table, surround the INSERT statements with disable and enable keys statements ¡¡
--dump-date dump-date Include dump date in "Dump completed on" comment if --comments is given 5.1.23
-E events Dump events from the dumped databases ¡¡
--extended-insert extended-insert Use multiple-row INSERT syntax that include several VALUES lists ¡¡
--fields-enclosed-by=string fields-enclosed-by This option is used with the -T option and has the same meaning as the corresponding clause for LOAD DATA INFILE ¡¡
--fields-escaped-by fields-escaped-by This option is used with the -T option and has the same meaning as the corresponding clause for LOAD DATA INFILE ¡¡
--fields-optionally-enclosed-by=string fields-optionally-enclosed-by This option is used with the -T option and has the same meaning as the corresponding clause for LOAD DATA INFILE ¡¡
--fields-terminated-by=string fields-terminated-by This option is used with the -T option and has the same meaning as the corresponding clause for LOAD DATA INFILE ¡¡
--lock-all-tables first-slave Deprecated. Now renamed to --lock-all-tables ¡¡
--flush-logs flush-logs Flush the MySQL server log files before starting the dump ¡¡
--flush-privileges flush-privileges Emit a FLUSH PRIVILEGES statement after dumping the mysql database ¡¡
--help ¡¡ Display help message and exit ¡¡
--hex-blob hex-blob Dump binary columns using hexadecimal notation (for example, 'abc' becomes 0x616263) ¡¡
--ignore-table=db_name.tbl_name ignore-table Do not dump the given table ¡¡
--insert-ignore insert-ignore Write INSERT statements with the IGNORE option ¡¡
--lines-terminated-by=string lines-terminated-by This option is used with the -T option and has the same meaning as the corresponding clause for LOAD DATA INFILE ¡¡
--lock-all-tables lock-all-tables Lock all tables across all databases ¡¡
--lock-tables lock-tables Lock all tables before dumping them ¡¡
--log-error=file_name log-error Append warnings and errors to the named file 5.1.18
--master-data[=value] master-data Write the binary log file name and position to the output ¡¡
--max_allowed_packet=value max_allowed_packet The maximum packet length to send to or receive from the server ¡¡
--net_buffer_length=value net_buffer_length The buffer size for TCP/IP and socket communication ¡¡
--no-autocommit no-autocommit Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements ¡¡
--no-create-db no-create-db This option suppresses the CREATE DATABASE statements ¡¡
--no-create-info no-create-info Do not write CREATE TABLE statements that re-create each dumped table ¡¡
--no-data no-data Do not write any table row information (that is, do not dump table contents) ¡¡
--no-set-names no-set-names Turn off complete-insert ¡¡
--opt opt This option is shorthand; it is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. ¡¡
--order-by-primary order-by-primary Sorts each table's rows by its primary key, or by its first unique index ¡¡
--password[=password] password The password to use when connecting to the server ¡¡
--pipe ¡¡ On Windows, connect to server via a named pipe ¡¡
--port=port_num port The TCP/IP port number to use for the connection ¡¡
--quick quick Retrieve rows for a table from the server a row at a time ¡¡
--quote-names quote-names Quote database, table, and column names within backtick characters ¡¡
--replace replace Write REPLACE statements rather than INSERT statements ¡¡
--result-file=file result-file Direct output to a given file ¡¡
-R routines Dump stored routines (procedures and functions) from the dumped databases ¡¡
--set-charset set-charset Add SET NAMES default_character_set to the output ¡¡
--single-transaction single-transaction This option issues a BEGIN SQL statement before dumping data from the server ¡¡
--skip-add-drop-table skip-add-drop-table Do not add ¡¡
--skip-add-locks skip-add-locks Do not add locks ¡¡
--skip-comments skip-comments Do not add comments to the dump file ¡¡
--skip-compact skip-compact Turn off compact ¡¡
--skip-disable-keys skip-disable-keys Do not disable keys ¡¡
--skip-extended-insert skip-extended-insert Turn off extended-insert ¡¡
--skip-opt skip-opt Turn off the options set by opt ¡¡
--skip-quick skip-quick Do not retrieve rows for a table from the server a row at a time ¡¡
--skip-quote-names skip-quote-names Turn off quote names ¡¡
--skip-set-charset skip-set-charset Suppress the SET NAMES statement ¡¡
--skip-triggers skip-triggers Turn off triggers ¡¡
--skip-tz-utc skip-tz-utc Turn off tz-utc ¡¡
--ssl-ca=file_name ssl-ca The path to a file that contains a list of trusted SSL CAs ¡¡
--ssl-capath=directory_name ssl-capath The path to a directory that contains trusted SSL CA certificates in PEM format ¡¡
--ssl-cert=file_name ssl-cert The name of the SSL certificate file to use for establishing a secure connection ¡¡
--ssl-cipher=cipher_list ssl-cipher A list of allowable ciphers to use for SSL encryption ¡¡
--ssl-key=file_name ssl-key The name of the SSL key file to use for establishing a secure connection ¡¡
--ssl-verify-server-cert ssl-verify-server-cert The server's Common Name value in its certificate is verified against the host name used when connecting to the server ¡¡
--tab=path tab Produce tab-separated data files ¡¡
--tables tables Override the --databases or -B option ¡¡
--triggers triggers Dump triggers for each dumped table ¡¡
--tz-utc tz-utc Add SET TIME_ZONE='+00:00' to the dump file ¡¡
--verbose ¡¡ Verbose mode ¡¡
--version ¡¡ Display version information and exit ¡¡
--where='where_condition' where Dump only rows selected by the given WHERE condition ¡¡
--xml xml Produce XML output
ÃßõÃßõ : 460 Ãßõ ¸ñ·Ï
¹øÈ£ Á¦¸ñ
2,885
input ÀÔ·Â ÇÊµå ¾ÕµÚ °ø¹é ½Ç½Ã°£ Á¦°Å
2,884
Placeholder Æ÷Ä¿½º½Ã °¨Ãß±â
2,883
MySQL Áߺ¹µÈ µ¥ÀÌÅ͸¦ »èÁ¦
2,882
MySQL Áߺ¹ µ¥ÀÌÅÍ È®ÀÎ
2,881
sessionStorage.getItem ¿Í sessionStorage.setItem
2,880
Á¦ÀÌÄõ¸® ·£´ýÀ¸·Î ¹è°æ»ö º¯°æ
2,879
preg match¿¡ °üÇÑ Á¤±Ô½Ä
2,878
Stream an audio file with MediaPlayer ¿Àµð¿À ÆÄÀÏ ½ºÆ®¸®¹Ö Çϱâ
2,877
Audio Streaming PHP Code
2,876
PHP $ SERVER ȯ°æ º¯¼ö Á¤¸®
2,875
Vimeo (ºñ¸Þ¿À) API ¸¦ »ç¿ëÇÏ¿© Ç÷¹À̾î ÄÁÆ®·ÑÇϱâ
2,874
iframe »ç¿ë½Ã ÇÏ´Ü¿¡ ¹ß»ýÇÏ´Â °ø¹é Á¦°Å¹æ¹ý
2,873
¾ÆÀÌÇÁ·¹ÀÓ(iframe) Àüüȭ¸é °¡´ÉÇÏ°Ô Çϱâ
2,872
ºÎÆ®½ºÆ®·¦(bootstrapk)¿¡¼­ »ç¿ëÇÏ´Â class¸í Á¤¸®
2,871
ºÎÆ®½ºÆ®·¦ CSS
2,870
Å©·Ò¿¡¼­ ¸¶Áø Á¶Àý
2,869
PHP ÇöÀç ÆäÀÌÁöÀÇ µµ¸ÞÀθíÀ̳ª urlµîÀÇ Á¤º¸ ¾Ë¾Æ¿À±â
2,868
PHP preg match all()
2,867
PHP ·Î À¥ÆäÀÌÁö ±Ü¾î¿À±â ¸ðµç ¹æ¹ý ÃÑÁ¤¸®!
2,866
[PHP] ¿ø°ÝÁö ÆÄÀÏ ÁÖ¼Ò ³ëÃâ ¾ÈÇÏ°í curl·Î ´Ù¿î·Îµå ¹Þ±â
2,865
PHP ÇÔ¼ö Á¤¸®
2,864
¾ÆÀÌÇÁ·¹ÀÓ(iframe) ºñÀ² À¯ÁöÇϸ鼭 Å©±â Á¶ÀýÇÏ´Â ¹æ¹ý
2,863
PHP ¹è¿­¿¡¼­ ¹«ÀÛÀ§·Î Çϳª »Ì¾ÆÁÖ´Â array rand() ÇÔ¼ö
2,862
PHP Á¤±Ô½Ä Á¤¸®
2,861
PHP Á¤±Ô½ÄÀ» È°¿ëÇÑ ÅÂ±× ¹× ƯÁ¤ ¹®ÀÚ¿­ Á¦°Å ¹× ÃßÃâ ¹æ¹ý
2,860
php Å©·Ñ¸µ ¶Ç´Â ÆÄ½Ì ÇÔ¼ö, Á¤±Ô½Ä ¸ðÀ½
2,859
Á¦ÀÌÄõ¸® ±âº» ¸í·É¾î
2,858
À¥ÆäÀÌÁö °¡·Î ¸ðµå¼¼·Î ¸ðµå ÀνÄÇϱâ
2,857
¸ð¹ÙÀÏ À¥ È­¸é °­Á¦ ȸÀü(°¡·Î¸ðµå °íÁ¤)
2,856
[HTML5]¿¡¼­ frameset ´ëü ¹æ¹ý°ú iframe ¼Ó¼º
¸ñ·Ï
¹ÂÁ÷Æ®·ÎÆ® ºÎ»ê±¤¿ª½Ã ºÎ»êÁø±¸ °¡¾ßµ¿ ¤Ó °³ÀÎÁ¤º¸Ãë±Þ¹æħ
Copyright ¨Ï musictrot All rights reserved.