mysql的备份和恢复的完整实践一,备份数据库之间的环境设置1，创建数据库test1，创建表tt插入如下数据mysqlcreatedatabasetest1;QueryOK,1rowaffected..
mysql的备份和恢复的完整实践
一,备份数据库之间的环境设置
1，创建数据库test1，创建表tt 插入如下数据
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，由于我设置的二进制日志文件的记录格式是row，所以每一行的数据改变就会记录一次日志
mysql> show variables like “%format%”
-> ;
+———————+——————-+
| Variable_name | Value |
+———————+——————-+
| binlog_format | ROW |
3， 此时只有一个二进制日志文件
mysql> show binary logs;
+—————–+———–+
| Log_name | File_size |
+—————–+———–+
| mysqlbin.000161 | 1133 |
+—————–+———–+
1 row in set (0.00 sec)
4，查看二进制日志文件的内容
二进制日志文件 end_log_pos 1133
[roo本文来源gao($daima.com搞@代@#码(网t@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*/;
二，备份数据库test1
1，mysqldump备份数据库
[root@test4 ~]# mysqldump –databases test1 –skip-opt –quick –extended-insert=false –lock-all-tables –master-data=2 -u root -p123456 > /tmp/test1.sql
2，查看备份文件
我们发现这个时候记录的开始位置正好是1133，如下就是证明
CHANGE MASTER TO MASTER_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
三，对表进行修改插入数据然后误删表
由于我们不小心把表tt给删除了，那么我们就要把表tt通过二进制日志给恢复过来
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)
四，，查看执行误操作的位置
通过show master status; 可以查看当前的二进制日志文件的位置
mysql> show master status;
+—————–+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+—————–+———-+————–+——————+
| mysqlbin.000161 | 1622 | | |
+—————–+———-+————–+——————+
1 row in set (0.00 sec)
转载请注明原文链接：mysql dba系统学习（17）mysql的备份和恢复的完整实践
