sed 替换密码后，以前工作的代码出现 MYSQL 用户创建语法错误

Question

我正在尝试启用主从之间的复制以进行单向复制。我必须创建 MySQL 语句，并用 sed 替换命令中的密码，以便用 Jenkins 凭据屏蔽它。

我的 MySQL 实例是 Amazon RDS。问题是，在我在本地测试时实现 sed 之前，我只是在命令中硬编码了密码，并且没有任何问题，所以我认为我只是在我没有看到的地方弄乱了一些语法。

MySQL版本:MySQL 5.7.19

当我对密码进行硬编码时，这有效。我不确定我是什么

这是运行该命令的 Jenkins 作业的输出。输出:

16:55:48 ==================== Create Replication User and Grant Permissions on Old Database =========================
#This is the command that is being ran to try and fix the create user issue.
16:55:48 DROP USER 'user'@'%' IDENTIFIED BY 'password';
16:55:48 mysql: [Warning] Using a password on the command line interface can be insecure.
16:55:48 ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'password'' at line 1
16:55:48 mysql: [Warning] Using a password on the command line interface can be insecure.
16:55:48 ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'password'' at line 1
16:55:48 mysql: [Warning] Using a password on the command line interface can be insecure.
16:55:48 mysql: [Warning] Using a password on the command line interface can be insecure.

#The second command to be ran
16:55:48 CREATE USER 'user'@'%' IDENTIFIED BY 'password';
16:55:48 mysql: [Warning] Using a password on the command line interface can be insecure.
16:55:48 ERROR 1396 (HY000) at line 1: Operation CREATE USER failed for 'user'@'%'
16:55:48 mysql: [Warning] Using a password on the command line interface can be insecure.
16:55:49 ERROR 1396 (HY000) at line 1: Operation CREATE USER failed for 'user'@'%'

#Third command to be ran
16:55:49 GRANT REPLICATION CLIENT, REPLICATION SLAVE ON main.tf variables.tf x_dev.backend.tf x_usdev.amis.tf x_useast.region.tf TO 'user'@'%' IDENTIFIED BY 'password';
16:55:49 mysql: [Warning] Using a password on the command line interface can be insecure.

16:55:49 ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'variables.tf x_dev.backend.tf x_usdev.amis.tf x_useast.region.tf TO 'user'@' at line 1
16:55:49 mysql: [Warning] Using a password on the command line interface can be insecure.
16:55:49 
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'variables.tf x_dev.backend.tf x_usdev.amis.tf x_useast.region.tf TO 'user'@' at line 1

正在运行的代码。

function create_users_and_grant_replication_old {

 drop_user_statement="DROP USER 'user'@'%' IDENTIFIED BY 'MYSQL_PASSWORD';"
 drop_user_statement=$(echo $drop_user_statement | sed -e "s/MYSQL_PASSWORD/${MYSQL_PASSWORD}/g")

 echo "${drop_user_statement}"

 mysql -h ${global_database_endpoint_old} -u ${MYSQL_USERNAME} --port=3306 --password="${MYSQL_PASSWORD}" -Bse "${drop_user_statement}"
 mysql -h ${tenant_database_endpoint_old} -u ${MYSQL_USERNAME} --port=3306 --password="${MYSQL_PASSWORD}" -Bse "${drop_user_statement}"

 mysql -h ${global_database_endpoint_old} -u ${MYSQL_USERNAME} --port=3306 --password="${MYSQL_PASSWORD}" -Bse "flush privileges;"
 mysql -h ${tenant_database_endpoint_old} -u ${MYSQL_USERNAME} --port=3306 --password="${MYSQL_PASSWORD}" -Bse "flush privileges;"

  mysql_statement=""
  mysql_statement="CREATE USER 'user'@'%' IDENTIFIED BY 'MYSQL_PASSWORD';"
  mysql_statement=$(echo $mysql_statement | sed -e "s/MYSQL_PASSWORD/${MYSQL_PASSWORD}/g")
  echo "${mysql_statement}"

  mysql -h ${global_database_endpoint_old} -u ${MYSQL_USERNAME} --port=3306 --password="${MYSQL_PASSWORD}" -Bse "${mysql_statement}"
  mysql -h ${tenant_database_endpoint_old} -u ${MYSQL_USERNAME} --port=3306 --password="${MYSQL_PASSWORD}" -Bse "${mysql_statement}"

  mysql_statement=""
  mysql_statement="GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'user'@'%' IDENTIFIED BY 'MYSQL_PASSWORD';"
  mysql_statement=$(echo $mysql_statement | sed -e "s/MYSQL_PASSWORD/${MYSQL_PASSWORD}/g")
  echo "${mysql_statement}"

  mysql -h ${global_database_endpoint_old} -u ${MYSQL_USERNAME} --port=3306 --password="${MYSQL_PASSWORD}" -Bse "${mysql_statement}"
  mysql -h ${tenant_database_endpoint_old} -u ${MYSQL_USERNAME} --port=3306 --password="${MYSQL_PASSWORD}" -Bse "${mysql_statement}"

}

我已经修复了删除用户和刷新权限部分，其中用户创建工作正常，但授权复制仍然说它有语法错误。

17:27:12 GRANT REPLICATION CLIENT, REPLICATION SLAVE ON main.tf variables.tf x_dev.backend.tf x_usdev.amis.tf x_useast.region.tf TO 'user'@'%' IDENTIFIED BY 'password';

这就是问题所在，。无论我如何 sed 替换它都会拾取该目录的文件。

main.tf 变量.tf x_dev.backend.tf x_usdev.amis.tf

    mysql_statement=""
 mysql_statement=$(echo 'GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO "repl_user"@"%" IDENTIFIED BY "MYSQL_PASSWORD";' | sed -e "s/MYSQL_PASSWORD/${MYSQL_PASSWORD}/g")
 echo "${mysql_statement}"

 #echo "${mysql_statement}"
 #mysql_statement=$(echo $mysql_statement | sed -e "s/\"/\'/g")
 #echo "${mysql_statement}"

 mysql -h ${global_database_endpoint_old} -u ${MYSQL_USERNAME} --port=3306 --password="${MYSQL_PASSWORD}" -Bse "${mysql_statement}"
 mysql -h ${tenant_database_endpoint_old} -u ${MYSQL_USERNAME} --port=3306 --password="${MYSQL_PASSWORD}" -Bse "${mysql_statement}"

Answer 1

我忘记了 sed 的 echo 周围的引号，需要清除用户并刷新权限。这是正确的工作答案代码。

function create_users_and_grant_replication_old {
 drop_user_statement="DROP USER 'repl_user';"
 drop_user_statement=$(echo "$drop_user_statement" | sed -e "s/MYSQL_PASSWORD/${MYSQL_PASSWORD}/g")

 mysql -h ${global_database_endpoint_old} -u ${MYSQL_USERNAME} --port=3306 --password="${MYSQL_PASSWORD}" -Bse "${drop_user_statement}"
 mysql -h ${tenant_database_endpoint_old} -u ${MYSQL_USERNAME} --port=3306 --password="${MYSQL_PASSWORD}" -Bse "${drop_user_statement}"

 mysql -h ${global_database_endpoint_old} -u ${MYSQL_USERNAME} --port=3306 --password="${MYSQL_PASSWORD}" -Bse "flush privileges;"
 mysql -h ${tenant_database_endpoint_old} -u ${MYSQL_USERNAME} --port=3306 --password="${MYSQL_PASSWORD}" -Bse "flush privileges;"

 mysql_statement=""
 mysql_statement="CREATE USER 'repl_user'@'%' IDENTIFIED BY 'MYSQL_PASSWORD';"
 mysql_statement=$(echo "$mysql_statement" | sed -e "s/MYSQL_PASSWORD/${MYSQL_PASSWORD}/g")

 mysql -h ${global_database_endpoint_old} -u ${MYSQL_USERNAME} --port=3306 --password="${MYSQL_PASSWORD}" -Bse "${mysql_statement}"
 mysql -h ${tenant_database_endpoint_old} -u ${MYSQL_USERNAME} --port=3306 --password="${MYSQL_PASSWORD}" -Bse "${mysql_statement}"

 mysql_statement=""
 mysql_statement='GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO "repl_user"@"%" IDENTIFIED BY "MYSQL_PASSWORD";'
 mysql_statement=$(echo "$mysql_statement" | sed -e "s/MYSQL_PASSWORD/${MYSQL_PASSWORD}/g")
 mysql_statement=$(echo "$mysql_statement" | sed -e "s/\"/\'/g")

 mysql -h ${global_database_endpoint_old} -u ${MYSQL_USERNAME} --port=3306 --password="${MYSQL_PASSWORD}" -Bse "${mysql_statement}"
 mysql -h ${tenant_database_endpoint_old} -u ${MYSQL_USERNAME} --port=3306 --password="${MYSQL_PASSWORD}" -Bse "${mysql_statement}"
}