LinuxQuestions.org
Latest LQ Deal: Linux Power User Bundle
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 06-08-2009, 04:13 PM   #1
Arty Ziff
Member
 
Registered: May 2008
Location: Tacoma, WA
Distribution: CentOS and RHEL
Posts: 102

Rep: Reputation: 15
Backing up MySQL db With Shell Script


Maybe this is a MySQL question, maybe not...

I've written a shell script to back up a database.

But when I run it, it prompts for password even though the script provides it. If I'm doing this manually, it's not a problem, but I want to make a cron job to do it...

Here's the script:
Quote:
#!/bin/bash

set -xv

#First let's rotate the backup files...
/bin/mv /home/cabazio/someDB-3.tar.gz /home/some/someDB-4.tar.gz
/bin/mv /home/some/someDB-2.tar.gz /home/some/someDB-3.tar.gz
/bin/mv /home/some/someDB-1.tar.gz /home/some/someDB-2.tar.gz
/bin/mv /home/some/someDB.tar.gz /home/some/someDB-1.tar.gz

#Now let's make the SQL dump. We could use the root pass, but let's be conservative...
mysqldump --add-drop-table -u SOME_user -p XXXXX someDB > someDB.sql

#A little shorthand to save typing. Not required.
LOGFILE=/home/some/someDB.sql

#And now we wrap it all up in a big tar ball...
/bin/tar -cvzf /home/some/someDB.tar.gz $LOGFILE

#Zero out the original dump, we don't need it.
echo "" > $LOGFILE
I've also tried with this line:
Quote:
mysqldump --add-drop-table someDB > someDB.sql
How do I accomplish this without the PWD prompt (I already provide it, why does it ask?) Obviously, if a cron job is executing this in the middle of the night, I'm not around to provide the PWB...

This is with MySQL v. 5.0.58 on CentOS v. 5

Last edited by Arty Ziff; 06-08-2009 at 04:15 PM.
 
Old 06-08-2009, 05:26 PM   #2
bathory
LQ Guru
 
Registered: Jun 2004
Location: Piraeus
Distribution: Slackware
Posts: 11,520

Rep: Reputation: 1502Reputation: 1502Reputation: 1502Reputation: 1502Reputation: 1502Reputation: 1502Reputation: 1502Reputation: 1502Reputation: 1502Reputation: 1502Reputation: 1502
You should put the password right after the "-p" without leaving a space in between.
Code:
mysqldump --add-drop-table -u SOME_user -pXXXXX someDB > someDB.sql
Regards
 
Old 06-08-2009, 07:10 PM   #3
Arty Ziff
Member
 
Registered: May 2008
Location: Tacoma, WA
Distribution: CentOS and RHEL
Posts: 102

Original Poster
Rep: Reputation: 15
I guess this really is a MySQL question, not a Linux question... Sorry!

I get this:
Quote:
mysqldump: Got error: 1044: Access denied for user 'SOME_user'@'localhost' to database 'XXXX' when using LOCK TABLES
Do I need to do this as root? It does work as root, but I'd rather do it as a user with only access to that specific DB...

Last edited by Arty Ziff; 06-08-2009 at 07:16 PM.
 
Old 06-08-2009, 11:23 PM   #4
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
As bathory said, no space between -p and the actual password
Code:
mysqldump --add-drop-table -u SOME_user -pXXXXX someDB > someDB.sql
mysqldump --add-drop-table -u SOME_user -p XXXXX someDB > someDB.sql
See the difference? The first one works, the second one not.

If something on the line does not start with a -, it's considered the name of a database.

Last edited by Wim Sturkenboom; 06-08-2009 at 11:25 PM.
 
Old 06-09-2009, 12:52 AM   #5
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.8, Centos 5.10
Posts: 17,240

Rep: Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324
Re 'LOCK TABLES'; the DB needs to do that to ensure a consistent backup. Just use the GRANT cmd to allow your 'backup' user to do that: http://dev.mysql.com/doc/refman/5.0/en/grant.html
 
Old 06-09-2009, 02:50 AM   #6
Arty Ziff
Member
 
Registered: May 2008
Location: Tacoma, WA
Distribution: CentOS and RHEL
Posts: 102

Original Poster
Rep: Reputation: 15
OK, the space issue and granting LOCK TABLES solves it.

Now, please, one more thing...

What beyond LOCK TABLES is the MINIMUM privileges that a user dedicated to backing up databases will need? I am using the --add-drop-table switch, but I'm not taking any actions on the tables / databases themselves other than dumping them into the .sql text file (and taring it up).

You all have been VERY helpful, and I appreciate it!
 
Old 06-09-2009, 03:48 AM   #7
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
Without knowing the databases, I can not say; e.g. for stored routines you need the select privilege on mysql.proc (according to the man page)

I suggest that you read the user manual and/or consult man mysqldump (search for privilege).
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
MySQL Updates With Null When Perl Script Run From Shell Script ThisGuyIKnow Programming 6 08-12-2008 10:56 AM
LXer: Backing up your MySQL database using your web browser and a PHP script LXer Syndicated Linux News 0 04-21-2007 11:16 PM
MySQL in a shell script?! zaubara Programming 2 05-31-2004 06:27 PM
MySQL connection in shell script - how to? philipz Programming 3 04-29-2004 08:04 PM
How get a mysql shell response in a script. philipina Programming 3 03-16-2004 12:51 PM


All times are GMT -5. The time now is 08:15 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration