摘要:本文主要向大家介绍了MySQL数据库之再谈MySQL全库备份 ,通过具体的内容向大家展现,希望对大家学习MySQL数据库有所帮助。
本文主要向大家介绍了MySQL数据库之再谈MySQL全库备份 ,通过具体的内容向大家展现,希望对大家学习MySQL数据库有所帮助。
再谈MySQL全库备份
简介
Part1:写在最前
在很早之前,我写过一个MySQL生产库全库备份脚本,今天有同事问我是不是要再加一个-R参数来备份存储过程,理由的话是由于mysqldump --help中 关于存储过程的默认备份是false。
routines FALSE
MySQL生产库全库备份脚本
//suifu.blog.51cto.com/9167728/1758022
实战
Part1:写在最前
我备份一般就三个参数
--single-transaction -A --master-data=2
分别是预防锁,全库备份和记录复制信息
有人问单库怎么恢复?可以移步
从MySQL全库备份中恢复某个库和某张表
//suifu.blog.51cto.com/9167728/1830651
Part2:创建存储过程
[root@HE3 ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.6.25-log MySQL Community Server (GPL)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database helei;Query OK, 1 row affected (0.02 sec)mysql> use helei;Database changed mysql> create table helei( -> id int(10) unsigned NOT NULL AUTO_INCREMENT, -> c1 int(10) NOT NULL DEFAULT '0', -> c2 int(10) unsigned DEFAULT NULL, -> c5 int(10) unsigned NOT NULL DEFAULT '0', -> c3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> c4 varchar(200) NOT NULL DEFAULT '', -> PRIMARY KEY(id), -> KEY idx_c1(c1), -> KEY idx_c2(c2) -> )ENGINE=InnoDB ;Query OK, 0 rows affected (0.02 sec)mysql> delimiter $$ mysql> drop procedure if exists `insert_helei` $$ and()*row_num),now(),repeat('su', floor(rand()*20)));set i = i+1; END while;end$$Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> create procedure `insert_helei`(in row_num int ) -> begin -> declare i int default 0; -> while i < row_num do -> insert into helei(c1, c2, c5,c3, c4) values( floor(rand()*row_num),floor(rand()*row_num),floor(rand()*row_num),now(),repeat('su', floor(rand()*20))); -> set i = i+1; -> END while; -> -> end$$ Query OK, 0 rows affected (0.00 sec)mysql>
Part3:执行不同的备份脚本
参数分别是
--single-transaction -A --master-data=2
--single-transaction -A -R --master-data=2
Part4:对比SQL文件
可以发现加了-R的文件中记录了创建存储过程的SQL语句,没加-R的没有记录。
-- Dumping routines for database 'helei'-- /*!50003 DROP PROCEDURE IF EXISTS `insert_helei` */;/*!50003 SET @saved_cs_client = @@character_set_client */ ;/*!50003 SET @saved_cs_results = @@character_set_results */ ;/*!50003 SET @saved_col_connection = @@collation_connection */ ;/*!50003 SET character_set_client = utf8 */ ;/*!50003 SET character_set_results = utf8 */ ;/*!50003 SET collation_connection = utf8_general_ci */ ;/*!50003 SET @saved_sql_mode = @@sql_mode */ ;/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;DELIMITER ;;CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_helei`(in row_num int )begin declare i int default 0; while i < row_num doinsert into helei(c1, c2, c5,c3, c4) values( floor(rand()*row_num),floor(rand()*row_num),floor(rand()*row_num),now(),repeat('su', floor(rand()*20)));set i = i+1; END while;end ;;DELIMITER ;/*!50003 SET sql_mode = @saved_sql_mode */ ;/*!50003 SET character_set_client = @saved_cs_client */ ;/*!50003 SET character_set_results = @saved_cs_results */ ;/*!50003 SET collation_connection = @saved_col_connection */ ;
Part1:验证
清空linux环境,重装mysql,导入不带-R参数即不带创建存储过程SQL的备份文件
[root@HE3 ~]# mysql -uroot -p < Master_db_201612051722.sql Enter password: [root@HE3 ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.6.25-log MySQL Community Server (GPL)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use helei;Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from helei;Empty set (0.00 sec)mysql> call insert_helei(5);Query OK, 1 row affected (0.01 sec)mysql> select * from helei;+----+----+------+----+---------------------+------------------------------------+| id | c1 | c2 | c5 | c3 | c4 |+----+----+------+----+---------------------+------------------------------------+| 1 | 1 | 2 | 0 | 2016-12-05 17:51:37 | sususususususususususu || 2 | 2 | 1 | 0 | 2016-12-05 17:51:37 | sususususususususususususususususu || 3 | 4 | 1 | 3 | 2016-12-05 17:51:37 | sususususususususususu || 4 | 3 | 2 | 3 | 2016-12-05 17:51:37 | sususususususususususu || 5 | 0 | 2 | 4 | 2016-12-05 17:51:37 | sususususususususu |+----+----+------+----+---------------------+------------------------------------+ 5 rows in set (0.00 sec)mysql>
发现即便没有添加-R参数,也依然可以调用之前创建的存储过程。
——总结——
存储过程存储在mysql.proc表中,而笔者由于采用了-A全库备份的策略,即包含mysql库,所以存储过程也得以备份。-R参数一般用于单库备份或者多库备份。由于笔者的水平有限,编写时间也很仓促,文中难免会出现一些错误或者不准确的地方,不妥之处恳请读者批评指正。
本文由职坐标整理并发布,希望对同学们学习MySQL有所帮助,更多内容请关注职坐标数据库MySQL数据库频道!
您输入的评论内容中包含违禁敏感词
我知道了
请输入正确的手机号码
请输入正确的验证码
您今天的短信下发次数太多了,明天再试试吧!
我们会在第一时间安排职业规划师联系您!
您也可以联系我们的职业规划师咨询:
版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
沪公网安备 31011502005948号