MySQL backup & binlog

- Full backup (dump tables to sql file)

shell> mysqldump --user=xxx --password=xxx --single-transaction --flush-logs --master-data --delete-master-logs --all-databases -r backup_sunday_1_PM.sql

- Differential backup (take binlog file)

shell> mysqladmin flush-logs

MySQL binary logs provide point-in-time recovery. But it may take huge disk space.

flush-logs: close the current binary log file and begin a new one
delete-master-logs: delete binary logs
master-data: write binary log information to dump file, so we know which is the next position in binlog. E.g., in dump file, we see

--
-- Position to start replication or point-in-time recovery from
--
CHANGE MASTER TO MASTER_LOG_FILE='hwm_binlog.000002', MASTER_LOG_POS=98;

And mysqlbinlog can be used to display the content of binlog.

C:\HWM\mysqldb\bin>mysqlbinlog ..\data\hwm_binlog.000002
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# at 4
#110830 23:16:33 server id 1  end_log_pos 98    Start: binlog v 4, server v 5.0.
24-community-nt-log created 110830 23:16:33
# Warning: this binlog was not closed properly. Most probably mysqld crashed wri
ting it.
# at 98
#110830 23:16:33 server id 1  end_log_pos 198   Query   thread_id=108   exec_tim
e=0     error_code=0
use webnmsdb;
SET TIMESTAMP=1314760593;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.uniq
ue_checks=1;
SET @@session.sql_mode=0;
/*!\C utf8 */;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@sessio
n.collation_server=8;
DELETE FROM `webnmsdb`.`extstats`;
# at 198