perl mysql connection note

### From mysql side ###

NOTE:

1. Install mysql server.

2. update /etc/mysql/my.cnf to listen outside from the localhost.

3. You should have a user with the access to a database, By default you can connect to remote mysql database as root user.

4. The user should have the access to connect from remote host.

https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql

# creating a test database

CREATE DATABASE test; 

# created a mysql user [ For this example, I have created an mysql user ]

CREATE USER ‘mysql'@'%' IDENTIFIED BY 'password';

# Grant the mysql user to access database [ Not secure example but, just for example ]

GRANT ALL PRIVILEGES ON * . * TO 'mysql@'%';

# Enable the mysql server to listen from outside

/etc/mysql/my.cnf

#bind-address           = 127.0.0.1

##########################################################

##########################################################

### From perl side ###

#!/usr/bin/perl

use DBI;

use strict;

my $host = ‘mysql_db_server';

my $driver = "mysql";

my $database = "test";

my $dsn = "DBI:$driver:$database;host=$host";

my $userid = "mysql";

my $password = "password";

my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;

NOTE: you need to have DBI::mysql module in your host.

####### another example ######

#!/usr/bin/perl

use DBI;

use strict;

my $host = ‘mysql_db_server';

my $driver = "mysql"; 

my $database = "test";

my $dsn = "DBI:$driver:$database;host=$host";

my $userid = "mysql";

my $password = "password";

my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;

#

#

my $query="show databases";

print "\n\UFollowing is the output of query: \"$query\"\n\n";

my $sth=$dbh->prepare($query);

$sth->execute();

while (my @row=$sth->fetchrow_array)

{

  print $row[0]."\n";

}

#

#

$query="show tables";

print "\n\UFollowing is the output of query: \"$query\"\n\n";

$sth=$dbh->prepare($query);

$sth->execute();

while (my @row=$sth->fetchrow_array)

{

  print $row[0]."\n";

}

#

#

$query="describe domains";

print "\n\UFollowing is the output of query: \"$query\"\n\n";

$sth=$dbh->prepare($query);

$sth->execute();

while (my @row=$sth->fetchrow_array)

{

  print $row[0]."\n";

}

$sth->finish;

## Note: close, only at the end. No need to close in middle.

### output of the above example ###

FOLLOWING IS THE OUTPUT OF QUERY: "SHOW DATABASES"

information_schema

mysql

performance_schema

test

FOLLOWING IS THE OUTPUT OF QUERY: "SHOW TABLES"

domains

FOLLOWING IS THE OUTPUT OF QUERY: "DESCRIBE DOMAINS"

id

name

url

descr

Ref links

http://aruljohn.com/code/perl/mysqlselect.html