laravel connect to online database

Question

I have offline application (installed in localhost) but database in online server.. because i want to make something like synchronizer...

I've tried to set my database.php like this

       'mysqlserver' => array(
            'host'      => '103.38.103.142',
            'port'      => '212',
            'driver'    => 'mysql',
            'database'  => 'lgspsb',
            'username'  => 'xxxxxxx',
            'password'  => 'xxxxxxx',
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),

but error like this...

SQLSTATE[HY000] [2013] Lost connection to MySQL server at 'reading initial communication packet', system error: 0

How I can't fix that? thanks before... and sorry for my bad english

are You sure that port is: 212 ? I guess normal mysql port: 3306 — num8er, Jun 21 '16 at 22:24
check You remote database to listen public network interface, and have user that grants You get in. — num8er, Jun 21 '16 at 22:26
connection to Your database by ip and port told me that 212 is not mysql port. it's ssh port. check this screenshot: http://joxi.ru/Vm6kkLCxn16Q2Z also laravel will not connect to mysql db through ssh tunnel, so keep in mind make mysql to listen to external network interfaces — num8er, Jun 21 '16 at 22:27
try connecting to that database with those credentials with a tool like SequelPro or MysqlWorkbench to make sure they are correct. Also, make sure the database allows external connections, they are often disabled for security reasons. This may help as well: http://stackoverflow.com/questions/5755819/lost-connection-to-mysql-server-at-reading-initial-communication-packet-syste — Pevara, Jun 21 '16 at 22:28
yes this is ssh port, `ssh root@103.38.103.142 -p 212` check your mysql port — DJafari, Jun 21 '16 at 22:30
i am so sorry... yes 212 is ssh port... mysql port is 3306.. — Megandi, Jun 21 '16 at 23:04
the problem is just.. how to make MySQL port 3306 publicly accessible.. and it was solved... btw thanks everyone!! — Megandi, Jun 21 '16 at 23:06

num8er · Accepted Answer · 2016-06-21T23:33:03.473

I'll put answer here for people that came here when looking for solution by Your question.

After doing:

telnet 103.38.103.142 212

I saw that the app that listens on 212th port is not mysql rdbms.

here is screenshot: http://joxi.ru/Vm6kkLCxn16Q2Z

I also saw that it's ubuntu.

So quick I'll make process of fixing Your issue step by step.

1) switch to root:

sudo su

2) edit /etc/mysql/my.cnf file and make sure it has binding under mysqld section (screenshot: http://joxi.ru/zANQQJhlqXn329):

bind-address = 0.0.0.0

3) restart Your mysql server:

service mysql restart

4) get in mysql console in terminal as mysql root user (screenshot: http://joxi.ru/a2X77bSyOM3Rmg):

mysql -u root -p

5) create remote user for Your database and flush privileges:

GRANT ALL ON lgspsb.* TO 'lgspsb_remote'@'%' IDENTIFIED BY 'somehardpassword';
FLUSH PRIVILEGES;

6) change database connection params:

   'mysqlserver' => array(
        'host'      => '103.38.103.142',
        'port'      => '3306',
        'driver'    => 'mysql',
        'database'  => 'lgspsb',
        'username'  => 'lgspsb_remote',
        'password'  => 'somehardpassword',
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix'    => '',
    ),

That's all (:

p.s. use .env file to avoid changing database config every-time. screenshot: http://joxi.ru/ZrJEEJh17ROwAj

p.s. of course it's for dev purposes. In production it's better to listen on concrete network interface that has more secure environment (for example to have pptp vpn connection and bind on pptp0 interface's ip)

For digitalocean lovers:
in digitalocean I have droplets.
I do have separate database servers that listen on internal network interface between droplets
and app servers that connect to them using same internal network.

It should be noted that allowing direct access to your MySQL database from any remote IP address as you have done is not a decision one should make lightly. This approach is a step in the wrong direction if you believe in the old security mantra of "defense in depth". — Mike Brant, Jun 21 '16 at 23:19
@MikeBrant of course it's for dev purposes. In multi server app structure, for example digitalocean droplets I do have separate database servers that listen on internal network interface between droplets, and app servers that connect to them using same internal network. where to bind is choice of devops or owner of project. it's just an example. — num8er, Jun 21 '16 at 23:30
@MikeBrant added some p.s. to bottom of my answer. Is that enough to be good answer? ;) — num8er, Jun 21 '16 at 23:37
Is this kind of connection secured? I mean, something like `https`. If not so, then how could I secure this connection between a Laravel application and a external online database; — Pathros, Sep 13 '17 at 17:52
@Pathros for that case You need to create VPN tunnel between APP server and DB server. I use IPSEC TOOLS (Racoon) to create site-to-site VPN tunnel. Someones use OpenVPN. Idea is to create secured data tunnel between remote hosts and do plain connections over it - most simple, secured way. — num8er, Sep 13 '17 at 18:09

laravel connect to online database

1 Answers1