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

Source: Internet
Author: User
Tags commit dba mysql backup backup

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 * *;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.