database copy shell script including password with special characters

Question

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?

Answer 1

I would do the following:

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

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

Answer 2

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)

Answer 3

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.

[mysqldump]
password="the$password"

[mysql]
password="the$password"