shell 报错'@'localhost' (using password: YES)'root

shell中写了简单的一段

energy.sh内容：

#!/bin/bash
host=127.0.0.1
user=******
passwd=******
dbname=******
tablename=device_value_300sct007p01_year
MONTH=$(date +%Y-%m)
sqlstr="insert into device_value_300sct007p01_year (id,year,month,device_id,device_value_id,value,type) select replace(uuid(), '-', ''),substring(month,1,4),month,device_id,device_value_id,sum(value),type from device_value_300sct007p01_month where month = ${MONTH} group by month,device_id,device_value_id"
mysql -h $host -u$user -p$passwd -D $dbname -e 'select * from device_value_300sct007p01_year'

最简单的一个查询，就是报错'@'localhost' (using password: YES)'root ，用户名密码都正确
最后发现原因：多了\r，脚本是在windows下写的，由于windows和linux换行符不一致导致

sh -x energy.sh

+ host=$'127.0.0.1\r'
+ user=$'******\r'
+ passwd=$'******\r'
+ dbname=$'******\r'
+ tablename=$'device_value_300sct007p01_year\r'
++ date +%Y-%m
+ MONTH=$'2015-03\r'
+ sqlstr='insert into device_value_300sct007p01_year (id,year,month,device_id,device_value_id,value,type) select replace(uuid(), '\''-'\'', '\'''\''),substring(month,1,4),month,device_id,devi'group by month,device_id,device_value_idalue_300sct007p01_month where month = 2015-03
++ -h $'127.0.0.1\r' $'-uroot\r' $'-pyunho201311\r' -D $'xcloud\r' -e 'select * from device_value_300sct007p01_year'
energy.sh: line 9: -h: command not found
+ mysql $'\r'
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

每个变量后都多了\r，导致用户名密码错误
解决方法：

删除文件中的\r

tr -d "\r" <energy.sh >energy1.sh

再次运行

sh energy1.sh

+----------------------------------+------+---------+--------------------+-----------------+-------+------+
| id                               | year | month   | device_id          | device_value_id | value | type |
+----------------------------------+------+---------+--------------------+-----------------+-------+------+
| a9be43fcd5cfc6c3e7e07aac6fd20621 | 2015 | 2015-02 | 110000001000000031 |               1 |  2.00 | sum  |
| a9be43fcd5cfc6c3e7e07aac6fd20622 | 2015 | 2015-03 | 110000001000000031 |               1 |  3.00 | sum  |
| a9be43fcd5cfc6c3e7e07aac6fd20623 | 2015 | 2015-04 | 110000001000000031 |               1 |  4.00 | sum  |
| a9be43fcd5cfc6c3e7e07aac6fd20627 | 2015 | 2015-01 | 110000001000000031 |               1 |  1.00 | sum  |
+----------------------------------+------+---------+--------------------+-----------------+-------+------+

shell 报错'@'localhost' (using password: YES)'root

推荐阅读