MySQL backup -- mysqldump, mysql -- mysqldump

MySQL backup -- mysqldump, mysql -- mysqldump

Back up MySQL directly by using the mysqldump command to back up it to the SQL format. The simplest command is:

mysqldump databasename > bak.sql

The generated bak. SQL content format is as follows:

-- 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

The above command has simply backed up the database named database databasename to the bak. SQL file. If we want to restore data, we can simply use the command:

source bak.sql

In the windows external directory, the path of mysqldump.exe in windows is: C: \ Program Files \ MySQL Server 5.6 \ bin \ mysqldump.exe. in Linux, the path of mysqldump is simpler.

Some USER commands cannot be successfully executed. You must use the following command:

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

This is because the system needs to confirm that the backup user has the permission to read the database.

Mysqldump has many operation options, which can help to generate various functions, such as backing up SQL statements. For example, whether to restore data by appending or clearing data before adding data, etc:

For detailed usage, refer to the official mysqldump documentation.

Back up all databases:

 mysqldump --all-databases > dump.sql

Back up multiple databases:

mysqldump --databases db1 db2 db3 > dump.sql

You can use the crontab command in Linux to regularly back up the Python scripts of the latest N databases.

#! /Usr/bin/env python #-*-coding: UTF-8-*-###@ arthur # @ date 2015-05-7 # @ dsc remote backup s data 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) cl Ass BackupDatabase (): ''' backup database ''' 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' \ % (self. dbIp, self. user, self. pwd, self. dbName, self. bakFile) return cmd def runBackup (self): cmd = self. _ generateCmd () try: OS. sy Stem (cmd) cannot: logger. error (traceback. format_exc ()) # Database ip _ mysqlIp = ''_ bakPath = R'/data/backup/strategydata '_ dbName = 'strategydata' _ user ='' _ pwd =' '# Number of backups _ bakCount = 5 # backup file suffix _ 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) ba KFiles. 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) handle T: 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 () handle T: logger. error (traceback. format_exc () if _ name _ = '_ main _': main ()