MySQL数据库连接被拒绝 [英] Mysql Database connection denied

问题描述

我正在尝试让我的Symfony 3应用在Amazon EC2实例上以生产模式运行.

I am trying to get my Symfony 3 app running in production mode on an Amazon EC2 instance.

我把代码放在正确的位置，然后尝试通过运行composer设置所有依赖项

I am have the code in the correct place, and then I try to setup any dependencies with composer by running

export SYMFONY_ENV=prod
composer install --no-dev --optimize-autoloader

但收到错误

> Sensio\Bundle\DistributionBundle\Composer\ScriptHandler::installAssets

[Doctrine\DBAL\Exception\ConnectionException]
An exception occured in driverL SQLSTATE[HY000] [1044] Access denied for user 'my-user'@'127.0.0.1' to database 'my_database'

然后在脚本终止之前将此错误传递给[Doctrine\DBAL\Driver\PDOException]和[PDOException]. 然后出现消息

This error is then passed down to [Doctrine\DBAL\Driver\PDOException] and [PDOException] before the script terminates. This is then followed by the message

[RuntimeException]
An error occured when executing the "'assets:install --symlink --rela'\''web'\'''" command:

，然后出现与开头相同的错误.

which is then followed by the same errors as at the beginning.

我的parameters.yml文件是

My parameters.yml file is

parameters:
    database_host: 127.0.0.1
    database_port: 3306
    database_name: my_database
    database_user: my-user
    database_password: mypassword
    server_version: 5.6
    mailer_transport: smtp
    mailer_host: 127.0.0.1
    mailer_user: null
    mailer_password: null
    secret: Mysecret
    registration_permitted: true
    registration_limit: 3

，并且用户my-user对数据库my_database拥有除GRANT之外的所有权限.

and the user my-user has all permissions on the database my_database except GRANT.

我已经尝试解决了几个小时而无济于事，遇到的其他解决方案涉及拼写错误的用户名/密码/数据库(经过三重检查)，或者需要添加服务器版本(没有任何区别)

I have been trying to solve this for a few hours to no avail, other solutions I came across involved misspelt usernames/passwords/databases (triple-checked) or needed the server version adding (which hasn't made a difference).

其他信息

• 运行mysql -u my-user -p会提示输入密码，然后允许我登录.
• 运行mysql -u my-user -p -D my_database提示输入密码，然后返回ERROR 1044 (42000): Access denied for user 'my-user'@'localhost' to database 'my_database'.
• 以root用户身份登录mysql并运行SHOW GRANTS FOR 'my-user';会返回ERROR 1141 (42000): There is no such grant defined for user 'my-user' on host '%'.
• 以root用户身份登录mysql并运行SHOW GRANTS FOR 'my-user'@'localhost';会返回GRANT USAGE ON *.* TO 'my-user'@'localhost' IDENTIFIED BY PASSWORD hashedPassword.

• Running mysql -u my-user -p prompts for the password and then allows me to log in.
• Running mysql -u my-user -p -D my_database prompts for the password and then returns ERROR 1044 (42000): Access denied for user 'my-user'@'localhost' to database 'my_database'.
• Logging into mysql as root and running SHOW GRANTS FOR 'my-user'; returns ERROR 1141 (42000): There is no such grant defined for user 'my-user' on host '%'.
• Logging into mysql as root and running SHOW GRANTS FOR 'my-user'@'localhost'; returns GRANT USAGE ON *.* TO 'my-user'@'localhost' IDENTIFIED BY PASSWORD hashedPassword.

其他信息2 删除my-user('my-user'@'localhost'和'my-user'@'127.0.0.1')，然后使用GRANT ALL PRIVILEGES ON my_database.* TO 'my-user'@'localhost' IDENTIFIED BY 'password';以及GRANT ALL PRIVILEGES ON my_database.* TO 'my-user'@'127.0.0.1' IDENTIFIED BY 'password';然后FLUSH PRIVILEGES;重新创建它们之后，SHOW GRANTS FOR 'my-user'@'localhost'的输出为

Additional Info 2 After deleting my-user ('my-user'@'localhost' and 'my-user'@'127.0.0.1') and then recreating them using GRANT ALL PRIVILEGES ON my_database.* TO 'my-user'@'localhost' IDENTIFIED BY 'password'; and also GRANT ALL PRIVILEGES ON my_database.* TO 'my-user'@'127.0.0.1' IDENTIFIED BY 'password'; and then FLUSH PRIVILEGES; The output of SHOW GRANTS FOR 'my-user'@'localhost' is then

GRANT USAGE ON *.* TO 'my-user'@'localhost' IDENTIFIED BY PASSWORD 'passwordHash'
GRANT ALL PRIVILEGES ON 'my_database' TO 'my-user'@'localhost'

和'my-user'@'127.0.0.1'的类似输出.

但这仍然给我和以前一样的错误，还有更多想法吗?

But this is still giving me the same errors as before, any more ideas?

推荐答案

如注释中所述，您对数据库没有任何特权. 因此，运行此命令可以解决您的问题:

As discussed in comments you did not have any privilege on the database. So running this fixes your problem:

GRANT ALL PRIVILEGES ON my_database.* TO 'my-user'@'localhost'

关于您的疑问，为什么要在参数中将其更改为localhost，因为这就是您要告诉mysql的内容 想象一下，您想将数据库访问权限仅限制为可以运行的网络中的特定IP

About your doubt why you had to change it to localhost in your params because that's what you're telling mysql Imagine you want to restrict database access to only a specific ip in your network you could run

GRANT ALL PRIVILEGES ON my_database.* TO 'my-user'@'192.168.1.10'

因此将192.168.1.10替换为127.0.0.1，即可在参数中使用127.0.0.1

So replace 192.168.1.10 with 127.0.0.1 and you can use 127.0.0.1 in your parameters

这篇关于MySQL数据库连接被拒绝的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！