MySQL DBA system learning (%) MySQL backup and recovery complete practice

The complete practice of MySQL backup and recovery

One, backing up the environment settings between databases

1, create the database test1, create the table TT Insert the following data

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 (MB), msg varchar) Engine=myisam;
Query OK, 0 rows affected (0.18 sec)
mysql> INSERT into the TT values (1, ' Chenzhongyang ', ' how are ');
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, because I set the binary log file record format is row, so each row of data changes will record a log

Mysql>showvariableslike "%format%"

->;

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

| variable_name| value|

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

|binlog_format| row|

3, there is only one binary log file at this time

mysql>showbinarylogs;

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

| log_name| file_size|

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

|mysqlbin.000161|1133|

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

1rowinset (0.00SEC)

4, view the contents of the binary log file

Binary log file 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=137839
1202/*!*/;
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=137839
1235/*!*/;
BEGIN/*!*/; # at 727 @ 775 #130905 22:27:15 Server ID 1 end_log_pos 775 table_map: ' test1 '. ' TT ' mapped to number #130905 22:2 7:15 Server ID 1 end_log_pos 827 write_rows:table ID flags:stmt_end_f binlog ' w5qouhmbaaaamaaaaacdaaaaabuaaaaaaae
Abxrlc3qxaaj0daadaw8pbcwbwaih 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=137839
1235/*!*/;
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=137839
1276/*!*/;BEGIN/*!*/; # at 966 @ 1014 #130905 22:27:56 Server ID 1 end_log_pos 1014 table_map: ' test1 '. ' TT ' mapped to number #130905 22: 27:56 Server ID 1 end_log_pos 1063 write_rows:table ID flags:stmt_end_f binlog ' 7JQoUhMBAAAAMAAAAPYDAAAAABUAAAAAAA
Eabxrlc3qxaaj0daadaw8pbcwbwaih 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=13783
91276/*!*/;
COMMIT/*!*/;
DELIMITER;
# End of log file ROLLBACK/* Added by Mysqlbinlog * *; /*!50003 SET completion_type= @OLD_COMPLETION_TYPE * *;