I am trying to create a shell script for a mysqldump (and mysql) operation where the password is stored directly in the script. The password contains special characters which need to be handled accordingly. However, I cannot figure out a combination that actually works.

For example, the following works when executed directly on the console:

mysqldump -u the_user --hex-blob --add-drop-table source_db --password=the\$password | mysql -u the_user target_db --password=the\$password

The password contains a $ which is then escaped with \$. However, the exact same command does not work when put into a shell_script.sh. When I execute the script, MySQL complains

Access denied for user 'the_user'@'localhost'

So far I have tried the following within the script:

mysqldump -u the_user --hex-blob --add-drop-table source_db --password=the\$password | mysql -u the_user target_db --password=the\$password

mysqldump -u the_user --hex-blob --add-drop-table source_db --password="the\$password" | mysql -u the_user target_db --password="the\$password"

mysqldump -u the_user --hex-blob --add-drop-table source_db --password='the\$password' | mysql -u the_user target_db --password='the\$password'

mysqldump -u the_user --hex-blob --add-drop-table source_db --password="the$password" | mysql -u the_user target_db --password="the$password"

mysqldump -u the_user --hex-blob --add-drop-table source_db --password='the$password' | mysql -u the_user target_db --password='the$password'

None of these seem to escape the password properly. Out of these, only the first one works properly when used directly on the shell.

What am I missing? How do I have to specify the password, so these commands also work from within a shell script?

  • Is the actual problematic character a dollar sign? – choroba Nov 12 '18 at 14:20
  • The actual password also contains other special characters, like %, =, ?, # for example. However, out of these I only need to escape the \$ - at least directly on the shell. The % is at the beginning of the password, if that makes any difference (which it might?). – fritzmg Nov 12 '18 at 14:25
  • % isn't special at all. ? might cause problems, though, as it might try to match. Whitespace could also be a problem. Backslashing every problematic character should work, though. – choroba Nov 12 '18 at 15:20
  • Still no luck. I have backslashed every character outside of [a-zA-Z0-9], and still the command does not work when executed via a script. It still only works when executed directly on the command line. – fritzmg Nov 12 '18 at 15:24
  • Are you sure there's no ssh or other additional level of interpretation involved in the script? – choroba Nov 12 '18 at 15:40

I would do the following:

printf -v password "%q" "\$password"
mysql --password=$password

More details on printf: printf on wiki.bash-hackers.org

  • That did not (quite) work either. Also with your version of the mysql part of the command (without the = between --password and $password), I simply end up in the mysql interface. – fritzmg Nov 12 '18 at 15:09
  • Actually the lack of = was a typo. Thanks for pointing out. – Konrad Botor Nov 12 '18 at 15:13
  • Yeah, I suspected as much. However, it still does not work :( – fritzmg Nov 12 '18 at 15:21

Try to put the password (without New Line and Without Escape) into a textfile e.g. pass.txt and then use the following

mysqldump -u the_user --hex-blob --add-drop-table source_db --password=$(cat pass.txt) | mysql -u the_user target_db --password=$(cat pass.txt)
  • That did not work. I am assuming by "without NR" you mean without escaping the characters? – fritzmg Nov 12 '18 at 14:32
  • without escaping and without the new lines. – justyy Nov 12 '18 at 14:34
  • Yeah, that's what I did. However echo pass.txt doesn't actually echo the contents of pass.txt... it just echos pass.txt ;) – fritzmg Nov 12 '18 at 14:36
  • Have you tried cat pass.txt? – Konrad Botor Nov 12 '18 at 15:14
  • yes, should be cat pass.txt. My mistakes – justyy Nov 14 '18 at 10:38

Add the password to the SQL ini file in a separate [mysqldump] section. If you need it for the MYSQL call too add it to the [mysql] section of the ini file also.



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.