|
Ä¿ÇÇÇâÀÌ ³ª´Â *NIX
Ä¿ÇǴнº
½Ã½ºÅÛ/³×Æ®¿÷/º¸¾ÈÀ» ´Ù·ç´Â °÷
|
|
|
|
ÀÌÀü ÁÖÁ¦ º¸±â :: ´ÙÀ½ ÁÖÁ¦ º¸±â |
±Û¾´ÀÌ |
¸Þ½ÃÁö |
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. |
|
À§·Î |
|
|
|
|
»õ·Î¿î ÁÖÁ¦¸¦ ¿Ã¸± ¼ö ÀÖ½À´Ï´Ù ´ä±ÛÀ» ¿Ã¸± ¼ö ÀÖ½À´Ï´Ù ÁÖÁ¦¸¦ ¼öÁ¤ÇÒ ¼ö ¾ø½À´Ï´Ù ¿Ã¸° ±ÛÀ» »èÁ¦ÇÒ ¼ö ¾ø½À´Ï´Ù ÅõÇ¥¸¦ ÇÒ ¼ö ¾ø½À´Ï´Ù
|
Powered by phpBB © 2001, 2005 phpBB Group
|