½Ã½ºÅÛ°ü¸®ÀÚÀÇ ½°ÅÍ Ä¿ÇǴнº Ä¿ÇÇÇâÀÌ ³ª´Â *NIX
Ä¿ÇǴнº
½Ã½ºÅÛ/³×Æ®¿÷/º¸¾ÈÀ» ´Ù·ç´Â °÷
 FAQFAQ   °Ë»ö°Ë»ö   ¸â¹ö¸®½ºÆ®¸â¹ö¸®½ºÆ®   »ç¿ëÀÚ ±×·ì»ç¿ëÀÚ ±×·ì   »ç¿ëÀÚ µî·ÏÇϱâ»ç¿ëÀÚ µî·ÏÇϱâ 
 °³ÀÎ Á¤º¸°³ÀÎ Á¤º¸   ºñ°ø°³ ¸Þ½ÃÁö¸¦ È®ÀÎÇÏ·Á¸é ·Î±×ÀÎÇϽʽÿÀºñ°ø°³ ¸Þ½ÃÁö¸¦ È®ÀÎÇÏ·Á¸é ·Î±×ÀÎÇϽʽÿÀ   ·Î±×Àηα×ÀΠ

°¡ÀÔ¾øÀÌ ´©±¸³ª ±ÛÀ» ¾µ ¼ö ÀÖ½À´Ï´Ù. °øÁö»çÇ׿¡ ´ëÇÑ ´ñ±Û±îÁöµµ..




BBS >> ¼³Ä¡, ¿î¿µ Q&A | ³×Æ®¿÷, º¸¾È Q&A | ÀÏ¹Ý Q&A || Á¤º¸¸¶´ç | AWS || ÀÚÀ¯°Ô½ÃÆÇ | ±¸Àα¸Á÷ || °øÁö»çÇ× | ÀÇ°ßÁ¦½Ã
mysqldump¸¦ ÀÌ¿ëÇÑ remote ¹é¾÷

 
±Û ¾²±â   ´äº¯ ´Þ±â    Ä¿ÇǴнº, ½Ã½ºÅÛ ¿£Áö´Ï¾îÀÇ ½°ÅÍ °Ô½ÃÆÇ À妽º -> *NIX / IT Á¤º¸
ÀÌÀü ÁÖÁ¦ º¸±â :: ´ÙÀ½ ÁÖÁ¦ º¸±â  
±Û¾´ÀÌ ¸Þ½ÃÁö
jjun



°¡ÀÔ: 2009³â 12¿ù 10ÀÏ
¿Ã¸° ±Û: 8

¿Ã¸®±â¿Ã·ÁÁü: 2011.4.11 ¿ù, 1:11 pm    ÁÖÁ¦: mysqldump¸¦ ÀÌ¿ëÇÑ remote ¹é¾÷ Àοë°ú ÇÔ²² ´äº¯

* ¸ñÇ¥
MySQL 5.1.x ¸¦ ¼­ºñ½ºÁßÀÎ db ¼­¹ö¿¡¼­ ¹é¾÷¿ëµµÀÇ db3 ¼­¹ö·Î remote ÀÚµ¿¹é¾÷ ½Ç½Ã


* »çÀü Áغñ
1. °¢ ¼­¹ö°£ ssh key ÀÎÁõ ¼³Á¤(db¿¡¼­ db3·Î)
2. db, db3¼­¹öÀÇ MySQL ÀÚµ¿ ·Î±×ÀÎ ¼³Á¤(~/.my.cnf)

root@db:~# cat ~/.my.cnf
ÄÚµå:

[client]
user = root
host = localhost
password = ¾ÏÈ£



* ½ºÅ©¸³Æ® ÀÛ¼º

1. DB list¿¡¼­ ¸ñ·ÏÀ» °¡Á®¿Í ¹é¾÷¼­¹öÀÎ db3¿¡ Database¸¦ »ý¼ºÇÑ´Ù.
2. mysqldump·Î ¸ñ·Ï¿¡ ÀÖ´Â ¼ø¼­´ë·Î ¹é¾÷ÇÑ ÈÄ, ÆÄÀÌÇÁ¶óÀΰú ssh¸¦ ÀÌ¿ëÇÏ¿© ¹é¾÷¼­¹öÀÎ db3·Î ³Ñ±ä´Ù.
3. db3¿¡¼­´Â ssh·Î ³Ñ¾î¿À´Â data¸¦ ¹Þ¾Æ ¹Ù·Î ¹é¾÷ÇÑ´Ù.
4. ¸ðµç °á°ú´Â log·Î ³²±ä´Ù.

root@db:~# cat dbbackup-from-filelist.sh
ÄÚµå:
export LOGFILE=/root/dbbackup4.log;
for db in $(cat /root/backuplist);
do echo "Database: $db" >> $LOGFILE;
ssh root@db3 "mysql -e 'create database IF NOT EXISTS $db'";
echo "Start time: `date '+2011%m%d_%H%M'`" >> $LOGFILE;
mysqldump -v --databases --opt $db --log-error=$LOGFILE | ssh root@db3 'mysql $db' 2>> $LOGFILE;
echo "End time: `date '+2011%m%d_%H%M'`" >> $LOGFILE;
echo "---------------------------------------------------" >> $LOGFILE; done



* ¹é¾÷ ´ë»ó Database list ÀÛ¼º
root@db:~# cat backuplist
ÄÚµå:

DB_1
DB_2
DB_3
DB_4


* Àüü DBÀÎ °æ¿ì¶ó¸é
ÄÚµå:

mysql -N -B -e 'show databases;' | egrep -v "information_schema|mysql" > backuplist

·Î »ý¼ºÇÑ´Ù.


* ¹é±×¶ó¿îµå·Î ½ÇÇà
root@db:~# nohup dbbackup-from-filelist.sh &


* ·Î±× È®ÀÎ
root@db:~# tail -f dbbackup4.log
ÄÚµå:

Database: DB_1
Start time: 20110411_0739

-- Connecting to localhost...
-- Retrieving table structure for table DB1_table1...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table DB1_table2...
-- Sending SELECT query...
-- Retrieving rows...
-- Disconnecting from localhost...
End time: 20110411_0926
---------------------------------------------------
Database:  DB_2
Start time: 20110411_0926
-- Connecting to localhost...
-- Retrieving table structure for table DB2_table1...



* ¹®Á¦ÇØ°á
ÄÚµå:

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table 'DB_1' at row: 339

¿Í °°ÀÌ ¹é¾÷ Áß°£¿¡ ¿¡·¯°¡ ³ª¸é¼­ Áߴܵȴٸé, ´ÙÀ½°ú °°ÀÌ ¾çÂÊ ¼­¹ö¿¡¼­ ¸í·ÉÇÑ´Ù. ¹Ì¸® º¯°æÇØÁÖ´Â°Ô ÁÁ°Ú´Ù.

±âº»°ªÀÌ 60ÃÊÀÎ timeout°ªÀ» ´Ã·ÁÁØ´Ù.

ÄÚµå:

mysql -e "set global net_read_timeout=10800;"
mysql -e "set global net_write_timeout=10800;"



Âü°í : http://blog.naver.com/junix/80128043759

Àüü 1.5TB ¿ë·®ÀÇ ÃÑ 303°³ÀÇ MySQL Database¸¦ ¿ø°ÝÁö¿¡ 1Â÷ ¹é¾÷Çϱâ À§ÇØ ÀÛ¾÷ÇÑ ³»¿ëÀÔ´Ï´Ù. ¶ÇÇÑ ÇöÀç InnoDB¸¦ MyISAMÀ¸·Îµµ º¯È¯ÇÏ°í¿ä.

´õ ÁÁÀº ¹æ¾ÈÀ» ¾Ë°í °è½Ã¸é ´ñ±Û ºÎŹµå¸³´Ï´Ù ^^
À§·Î
»ç¿ëÀÚ Á¤º¸ º¸±â ºñ¹Ð ¸Þ½ÃÁö º¸³»±â
jjun



°¡ÀÔ: 2009³â 12¿ù 10ÀÏ
¿Ã¸° ±Û: 8

¿Ã¸®±â¿Ã·ÁÁü: 2011.10.13 ¸ñ, 10:15 am    ÁÖÁ¦: mysqldump ¾à°£ ´õ ¾÷±×·¹À̵å Àοë°ú ÇÔ²² ´äº¯

À̹ø¿¡´Â ÆÄÀÌÇÁ¶óÀÎÀ» ÅëÇØ MyISAMÀ» InnoDB·Î º¯È¯ÇÏ¿© ´ýÇÁ¸¦ ¹Þ°í, À̸¦ ´Ù½Ã ÆÄÀ̶óÀÎÀ» ÅëÇØ SSH·Î Àü¼ÛÇϵÇ, ¾ÏÈ£(¾ÐÃà)¾Ë°í¸®ÁòÀ» º¯°æÇÏ¿© Àü¼Û¼Óµµ Çâ»óÀ» ²ÏÇÑ ¹æ¹ýÀÔ´Ï´Ù.

ÄÚµå:

LOGFILE=/root/db_backup_$(date '+2011%m%d_%H%M').log;
for db in $(cat /root/db_backup.list);
do echo "Database: $db" >> $LOGFILE;
ssh root@db3 "mysql -e 'create database IF NOT EXISTS $db'";
echo "Start time: $(date '+2011%m%d_%H%M')" >> $LOGFILE;
mysqldump -v -c --add-drop-database --add-drop-table --databases --opt $db --log-error=$LOGFILE | sed -r s/MyISAM/InnoDB/g | ssh root@db3 -p 22 -c arcfour,blowfish-cbc 'mysql $db' 2>> $LOGFILE;
echo "End time: $(date '+2011%m%d_%H%M')" >> $LOGFILE;
echo "---------------------------------------------------" >> $LOGFILE; done


1.1G¿ë·®ÀÇ MyISAM(MySQL 5.1)À» remoteÀÇ InnoDB(MySQL 5.5)·Î restoreÇϴµ¥ ¾à 30ºÐ ¼Ò¿äµÊ. 1Gb NIC, SATA RAID 10.
À§·Î
»ç¿ëÀÚ Á¤º¸ º¸±â ºñ¹Ð ¸Þ½ÃÁö º¸³»±â
ÀÌÀü ±Û Ç¥½Ã:   
±Û ¾²±â   ´äº¯ ´Þ±â    Ä¿ÇǴнº, ½Ã½ºÅÛ ¿£Áö´Ï¾îÀÇ ½°ÅÍ °Ô½ÃÆÇ À妽º -> *NIX / IT Á¤º¸ ½Ã°£´ë: GMT + 9 ½Ã°£(Çѱ¹)
ÆäÀÌÁö 1 Áß 1

 
°Ç³Ê¶Ù±â:  
»õ·Î¿î ÁÖÁ¦¸¦ ¿Ã¸± ¼ö ÀÖ½À´Ï´Ù
´ä±ÛÀ» ¿Ã¸± ¼ö ÀÖ½À´Ï´Ù
ÁÖÁ¦¸¦ ¼öÁ¤ÇÒ ¼ö ¾ø½À´Ï´Ù
¿Ã¸° ±ÛÀ» »èÁ¦ÇÒ ¼ö ¾ø½À´Ï´Ù
ÅõÇ¥¸¦ ÇÒ ¼ö ¾ø½À´Ï´Ù


Powered by phpBB © 2001, 2005 phpBB Group