二，启用binlogm
vi my.cnf
log-bin=/var/lib/mysql/mysql-bin.log，如果是这样的话log-bin=mysql-bin.log默认在datadir目录下面
[root@BlackGhost mysql]# ls |grep mysql-bin
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004
mysql-bin.000005
mysql-bin.000006
mysql-bin.index
启动后会产生mysql-bin这样的文件，每启动一次，就会增加一个或者多个。
mysql-bin.000002这样文件存放的是数据库每天增加的数据，所有数据库的数据增量都在这里面。
三，查看mysql-bin.000002这样的文件里面到底是什么东西
[root@BlackGhost mysql]# mysqlbinlog /var/lib/mysql/mysql-bin.000002 > /tmp/add.sql
查看复制打印? [root@BlackGhost mysql]# cat /tmp/add.sql // 下面是根据mysql-bin生成的文件（部分内容） /*!40019 SET @@session.max_insert_delayed_threads=0*/ ; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/ ; DELIMITER /*!*/ ; # at 4 #100929 21:23:52 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.50-log created 100929 21:23:52 at startup # Warning: this binlog was not closed properly. Most probably mysqld crashed writing it. ROLLBACK /*!*/ ; BINLOG ‘ 6D2jTA8BAAAAZgAAAGoAAAABAAQANS4xLjUwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAADoPaNMEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC ‘ /*!*/ ; # at 106 #100929 21:29:35 server id 1 end_log_pos 134 Intvar SET INSERT_ID=16 /*!*/ ; # at 134 #100929 21:29:35 server id 1 end_log_pos 237 Query thread_id=1 exec_time=0 error_code=0 use test /*!*/ ; //这里是test数据库 SET TIMESTAMP=1285766975 /*!*/ ; SET @@session.pseudo_thread_id=1 /*!*/ ; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1 /*!*/ ; SET @@session.sql_mode=0 /*!*/ ; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1 /*!*/ ; /*!\C utf8 */ /*!*/ ; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33 /*!*/ ; SET @@session.lc_time_names=0 /*!*/ ; SET @@session.collation_database=DEFAULT /*!*/ ; insert into aa(name)values( ‘cccccccccc‘ ) /*!*/ ; # at 237 #100929 21:32:21 server id 1 end_log_pos 265 Intvar SET INSERT_ID=12 /*!*/ ; # at 265 #100929 21:32:21 server id 1 end_log_pos 370 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1285767141 /*!*/ ; insert into user(name)values( ‘cccccccccc‘ ) /*!*/ ; # at 370 #100929 21:35:25 server id 1 end_log_pos 440 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1285767325 /*!*/ ; BEGIN /*!*/ ; # at 440 #100929 21:35:25 server id 1 end_log_pos 468 Intvar SET INSERT_ID=45 /*!*/ ; # at 468 #100929 21:35:25 server id 1 end_log_pos 573 Query thread_id=1 exec_time=0 error_code=0 use blog /*!*/ ; //这里是blog数据库 SET TIMESTAMP=1285767325 /*!*/ ; insert into city(CityName)values( ‘asdf‘ ) /*!*/ ; # at 573 #100929 21:35:25 server id 1 end_log_pos 600 Xid = 205 COMMIT /*!*/ ; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */ ; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/ ;下面还有一个重要索引文件就是mysql-bin.index
[root@BlackGhost mysql]# cat mysql-bin.index ./mysql-bin.000001 ./mysql-bin.000002 ./mysql-bin.000003 ./mysql-bin.000004 ./mysql-bin.000005 ./mysql-bin.000006四，增量备份和增量还原
1，增量备份
既然我们知道了，mysql里面新增加的数据在mysql-bin这样的文件里面，我们只要把mysql-bin这样的文件进行备份就可以了。
cp /var/lib/mysql/mysql-bin* /data/mysql_newbak/
2，增量还原，讲几个常用的，比较有用的
a），根据时间来还原 --start-date,--stop-date
[root@BlackGhost mysql]# /usr/local/mysql/bin/mysqlbinlog --start-date="2010-09-29 18:00:00" --stop-date="2010-09-29 23:00:00" /var/lib/mysql/mysql-bin.000002 |mysql -u root -p
根据条件看一下数据
查看复制打印? [root@BlackGhost mysql]# /usr/local/mysql/bin/mysqlbinlog --start- date = "2010-09-29 18:00:00" --stop- date = "2010-09-29 23:00:00" / var /lib/mysql/mysql-bin.000002 //下面是部分内容，其实也就是一些对数据进行操作的sql语句 # at 237 #100929 21:32:21 server id 1 end_log_pos 265 Intvar SET INSERT_ID=12 /*!*/ ; # at 265 #100929 21:32:21 server id 1 end_log_pos 370 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1285767141 /*!*/ ; insert into user(name)values( ‘cccccccccc‘ ) /*!*/ ; # at 370 #100929 21:35:25 server id 1 end_log_pos 440 Query thread_id=1 exec_time=0 error_code=0