asked on

Mysql database dump gives almost empty file

Hi,

I'm trying to get a daily dump of a mysql database on a windows server using this code in a .bat file:

@echo off
echo Running dump...
set curDate=
for /f "skip=1" %%x in ('wmic os get localdatetime') do if not defined curDate set curDate=%%x
set "day=%curDate:~6,2%"
"D:\wamp\bin\mysql\mysql5.5.8\bin\mysqldump" --host="localhost" --user="root" --password="mysql" database> "D:\wamp\database-backups\backupDay.%day%.sql"
echo Done!

Open in new window

The output only contains:

-- MySQL dump 10.13  Distrib 5.5.8, for Win32 (x86)
--
-- Host: localhost    Database: database
-- ------------------------------------------------------
-- Server version	5.5.8-log

/*!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 */;

Open in new window

Why isn't the whole database dumped? The script works on my localhost.

Help welcome :)
Thanks!

Kimputer

When running this batch file (or maybe test only that mysql command in a commandbox), what does it return (success or error message?)

peps03

ASKER

i have no idea, the window is closed in a blink...

Kimputer

That's why you have to open a cmd box (Start > Run > cmd (enter)
Then type or copy & paste:
"D:\wamp\bin\mysql\mysql5.5.8\bin\mysqldump" --host="localhost" --user="root" --password="mysql" database> "D:\wamp\database-backups\backupDay.xxxxxx.sql"

peps03

ASKER

Ah, i get error:

error 1044 access denied for user USERNAME to database DATABASE when using lock tables

Kimputer

Again, do the same, but with:

"D:\wamp\bin\mysql\mysql5.5.8\bin\mysqldump" –-single-transaction --host="localhost" --user="root" --password="mysql" database> "D:\wamp\database-backups\backupDay.xxxxxx.sql"

peps03

ASKER

got error 1044 access denied for user USER@localhost to database DATABASE '?-single-transaction' when selecting database

peps03

ASKER

Sql output:

-- MySQL dump 10.13  Distrib 5.5.8, for Win32 (x86)
--
-- Host: localhost    Database: –-single-transaction
-- ------------------------------------------------------
-- Server version	5.5.8-log

/*!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 */;

Open in new window

ASKER CERTIFIED SOLUTION

Kimputer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

peps03

ASKER

Wow! That worked!!!

peps03

ASKER

Thanks a lot!
What does your command do exactly?

Kimputer

It's quite technical, but if you're up for it, read it here: https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_single-transaction