Monday 14 December 2015

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

Friday 11 December 2015

Using ssh-agent


Using ssh-agent

ssh-agent -s > AmundSshAgent
source AmundSshAgent
ssh-add <key>


Sometime,  we need to perform some daily task, that need grant access. In these case, we can use the following few tricks to make use along with cronjobs.

[step1]

$ ssh-agent -s > sshagent_amit

[step2] [you should see something like following output. ]

$ source sshagent_amit

$ cat sshagent_amit

SSH_AUTH_SOCK=/tmp/ssh-iHFFf28257/agent.28257; export SSH_AUTH_SOCK;
SSH_AGENT_PID=28258; export SSH_AGENT_PID;
echo Agent pid 28258;

[step3]
ssh-add <your_keys>

NOTE: After the system got restared, you need to update your agent file, or you can automate this one too.

Now you can just logout, and login again and source the file [ example: sshagent_amund] and then, you should see your keys, when you type "ssh-add -L"

[step4]
The script that we have plans to add into the crontab.

#!/bin/bash
source sshagent_amund
ssh amit@remote_host "command" > output_file.txt


In cron entry:

* * * * * /bin/bash -l -c '/path/to/your/script'


-l        Make bash act as if it had been invoked as a login shell (see INVO-CATION below).
-c        Command


update the cron time as per your need.