- mysqldump »ç¿ë ¿¹Á¦
- °í·ÁÇØº¼¸¸ÇÑ mysqldump ¿É¼Çµé
- mysqldump + crontab
- ¹é¾÷ ½ºÅ©¸³Æ®
- ±âº»ÀûÀÎ ½ºÅ©¸³Æ®
- ftp Àü¼Û
1 mysqldump »ç¿ë ¿¹Á¦
¹¹, ¸Å´º¾ó ÆäÀÌÁö º¸¸é ´Ù ³ª¿Ã ³»¿ëÀÌ´Ù¸¸, ¿É¼ÇÀÌ ¿ö³« ¸¹¾Æ¼... °£´ÜÈ÷ Á¤¸®Çسõ´Â °Ô ÁÁÀ» µí ÇÏ´Ù.
- µ¥ÀÌÅͺ£À̽ºÀÇ Å×ÀÌºí¸¸ dump ¹Þ±â
mysqldump -u USERNAME -p DATABASE_NAME TABLE_NAME > SQL.sql
- µ¥ÀÌÅͺ£À̽ºÀÇ ¸ðµç Å×À̺í dump ¹Þ±â
mysqldump -u USERNAME -p DATABASE_NAME > SQL.sql
- µ¥ÀÌÅͺ£À̽º¿¡¼ ÇϳªÀÇ Å×ÀÌºí ½ºÅ°¸¶¸¸ dump ¹Þ±â
mysqldump -u USERNAME -p -d DATABASE_NAME TABLE_NAME > SCHEMA_SQL.sql
- µ¥ÀÌÅͺ£À̽º¿¡¼ ¸ðµç Å×À̺íÀÇ ½ºÅ°¸¶¸¸ dump ¹Þ±â
mysqldump -u USERNAME -p -d DATABASE_NAME > SCHEMA_SQL.sql
- ¿©·¯ µ¥ÀÌÅͺ£À̽º dump ¹Þ±â
mysqldump -u USERNAME -p --databases DATABASE1 DATABASE2 DATABASE3
- µ¥ÀÌÅͺ£À̽ºÀÇ ¸ðµç Å×À̺íÀ» ¾ÐÃàÇØ¼ dump ¹Þ±â
mysqldump -u USERNAME -p DATABASE_NAME | gzip > SQL.gz
- µ¥ÀÌÅͺ£À̽ºÀÇ ÇϳªÀÇ Å×À̺íÀ» ¾ÐÃàÇØ¼ dump ¹Þ±â
mysqldump -u USERNAME -p DATABASE_NAME TABLE_NAME | gzip > SQL.gz
- ¾ÐÃàÇØ¼ ´ýÇÁ¹ÞÀº ÆÄÀÏÀ» µ¥ÀÌÅͺ£À̽º¿¡ Áý¾î³Ö±â
gunzip < comp_section.gz | mysql -u USERNAME -p DATABASE_NAME
- ¿ø°Ý È£½ºÆ®ÀÇ µ¥ÀÌÅͺ£À̽º¸¦ ¾ÐÃàÇØ¼ dump ¹Þ±â
mysqldump -h HOSTNAME -u USERNAME -p DATABASE_NAME | gzip > comp.sql.gz
±ÇÇÑ Ã¼Å©¸¦ ÁÖÀÇÇÏÀÚ.
2 °í·ÁÇØº¼¸¸ÇÑ mysqldump ¿É¼Çµé
- --add-drop-table
CREATE TABLE TABLENAME ¹® ¾Õ¿¡´Ù°¡ DROP TABLE IF EXISTS TABLENAME ¹®À» Áý¾î³Ö´Â´Ù. Áï ´ýÇÁ ¹ÞÀº µ¥ÀÌÅͺ£À̽º¸¦ Á» ´õ ½±°Ô µ¥ÀÌÅͺ£À̽º¿¡´Ù Áý¾î³ÖÀ» ¼ö ÀÖ°Ô ¸¸µé¾îÁØ´Ù.
- -q ¶Ç´Â --quick
Äõ¸®¸¦ ¹öÆÛ¸µÇÏÁö ¾Ê°í, ´ÙÀÌ·ºÆ®·Î Ãâ·ÂÇÑ´Ù.
- --add-locks
INSERT ¹® ¾Õ µÚ·Î ¶ôÀ» °É¾îÁØ´Ù.
- --e ¶Ç´Â --extended-insert
´ÙÇà(multiline) INSERT ¹®À» »ç¿ëÇÏ¿© Á» ´õ ÀÛ°í ºü¸¥ INSERT ¹®À» »ý¼ºÇÑ´Ù.
- -l ¶Ç´Â --lock-tables
¹é¾÷¿ë Àб⸦ À§ÇØ ¸ðµç Å×ÀÌºí¿¡ ¶ôÀ» °Ç´Ù.
- --opt
--add-drop-table --add-locks --all --extended-insert --quick --lock-tables Ç÷¡±×¸¦ ¸ðµÎ ¼³Á¤ÇÑ °Í°ú °°´Ù.
3 mysqldump + crontab
´ÙÀ½°ú °°Àº ¸í·É¾î¸¦ Å©·ÐÅÇ¿¡´Ù ³ÖÀ¸¸é, ¸Å½Ã°£¸¶´Ù BACKUP_2003_12_15_06 °ú °°Àº À̸§ÀÇ ¹é¾÷ ÆÄÀÏÀÌ »ý¼ºµÈ´Ù. (Å©·ÐÅÇ ¾È¿¡¼ ¹Ù·Î ÇÒ ¼öµµ Àִµ¥, FreeBSD¿¡¼´Â ¿ØÁö Á¦´ë·Î ¾È µÇ¾î¼ ½ºÅ©¸³Æ®·Î »©³Â´Ù.)
backup_database
#!/bin/sh
/usr/local/mysql/bin/mysqldump -u USERNAME -p PASSWORD --all-databases > /db_backup/BACKUP_`date +%Y_%m_%d_%H`
crontab -e
0 6 * * * /root/backup_database
4 ¹é¾÷ ½ºÅ©¸³Æ®
¹é¾÷À» º»°ÝÀû(?)À¸·Î ÇÏ°í ½Í´Ù¸é ´ÙÀ½°ú °°Àº ½ºÅ©¸³Æ®¸¦ ¸¸µå´Â °Íµµ ±¦ÂúÀ» µí ÇÏ´Ù.
4.1 ±âº»ÀûÀÎ ½ºÅ©¸³Æ®
#!/bin/sh
####################################################
# MySQL backup script
####################################################
# ³¯Â¥, ÆÄÀϸí, µð·ºÅ丮 ¼³Á¤
DATE=`/bin/date +%d-%b-%Y-%H-%M-%S`
FILENAME="BACKUP-$DATE"
BACKUPDIR="/tmp"
# °èÁ¤°ú ºñ¹Ð ¹øÈ£
USERNAME="root"
PASSWORD="ohmygod"
DATABASE="test"
# ¸ÞÀÏ Á¤º¸ - ¹é¾÷ ¼º°ø½Ã ¸ÞÀÏ º¸³»±â
RECIPIENT="excel96@excel96.com"
NICEDATE=`bin/date +%b-%d-%Y`
SUBJECT="MySQL ¹é¾÷ ¼º°ø - $NICEDATE"
# ½ÇÁ¦¹é¾÷
/usr/local/mysql/bin/mysqldump -u$USERNAME -p$PASSWORD $DATABASE > $BACKUPDIR/$FILENAME
| tar -cfz $BACKUPDIR/$FILENAME.tar.gz $BACKUPDIR/$FILENAME
| rm -f $BACKUPDIR/$FILENAME.sql
# ¸ÞÀϺ¸³»±â
echo "good" > mail -s $SUBJECT $RECIPIENT
4.2 ftp Àü¼Û
#!/bin/bash
#####################################
### MySQL Configuration Variables ###
#####################################
# MySQL Hostname
DBHOST='localhost'
# MySQL Username
DBUSER='root'
# MySQL Password
DBPASSWD='password'
#####################################
### FTP Configuration Variables #####
#####################################
# FTP Hostname
FTPHOST='www.example.com'
# FTP Username
FTPUSER='username'
# FTP Password
FTPPASSWD='password'
# Local Directory for Dump Files
LOCALDIR=/path/to/local/directory/
# Remote Directory for Offsite Backup
REMOTEDIR=/path/to/remote/directory/
# Prefix for offsite .tar file backup
TARPREFIX=db1
#####################################
### Edit Below If Necessary #########
#####################################
cd $LOCALDIR
SUFFIX=`eval date +%y%m%d`
DBS=`mysql -u$DBUSER -p$DBPASSWD -h$DBHOST -e"show databases"`
for DATABASE in $DBS
do
if [ $DATABASE != "Database" ]; then
FILENAME=$SUFFIX-$DATABASE.gz
mysqldump -u$DBUSER -p$DBPASSWD -h$DBHOST $DATABASE | gzip --best > $LOCALDIR$FILENAME
fi
done
chmod 400 $LOCALDIR*.gz
tar -cf $TARPREFIX-$SUFFIX.tar $SUFFIX-*.gz
ftp -n $FTPHOST <<END_SCRIPT
quote USER $FTPUSER
quote PASS $FTPPASSWD
cd $REMOTEDIR
put $TARPREFIX-$SUFFIX.tar
quit
END_SCRIPT
rm -f $TARPREFIX-$SUFFIX.tar
exit 0
SeriousMoin v1 (koMoinMoin 1.0a4 Modified)