How to Setup a MySql server to accept remote connections

on ubuntu machines?

or

common mysql error while doing remote Connection

error 2003 (hy000) can't connect to mysql server on

 (111)

On server side:

step #1. Open your my.cnf (/etc/mysql/my.cnf usually)  in a editor.

 step #2. bind-address:  IP Address to bind. set this to  whatever IP your machine uses to connect to the outside world.

bind-address= 127.0.0.1

set this to

bind-address=YOUR-SERVER-IP

step #3. skip-networking: Don't Listen to TCP/IP connections at all. only communicate with local host not external world.

skip-networking

comment this line by adding a "#" in front of it.

step #4. save and close the file.

Don't forget to restart your mysqld after these change.

/etc/init.d/mysqld restart

step #5. Grant access to remote IP address

Connect to mysql Server:

# mysql -u root -p mysql

Grant access to new database:
let satya is a remote user and his ip is 10.100.39.172.

mysql>CREATE DATABASE mydb;
mysql>GRANT ALL PRIVILEGES on *.* To 'satya'@'10.100.39.172'
IDENTIFIED BY 'password' WITH GRANT OPTION;

To check it out, whether a user is add or not:

select user form mysql.user;

To see full details of permissions granted to a user:

select * from information_schema.user_privileges;

How to Grant access to existing database:

mysql>update db set Host='10.100.39.172' where Db='mydb';
mysql>update user set Host='10.100.39.172' where user='satya';

step #6.Logout of MySQL

mysql>exit

On Client Side:

# mysql -u satya -h 10.100.39.172 -p 
# password:prompt for password

u:user,  h: host, p: prompt for password

That's it your remote mysql database connection is established, successfully.