再谈MySQL全库备份

23312321

简介Part1:写在最前
在很早之前，我写过一个MySQL生产库全库备份脚本，今天有同事问我是不是要再加一个-R参数来备份存储过程，理由的话是由于mysqldump --help中 关于存储过程的默认备份是false。
routines                          FALSE



实战Part1:写在最前
我备份一般就三个参数

--single-transaction-A --master-data=2

分别是预防锁，全库备份和记录复制信息


Part2:创建存储过程

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52

[iyunv@HE3 ~]# mysql -uroot -p Enter 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的没有记录。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

-- 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 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 ;; 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的备份文件

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

[iyunv@HE3 ~]# mysql -uroot -p < Master_db_201612051722.sql Enter password: [iyunv@HE3 ~]# mysql -uroot -p Enter 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参数，也依然可以调用之前创建的存储过程。