mysql dump and import database huge

[golfreeze]

##เริ่มด้วยการ dump database ออกมาก่อน
mysqldump -u[USER] -p[PASS] [DBNAME] | bzip2 --best > DBNAME.sql.bz2
mysqldump -u[USER] -p[PASS] [DBNAME] table1 | bzip2 --best > DBNAME_table.sql.bz2

ก่อน dump กลับปรับจูนค่า ในการ import
[mysqld]
# Performance settings used for import.
delay_key_write=ALL
bulk_insert_buffer_size=256M

##You are now in mysql shell. Set network buffer length to a large byte number. The default value may throw errors for such large data files
set global net_buffer_length=1000000;

##Set maximum allowed packet size to a large byte number.The default value may throw errors for such large data files.
set global max_allowed_packet=1000000000;

##Disable foreign key checking to avoid delays,errors and unwanted behaviour
SET foreign_key_checks = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;

##การ import ข้อมูลกลับลงไปใน mysql DB
create databases  db_name;
bunzip2 [DBNAME].sql.bz2 | mysql -u[USER] -p[PASS] [DBNAME]

หรือจะ dump เฉพาะ table
mysql -uroot -p database  < dbname_table1.sql

หลังจากทำ import เสร็จแล้ว

SET foreign_key_checks = 1;
SET UNIQUE_CHECKS = 1;
SET AUTOCOMMIT = 1;

[golfreeze]

ลง db server ให้เสร็จแล้ว
อีกวิธีคือ copy data  /var/lib/mysql + ibdata , my.cnf 
มาอีก node เลย

[golfreeze]

#วิธีการเชคขนาดของแต่ละ table ใน database

SELECT table_name AS "Table",  round(((data_length + index_length) / 1024 / 1024), 2) as TEST   FROM information_schema.TABLES  WHERE table_schema = "YOUR_DATABASE_NAME"  order by TEST;

[golfreeze]

SELECT table_schema "DB Name",
Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM   information_schema.tables
GROUP  BY table_schema;

[golfreeze]

## ในกรณีที่ password ที่ใส่มี special character ให้ใส่ ' ' ลงไปเพิ่มด้วยนะครับ
mysqldump -hlocalhost -uUSERNAME -p'PA$$W0RD' DATABASE | gzip > /home/USERNAME/backups-mysql/BACKUP.gz

[golfreeze]

ในกรณีจะ mysqldump ที่มีข้อมูลมากๆ ให้ใส่ option : --single-transaction ลงไปด้วยเพื่อป้องกันการ lock database ครับ
เพราะโดยปกติเวลา รัน mysqldump ตัว service mysqld ตัวออนอยู่ด้วยเสมอครับ

#mysqldump --user=mydbuser --password=mypass --default-character-set=utf8 --single-transaction mydatabase | gzip > "$fullpathbackupfile"