mysqldump with automatically generated export name

Question

When I do database dumps through MySQL WorkBench it picks a filename for me which is the current date (YYYMMDD).

Now, I want to do this in a batch file since I want to make weekly dumps automatically by simply running a simple script (our DB is running on a windows machine)

Do I have to make the filename myself or is there a way to have mysqldump do it for me? I can use date /t and parse it but it would be nice to know if there was some built-in functionality.

• Vérace: You might want to check out cygwin - it'll mean that you can run bash scripts on Windows - reducing duplication of effort (and also reducing potential for error).

Answer 1

I have an incredibly cheesy but effective way to create a backup file with a datetime as a name.

SAMPLE DATA

I have a table called test.rangedata as a sample

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.14 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test
Database changed
mysql> show create table rangedata\G
*************************** 1. row ***************************
 Table: rangedata
Create Table: CREATE TABLE `rangedata` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `open` float DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=34 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select * from rangedata;
+----+---------+
| id | open |
+----+---------+
| 1 | 1.30077 |
| 2 | 1.30088 |
| 3 | 1.30115 |
| 4 | 1.30132 |
| 5 | 1.30135 |
| 6 | 1.30144 |
| 7 | 1.30132 |
+----+---------+
7 rows in set (0.00 sec)
mysql>

Since mysqldump cannot create names, how about having the mysql client get the name? In fact, I will use the function DATE_FORMAT to create a batch file called C:\MyDumpScript.bat that will execute the mysqldump. The mysqldump output will be formatted MySQLBackup_YYYMMDD_HHMMSS.

Ready ?

C:\>echo @echo off > C:\MyDumpScript.bat
C:\>mysql -Dtest -ANe"select date_format(NOW(),'mysqldump test rangedata > MySQLBackup_%Y%m%d_%H%I%S.sql')" >> C:\MyDu
mpScript.bat
C:\>type C:\MyDumpScript.bat
@echo off
mysqldump test rangedata > MySQLBackup_20140314_150353.sql
C:\>

Does it run ? Watch ...

C:\>C:\MyDumpScript.bat
C:\>type MySQLBackup_20140314_150353.sql
-- MySQL dump 10.13 Distrib 5.6.14, for Win64 (x86_64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.6.14
/*!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 `rangedata`
--
DROP TABLE IF EXISTS `rangedata`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `rangedata` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `open` float DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=34 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `rangedata`
--
LOCK TABLES `rangedata` WRITE;
/*!40000 ALTER TABLE `rangedata` DISABLE KEYS */;
INSERT INTO `rangedata` VALUES (1,1.30077),(2,1.30088),(3,1.30115),(4,1.30132),(5,1.30135),(6,1.30144),(7,1.30132);
/*!40000 ALTER TABLE `rangedata` 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 2014年03月14日 15:05:57
C:\>

I told you it was cheesy (This is Windows, after all) !!!

Answer 2

Here a script i have running my database backup :
-it create daily zipped bkps in their onw daily data dirs.
-it may look strange that no passw in the conf ! is because i have configured my user root to run mysqldump without asking for password(this is for security reasons- you don't what your OS admin to sniff the process and see the password :))

To configure password less access just create a .my.cnf file in your root home dir containing :

[mysqldump]
user = root
password = password 

Script

 #!/bin/bash
 #Script backup mysql individual
 USER="root"
 DIR="`date | awk {'print 2ドル"_"3ドル"_"6ドル'}`"
 PWDDIR="/mysql/backup_mysql/_mysqldump/"
 FINALDIR="$PWDDIR$DIR"
 GZIP_ENABLED=1
 GZIP="/bin/gzip"
 MYSQLDUMP="/usr/bin/mysqldump"
 MYSQL="/usr/bin/mysql"
 mkdir $FINALDIR
 databases=`$MYSQL --user=$USER -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema)"`
 for db in $databases; do
 echo $db
 if [ $GZIP_ENABLED == 1 ]; then
 mysqldump --force --opt --user=$USER --databases $db | gzip > "$FINALDIR/$db.gz"
 else
 mysqldump --force --opt --user=$USER --databases $db > "$FINALDIR/$db.sql"
 fi
 done

• the script will create data dir for every day:

drwxr-xr-x 2 root root 4096 Mar 13 02:00 Mar_11_2014

---

Windows version for a nice mysqldump follow this link

• MxLDevs: I would prefer a batch file since this is a windows machine, but I'm guessing we have to make the filenames ourselves.
• Up_One: ooooooohhh Windowzzzzz ! kkk
• Up_One: check the link i have posted !!