mysql dump备份脚本

#!/bin/bashsource /etc/profileTODAY_DIR=`date +%Y%m%d`;Olddir=`date +%Y%m%d --date='7 days ago'`;cd /home/database_backup/if [ -f $Olddir ]; thenrm -rf $Olddirfiif [ ! -d $TODAY_DIR ]; thenmkdir -p $TODAY_DIR/jl_wtmkdir -p $TODAY_DIR/gs_wtmkdir -p $TODAY_DIR/netcentrefimysqldump -uroot -pbus365_0502 -h10.10.1.25 --protocol=tcp --port=3306 --single-transaction --master-data=2 jl_wt > $TODAY_DIR/jl_wt/jl_wt_${TODAY_DIR}.sql##备份存储过程mysqldump -uroot -pbus365_0502 -h10.10.1.25 --protocol=tcp --port=3306 -n -d -t -R jl_wt > $TODAY_DIR/jl_wt/jl_wt_proc${TODAY_DIR}.sqlmysqldump -uroot -pbus365_0502 -h10.10.1.25 --protocol=tcp --port=3306 --single-transaction --master-data=2 jl_u1wt > $TODAY_DIR/jl_wt/jl_u1wt_${TODAY_DIR}.sqlmysqldump -uroot -pbus365_0502 -h10.10.1.25 --protocol=tcp --port=3306 -n -d -t -R jl_u1wt > $TODAY_DIR/jl_wt/jl_u1wt_proc${TODAY_DIR}.sqlmysqldump -uroot -pbus365_0502 -h10.10.1.25 --protocol=tcp --port=3306 --single-transaction --master-data=2 jl_u2wt > $TODAY_DIR/jl_wt/jl_u2wt_${TODAY_DIR}.sqlmysqldump -uroot -pbus365_0502 -h10.10.1.25 --protocol=tcp --port=3306 -n -d -t -R jl_u2wt > $TODAY_DIR/jl_wt/jl_u2wt_proc${TODAY_DIR}.sqlmysqldump -uroot -pbus365_0502 --single-transaction --master-data=2 netcentre > $TODAY_DIR/netcentre/netcentre_${TODAY_DIR}.sqlmysqldump -uroot -pbus365_0502 -h10.10.1.25 --protocol=tcp --port=33063306 -n -d -t -R netcentre > $TODAY_DIR/netcentre/netcentre_proc${TODAY_DIR}.sqlmysqldump -uroot -pbus365_0502 --protocol=tcp --port=3307 --single-transaction --master-data=2 gs_wt > $TODAY_DIR/gs_wt/gs_wt_${TODAY_DIR}.sqlmysqldump -uroot -pbus365_0502 -h10.10.1.25 --protocol=tcp --port=3307 -n -d -t -R gs_wt > $TODAY_DIR/gs_wt/gs_wt_proc${TODAY_DIR}.sqlmysqldump -uroot -pbus365_0502 --protocol=tcp --port=3307 --single-transaction --master-data=2 jl_u1wt > $TODAY_DIR/gs_wt/gs_u1wt_${TODAY_DIR}.sqlmysqldump -uroot -pbus365_0502 --protocol=tcp --port=3307 --protocol=tcp --port=3307 -n -d -t -R gs_u1wt > $TODAY_DIR/gs_wt/gs_u1wt_proc${TODAY_DIR}.sqlmysqldump -uroot -pbus365_0502 --protocol=tcp --port=3307 --single-transaction --master-data=2 gs_u2wt > $TODAY_DIR/gs_wt/gs_u2wt_${TODAY_DIR}.sqlmysqldump -uroot -pbus365_0502 --protocol=tcp --port=3307 -n -d -t -R gs_u2wt > $TODAY_DIR/gs_wt/gs_u2wt_proc${TODAY_DIR}.sqlmysqldump -uroot -pbus365_0502 --protocol=tcp --port=3307 --single-transaction --master-data=2 netcentre > $TODAY_DIR/netcentre/gs_netcentre_${TODAY_DIR}.sqlmysqldump -uroot -pbus365_0502 --protocol=tcp --port=3307 -n -d -t -R netcentre > $TODAY_DIR/netcentre/gs_netcentre_proc${TODAY_DIR}.sql  -R, --routines      Dump stored routines (functions and procedures).  mysqldump -utest -p123456 -R  plmsdb   备份表数据和存储过程  默认不备份存储过程:  [mysql@master ~]$ mysqldump -utest -p123456 plmsdb  >a1.sqlWarning: Using a password on the command line interface can be insecure.[mysql@master ~]$ cat a1.sql-- MySQL dump 10.13  Distrib 5.6.16, for Linux (x86_64)---- Host: localhost    Database: plmsdb-- -------------------------------------------------------- Server version5.6.16-log/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;---- Table structure for table `area`--DROP TABLE IF EXISTS `area`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `area` (  `id` int(11) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `area`--LOCK TABLES `area` WRITE;/*!40000 ALTER TABLE `area` DISABLE KEYS */;/*!40000 ALTER TABLE `area` ENABLE KEYS */;UNLOCK TABLES;---- Table structure for table `person`--DROP TABLE IF EXISTS `person`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `person` (  `number` int(11) DEFAULT NULL,  `name` varchar(255) DEFAULT NULL,  `birthday` date DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `person`--LOCK TABLES `person` WRITE;/*!40000 ALTER TABLE `person` DISABLE KEYS */;INSERT INTO `person` VALUES (1,'aaa','2014-01-01'),(2,'b','2015-05-01');/*!40000 ALTER TABLE `person` ENABLE KEYS */;UNLOCK TABLES;---- Table structure for table `test`--DROP TABLE IF EXISTS `test`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `test` (  `id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `test`--LOCK TABLES `test` WRITE;/*!40000 ALTER TABLE `test` DISABLE KEYS */;INSERT INTO `test` VALUES (1),(100);/*!40000 ALTER TABLE `test` ENABLE KEYS */;UNLOCK TABLES;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2015-03-26 10:35:08[mysql@master ~]$  mysqldump -utest -p123456 -R <span style="font-family: Arial, Helvetica, sans-serif; font-size: 12px;">--master-data=2</span><span style="font-size: 12px; font-family: Arial, Helvetica, sans-serif;">  plmsdb >a2.sql</span>Warning: Using a password on the command line interface can be insecure.[mysql@master ~]$ cat a2.sql-- MySQL dump 10.13  Distrib 5.6.16, for Linux (x86_64)---- Host: localhost    Database: plmsdb-- -------------------------------------------------------- Server version5.6.16-log/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;---- Table structure for table `area`--DROP TABLE IF EXISTS `area`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `area` (  `id` int(11) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `area`--LOCK TABLES `area` WRITE;/*!40000 ALTER TABLE `area` DISABLE KEYS */;/*!40000 ALTER TABLE `area` ENABLE KEYS */;UNLOCK TABLES;---- Table structure for table `person`--DROP TABLE IF EXISTS `person`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `person` (  `number` int(11) DEFAULT NULL,  `name` varchar(255) DEFAULT NULL,  `birthday` date DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `person`--LOCK TABLES `person` WRITE;/*!40000 ALTER TABLE `person` DISABLE KEYS */;INSERT INTO `person` VALUES (1,'aaa','2014-01-01'),(2,'b','2015-05-01');/*!40000 ALTER TABLE `person` ENABLE KEYS */;UNLOCK TABLES;---- Table structure for table `test`--DROP TABLE IF EXISTS `test`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `test` (  `id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `test`--LOCK TABLES `test` WRITE;/*!40000 ALTER TABLE `test` DISABLE KEYS */;INSERT INTO `test` VALUES (1),(100);/*!40000 ALTER TABLE `test` ENABLE KEYS */;UNLOCK TABLES;---- Dumping routines for database 'plmsdb'--/*!50003 DROP PROCEDURE IF EXISTS `proc1` */;/*!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  = latin1 */ ;/*!50003 SET character_set_results = latin1 */ ;/*!50003 SET collation_connection  = latin1_swedish_ci */ ;/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;DELIMITER ;;CREATE DEFINER=`root`@`localhost` PROCEDURE `proc1`(OUT s int)BEGINSELECT COUNT(*) INTO s FROM test;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 */ ;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2015-03-26 10:36:06此时有存储过程的信息