mysql dba系统学习（17）mysql的备份和恢复的完整实践

一,备份数据库之间的环境设置

1，创建数据库test1，创建表tt插入如下数据

mysql> create  database test1;
Query OK, 1 row affected (0.04 sec)
mysql> use test1
Database changed
mysql> create table tt(id int,name varchar(100),msg varchar(200)) engine=myisam;
Query OK, 0 rows affected (0.18 sec)
mysql> insert into tt values(1,'chenzhongyang','how are you');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tt values(2,'tianhongyan','BMW');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tt values(3,'jisuanji','why');
Query OK, 1 row affected (0.00 sec)

2，由于我设置的二进制日志文件的记录格式是row，所以每一行的数据改变就会记录一次日志

mysql>showvariableslike"%format%"

->;

+---------------------+-------------------+

|Variable_name|Value|

+---------------------+-------------------+

|binlog_format|ROW|

3，此时只有一个二进制日志文件

mysql>showbinarylogs;

+-----------------+-----------+

|Log_name|File_size|

+-----------------+-----------+

|mysqlbin.000161|1133|

+-----------------+-----------+

1rowinset(0.00sec)

4，查看二进制日志文件的内容

二进制日志文件end_log_pos1133

[root@test4 ~]# mysqlbinlog '/tmp/mysqlbin.000161'
。。。。。。。。。。。。。。。。。。。
# at 588
#130905 22:26:42 server id 1  end_log_pos 658   Query   thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1378391202/*!*/;
COMMIT
/*!*/;
# at 658
#130905 22:27:15 server id 1  end_log_pos 727   Query   thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1378391235/*!*/;
BEGIN
/*!*/;
# at 727
# at 775
#130905 22:27:15 server id 1  end_log_pos 775   Table_map: `test1`.`tt` mapped to number 21
#130905 22:27:15 server id 1  end_log_pos 827   Write_rows: table id 21 flags: STMT_END_F
BINLOG '
w5QoUhMBAAAAMAAAAAcDAAAAABUAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIH
w5QoUhcBAAAANAAAADsDAAAAABUAAAAAAAEAA//4AgAAAAsAdGlhbmhvbmd5YW4DAEJNVw==
'/*!*/;
# at 827
#130905 22:27:15 server id 1  end_log_pos 897   Query   thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1378391235/*!*/;
COMMIT
/*!*/;
# at 897
#130905 22:27:56 server id 1  end_log_pos 966   Query   thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1378391276/*!*/;
BEGIN
/*!*/;
# at 966
# at 1014
#130905 22:27:56 server id 1  end_log_pos 1014  Table_map: `test1`.`tt` mapped to number 21
#130905 22:27:56 server id 1  end_log_pos 1063  Write_rows: table id 21 flags: STMT_END_F
BINLOG '
7JQoUhMBAAAAMAAAAPYDAAAAABUAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIH
7JQoUhcBAAAAMQAAACcEAAAAABUAAAAAAAEAA//4AwAAAAgAamlzdWFuamkDAHdoeQ==
'/*!*/;
# at 1063
#130905 22:27:56 server id 1  end_log_pos 1133  Query   thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1378391276/*!*/;
COMMIT
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

（编辑：济南站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!