Mysql DBA system learning (17) Complete mysql backup and recovery practices

Full practices of mysql backup and recovery 1. Environment Settings between backup databases 1. Create Database test1, create table tt Insert the following data mysqlcreatedatabasetest1; QueryOK, 1rowaffected ..

Full practices of mysql backup and recovery 1. Environment Settings between backup databases 1. Create Database test1, create table tt Insert the following data mysqlcreatedatabasetest1; QueryOK, 1rowaffected ..

Complete mysql backup and recovery practices

1. Environment Settings between backup Databases

1. Create Database test1, create table tt, and insert the following data

Mysql> create database test1; Query OK, 1 row affected (0.04 sec) mysql> use test1Database changedmysql> 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. Because the record format of the binary log file I set is row, every data change in a row will record the log once.

Mysql> show variables like "% format %"

->;

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

| Variable_name | Value |

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

| Binlog_format | ROW |

3. There is only one binary log file.

Mysql> show binary logs;

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

| Log_name | File_size |

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

| Mysqlbin.000161 | 1133 |

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

1 row in set (0.00 sec)

4. view the binary log file content

Binary log file end_log_pos 1133

[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_FBINLOG 'tables // 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_FBINLOG 'tables // 4 AwAAAAgAamlzdWFuamkDAHdoeQ = '/*! */; # 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 fileROLLBACK/* added by mysqlbinlog */;/*! 50003 SET COMPLETION_TYPE = @ OLD_COMPLETION_TYPE */;

2. Backup database test1

1. mysqldump backup database

[Root @ test4 ~] # Mysqldump -- databases test1 -- skip-opt -- quick -- extended-insert = false -- lock-all-tables -- master-data = 2-u root-p123456>/tmp/test1. SQL

2. view the backup file

We found that the starting position of the record at this time is exactly 1133, as shown in the following example:

Change master to MASTER_LOG_FILE = 'mysqlbin. 100', MASTER_LOG_POS = 000161;

[Root @ test4 ~] # Cat/tmp/test1. SQL -- MySQL dump 10.13 Distrib 5.1.70, for unknown-linux-gnu (x86_64) ---- Host: localhost Database: test1 -- Login Server version 5.1.70-log /*! 40103 SET @ OLD_TIME_ZONE = @ TIME_ZONE */;/*! 40103 SET TIME_ZONE = '+ '*/;/*! 40014 SET @ OLD_UNIQUE_CHECKS = @ UNIQUE_CHECKS, UNIQUE_CHECKS = 0 */;/*! 40014 SET @ OLD_FOREIGN_KEY_CHECKS = @ FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0 */;/*! 40101 SET @ OLD_ SQL _MODE = @ SQL _MODE, SQL _MODE = 'no _ AUTO_VALUE_ON_ZERO '*/;/*! 40111 SET @ OLD_ SQL _NOTES = SQL _notes, SQL _NOTES = 0 */; ---- Position to start replication or point-in-time recovery from ---- CHANGE MASTER TO MASTER_LOG_FILE = 'mysqlbin. 000161 ', MASTER_LOG_POS = 1133; ---- Current Database: 'test1' -- create database /*! 32312 if not exists */'test1 '/*! 40100 default character set utf8 */; USE 'test1'; ---- Table structure for table 'TT '--/*! 40101 SET @ saved_cs_client = @ character_set_client */;/*! 40101 SET character_set_client = utf8 */; create table 'TT' ('id' int (11) default null, 'name' varchar (100) default null, 'msg 'varchar (200) default null );/*! 40101 SET character_set_client = @ saved_cs_client */; ---- Dumping data for table 'TT' -- insert into 'TT' VALUES (1, 'chenzhongyang ', 'how are you '); insert into 'TT' VALUES (2, 'tianhongyan ', 'bmw'); insert into 'TT' VALUES (3, 'jisuanji', 'why ');/*! 40103 SET TIME_ZONE = @ OLD_TIME_ZONE */;/*! 40101 SET SQL _MODE = @ OLD_ SQL _MODE */;/*! 40014 SET FOREIGN_KEY_CHECKS = @ OLD_FOREIGN_KEY_CHECKS */;/*! 40014 SET UNIQUE_CHECKS = @ OLD_UNIQUE_CHECKS */;/*! 40111 SET SQL _NOTES = @ OLD_ SQL _NOTES */; -- Dump completed on 22:48:50

3. Modify and insert data to the table and delete the table by mistake.

Because we accidentally delete the table tt, we need to restore the table tt through binary logs.

Mysql> insert into tt values (4, 'shuijunyi', 'boss ');

Query OK, 1 row affected (0.01 sec)

Mysql> insert into tt values (5, 'zhujun', 'May I know your name ');

Query OK, 1 row affected (0.00 sec)

Mysql> select * from tt;

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

| Id | name | msg |

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

| 1 | chenzhongyang | how are you |

| 2 | tianhongyan | BMW |

| 3 | jisuanji | why |

| 4 | shuijunyi | boss |

| 5 | zhujun | may I know your name |

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

5 rows in set (0.01 sec)

Mysql> drop table tt;

Query OK, 0 rows affected (0.00 sec)

4. view the locations of misoperations

You can use show master status to view the location of the current binary log file.

Mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| Mysqlbin.000161 | 1622 |

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

1 row in set (0.00 sec)