mysql dump doesn't work anymore

[Robert Blackwell]

A while ago I had some help from here to make a batch file that would dump
my db. Everything was working just fine until 2 days ago and I just found
out.

I checked my backup folder and found that starting on 4/25/2006 the dump
files are empty and only shows a few lines of comments or something instead
of creating a normal dump as it had been doing just fine for the last few
weeks.

Quote:
-- MySQL dump 10.9
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 4.1.14-nt

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

I don't know what all of a sudden caused the mysql dumps to stop working. I
have not made any changes to the mysql server other then adding a new db or
2 and user. I am getting this problem when I try dumping any of the dbs I
have. All I know is that just by looking at the file date created, the last
backup it was able to create succesfully was on 4/24/2006 1:58pm

Please help.

[Bill Karwin]

Robert Blackwell wrote:

I don't know what all of a sudden caused the mysql dumps to stop working. I
have not made any changes to the mysql server other then adding a new db or
2 and user.

What user do you use when running the mysqldump command? Does that user
have both SELECT and LOCK TABLES privilege on the databases? In other
words, if you do this:

mysqldump --user=mybackupuser --password=foo databasename > dump.sql

Then mybackupuser must have the privileges on the database.

When you try the mysqldump command manually at a shell prompt, do you
see any error message? Or does it appear to succeed, but generates an
empty output file?

Regards,
Bill K.

[Robert Blackwell]

Hi Bill, I'm running this as a scheduled task or by double clicking it.
There appears to be no error when it runs. I don't know how to use shell.

Here's the script that you helped me with last time. It was working just
perfectly until the 25th and I honestly have no clue what has changed.

REM @echo off

for /f "tokens=1" %%i in ('date /t') do set DATE_DOW=%%i

for /f "tokens=2" %%i in ('date /t') do set DATE_DAY=%%i
for /f %%i in ('echo %date_day:/=-%') do set DATE_DAY=%%i
for /f %%i in ('time /t') do set DATE_TIME=%%i
for /f %%i in ('echo %date_time::=-%') do set DATE_TIME=%%i
"C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqldump" -u
anyuser -p"password" anydatabase >"C:\Documents and
Settings\Administrator\My
Documents\mysqldump\"%DATE_DAY%_%DATE_TIME%_anydat abase.sql

I remember last time it was a problem with lock tables and after checking
and granting lock tables it worked. But right now I'm even trying with Root!
and it is still not working.

I'm double checking as I type and what the heck! My users dont' have lock
permissions anymore. Something somehow must have removed those permissions
on april 24th evening or 25th morning...

I use this program called mysql yog and in its permissions manager there
isn't a lock option just select and everything else. So I loaded this
program called mysql front to grant lock tables and it can also show the
code for permissions so like on this one user it has

GRANT LOCK TABLES ON *.* TO 'wowcentral'@'%' IDENTIFIED BY PASSWORD
'69c4a4d359c7dd6b';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX,
ALTER, LOCK TABLES ON `amazon shop`.* TO 'wowcentral'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX,
ALTER, LOCK TABLES ON `smftest`.* TO 'wowcentral'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX,
ALTER, LOCK TABLES ON `test`.* TO 'wowcentral'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX,
ALTER, LOCK TABLES ON `wowc`.* TO 'wowcentral'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX,
ALTER, LOCK TABLES ON `wowcguild`.* TO 'wowcentral'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX,
ALTER, LOCK TABLES ON `wowcmambo`.* TO 'wowcentral'@'%';

It really doesn't look like a permissions issue since that code clearly
shows lock tables, and plus, root doesn't even work either.

So frustrating.

[Bill Karwin]

Robert Blackwell wrote:

GRANT LOCK TABLES ON *.* TO 'wowcentral'@'%' IDENTIFIED BY PASSWORD
'69c4a4d359c7dd6b';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX,
ALTER, LOCK TABLES ON `amazon shop`.* TO 'wowcentral'@'%';

These two lines describe the privileges of different logins. You get
one set of privileges when you supply the password, you get a different
set of privileges when you supply no password.

In the case above, the account name 'wowcentral' seems to have a bunch
of privileges when it logs in with no password (that is from the second
line). When the password is given, the account by the same name has
only LOCK TABLES and no other passwords.

MySQL is very flexible when it comes to privileges. So flexible, that
it is often confusing. MySQL permits the same account name to have
different privileges, depending on which password is given, or if no
password is given. That's why the "IDENTIFIED BY" clause is optional.
Leave it out, and you're describing the privileges in the case when the
username is specified, but no password is.

Likewise, the "-p" option of MySQL command-line tools is optional.
Leave that option out, and you're logging in using the specified account
name, but no password.

Regards,
Bill K.

[Robert Blackwell]

It turns out some tables got corrupted and that's why it wasn't successful.
I was able to wipe the tables as they only had session data in them so all
is well.