Another talk about MySQL full-Library backup

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M00/8B/22/wKiom1hFNLHhCGo6AAA4eVx2Dz8403.jpg "title=" icon 2. JPG "alt=" Wkiom1hfnlhhcgo6aaa4evx2dz8403.jpg "/>

A brief discussion on MySQL full-Library backup

Part1 : write at the top

Earlier, I wrote a MySQL production library full library backup script, and today some colleagues asked me if I want to add another-R parameter to back up the stored procedure, because the default backup of the stored procedure in Mysqldump--help is false.

Routines FALSE

MySQL Production library full library backup script

http://suifu.blog.51cto.com/9167728/1758022

Actual combat

Part1:written in the first

I backup general on three parameters

--single-transaction-a--master-data=2

Prevention lock, full library backup and record copy information, respectively

Someone asked the library how to recover? Can be

Recover a library and a table from a MySQL full-library backup

http://suifu.blog.51cto.com/9167728/1830651

Part2 :

[[email protected] ~]# mysql -uroot -penter password: welcome to  The mysql monitor.  commands end with ; or \g.your mysql  connection id is 7Server 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  Itsaffiliates. other names may be trademarks of their respectiveowners . 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  changedmysql>&Nbsp;create table helei (    -> id int)  unsigned NOT  null auto_increment,    -> c1 int (Ten)  NOT NULL  default  ' 0 ',     -> c2 int (Ten)  unsigned default null,     -> c5 int (Ten)  unsigned NOT NULL DEFAULT  ' 0 ',     -> c3 timestamp not null default current_timestamp  on update current_timestamp,    -> c4 varchar ( NOT)  NULL DEFAULT  ",     -> primary key (ID),    &NBSP;-&GT;&NBSP;KEY&NBSP;IDX_C1 (C1), &NBSP;&NBSP;&NBSP;&NBSP;-&GT;&NBSP;KEY&NBSP;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,&NBSP;C5,C3,&NBSP;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: Perform a different backup script

Parameters are

--single-transaction-a--master-data=2

--single-transaction-a-R--master-data=2

PART4: Compare SQL Files

It can be found that the SQL statement that created the stored procedure is recorded in the file with the-R and no-R is logged.

-- 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 '  */ ;D elimiter ;; 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,&NBSP;C2,&NBSP;C5,C3,&NBSP;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 ;;D elimiter ;/*!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  */ ;

Verify

Part1: Validation

Empty the Linux environment, reload MySQL, import without the-R parameter without the backup file that created the stored procedure SQL

[[Email protected] ~]# mysql -uroot -p < master_db_201612051722.sql  Enter password: [[email protected] ~]# mysql -uroot -pEnter  password: welcome to the mysql monitor.  commands end with ;  or \g.your mysql connection id is 9server 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  Itsaffiliates. other names may be trademarks of their respectiveowners . 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 namesyou  can turn off this feature to get a quicker startup  with -adatabase changedmysql> select * from helei; empty set  (0.00&NBSP;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>

It is found that stored procedures created earlier can still be called even if the-R parameter is not added.

-- summary --

The stored procedure is stored in the Mysql.proc table, and the stored procedure is backed up by the use of a full-library backup strategy that includes the MySQL library. The-r parameter is typically used for single-Library or multi-Library backups. Because the level of the pen is limited, the writing time is also very hasty, the text will inevitably appear some errors or inaccurate places, the wrong place to ask readers to criticize correct.

This article is from the "He Lei Technology Blog" blog, make sure to keep this source http://suifu.blog.51cto.com/9167728/1879662

Another talk about MySQL full-Library backup