Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

up vote 1 down vote accepted

Another ways:

sed

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

bash native

for db in '$mysqldatabases'; do
    db=${db//\r/}
share|improve this answer

The \r (aka ^M, as you found out) is a carriage return (ASCII control character CR). Some operating systems use it as part of the line end, e.g. Windows ends lines with \r\n (CR, NL, as ASCII intended), Mac does \n\r, and in their infinite wisdom Unix creators s(h)aved a byte by ending lines \n. So you are handling text in Windows/DOS convention on Unix/Linux. There is a tool called dos2unix that fixes lineendings (here in Fedora the package is called dos2unix). Be careful, just stripping \r can corrupt (binary) files.

share|improve this answer

I would try using raw parameter to mysql first --raw or -r.

--raw, -r

           For tabular output, the "boxing" around columns enables one column
           value to be distinguished from another. For nontabular output (such
           as is produced in batch mode or when the --batch or --silent option
           is given), special characters are escaped in the output so they can
           be identified easily. Newline, tab, NUL, and backslash are written
           as \n, \t, \0, and \\. The --raw option disables this character
           escaping.

           The following example demonstrates tabular versus nontabular output
           and the use of raw mode to disable escaping:

               % mysql
               mysql> SELECT CHAR(92);
               +----------+
               | CHAR(92) |
               +----------+
               | \        |
               +----------+
               % mysql -s
               mysql> SELECT CHAR(92);
               CHAR(92)
               \\
               % mysql -s -r
               mysql> SELECT CHAR(92);
               CHAR(92)
               \

If this wouldn't work I would remove them with tr e.g.

tr -d '\r'
share|improve this answer
    
Thanks. --raw option didn't help. sed (and tr too) solved this problem. –  Evgeniy Yablokov Mar 21 '13 at 15:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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