簡介
Part1: 寫在最前
在很早之前,我寫過一個MySQL生産庫全庫備份腳本,今天有同僚問我是不是要再加一個-R參數來備份存儲過程,理由的話是由于mysqldump --help中 關于存儲過程的預設備份是false。
routines FALSE
MySQL生産庫全庫備份腳本
實戰
Part1: 寫在最前
我備份一般就三個參數
--single-transaction -A --master-data=2
分别是預防鎖,全庫備份和記錄複制資訊
有人問單庫怎麼恢複?可以移步
從MySQL全庫備份中恢複某個庫和某張表
Part2: 建立存儲過程
[[email protected] ~]# 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 `×××ert_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 `×××ert_helei`(in row_num int )
-> begin
-> declare i int default 0;
-> while i < row_num do
-> ×××ert 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 `×××ert_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 `×××ert_helei`(in row_num int )
begin
declare i int default 0;
while i < row_num do
×××ert 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的備份檔案
[[email protected] ~]# mysql -uroot -p < Master_db_201612051722.sql
Enter password:
[[email protected] ~]# 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 ×××ert_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參數一般用于單庫備份或者多庫備份。由于筆 者的水準有限,編寫時間也很倉促,文中難免會出現一些錯誤或者不準确的地方,不妥之處懇請讀者批評指正。
轉載于:https://blog.51cto.com/14311648/2409287