How to make mysql accept connections externally


Problem :

I have a VPS and I want to make mysql DB accept connection externally (from my PC for instance). I have Debian Linux installed on the server. I checked some tutorials online and they said to comment out:

bind-address          = 127.0.0.1

But this didn't seem to help! is there anything specific for VPSs? or Am I missing something else? The command that runs mysql is:

/usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock


Solution :

The MySQL server must be configured to accept connections externally (binding to the correct network interface as appropriate), and its firewall must be configured to allow incoming connections on that port (TCP port 3306). This may or may not already be set up when you installed MySQL (see iptables if you're on *nix).

You must also account for this in the MySQL permissions as follows.

Often, when setting up your MySQL permissions, you'll set user access rights only for @'localhost'. You'll need to make sure that both the user account and its granted permissions are set for the appropriate hostname or IP address you will be connecting from. For example, you could create a new authorised user with:

GRANT ALL PRIVILEGES ON somedatabase.* TO [email protected]'somehostname' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

You have to do all of this before you can connect to that server remotely, using something like this (this example uses PHP):

mysql_connect('mysqlservername', 'someuser', 'password');

Mysql Tutorials

Mysql Howto..

how to search for multiple full and partial values in MySQL?

How to differentiate between 0 and null in an INT column in MySQL

How to use PHP variables in MySQL queries with WHILE fetched rows?

mysql how to update a field only if new value is lower then the existing value

MySQL - If Table A is LEFT JOINed to Table B, how do I ORDER BY a field in Table B?

SQL - How to create a new relation between tables A & C based on an existing relationship between A & B - en mass?

php color not showing properly

How Secure is this MySQL statement in a PHP script?

How to do Inheritance Modeling in Relational Databases?

How can I group rows with the same value from mySQL into php?

How to escape a dot in mysql query?

How to update MySQL database from Outlook Express?

How to convert rows data into column data in mysql by avoiding Null Values

How can I tell if the current result is the last result in PHP

Python scraper how to add mysql insert

How do I set Full Text on MySQL Workbench?

How to specify an auto-incrementing (int) identity column using Fluent-NHibernate and MySQL

MySQL python DBAPI. How to get a dictionary instead of a tuple?

How to pass current table row and JavaScript prompt values to PHP/MySQL?

How to use INNER/OUTER JOIN in MYSQL

How to calculate desired radius or distance with specific latitude and longitude in MYSQL with proper SQL standard?

how to associate a randomly generated key with mysql primary key using php

How to edit a “PHP-MySQL-returned” HTML table and update it on the database

How to count total by current year of current month in mysql

MySql merge rows and show item count in result table?

How to change password for MySQL Console in WAMP 2.5

How to compare MySQL database schemas [closed]

How to update value in same table using mysql?

how to output in 5-minute interval?(mysql )

How to store decimal in MySQL?