การ Backup ฐานข้อมูล MySQL ไปเก็บไว้บน Amazon S3

Screen Shot 2556-01-29 at 10.17.29 AM

เรื่องก็มีอยู่ว่าอยากจะทำการ backup ฐานข้อมูลทั้งหมดของ MySQL ไปเก็บไว้นอก database server ซึ่งปกติก็มักจะเป็น backup server ที่เราจัดหาไว้สำหรับ backup ข้อมูลภายในองค์กร แต่การจะจัดซื้อ server สักเครื่องก็ไม่ใช่เรื่องง่าย เพราะเงินไม่ใช่ของเรา วิธีการที่ง่ายกว่าคือหาพื้นที่ข้างนอกที่ให้ใช้งานฟรี หรือเช่าพื้นที่ข้างนอกซึ่งจะใช้เงินลงทุนน้อยกว่า และอาจจะทำให้เจ้าของเงินตัดสินใจได้ง่ายขึ้นด้วย

พอคิดแบบนี้ แวบแรกนึกไปถึง Dropbox แต่คิดได้ว่า Dropbox เองก็ใช้ Amazon S3 อีกต่อนึง และเคยมีข้อมูลผ่านตาอยู่บ่อยๆว่ามี library ในการจัดการไฟล์ใน S3 อยู่มากมายคิดแล้วก็เลย Google ดู และไปเจอว่า มีคนทำไว้แล้ว บทความนี้เลยขอลอก script เค้ามาทดสอบเอาดื้อๆ แต่ตอนใช้งานจริงข้อมูลที่ copy/paste จากในเว็บนั้นเพี้ยน เช่นอักขระบางตัว รวมไปถึง indent ของ Python ซึ่งทำให้ script ที่ลอกมานั้นทำงานไม่ได้ จึงต้องมีการแก้ไขนิดหน่อย ถ้าไม่อยากพิมพ์เอง ก็สามารถ download script mysql_to_s3.py ไปใช้งานได้

เรื่องของวิธีการใช้ Amazon S3 คงไม่ขออธิบายในบทความนี้เพราะบทความจะยาวเกินไป ลองศึกษาได้จาก Amazon S3 – The Beginner’s Guide

Database server ที่ใช้ทดสอบนี้เป็น Debian Linux ถ้าหากใช้ platform อื่นก็สามารถประยุกต์ไปใช้ได้ไม่ยาก และอาจต้องมีการแก้ไข script mysql_to_s3.py บ้างนิดหน่อยในบรรทัดที่ 10-14 โดยอาจจะ hard code username password และ hostname เข้าไปเลยก็ได้

อ่านเพิ่มเติม

mysqldump to CSV

ถ้าหากเคยใช้ MySQL กันมาบ้าง อาจจะเคยใช้คำสั่ง mysqldump ซึ่งเป็นคำสั่งที่ช่วยเอาข้อมูลจากฐานข้อมูลมาเก็บอยู่ในรูปของไฟล์ ปกติแล้วการ dump ข้อมูลออกมาจะนิยมเก็บในรูปของ sql statement เนื่องจากเอาไปใช้ต่อได้ง่าย เช่น การ backup หรือ การคัดลอกฐานข้อมูล

แต่ถ้าหากต้องการข้อมูลในรูปแบบอื่นที่ไม่ใช่ SQL statement เช่นในรูปแบบ CSV file เพื่อสามารถนำไป import เข้า excel หรือฐานข้อมูลอื่นที่ไม่ใช่ MySQL หรือให้คนอ่านได้ง่าย เราสามารถทำได้ดังนี้

mysqldump -u<username> -p -t -T ./<dir> <database>
<table> --fields-enclosed-by=\" --fields-terminated-by=,
<username>

คือ username ที่ใช้เชื่อมต่อกับฐานข้อมูล

<dir>

คือชื่อ directory ที่จะใช้เก็บข้อมูล

<database>

คือชื่อ database ที่ต้องการ dump

<table>

คือชื่อ table ที่ต้องการ dump

MySQL benchmark

ลองทำ MySQL benchmark ด้วยโปรแกรม sysbench มี 3 version ซึ่งใช้ patch ต่างกัน มีของ Google Percona และของเดิมๆจาก Gentoo portage โดยทำการทดสอบบน Intel Quad core (Q6600) 2.4GHz Ram 4GB version ของ MySQL ต่างกันนิดหน่อย เพราะ patch ที่ออกมาไม่เท่ากัน แต่ใช้ MySQL configuration เดียวกัน ไม่ได้ tune อะไรเป็นพิเศษ เท่าที่ดูจากกราฟของเดิมๆจาก Gentoo ดูดีที่สุด กราฟสีแดงเป็นของแถม ซึ่งเป็น MySQL ที่ติดตั้งบน Xeon Quad (X3320) server Ram 4GB ลง Debian ตัว MySQL ใช้ configuration เดิมๆ ของ Debian แต่เดิมเครื่องนี้มีปัญหากับ Gentoo ซึ่งก็ยังหาสาเหตุไม่ได้ (เขียนไว้ใน blog ก่อนหน้านี้) เอาไว้มีเวลาจะกลับมาค้นดูใหม่ว่าเกิดอะไรขึ้น bench-mysql1 ดูเหมือนว่า version หลังๆ bug หลายอย่างได้ถูกแก้ไขไปแล้ว อย่าง smp patch ของ google ก็ไม่จำเป็นอีกต่อไป ไม่รู้ว่าถ้า tune ให้เข้ทีเข้าทางแล้ว มันจะรีดความสามารถของ MySQL ได้ขนาดไหน

อาการแปลกๆของ MySQL บน Xeon quad-core

เรื่องมีอยู่ว่า ผมทำ benchmark MySQL เพื่อให้แน่ใจว่ามันสามารถรับ load ได้จริง แต่เนื่องจากยุคนี้เป็นยุคของ Multi-cores การขยาย CPU จะไม่แข่งกันด้วย clock speed แต่จะแข่งกันที่จำนวน core ของ CPU โปรแกรมก็ต้องปรับเปลี่ยนเพื่อให้สามารถทำงานได้ดีบนจำนวน core ที่มากขึ้นเหล่านี้

MySQL เป็น database server ตัวหนึ่งที่ทำงานในลักษณะของ multi-threads ซึ่ง OS กระจาย thread ไปทำงานในแต่ละ core ของ CPU เคยได้ยินมาว่า MySQL ทำงานได้ไม่ดีนักกับจำนวน thread มากๆ มีหลายคนที่พยายามจะแก้ข้อจำกัดนี้ หนึ่งในนั้นคือ google ซึ่งมี code อยู่บน google code โดยจะมี patch เพื่อแก้ปัญหานี้

เดิมผมมี MySQL ทำงานอยู่บนเครื่อง PC Quad-core (Q6600) เครื่องหนึ่ง ลง Gentoo Linux ไว้ เมื่อได้ source code MySQL มาก็จัดแจง patch compile และ run โปรแกรม sysbench เพื่อทำ benchmark ผลออกมาน่าพอใจ throughput ออกมาได้พัน transaction ต่อวินาที (กราฟสีแดง)

ต่อมาได้ server ที่มี CPU Xeon quad-core มาทดสอบ ก็จัดแจงทำเหมือนเดิม แม้กระทั่ง mysql configuration file ก็ใช้ของเดิม พอ run sysbench อีกครั้ง ผลที่ออกมาน่าตกใจมาก (กราฟสีเขียว) throughput หล่นลงไปเหลือร้อยนิดๆ ลองเจ้าไปดูระระบบ CPU ก็ idle เกือบ 90% มันเกิดอะไรขึ้นหว่า พยายามหาคำตอบอยู่สองวันสองคืน ค้นใน Google ไม่มีใครพูดถึงเรื่องนี้เลย

sysbench-1

พี่ที่ทำงานช่วยดูเรื่องนี้ด้วยโดยลองลง FreeBSD 7.1 ให้ผลที่ไม่ต่างกัน ลองทำแบบเดิมในเครื่อง server ที่ใช้ Xeon quad-core อีกเครื่องก็ได้ผลไม่ต่างกัน นั่นแสดงว่าไม่ใช่ความบังเอิญ ผมยังไม่มีคำตอบในเรื่องนี้ เพราะไม่มีปัญญาจะแก้ไขอะไรได้

วิธีแก้ปัญหาของผมคือ เปลี่ยน OS ผมเริ่มด้วยการลง CentOS 5.2 ไว้ที่ server เครื่องหนึ่ง และตามด้วย Open Solaris ไว้ที่ server อีกเครื่องโดยที่ยังไม่ได้ทำ benchmark ใน CentOS เพราะคิดว่า Linux เหมือนกันคงเจอปัญหาเดียวกัน เลยมุ่งเป้าไปที่ Open Solaris มากกว่า พอลงเร็จพยายาม set network ให้ Open Solaris อยู่นานแต่ไม่สำเร็จ เริ่มเบื่อเลยกลับมาทำ benchmark ใน CentoOS ที่ลงไว้ก่อนหน้านี้ ผลออกมาอยากจะกรี๊ดดังๆ เพราะอดนอนมาสองคืน แต่แก้ได้ด้วย CentOS สาเหตุเกิดจากอะไรไว้จะกลับมาหาคำตอบ

sysbench-2