How to configure MySQL server in linux and connect from remote server?

I recently required to connect to start the MySQL server and connect it from another client machine remotely. It wasn't too easy as I expected and I came across couple of issues during this, and I thought of blog it as it'll be useful to for someone else also. :)

These are steps I followed during this.

• Install the MySQL server.

                sudo apt-get install mysql-server

• Connect to MySQL server with 

                mysql --user=root --password=root

• Did ifconfig from my machine to find out my ip-address.
• And then used that ip address to connect to the MySQL server

               mysql --host=x.x.x.x --user=root --password=root

         Then I got the below error.
               ERROR 2003 (HY000): Can't connect to MySQL server on 'x.x.x.x' (111)

• The above error comes because of the bind-address of my sql server. In my.cnf file the bind-address of the my sql server has been mentioned and when the my sql server start up it'll bind to that address. By default the address in 127.0.0.1 which is the loop back address, and we can't connect via this address from another machine remotely.  The address you specify in bind tells mysql where to listen. 0.0.0.0 is a special address, which means "bind to every available network".

           Go to /etc/mysql/my.cnf  and change the bind address to 0.0.0.0.

• Then again when i tried to connect using the same command remotely I encountered the below error.

          "ERROR 1130 (HY000): Host 'hostname' is not allowed to connect to this MySQL server".

           

• After a bit of research, the fix in my case was to "GRANT" user root to connect to MySQL on any hosts. By default, user "root" was only allowed to connect to localhost and 127.0.0.1 hosts of MySQL. 

            #GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '<roots-password>' WITH GRANT OPTION;

   

After all the configuration above I could connect MySQL server remotely.:-)

Some useful tips:
- Start the my sql server ---> sudo service mysql start
- Stop the my sql server ---> sudo service mysql stop