๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ
๊ฐœ๋ฐœ/Linux

[Linux] MySQL DB ๋ฐฑ์—… ์Šคํฌ๋ฆฝํŠธ - ์ž๋™์œผ๋กœ ๋ฐฑ์—…ํ•˜๊ธฐ

by ynzu๐Ÿค 2021. 12. 17.
๋ฐ˜์‘ํ˜•

๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์˜ ๋ฐ์ดํ„ฐ๋ฅผ ์ž๋™์œผ๋กœ ๋ฐฑ์—…ํ•  ์ˆ˜ ์žˆ๋„๋ก ์Šคํฌ๋ฆฝํŠธ๋ฅผ ์ž‘์„ฑํ–ˆ๋‹ค.

#! /bin/bash 
# backup directory
dir=$(echo /home1/test01/project01/db/backup)

# get backup file time and now time
customer_BackupFileDate=$(date -r $dir/backup_customer.sql '+%Y-%m-%d %H:%M:%S')
customer_BackupFileDateString=$(date -r $dir/backup_customer.sql '+%Y%m%d_%H%M%S')

# rename last backup file
mv $dir/backup_customer.sql $dir/backup_customer_$customer_BackupFileDateString.sql

# backup customer table from test01
sudo /home1/test01/db/mysql/bin/mysqldump -utest01 -p1q2w3e4r -S /home1/test01/db/mysql/tmp/mysql.sock test01 customer --where="create_date > '$customer_BackupFileDate'" --skip-add-drop-table > $dir/backup_customer.sql
sudo chown test01:test01 $dir/backup_customer.sql

ํ•ด๋‹น ์Šคํฌ๋ฆฝํŠธ๋ฅผ ๋ฆฌ๋ˆ…์Šค ์ž‘์—… ์Šค์ผ€์ค„๋Ÿฌ(crontab)์— ๋“ฑ๋กํ•˜์—ฌ ๋ฐ˜๋ณต์ ์œผ๋กœ ์‹คํ–‰๋˜๊ฒŒ ํ•˜๋ฉด ๋œ๋‹ค.

๋ณธ์ธ์€ ์ง์ „์— ๋ฐฑ์—…๋œ ํŒŒ์ผ์ด ์ƒ์„ฑ๋œ ์ผ์ž ์ดํ›„๋กœ ๋“ฑ๋ก๋œ customer ์ •๋ณด๋งŒ ๋ฐฑ์—…ํ•˜๊ณ ์ž ์œ„์™€ ๊ฐ™์ด ์ž‘์„ฑํ–ˆ๋Š”๋ฐ ์ „์ฒด ๋ฐ์ดํ„ฐ๋ฅผ ๋‹ค ๋ฐฑ์—…ํ•˜๊ณ ์ž ํ•œ๋‹ค๋ฉด where ์ ˆ์„ ์ œ๊ฑฐํ•˜๊ณ  ์‚ฌ์šฉํ•˜๋ฉด ๋œ๋‹ค!

์ŠคํŠธ๋ฆฝํŠธ์˜ ๋‚ด์šฉ์„ ์„ค๋ช…ํ•ด๋ณด์ž๋ฉด

# get backup file time and now time

: ๊ธฐ์กด์— ์ƒ์„ฑ๋œ ๋ฐฑ์—…ํŒŒ์ผ์ด ์ƒ์„ฑ๋œ ์‹œ๊ฐ„์„ ํš๋“ํ•˜์˜€๋‹ค. ํ•˜๋‚˜๋Š” date ํƒ€์ž…์ด๊ณ  ํ•˜๋‚˜๋Š” string ํƒ€์ž…์ธ๋ฐ date ํƒ€์ž…์€ where์ ˆ์— ์กฐ๊ฑด์„ ์ฃผ๊ธฐ ์œ„ํ•จ์ด๊ณ , string ํƒ€์ž…์€ ๊ธฐ์กด ๋ฐฑ์—…ํŒŒ์ผ์˜ ํŒŒ์ผ๋ช…์„ ๋ฐ”๊ฟ”์ฃผ๊ธฐ ์œ„ํ•จ์ด๋‹ค.

# rename last backup file

: ๋งˆ์ง€๋ง‰์œผ๋กœ ์ƒ์„ฑ๋œ ๋ฐฑ์—…ํŒŒ์ผ์˜ ํŒŒ์ผ๋ช…์„ ๋ณ€๊ฒฝํ•˜์˜€๋‹ค. ( ์œ„์—์„œ ํš๋“ํ•œ string ํƒ€์ž…์˜ ์ผ์ž ๋ถ™์—ฌ์คŒ)

# backup customer table from test01

: test01 ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์˜ customer ํ…Œ์ด๋ธ”์˜ ๋ฐ์ดํ„ฐ๋ฅผ dumpํ•˜๊ธฐ ์œ„ํ•œ ๊ณผ์ •์ด๋‹ค.

 

 

์•„๋ž˜์™€ ๊ฐ™์ด ์ƒํ™ฉ์— ๋”ฐ๋ผ ์ˆ˜์ •ํ•ด์„œ ์‚ฌ์šฉํ•˜๋ฉด ๋ ๋“ฏ!

  • ์ „์ฒด ๋ฐฑ์—…
    mysqldump -u๊ณ„์ •์•„์ด๋”” -p๊ณ„์ •ํŒจ์Šค์›Œ๋“œ -A > all.sql

 

  • ํŠน์ • DB ๋ฐฑ์—…
    mysqldump -u๊ณ„์ •์•„์ด๋”” -p๊ณ„์ •ํŒจ์Šค์›Œ๋“œ DB๋ช… > DB๋ช….sql

 

  • ํŠน์ • DB์˜ ํŠน์ • ํ…Œ์ด๋ธ” ๋ฐฑ์—…
    mysqldump --u๊ณ„์ •์•„์ด๋”” -p๊ณ„์ •ํŒจ์Šค์›Œ๋“œ DB๋ช… table๋ช… > DB๋ช…_table๋ช….sql

 

 

 

728x90
๋ฐ˜์‘ํ˜•

๋Œ“๊ธ€