My Online Log

What is MySQL ?

MySQL is a relational database management system (RDBMS) based on SQL (Structured Query Language). First released in January, 1998, MySQL is now one component of parent company MySQL AB’s product line of database servers and development tools.

MySQL version

i assume you have installed mysql before, if not you can use yum install mysql* to installed it

[root@adityo ~]# rpm -qa | grep mysql
mysql-5.0.45-7.el5
mysql-server-5.0.45-7.el5

Btw i am using Heidisql version 3.2 (http://heidisql.googlecode.com/files/HeidiSQL_3.2_Setup.exe) as my mysql client program

By default, MySQL database server remote access disabled for security reasons. However, some time you need to provide the remote access to database server from home or from web server. You will see this error when you access it.

what you need to do ?

Grant access to remote IP address

# mysql -u root -p mysql

If you want to add new database called test for username testing and remote IP 202.54.11.24 then you need to type following commands

//Create a new database name test

mysql> CREATE DATABASE test;

//Grant access to test user that comes from 202.54.11.24
mysql> GRANT ALL ON foo.* TO test@'202.54.11.24' IDENTIFIED BY 'PASSWORD';

if you want add root access to all database you can use this command

//grant access to user-remote host combination
mysql>GRANT USAGE ON *.* to root@'202.54.11.24' IDENTIFIED BY 'newpassword';

Grant all priviledges to the user so you can see all database and udpate it

mysql>grant all privileges on *.* to root@'202.54.11.24' ;
//force update of authorization changes
mysql>FLUSH PRIVILEGES;

Now you can access it again

If you still cannot access the mysql remotely you need to checked the firewall

you can test the connection by typing this on  your command prompt

telnet 202.54.11.24 3306

Connecting To 202.54.11.24…Could not open connection to the host, on port 33
06: Connect failed

it means  you need to open port 3306 using iptables

A sample iptables rule to open Linux iptables firewall

/sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT

OR only allow remote connection from your web server located at 202.54.11.24:

/sbin/iptables -A INPUT -i eth0 -s 202.54.11.24 -p tcp --destination-port 3306 -j ACCEPT

OR only allow remote connection from your lan subnet 192.168.1.0/24:

/sbin/iptables -A INPUT -i eth0 -s 192.168.1.0/24 -p tcp --destination-por

If you still cannot connected , please checked the my.cnf on /etc/my.cnf

Make sure line skip-networking is commented (or remove line) and add following line

bind-address=YOUR-SERVER-IP

For example, if your MySQL server IP is 65.55.55.2 then entire block should be look like as follows:
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/English
bind-address = 65.55.55.2
# skip-networking
....

 Then do not forget to restart the mysql service
#service mysqld restart

How to test the connection from your mysql server ?

You can use the mysql command
 mysql -h hostname address -u username -p then enter password

example:

# mysql -h 203.48.184.101 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 5714
Server version: 5.0.51a-3ubuntu5.4 (Ubuntu)

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.

mysql> show databases;
+——————–+
| Database           |
+——————–+
| information_schema |
+——————–+
1 rows in set (0.01 sec)

mysql>

If you only see information_schema database on your mysql source target ( in example i use 203.48.184.101 ) but there actualy 6 database on the 203.48.184.101it’s because you have not set the user permission , please go to your 203.48.184.101 source and edit the permission ( i use phpmyadmin to make it easy ) go to mysql database -> user tables -> select the user -> edit