2

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.

  • 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). – Vérace Sep 4 '15 at 14:27
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) !!!

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

  • I would prefer a batch file since this is a windows machine, but I'm guessing we have to make the filenames ourselves. – That Umbrella Guy Mar 14 '14 at 18:58
  • ooooooohhh Windowzzzzz ! kkk – Up_One Mar 14 '14 at 19:03
  • check the link i have posted !! – Up_One Mar 14 '14 at 19:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.