再談MySQL全庫備份

標籤：mysql全庫備份

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

再談MySQL全庫備份簡介

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:建立預存程序

[[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> 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的備份檔案

[[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 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參數一般用於單庫備份或者多庫備份。由於筆者的水平有限，編寫時間也很倉促，文中難免會出現一些錯誤或者不準確的地方，不妥之處懇請讀者批評指正。

本文出自 “賀磊的技術部落格” 部落格，請務必保留此出處http://suifu.blog.51cto.com/9167728/1879662

再談MySQL全庫備份