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.
i assume you have installed mysql before, if not you can use yum install mysql* to installed it
[root@adityo ~]# rpm -qa | grep mysql
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 188.8.131.52 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
mysql> GRANT ALL ON foo.* TO test@'184.108.40.206' IDENTIFIED BY 'PASSWORD';
if you want add root access to all database you can use this command
//grant access to user-remote host combination
GRANT USAGE ON *.* to root@'
' IDENTIFIED BY 'newpassword';
Grant all priviledges to the user so you can see all database and udpate it
grant all privileges on *.* to
//force update of authorization changes
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 220.127.116.11 3306
Connecting To 18.104.22.168…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 22.214.171.124:
/sbin/iptables -A INPUT -i eth0 -s 126.96.36.199 -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
For example, if your MySQL server IP is 188.8.131.52 then entire block should be look like as follows:
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 = 184.108.40.206
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
# mysql -h 220.127.116.11 -u root -p
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)
If you only see information_schema database on your mysql source target ( in example i use 18.104.22.168 ) but there actualy 6 database on the 22.214.171.124it’s because you have not set the user permission , please go to your 126.96.36.199 source and edit the permission ( i use phpmyadmin to make it easy ) go to mysql database -> user tables -> select the user -> edit