MySQL version<\/p>\n
i assume you have installed mysql before, if not you can use yum install mysql* to installed it<\/p>\n
[root@adityo ~]# rpm -qa | grep mysql
\nmysql-5.0.45-7.el5
\nmysql-server-5.0.45-7.el5<\/p>\n
Btw i am using Heidisql version 3.2 (http:\/\/heidisql.googlecode.com\/files\/HeidiSQL_3.2_Setup.exe) as my mysql client program<\/p>\n
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.<\/p>\n
![\"\"](\"http:\/\/blog.binus-edu.com\/wp-content\/uploads\/heidi(1).jpg\")
<\/p>\n
what you need to do ?<\/em><\/p>\n Grant access to remote IP address<\/p>\n 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<\/p>\n \/\/Create a new database name test<\/p>\n Now you can access it again<\/p>\n If you still cannot access the mysql remotely you need to checked the firewall<\/strong><\/p>\n you can test the connection by typing this on\u00a0 your command prompt<\/p>\n telnet 202.54.11.24 3306<\/p>\n Connecting To 202.54.11.24…Could not open connection to the host, on port 33 it means\u00a0 you need to open port 3306 using iptables<\/p>\n OR only allow remote connection from your web server located at 202.54.11.24:<\/p>\n OR only allow remote connection from your lan subnet 192.168.1.0\/24:<\/p>\n Make sure line skip-networking is commented (or remove line) and add following line<\/p>\n bind-address=YOUR-SERVER-IP<\/p>\n For example, if your MySQL server IP is 65.55.55.2 then entire block should be look like as follows: example:<\/p>\n # mysql -h 203.48.184.101 -u root -p Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.<\/p>\n mysql> show databases; mysql><\/p>\n If you only see information_schema database on your mysql source target (\u00a0in 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<\/p>\n 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[491],"tags":[911],"class_list":["post-73","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-mysql-db-connection-setting"],"_links":{"self":[{"href":"https:\/\/adityo.blog.binusian.org\/index.php?rest_route=\/wp\/v2\/posts\/73","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/adityo.blog.binusian.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/adityo.blog.binusian.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/adityo.blog.binusian.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/adityo.blog.binusian.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=73"}],"version-history":[{"count":3,"href":"https:\/\/adityo.blog.binusian.org\/index.php?rest_route=\/wp\/v2\/posts\/73\/revisions"}],"predecessor-version":[{"id":190,"href":"https:\/\/adityo.blog.binusian.org\/index.php?rest_route=\/wp\/v2\/posts\/73\/revisions\/190"}],"wp:attachment":[{"href":"https:\/\/adityo.blog.binusian.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=73"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/adityo.blog.binusian.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=73"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/adityo.blog.binusian.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=73"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}# mysql -u root -p mysql<\/code><\/p>\nmysql> CREATE DATABASE test;<\/code><\/p>\n\/\/Grant access to test user that comes from <\/code>202.54.11.24<\/code>
\nmysql> GRANT ALL ON foo.* TO test@'202.54.11.24' IDENTIFIED BY 'PASSWORD';<\/code><\/p>\nif you want <\/code>add root access to all database you can use this command<\/strong><\/p>\n\/\/grant access to user-remote host combination
\n<\/code>mysql><\/code>GRANT USAGE ON *.* to root@'<\/code>202.54.11.24<\/code>' IDENTIFIED BY 'newpassword';<\/code><\/p>\nGrant all priviledges to the user so you can see all database and udpate it<\/code><\/p>\nmysql><\/code>grant all privileges on *.* to <\/code>root@'<\/code>202.54.11.24<\/code>'<\/code> ;
\n\/\/force update of authorization changes
\n<\/code>mysql><\/code>FLUSH PRIVILEGES; <\/code><\/p>\n![\"\"](\"http:\/\/blog.binus-edu.com\/wp-content\/uploads\/heidi3.jpg\")
<\/p>\n
\n06: Connect failed<\/p>\nA sample iptables rule to open Linux iptables firewall<\/h4>\n
\/sbin\/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT<\/code><\/pre>\n\/sbin\/iptables -A INPUT -i eth0 -s 202.54.11.24 -p tcp --destination-port 3306 -j ACCEPT<\/pre>\n
\/sbin\/iptables -A INPUT -i eth0 -s 192.168.1.0\/24 -p tcp --destination-por\r\n\r\nIf you still cannot connected , please checked the my.cnf on \/etc\/my.cnf<\/strong><\/pre>\n
\n[mysqld]
\nuser = mysql
\npid-file = \/var\/run\/mysqld\/mysqld.pid
\nsocket = \/var\/run\/mysqld\/mysqld.sock
\nport = 3306
\nbasedir = \/usr
\ndatadir = \/var\/lib\/mysql
\ntmpdir = \/tmp
\nlanguage = \/usr\/share\/mysql\/English
\nbind-address = 65.55.55.2
\n# skip-networking
\n....<\/code><\/p>\n\u00a0Then do not forget to restart the mysql service\r\n#service mysqld restart<\/pre>\n
How to test the connection from your mysql server ?\r\n\r\nYou can use the mysql command\r\n mysql -h hostname address -u username -p then enter password<\/pre>\n
\nEnter password:
\nWelcome to the MySQL monitor.\u00a0 Commands end with ; or g.
\nYour MySQL connection id is 5714
\nServer version: 5.0.51a-3ubuntu5.4 (Ubuntu)<\/p>\n
\n+——————–+
\n| Database\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |
\n+——————–+
\n| information_schema |
\n+——————–+
\n1 rows in set (0.01 sec)<\/p>\n![\"mysqlpriviledgespng\"](\"http:\/\/adityo.blog.binusian.org\/files\/2009\/03\/mysqlpriviledgespng.png\" "\\"mysqlpriviledgespng\\"")
<\/p>\n","protected":false},"excerpt":{"rendered":"