Personal tools
You are here: Home Downloads Tutorials Python MySQL DataBase Access from Remote machine
nrcfoss logo aukbc cdac iit mumbai iit madras ow2 iosn flosscc
india.gov.in
 

MySQL DataBase Access from Remote machine

This stuff enables you to establish a connection to a database through python

MySQL DataBase Access from Remote machine

Using Python  

 

Basic set up

 

You need to locate your  my.cnf  file

# find / -name my.cnf

 

#ee  my.cnf     // ee  is easy editor in FreeBSD UNIX, for a linux user it can be vi ,  vim  or your favorite  text editor

Locate the section starts with   [mysqld]

 

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

 

bind-address= Your_mysql_server_IP

eg:

[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    = 192.168.x.x
# skip-networking

 

save and close file and restart mysql server.

 

# /etc/init.d/mysql restart


Grant access to new database from Remote Ip

 

mysql> CREATE DATABASE DAC;
mysql>GRANT ALL ON DAC.* TO dac_user@'192.168.36.x' IDENTIFIED BY 'dac_password';


 

 

Make sure your  server port 3306 is open

 

 

If It blocked,  Do the  following tips to enable if you using software firewall at your machine/remote machine/router. 

 

On  FreeBSD:

 

FreeBSD     pf rule ( /etc/pf.conf)

pass in on $ext_if proto tcp from any to any port 3306

OR allow only access from your web server located at 192.168.X.X

pass in on $ext_if proto tcp from 192.168.X.X to any port 3306  flags S/SA synproxy state

 

 

for linux

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

 

 OR only allow remote connection from your web server located at 192.168.X.X:

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

 

 
OR only allow remote connection from your lan subnet 192.168.1.0/24:    ( You need add your CIDR address )

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

Finally save all rules:
# service iptables save


 

 

 

 Test it

From your remote system or your desktop type the following command:

$ mysql -u dac_user –h Your_db_servers_ipaddress –p

 

Where,
  • -u dac_user:  dac_user is MySQL  username which you granted privileges
  • -h IP or hostname:   Your_db_server_ipaddress is MySQL server IP address or hostname (FQDN) Fully Qualified Domain Name
  • -p : Prompt for password

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


 NEXT  Steps:  

You need to install  MySQLdb adapter  for  python

Do connect via Python Script

 

 
 
import MySQLdb

# Create a connection object and create a cursor
Con = MySQLdb.Connect(host="127.0.0.1", port=3306, user="dac_user", passwd="myPassword", db="tst")
Cursor = Con.cursor()

# Make SQL string and execute it
sql = "SELECT * FROM Users"
Cursor.execute(sql)

# Fetch all results from the cursor into a sequence and close the connection
Results = Cursor.fetchall()
Con.close()

 

 

 

thats it enjoy it  

 

 

******#############################################******

Document Actions