Thursday, June 28, 2007

MySQL備份筆記

mysqldump基本語法(5.0以上版本):
mysqldump -u Username -pPassword --databases Mydatabase > mydatabase.sql
只備份某些table:
mysqldump -u Username -pPassword Mydatabase mytable1 mytable2 > mydatabase.sql
按日期備份,並以gzip壓縮(使用shellscript):
DEST="/backup_dir"

CURRENT_DATE="$(date +"%Y%m%d")"
FILE="$DEST/backup.$CURRENT_DATE.gz"

mysqldump -v -R --add-drop-table --default-character-set=utf8 -u Username -h Host -pPassword Mydatabase | gzip -9 > $FILE

把dumpfile還原到資料庫:
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
mysql -u Userame -pPassword Mydatabase < dump_file

No comments: