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.

Answer 1

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

Answer 2

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