Mysql Backup and Recovery

[size=medium]
[b]1.Backup and Recovery Types[/b]
1).logical backup versus physical backups
2).online backup versus offline backups
3).local versus remote backups
4).Full Versus Incremental Backups
Incremental backups are made possible by enabling the server's binary log, which the server uses to record data changes.
5).Full Versus Point-in-Time (Incremental) Recovery
Point-in-Time Recovery need binary log
6).Backup Scheduling, Compression, and Encryption

[b]2.Backup examples:[/b]
a).make a full backup with mysqldump
mysql> mysqldump --single-transation --flush-logs --master-data=2 \
mysql> --all-databases > backup.sql
the --flush-logs option causes the server to flush its logs. The --master-data option causes mysqldump to write binary log information to its output(MASTER_LOG_FILE and MASTER_LOG_POS)
b).we can create an incremental backup by flushing the logs to begin a new binary log file.
#mysqladmin flush-logs
#copy the binary log to safe place
c).using backups for recovery
#mysql < backup.sql
process incremental backup binlog
#mysqlbinlog _binlogfiles_ | mysql
process some other binlog from the server binlogfiles

[b]3.mysqldump tips[/b]
a).#mysqldump -uroot -p --default-character-set=utf8 -c -e
db_name tbl1 tbl2 > backup.sql
[tbl1,tbl2可选,没有表名则备份整个数据库]
-c Use complete insert statements
-e Use multiple-row INSERT syntax that include several VALUES lists
ps: 其他可能有用的参数
--ingore-table=db_name.tbl3 (如果要ignore两个以上的表,需要再写这个参数选项)
--insert-ignore

b).导入sql文件到数据库
一般sql文件编码是utf8.
导入sql文件到数据库有两种方法，不过linux下只能使用:
mysql -uroot -p db_name < backup.sql这种方式
还有一种是进入mysql>命令行时使用source 命令
这里应该注意使用set names utf8命令，不然sql文件里的中文会出现乱码.
可以考虑将这条命令加在sql文件之前.

[/size]