简介
Part1:写在最前
在很早之前，我写过一个MySQL生产库全库备份脚本，今天有同事问我是不是要再加一个-R参数来备份存储过程，理由的话是由于mysqldump --help中 关于存储过程的默认备份是false。
routines FALSE
MySQL生产库全库备份脚本
http://suifu.blog.51cto.com/9167728/1758022
实战
Part1:写在最前
我备份一般就三个参数
--single-transaction -A --master-data=2
分别是预防锁，全库备份和记录复制信息
有人问单库怎么恢复？可以移步
从MySQL全库备份中恢复某个库和某张表
http://suifu.blog.51cto.com/9167728/1830651
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
|
[root@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
|
[root@HE3 ~]
# mysql -uroot -p < Master_db_201612051722.sql
Enter password:
[root@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参数，也依然可以调用之前创建的存储过程。
——总结——
存储过程存储在mysql.proc表中，而笔者由于采用了-A全库备份的策略，即包含mysql库，所以存储过程也得以备份。-R参数一般用于单库备份或者多库备份。由于笔者的水平有限，编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。
简介
Part1:写在最前
在很早之前，我写过一个MySQL生产库全库备份脚本，今天有同事问我是不是要再加一个-R参数来备份存储过程，理由的话是由于mysqldump --help中 关于存储过程的默认备份是false。
routines FALSE
MySQL生产库全库备份脚本
http://suifu.blog.51cto.com/9167728/1758022
实战
Part1:写在最前
我备份一般就三个参数
--single-transaction -A --master-data=2
分别是预防锁，全库备份和记录复制信息
有人问单库怎么恢复？可以移步
从MySQL全库备份中恢复某个库和某张表
http://suifu.blog.51cto.com/9167728/1830651
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
|
[root@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
|
[root@HE3 ~]
# mysql -uroot -p < Master_db_201612051722.sql
Enter password:
[root@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参数，也依然可以调用之前创建的存储过程。
——总结——
存储过程存储在mysql.proc表中，而笔者由于采用了-A全库备份的策略，即包含mysql库，所以存储过程也得以备份。-R参数一般用于单库备份或者多库备份。由于笔者的水平有限，编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。
本文转自 dbapower 51CTO博客，原文链接：http://blog.51cto.com/suifu/1879662，如需转载请自行联系原作者
