Backup ข้อมูลพวก .sql มันมีแบบ อัตโนมัติไหมครับ

Technical topics
1

จากลิ้ง

คือผมเองก็ใช้งานเครื่องตัวเองประเภทไม่ค่อย backup sql ของตัวเองด้วยนาน ๆ backup ทีเพราะ backup มือ

ผมอยากทราบว่ามันมีวิธีการ backup อัตโนมัติไหมครับ

เช่นมีเครื่อง 2 เครื่อง เครื่องที่ 1 ใช้งาน เครื่องที่ 2 backup ข้อมูล อะไรประมาณนี้อ่าครับ linux

2

เขียน script backup หรือใช้ software backup เฉพาะ

Directadmin ก็ทำได้ครับ

3

ขอถามเสริมอีกนิดนึงครับ การ backup ถี่ ๆ จะมีผลอะไรกับเครื่องที่ใช้งานหรือเครื่องที่ backup ไหมครับแบบไฟล์ไม่ใหญ่มาก สมมุติ sql 200mb เล่น backup ทุก ๆ 10 นาทีอะไรงี้

4

เครื่องทำงาน หนัก ครับ อาจส่งผลเสียต่อระบบที่ทำงานอยู่ ในเครื่องนั้น รวมถึงตัว SQL ด้วย

เพราะ ระบบจะ lock table ทำให้ ไม่สามารถ access SQL ช่วงเวลา backup

ถ้าบ่อยขนาดนั้น แนะนำใช้ เทคโนโลยี่ของ Storage น่าจะเหมาะ สมกว่า

5

Script ที่ผมใช้ export & ftp



01 #!/bin/sh

02 output_name=mysql.all.db.`date +%Y%m%d%H%M`.sql

03 #echo $output_name

04 mysqldump -u<mysql user> -p<mysql password> --all-databases > /path/to/backup/$output_name

05 gzip /path/to/backup/$output_name

06 /usr/local/bin/ncftpput -u <ftp user> -p <ftp password> <ftp server IP> / /path/to/backup/$output_name.gz

07 rm -Rf /path/to/backup/$output_name.gz

คำอธิบาย

01 shell script header

02 กำหนดชื่อไฟล์ ตามด้วย ปี เดือน วัน ชั่วโมง นาที

03 Debug code ถ้าเอา # ออก มันจะแสดงชื่อไฟล output จากบรรทัด 02

04 สั่ง export mysql ทุก database ออกมาใส่ไว้ในไฟล์ จากบรรทัด 02 โปรดสังเกต -u<mysql user> และ -p<mysql password> ไม่ต้องมีเว้นวรรคหลัง -u และ -p

05 สั่ง gzip ซะ

06 สั่ง FTP ไปไว้ที่ FTP server โปรดสังเกต หลัง -u <ftp user> และ -p <ftp password> ตรงนี้ต้องเว้นวรรคระหว่าง -u <ftp user> และ -p <ftp password>

07 สั่งลบ local file ซะ

ปล. โปรดสังเกตอีกสองจุดคือ บรรทัดที่ 06, 07 หลังจาก $output_name จะต้องต่อท้ายด้วย .gz เนื่องมาจากบรรทัดที่ 05 เราทำการสั่ง gzip ไฟล์ ซึ่งจะทำให้ไฟล์นั้นๆ ถูกต่อท้ายด้วย .gz โดยอัตโนมัติ

ปล2. หลังจาก Backup แล้ว อย่าลืมทดสอบ restore กลับบนอีกเครื่องด้วยนะครับ มีบางกรณีที่ restore กลับแล้ว error ถ้าเจอก็ลองแก้ไขดู เดี๋ยวจะต้องมานั่งปวดตับ ณ เวลาที่ต้องการใช้งานมันจริงๆ

6

มาแจกแต้ม +1 > Dino

7

เยี่ยมครับ คุณ Dino

8

เขียน script เสร็จแล้ว ก็ตั้ง cron ให้ทำงานตามเวลาที่ต้องการ เช่น

filename: /etc/cron.d/backupsql

0 */2 * * * root /path/to/scriptbackup

เป็นการตั้งให้ backup ทุกๆ 2 ชม

9

เยี่ยมครับ คุณ Dino

10

ขอบคุณมาก ๆ ครับ แล้วถ้าจะแก้ไขให้มันส่งไป backup ข้ามเครื่องอ่าครับ

แล้วก็พอมีทริกเกี่ยวกับการ backup ข้อมูล sql ไหมครับแบบว่าข้อมูลมันมีการอัพเดทแก้ไขตลอด เราควรจะมันแบบไหนดี

ถ้าจุดประสงค์ของผมเป็น แบบเราเข้าไปใน sql ไปที่ ส่งออก แล้วคลิ๊กส่งแนบมาเป็นไฟล์ แล้วก็ลงมือ ผมจะข้อมูลแค่นั้นพอ

มันไม่น่าจะขนาดใหญ่มาก แต่จะเอาแบบอัตโนมัติให้มันส่งไปอีกเครื่อง จะเขียน shell ยังไงครับผม

11

เขียนแยกให้ backup เป็น database น่าจะดีกว่านะครับ backup ทุก db ทีเดียวพร้อมกันเวลาเอาออกมาใช้มันยาก

code เดียวผมลองหาในเครื่องผมให้ครับ เหมือนเคยเขียนไว้ นานแล้ว

12

ก็เป็นเรื่องดีครับ

13

จากประสบการณ์แล้ว dumb ออกมาเป็น sql บางครั้งมีปัญหาเรื่องภาษาไทย จะมีปัญหาตอน restore กลับ ภาษาไทยเละ

แนะนำให้ copy folder mysql เก็บไว้เลย โดยใช้คำสั่้ง mysqlhotcopy จากนั้นจะเอาไฟล์ copy ไปเก็บไว้อีกเครื่องนึงยังไงก็ได้

14

#!/bin/bash

#auto backup mysql script by toey@tc20.net

#first setup

#add this to /usr/dbbackuptool/autobackup_mysql.sh

#chmod 700 /usr/dbbackuptool/autobackup_mysql.sh

#mkdir -p /usr/dbbackuptool/{ptmp,inc}

#addmysql username passwd ip to /usr/dbbackuptool/inc/inc_database

#global var

dname1=$(date +%Y-%m-%d)

backup_path=’/backup/mysqldb’

prg_backup_path=’/usr/dbbackuptool’

prg_backup_tmp_file="$prg_backup_path/ptmp/all_backup_database_name"

prg_backup_inc_file="$prg_backup_path/inc/inc_database"

################################################# all function #####################################################

#func dump database and compress files

mysql_dump()

{

echo “--------------------start backup from host $3 database $4--------------------------”

echo “now backup $4 to $backup_path/$3/$dname1/$4.sql.gz”

/usr/bin/mysqldump -u $1 -p$2 -h $3 -c -f --insert-ignore $4 | gzip > $backup_path/$3/$dname1/$4.sql.gz

echo “backup $4 to $backup_path/$3/$dname1/$4.sql.gz $(du -sh $backup_path/$3/$dname1/$4.sql.gz | awk ‘{print $1}’) complete”

echo “--------------------end backup from host $3 database $4----------------------------”

}

#func get database name

mysql_get_dbname()

{

/bin/rm -f $prg_backup_tmp_file

/bin/echo “show databases” | /usr/bin/mysql -u $1 -p$2 -h $3 | grep -v Database > $prg_backup_tmp_file

}

#func clear old backup db

clear_old_backup_db()

{

if [ -d “$backup_path/$1/” ]; then

/usr/bin/find “$backup_path/$1/” -mindepth 1 -maxdepth 1 -type d -mtime +30 -exec rm -rf {} ;

fi

}

#func check null

check_null()

{

if [ -z $1 ] ; then

echo ‘VALUE is null’

exit

fi

}

################################################ start shell script #################################################

if [ ! -f $prg_backup_inc_file ]; then

exit 0

fi

if [ ! -d “$backup_path/logs/$dname1” ]; then

mkdir -p “$backup_path/logs/$dname1”

fi

#read username password host from file

while read f_username f_password f_dbhostname

do

#check null data

check_null $f_username

check_null $f_password

check_null $f_dbhostname

#check full path backup directory

if [ ! -d “$backup_path/$f_dbhostname/$dname1” ]; then

/bin/mkdir -p “$backup_path/$f_dbhostname/$dname1”

fi

#get database name

mysql_get_dbname $f_username $f_password $f_dbhostname

#backup database

while read loopdbname

do

if [[ $loopdbname == “information_schema” ]]; then

continue

fi

mysql_dump $f_username $f_password $f_dbhostname $loopdbname

done < $prg_backup_tmp_file &> “$backup_path/$f_dbhostname/$dname1/auto_mysql_backup.log”

#run claer old backup db

clear_old_backup_db $f_dbhostname

#send report to email

/usr/bin/mail -s “auto mysql daily backup form host $f_dbhostname date $dname1” "to@mydomain.com" – -f from@mydomain.com < “$backup_path/$f_dbhostname/$dname1/auto_mysql_backup.log”

done < $prg_backup_inc_file

15

หลักการทำงานคือมีเครื่องหลักไว้ backup mysql แล้ว remote เข้าไปยัง server mysql แต่ล่ะตัวแล้วดูดมาเก็บไว้

วิธีเพิ่มเข้าไปโดยใส่ username passwd ip ของ mysql ไปไว้ใน /usr/dbbackuptool/inc/inc_database

สามารถใส่ได้หลายๆ ip โดยเพิ่มต่อโดยขึ้นบรรทัดใหม่ครับ

อย่าลืมเปิด firewall ให้ ip ของ backup ด้วยนะครับ

แล้วก็เอา file นี้ไปใส่ใน cron ให้รันทุกวัน /usr/dbbackuptool/autobackup_mysql.sh

มันจะเก็บไว้ 30 วันแล้วลบออกให้เองครับ

มันจะส่งเมลไปบอกด้วยครับว่า backup อะไรไปบ้าง สำเร็จหรือไม่ มี error หรือป่าว

ลองดูครับเอา code version เก่ามาให้เอาไปปรับปรุงกันต่อครับ

16

ขอบคุณมาก ๆ ครับ อันนี้คือมัน backup ตลอดเวลาเลยใช่ไหมครับ หรือเป็นทุก ๆ กี่วิ กี่นาที กี่ชั่วโมงครับ

17

ผมใช้ mysqldump ไม่เคยเจอปัญหานี้นะครับ db เพี้ยน เละแค่ไหนมันก็ dump ได้ ใส่กลับได้ถูกตลอด

18

ถ้า DB ก้อนใหญ่จัด ผมชอบเจอ Syntax error ตอน import กลับเข้าไปคืนนะ หาวิธีแก้อยู่เหมือนกัน ถึงได้เตือนไว้ก่อนว่าให้ test ล่วงหน้า

19

ใช้คำสั่ง rsync Backup ไฟล์ใน /var/lib/mysql (Directadmin) มาทั้งก้อนก็น่าจะไม่ทำให้เครื่องโหลดนะครับ

แล้วเวลา restore ก็แค่เอาไปทับของเดิม วิธีนี้ผมใช้กับ database ที่มีขนาดใหญ่มากๆ ไม่มีปัญหาในเรื่องภาษาด้วย

20

xtrabackup ชัวร์กว่าครับ