Mysql – How to remove correctly ‘\r’ symbol during the bash script execution

bashMySQL

I have a bash script:

#!/bin/bash

set -x
PATH=/bin:/sbin:/usr/bin:/usr/sbin

server="$1"
curdate=$(date +'%Y%m%d')
sshoptions="-i /root/.ssh/backup -q -t"

mysqluser="root"
mysqlpassword=$(ssh $sshoptions root@$server "cat /root/.my.passwd")
mysqlaccess="-u$mysqluser -p$mysqlpassword"
mysqldatabases=$(ssh $sshoptions root@$server "mysql $mysqlaccess -B -N -e \"SHOW DATABASES;\" | grep -E -v 'information_schema|mysql'")
mysqldump="mysqldump $mysqlaccess --opt --skip-comments -B -R"

for db in $mysqldatabases; do
    echo "$(date +"%Y/%m/%d %H:%M:%S") mysqldump: dump $db"
    ssh $sshoptions root@$server "$mysqldump $db" | gzip -9 > $db_$curdate.sql.gz
done

As you can see it is for backups of MySQL databases (I'm planning to use it with rsnapshot).

Here is a problem when I run this script:

# bash /etc/rsnapshot.scripts/mysql.sh sugar.dev.host.com
+ PATH=/bin:/sbin:/usr/bin:/usr/sbin
+ server=sugar.dev.host.com
++ date +%Y%m%d
+ curdate=20130321
+ sshoptions='-i /root/.ssh/backup -q -t'
+ mysqluser=root
++ ssh -i /root/.ssh/backup -q -t root@sugar.dev.host.com 'cat /root/.my.passwd'
+ mysqlpassword=XXXXXX
+ mysqlaccess='-uroot -pXXXXXX'
++ ssh -i /root/.ssh/backup -q -t root@sugar.dev.host.com 'mysql -uroot -pXXXXXX -B -N -e "SHOW DATABASES;" | grep -E -v '\''information_schema|mysql'\'''
+ mysqldatabases=$'sugarcrm\r'
+ mysqldump='mysqldump -uroot -pXXXXXX --opt --skip-comments -B -R'
+ for db in '$mysqldatabases'
++ date '+%Y/%m/%d %H:%M:%S'
' echo '2013/03/21 17:41:04 mysqldump: dump sugarcrm
2013/03/21 17:41:04 mysqldump: dump sugarcrm
+ gzip -9
' ssh -i /root/.ssh/backup -q -t root@sugar.dev.host.com 'mysqldump -uroot -pXXXXXX --opt --skip-comments -B -R sugarcrm

So, I have '\r' symbol which got from SQL query parsed. And I see this error in the dump:

mysqldump: Got error: 1102: Incorrect database name 'sugarcrm^M' when selecting the database

How can I trim it correctly? With 'correctly' I mean that we can't do it right away 'mysqldatabases' variable has a value. Because here's a one DB, but if it will be two and more, stdout will be wrong. So my opinion is that we must trim the symbol at the 'for' cycle.

Please suggest a right way. Thank you.

Best Answer

Another ways:

sed

for db in '$mysqldatabases'; do
    db=$(echo $db|sed 's/\r$//')

bash native

for db in '$mysqldatabases'; do
    db=${db//\r/}