如何使用PHP页面中的shell脚本执行MySQL命令？

I want to execute a shell script from php page which will execute a MySQL command.

我想从php页面执行一个shell脚本,它将执行一个MySQL命令。

To do this I followed the way shown here: https://*.com/a/8055745/2117868

为此,我按照此处显示的方式进行操作:https://*.com/a/8055745/2117868

Here is my sqlscript.sh

这是我的sqlscript.sh

#!/bin/sh
sudo wget -t 50 -O /tmp/update.sql http://example.com/update.sql
if [ $? -eq 0 ]; then
    mysql -h "localhost" -u "root" "-pXXXXXXXX" "database-name" < "/tmp/update.sql"
    if [ $? -eq 0 ]; then
        sudo rm /tmp/update.sql
        echo "200"
    else
        echo "502"
    fi
else
    echo "404"
fi

And my php page is runscript.php

我的php页面是runscript.php

<?php
    shell_exec("sudo /path/to/script/sqlscript.sh");
?>

Now, when I'm calling the sqlscript.sh from the server console or php page runscript.php it works perfectly and returns 200 as expected.

现在,当我从服务器控制台或php页面runscript.php调用sqlscript.sh时,它可以正常工作并按预期返回200。

But when I take MYSQL user and password in ~/.my.cnf so I don't have to put it on the command-line at all:

但是当我在〜/ .my.cnf中使用MYSQL用户和密码时,我根本不需要把它放在命令行上:

[client]
user = root
password = XXXXXXXX

And added credentials in sudoer to execute this script without password.

并在sudoer中添加凭据以执行此脚本而无需密码。

User_Alias WWW_USER = www-data
Cmnd_Alias WWW_COMMANDS_SQL = /path/to/script/sqlscript.sh
WWW_USER ALL = (ALL) NOPASSWD: WWW_COMMANDS_SQL

Here is my sqlscript.sh

这是我的sqlscript.sh

#!/bin/sh
sudo wget -t 50 -O /tmp/update.sql http://example.com/update.sql
if [ $? -eq 0 ]; then
    mysql -h "localhost" "database-name" < "/tmp/update.sql"
    if [ $? -eq 0 ]; then
        sudo rm /tmp/update.sql
        echo "200"
    else
        echo "502"
    fi
else
    echo "404"
fi

Now, when I'm calling the sqlscript.sh from the server console it works perfectly and returns 200 as expected. But if I call it from the php page

现在,当我从服务器控制台调用sqlscript.sh时,它可以正常工作并按预期返回200。但如果我从php页面调用它

http://example.com/runscript.php

it returns 502 as it couldn't execute the SQL command.

它返回502,因为它无法执行SQL命令。

Update:

Here is the Apache error.log

这是Apache error.log

--2014-12-05 10:51:55--  http://example.com/update.sql
Resolving example.com (example.com)... 162.144.71.XXX
Connecting to example.com (example.com)|162.144.71.XXX|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 146 [text/x-sql]
Saving to: `/tmp/update.sql'

     0K                                                       100% 10.9M=0s

2014-12-05 10:51:55 (10.9 MB/s) - `/tmp/update.sql' saved [146/146]

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

So, please help me to solve the issue.

那么,请帮我解决这个问题。

2 个解决方案

#1

Solved by adding the --defaults-extra-file option.
mysql --defaults-extra-file=~/.my.cnf ...

通过添加--defaults-extra-file选项解决。 mysql --defaults-extra-file =〜/ .my.cnf ...

Or with absolute path (might be required via apache, depends on server config):
mysql --defaults-extra-file=/absolute/path/to/.my.cnf ...

或者使用绝对路径(可能需要通过apache,取决于服务器配置):mysql --defaults-extra-file = / absolute / path / to / .my.cnf ...

#2

Have you tried this?

你试过这个吗?

In your shell script, instead of

在shell脚本中,而不是

mysql -h "localhost" "database-name" < "/tmp/update.sql"

use

mysql -h "localhost"-u root -ppassword "database-name" < "/tmp/update.sql"

Basically, try to pass MySQL username and password inside your shell script. Of course you can put them into variables/configs/whatever to obsecure it

基本上,尝试在shell脚本中传递MySQL用户名和密码。当然,您可以将它们放入变量/配置/任何以保护它

MySQL 28000 error showed that your script was able to attempt to login as a root but because there was no parameter password, it failed

MySQL 28000错误显示您的脚本能够尝试以root身份登录,但由于没有参数密码,因此失败

#1