Pengingat mysql command 1

How to connect to remote mysql

# vi /etc/my.cnf

enable bind-address

bind-address = 65.55.55.2
….
..
….Where,

* bind-address : IP address to bind to.
* skip-networking : Don’t listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. Since you need to allow remote connection this line should removed from file or put it in comment state.

Restart your mysql service to take change in effect:# /etc/init.d/mysql restart

Grant access to remote IP address

# mysql -u root -p mysql Grant access to new database
If you want to add new database called foo for user bar and remote IP 202.54.10.20 then you need to type following commands at mysql> prompt:

mysql> CREATE DATABASE foo;
mysql> GRANT ALL ON foo.* TO bar@’202.54.10.20′ IDENTIFIED BY ‘PASSWORD’;

How Do I Grant access to existing database?

Let us assume that you are always making connection from remote IP called 202.54.10.20 for database called webdb for user webadmin, To grant access to this IP address type the following command At mysql> prompt for existing database:mysql> update db set Host=’202.54.10.20′ where Db=’webdb’;
mysql> update user set Host=’202.54.10.20′ where user=’webadmin’;

Logout of MySQL

Test it
From remote system type command:

$ mysql -u webadmin –h 65.55.55.2 –p

Where,

* -u webadmin: webadmin is MySQL username
* -h IP or hostname: 65.55.55.2 is MySQL server IP address or hostname (FQDN)
* -p : Prompt for password

You can also use telnet to connect to port 3306 for testing purpose:$ telnet 65.55.55.2 3306