Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

mysql

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 ;-)

like image 904
govinda Avatar asked Dec 29 '11 02:12

govinda


People also ask

What permissions are needed for Mysqldump?

mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the --single-transaction option is not used. Certain options might require other privileges as noted in the option descriptions.

Why Mysqldump is not working?

If mysqldump is not identified by the cmd prompt that means it cannot recognize where the mysqldump.exe is located. You need to add path of the directory where the exe is located in the PATH variable under environment variables. After doing that your command will start working in the cmd prompt.

Does Mysqldump lock table?

By default, the mysqldump utility, which allows to back a MySQL database, will perform a lock on all tables until the backup is complete.

How can I get Mysqldump from all databases?

To dump entire databases, do not name any tables following db_name , or use the --databases or --all-databases option. To see a list of the options your version of mysqldump supports, issue the command mysqldump --help .


2 Answers

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.

like image 95
govinda Avatar answered Oct 16 '22 08:10

govinda


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

like image 34
krazybean Avatar answered Oct 16 '22 08:10

krazybean