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

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> showvariableslike "% format %"

->;

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

| Variable_name | Value |

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

| Binlog_format | ROW |

3. There is only one binary log file.

Mysql> showbinarylogs;

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

| Log_name | File_size |

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

| Mysqlbin.000161 | 1133 |

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

1 rowinset (0.00sec)

4. view the binary log file content

Binary Log Fileend_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=0SET 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=0SET 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 'w5QoUhMBAAAAMAAAAAcDAAAAABUAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIHw5QoUhcBAAAANAAAADsDAAAAABUAAAAAAAEAA//4AgAAAAsAdGlhbmhvbmd5YW4DAEJNVw=='/*!*/;# at 827#130905 22:27:15 server id 1  end_log_pos 897   Query   thread_id=7 exec_time=0 error_code=0SET 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=0SET 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 '7JQoUhMBAAAAMAAAAPYDAAAAABUAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIH7JQoUhcBAAAAMQAAACcEAAAAABUAAAAAAAEAA//4AwAAAAgAamlzdWFuamkDAHdoeQ=='/*!*/;# at 1063#130905 22:27:56 server id 1  end_log_pos 1133  Query   thread_id=7 exec_time=0 error_code=0SET 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 -- databasestest1 -- skip-opt -- quick -- extended-insert = false -- lock-all-tables -- master-data = 2-uroot-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:

CHANGEMASTERTOMASTER_LOG_FILE='mysqlbin.000161',MASTER_LOG_POS=1133;

[root@test4 ~]# cat /tmp/test1.sql-- MySQL dump 10.13  Distrib 5.1.70, for unknown-linux-gnu (x86_64)---- Host: localhost    Database: test1-- -------------------------------------------------------- Server version   5.1.70-log/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */;/*!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 2013-09-05 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> insertintottvalues (4, 'shuijunyi', 'boss ');

QueryOK, 1 rowaffected (0.01sec)

Mysql> insertintottvalues (5, 'zhujun', 'mayiknowyourname ');

QueryOK, 1 rowaffected (0.00sec)

Mysql> select * fromtt;

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

| Id | name | msg |

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

| 1 | chenzhongyang | howareyou |

| 2 | tianhongyan | BMW |

| 3 | jisuanji | why |

| 4 | shuijunyi | boss |

| 5 | zhujun | mayIknowyourname |

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

5 rowsinset (0.01sec)

Mysql> droptablett;

QueryOK, 0 rowsaffected (0.00sec)

4. view the locations of misoperations

You can use showmasterstatus to view the location of the current binary log file.

Mysql> showmasterstatus;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| Mysqlbin.000161 | 1622 |

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

1 rowinset (0.00sec)

We can see that the starting position of droptablett is 1622. Therefore, you only need to restore the table tt that was accidentally deleted to the 1622 position.

mysql>  show binlog events  in 'mysqlbin.000161'-> ;| mysqlbin.000161 | 1250 | Write_rows  |         1 |        1301 | table_id: 22 flags: STMT_END_F                                                        || mysqlbin.000161 | 1301 | Query       |         1 |        1371 | COMMIT                                                                                || mysqlbin.000161 | 1371 | Query       |         1 |        1440 | BEGIN                                                                                 || mysqlbin.000161 | 1440 | Table_map   |         1 |        1488 | table_id: 22 (test1.tt)                                                               || mysqlbin.000161 | 1488 | Write_rows  |         1 |        1552 | table_id: 22 flags: STMT_END_F                                                        || mysqlbin.000161 | 1552 | Query       |         1 |        1622 | COMMIT                                                                                || mysqlbin.000161 | 1622 | Query       |         1 |        1699 | use `test1`; drop table tt                                                            || mysqlbin.000161 | 1699 | Rotate      |         1 |        1741 | mysqlbin.000162;pos=4                                                                 |+-----------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------+26 rows in set (0.00 sec)

5. Restore the database

At this time, we found that only three pieces of data were restored, but we had a total of five pieces of data. At this time, we had to recover them through the binary log file.

Note that binary log files will also be generated when we recover the database. Therefore, we must be clear about the binary log files before backup and the binary log files after recovery.

Mysql> dropdatabasetest1;

QueryOK, 0 rowsaffected (0.00sec)

Mysql> showdatabases;

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

| Database |

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

| Information_schema |

| Mysql |

| Test |

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

3 rowsinset (0.00sec)

[Root @ test4 ~] # Mysql-uroot-p123456 </tmp/test1. SQL

Mysql> showdatabases;

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

| Database |

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

| Information_schema |

| Mysql |

| Test |

| Test1 |

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

4 rowsinset (0.00sec)

Mysql> usetest1

Databasechanged

Mysql> select * fromtt;

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

| Id | name | msg |

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

| 1 | chenzhongyang | howareyou |

| 2 | tianhongyan | BMW |

| 3 | jisuanji | why |

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

3 rowsinset (0.00sec)

6. Restore the other two data before the misoperation

This is the time from where the backup starts to where the table is deleted. Because this location is where the other two pieces of data are inserted.

We can clearly see the two data.

[root@test4 ~]# mysqlbinlog  -p123456 --start-position=1133 --stop-position=1622  -vv  /tmp/mysqlbin.000161/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4#130905 21:02:49 server id 1  end_log_pos 106   Start: binlog v 4, server v 5.1.70-log created 130905 21:02:49 at startupROLLBACK/*!*/;BINLOG '+YAoUg8BAAAAZgAAAGoAAAAAAAQANS4xLjcwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAD5gChSEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC'/*!*/;# at 1133#130905 23:06:50 server id 1  end_log_pos 1202  Query   thread_id=7 exec_time=0 error_code=0SET TIMESTAMP=1378393610/*!*/;SET @@session.pseudo_thread_id=7/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=0/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8 *//*!*/;SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;BEGIN/*!*/;# at 1202# at 1250#130905 23:06:50 server id 1  end_log_pos 1250  Table_map: `test1`.`tt` mapped to number 22#130905 23:06:50 server id 1  end_log_pos 1301  Write_rows: table id 22 flags: STMT_END_FBINLOG 'Cp4oUhMBAAAAMAAAAOIEAAAAABYAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIHCp4oUhcBAAAAMwAAABUFAAAAABYAAAAAAAEAA//4BAAAAAkAc2h1aWp1bnlpBABib3Nz'/*!*/;### INSERT INTO `test1`.`tt`### SET###   @1=4 /* INT meta=0 nullable=1 is_null=0 */###   @2='shuijunyi' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */###   @3='boss' /* VARSTRING(600) meta=600 nullable=1 is_null=0 */# at 1301#130905 23:06:50 server id 1  end_log_pos 1371  Query   thread_id=7 exec_time=0 error_code=0SET TIMESTAMP=1378393610/*!*/;COMMIT/*!*/;# at 1371#130905 23:07:39 server id 1  end_log_pos 1440  Query   thread_id=7 exec_time=0 error_code=0SET TIMESTAMP=1378393659/*!*/;BEGIN/*!*/;# at 1440# at 1488#130905 23:07:39 server id 1  end_log_pos 1488  Table_map: `test1`.`tt` mapped to number 22#130905 23:07:39 server id 1  end_log_pos 1552  Write_rows: table id 22 flags: STMT_END_FBINLOG 'O54oUhMBAAAAMAAAANAFAAAAABYAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIHO54oUhcBAAAAQAAAABAGAAAAABYAAAAAAAEAA//4BQAAAAYAemh1anVuFABtYXkgSSBrbm93IHlvdXIgbmFtZQ=='/*!*/;### INSERT INTO `test1`.`tt`### SET###   @1=5 /* INT meta=0 nullable=1 is_null=0 */###   @2='zhujun' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */###   @3='may I know your name' /* VARSTRING(600) meta=600 nullable=1 is_null=0 */# at 1552#130905 23:07:39 server id 1  end_log_pos 1622  Query   thread_id=7 exec_time=0 error_code=0SET TIMESTAMP=1378393659/*!*/;COMMIT/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

Start data recovery

[Root @ test4 ~] # Mysqlbinlog -- start-position = 1133 -- stop-position = 1622-vv/tmp/mysqlbin.000161 | mysql-uroot-p123456

Data will be returned at this time.

Mysql> select * fromtt;

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

| Id | name | msg |

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

| 1 | chenzhongyang | howareyou |

| 2 | tianhongyan | BMW |

| 3 | jisuanji | why |

| 4 | shuijunyi | boss |

| 5 | zhujun | mayIknowyourname |

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

5 rowsinset (0.00sec)

This article from "Good to live" blog, please be sure to keep this source http://wolfword.blog.51cto.com/4892126/1289659