0

I want to create a script to dump all my databases into separate files. I found a similar script online but on my machine it does not execute properly. The script is:

#! /bin/bash
HOST="host" 
USER="user"
PASSWORD="pass" 

databases=`mysql -h$HOST –-user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | grep -Ev "Database"` 
for db in $databases; do
    mysqldump -h$HOST –-user=$USER --password=$PASSWORD --routines \
      –-databases $db | gzip > "$db.gz" 
done

When i run the script I get: ERROR 1045 (28000): Access denied for user 'some_user'@'host' (using password: YES). What am I doing wrong ?

I am connecting to mysql with another user that the one I am logged in the system. Is this a problem? The mysql user used has all the privileges.

If I run the command that selects databases manually everything works fine. Ideas?

1 Answer 1

1

MySQL stores its users in an internal database called mysql; they are completely independent from the system users.

The table mysql.user contains boolean columns like Select_priv, Insert_priv, etc which tell the rights the users have. These can be altered by the grant command.

In short, it is like this (here, ALL PRIVILIGES renders the user to be like root to the given database and table):

GRANT ALL PRIVILEGES ON database.table TO 'user'@'host' WITH GRANT OPTION;
FLUSH PRIVILEGES;

A mysqldump without restrictions needs all privileges. (you restrict to only stored procedures, I think this also needs all privileges.)

When running as shell script, consider quoting and escaping your password (use a password which does not need escaping) on the executing line:

mysql ... --password="$PASSWORD" ...
mysqldump ... --password="$PASSWORD" ...
1
  • Thanks a lot for your answer. The problem was the grep in the show databases line. Jun 8, 2015 at 9:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .