MySQL 備份--mysqldump，mysql--mysqldump

MySQL 備份--mysqldump，mysql--mysqldump

對MySQL的備份，直接通過mysqldump命令備份成sql格式就行了，最簡單的一個命令就是:

mysqldump databasename > bak.sql

產生的bak.sql 內容格式如下：

-- MySQL dump 10.13  Distrib 5.6.17, for Win64 (x86_64)---- Host: localhost    Database: test-- -------------------------------------------------------- Server version5.6.17/*!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 `tb`--DROP TABLE IF EXISTS `tb`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `tb` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `value` varchar(64) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `tb`--LOCK TABLES `tb` WRITE;/*!40000 ALTER TABLE `tb` DISABLE KEYS */;INSERT INTO `tb` VALUES (3,'value');/*!40000 ALTER TABLE `tb` ENABLE KEYS */;UNLOCK TABLES;---- Table structure for table `tb2`--DROP TABLE IF EXISTS `tb2`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `tb2` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(64) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `tb2`--LOCK TABLES `tb2` WRITE;/*!40000 ALTER TABLE `tb2` DISABLE KEYS */;/*!40000 ALTER TABLE `tb2` 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-05-07 16:54:19

上邊的命令就已經簡單地把名字為資料庫 databasename的Database Backup到 bak.sql檔案中，如果我們要恢複資料可以簡單的使用命令：

source bak.sql

windows 系統下需要把mysqldump.exe路徑添加到環境變數PATH中，或者在命令列視窗中進入到mysqldump.exe 目錄下，windows下mysqldump.exe的路徑大概為：C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqldump.exe；Linux下的就更簡單了，直接whereis mysqldump就可以知道mysqldump路徑

有些使用者上邊的命令就無法執行成功，必須使用一下命令：

mysqldump --user=root --password=root test > bak.sql

這是因為系統需要確定備份的使用者具有讀資料庫的許可權

mysqldump 有很多操作的選項，可以協助產生各種功能是備份sql，如恢複資料是是追加，還是清空再增加，等等：

詳細使用方法可以參考mysqldump官方文檔

備份所有資料庫：

 mysqldump --all-databases > dump.sql

備份多個資料庫：

mysqldump --databases db1 db2 db3 > dump.sql

下邊是自己簡單實現的可以在Linux 下通過crontab命令定時備份最新N個資料庫的Python指令碼

#!/usr/bin/env python# -*- coding: utf-8 -*-##@arthur  #@date    2015-05-7#@dsc     遠程備份s資料import osimport sysimport loggingimport tracebackimport timelogger = logging.getLogger()logging.basicConfig(                    format = '%(asctime)s %(levelname)s %(module)s.%(funcName)s Line:%(lineno)d\t%(message)s',                    filename = os.path.join(os.path.dirname(__file__), r'backStrategyData.log'),                    filemode = 'a+',                    level = logging.NOTSET                    )class BackupDatabase():    '''    備份資料庫    '''    def __init__(self, dbIp, dbName, bakFile, user, pwd):        self.dbIp = dbIp        self.dbName = dbName        self.bakFile = bakFile        self.user =user        self.pwd = pwd            def __generateCmd(self):        cmd = 'mysqldump --host %s --user=%s --password=%s %s > %s' \            % (self.dbIp, self.user, self.pwd, self.dbName, self.bakFile)        return cmd                def runBackup(self):        cmd = self.__generateCmd()        try:            os.system(cmd)        except:            logger.error(traceback.format_exc())#資料庫ip__mysqlIp = ''__bakPath = r'/data/backup/strategydata'__dbName = 'strategydata'__user = ''__pwd = ''# 備份個數__bakCount = 5# 備份檔案尾碼__bakSuffix = 'strategydata.sql'def backStrategyData():    files = os.listdir(__bakPath)    bakFiles = []    for f in files:        if os.path.isfile(os.path.join(__bakPath, f)) and f.endswith(__bakSuffix):            bakFiles.append(f)    bakFiles.sort(reverse=True)    delFiles = bakFiles[__bakCount - 1:]    try:        for f in delFiles:            df = os.path.join(__bakPath, f)            logger.info('delete file %s.' % df)            os.remove(df)    except:        logger.error(traceback.format_exc())    newBakFile = os.path.join(__bakPath, time.strftime("%Y-%m-%d-%H-%M-%S") + __bakSuffix)    logger.info('backup %s:%s to %s!' % (__mysqlIp, __dbName, newBakFile))    oBack = BackupDatabase(__mysqlIp, __dbName, newBakFile, __user, __pwd)    oBack.runBackup()        def main():    try:        backStrategyData()    except:        logger.error(traceback.format_exc())        if __name__ == '__main__':    main()