连接到ubuntu服务器中的mysql数据库

Question

I am using below code to connect MySQL database in PHP.

try {
    shell_exec("ssh -f -L 3307:127.0.0.1:3306 ronak@server_ip sleep 60 >> logfile");
    $this->_conn = $this->dbh = new PDO('mysql:host=127.0.0.1;dbname=my_db', DB_USER, DB_PASS);
    $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Couldn't connect to database. Please try again!");
}

I want to direct connect with remote MySQL host. Server is on azure virtual machine. How can I do such a configurations?

I already opened 3306 port in azure portal for MySQL. I don't know how to use it in here without ssh tunnel.

Thanks.

Answer 1

1) change my.conf (whatever your mysql conf file is called). And set bind-address to 0.0.0.0 as it is prob 127.0.0.1

2) stop/restart mysql daemon

Connections now are not limited to those from localhost (what you are when you ssh). The default is localhost for obvious security reason until dev guy tweaks it

3) add database user perhaps coming in from diff ip addr

CREATE USER 'fred7'@'192.168.2.7' IDENTIFIED BY 'my_password';

4) grants

GRANT ALL PRIVILEGES ON test7db.* TO 'fred7'@'192.168.2.7';

5) firewall

For 3 and 4, let's say the mysql server is on aws ec2 and i am sitting at a public library using sqlyog or workbench. That prog will alert me if connect failure stating something like connection failed for user 'fred7'@'gfs6.nyc.comcastbusiness.net'. So it is pretty obvious how to do 3 and 4 then.

Good luck!