CODEWITHSUNDEEP
mysql
govinda
govinda

Reputation: 1693

MySQL: Why does basic MySQLdump on db table fail with "Permission denied"

This should be quick and simple, but after researching on Google quite a bit I am still stumped. I am mostly newbie with: server admin, CLI, MySQL.

I am developing my PHP site locally, and now need to move some new MySQL tables from my local dev setup to the remote testing site. First step for me is just to dump the tables, one at a time.

I successfully login to my local MySQL like so: 

Govind% /usr/local/mysql/bin/mysql -uroot

but while in this dir (and NOT logged into MySQL): 

/usr/local/mysql/bin

...when I try this 

mysqldump -uroot -p myDBname myTableName > myTestDumpedTable.sql

..then I keep getting this: 

"myTestDumpedTable.sql: Permission denied."

Same result if I do any variation on that (try to dump the whole db, drop the '-p', etc.)

I am embarrassed as I am sure this is going to be incredibly simple, or just reveal a gaping (basic) hole in my knowledge. .. but please help ;-)

Upvotes: 16

Views: 47797

Answers (8)

Jay
Jay

Reputation: 604

mysqldump don't work with sudo, if you are using

sudo mysqldump then try below solution:

sudo su
mysqldump -u[username] -p[password] db_name > newbackupfile.bkp

Upvotes: 1

Jeevan
Jeevan

Reputation: 31

Even I was facing the same problem, the issue is with user access to 'root/bin' dir.

switch your user access as root

    sudo -s

Then execute the command

mysqldump -uroot -p homestayadvisorDB > homestayadvisor_backup.sql

This will resolve the issue. Let me know if this doesn't work.

Upvotes: 3

Pathros
Pathros

Reputation: 10750

In my case I'd created the directory with $ sudo mkdir /directory/to/store/sql/files. The owner of that directory is root. So changing the owner by using $ sudo chown me:me /directory/to/store/sql/files and also changing permissions to maybe $ sudo chmod 744 /directory/to/store/sql/files did the trick for me.

Upvotes: 1

Agusti Febrer
Agusti Febrer

Reputation: 11

I think you're missing the ./ from the command, try: being inside 

/usr/local/mysql/bin$ ./mysqldump -u root -p myDBname > "/Users/yourUserName/Documents/myTestDumpedTable.sql"

So it is a script, and in linux you execute a script with ./myscript. I found it just today, and for me, in my mac OSX, I didn't use the -p, maybe because password not needed, don't know already. I mean, try also: 

./mysqldump -u root myDBname > "/Users/yourUserName/Documents/myTestDumpedTable.sql"

Upvotes: 0

Lisong
Lisong

Reputation: 1

You should provide with a full path for SQL backup file, such as 

mysqldump -u root -p databasexxx > /Users/yourusername/Sites/yoursqlfile.sql

Upvotes: 0

govinda
govinda

Reputation: 1693

The answer came from a helpful person on the MySQL list:
As you guys (Anson and krazybean) were thinking - I did not have permission to be writing to the /usr/local/mysql/bin/ dir. But starting from any other directory, calls to mysqldump were failing because my shell PATH var (if I said that right) is not yet set up to handle mysqldump from another dir. Also, for some reason I do not really understand yet, I also needed to use a full path on the output, even if I was calling mysqldump effectively, and even if I had permission to write to the output dir (e.g. ~/myTestDumpedTable.sql. So here was my ticket, for now (quick answer):

Govind% /usr/local/mysql/bin/mysqldump -uroot -p myDBname myTableName > /Users/Govind/myTestDumpedTable.sql

You can write to wherever your shell user has permission to do so. I just chose my user's home dir.

Hope this helps someone someday.
Cheers.

Upvotes: 27

Anson
Anson

Reputation: 2674

Take a look at the man page for mysqldump for correct argument usage. You need a space between the -u flag and the username, like so:

mysqldump -u root -p myDBname myTableName > myTestDumpedTable.sql

Alternatively you can do

mysqldump --user=root -p myDBname myTableName > myTestDumpedTable.sql

Since you're not providing a password in the list of arguments, you should be prompted for one. You can always provide the password in the list of arguments, but the downside to that is it appears in cleartext and will show up in the shell's command history.

Upvotes: 1

krazybean
krazybean

Reputation: 31

Generally I stick with defining the hostname anyways, but as you being root doesn't seem like it would be the problem, I would question where are you writing this to? What happens when you dump to > ~/myTestDumpedTable.sql

Upvotes: 3

Related Questions