Category: Database

Mysql SQL in bash one-liner

Helge on

If you just need a quick way to get some data from a mysql database in your shell (bash), you could do something like this in one line:

mysql -h your.server.edu -u db_username -p`cat /path/to/your/homedir/secretpasswordfile` -e ";use databasename; SELECT tablename.columnname FROM tablename where id like '421111' and something like '1' and option like '23';"; > /tmp/datayouwant.txt; while read i; do echo ";$i";; done < /tmp/datayourwant.txt | sort | uniq

If you don't like to scroll:
-bash-3.2$ mysql -h your.server.edu -u db_username -p`cat /path/to/your/homedir/secretpasswordfile` -e "use databasename; SELECT tablename.columnname FROM tablename where id like '421111' and something like '1' and option like '23';" > /tmp/datayouwant.txt; while read i; do echo "$i"; done < /tmp/datayourwant.txt | sort | uniq

On my server I would then get a list of words/numbers or whatever you might have in the database, which one might want to use further in another script or command:

Dikult
Drupal
DSpace
Mediawiki
Open Journal Systems
Piwik
Postgresql og Mysql
Redhat Enterprise Linux 6 (RHEL6)
Redmine
Solr
Webmail (RoundCubemail)
Wordpress
Xibo

Finding spam users

Helge on

We had a lot of spam users in our multisite wordpress system. This was because we had self-registration enabled for a period. Not a smart thing to do…

anyway, I wrote a bash script in order to find which users id’s from the Mysql database that could potentially be spam users. With this list of ID’s I would run another SQL directly where I would set the “spam” to the number “1”. This would make sure the user could not log in, and after a month or so, the user could be deleted.

As you can see from one of the sql’s, the “%uib.no” is the trusted emails from our organization.

 

[bash]
#!/bin/bash

# Which one of all the users in the multisite blog system are spamusers?
# If the user doesn’t have any relations in users_blogg, most likely the user is a spam user.
# But the user can have a meta_value in wp_usermeta table like source_domain.
# These are valid users, and should not be marked as spam

# Find all users ID’s
dbquery=$(mysql -u root -p`cat /root/mysql` -e “use blog; select ID from wp_users;”)
array=($(for i in $dbquery; do echo $i; done))

# echo ${array[@]}

# for all the users, do:
for i in ${array[@]}
do
dbquery2=$(mysql -u root -p`cat /root/mysql` -e “use blog;SELECT wp_bp_user_blogs.id FROM wp_bp_user_blogs WHERE wp_bp_user_blogs.user_id = $i”;)
array2=($(for j in $dbquery2; do echo $j; done))
if [ ${#array2[@]} -eq 0 ];then
dbquery3=$(mysql -u root -p`cat /root/mysql` -e “use blog; select user_email from wp_users WHERE ID = $i and user_email not like ‘%uib.no’;”)
dbquery4=$(mysql -u root -p`cat /root/mysql` -e “use blog; select meta_value from wp_usermeta WHERE user_id = $i and meta_key like ‘source_domain’;”)
array4=($(for r in $dbquery4; do echo $r; done))
for n in ${array4[@]}
do
echo “User $i has a blogg with name $n! Please don’t delete this user.”
done

array3=($(for k in $dbquery3; do echo $k; done))
for m in ${array3[@]}
do
echo “User $i with email $m is not connected to any blog and should be marked as a spam user!”
done
fi
done
[/bash]

Alert Blog Admin php script

Helge on

I had to send emails to all the Blog admins in our WordPress Multisite installation. This is how I did it:

[code lang=”php”]
<?php
# Alert Blog Admin php script
# connect to blog db, select from, find all blog admin email adresses
# get subject, body from pre-created local files, send the email

# how to run the script
# php alertblogadmin.php -s subjecttext.txt -b bodytext.txt

$mysqlserver="localhost";
$mysqluser="dbuser";
$mysqlpassword="XXXXXXX";
$database="blog";

$ERROR=0;

# This script require arguments
$arguments = getopt("s:b:f:dr");

# Test if array $arguments as at least s,b and r
# maybe to make sure that $arguments array contains at least 3 items?
if ( count($arguments) < 3 ){
echo "Missing arguments…\n";
echo "Usage: php alertblogadmin.php -s subjecttext.txt -b bodytext.txt\n";
die;
}

# 2 text files need to exist
if ( !file_exists($arguments["s"])) {
echo "File $arguments[s] does not exist?\n";
$ERROR=1;
}
if ( !file_exists($arguments["b"])) {
echo "File $arguments[b] does not exist?\n";
$ERROR=1;
}

if ($ERROR == "1"){
die;
}

$link = mysql_connect($mysqlserver, $mysqluser, $mysqlpassword);
if (!$link) {
die(‘Could not connect: ‘ . mysql_error());
}
mysql_select_db ($database,$link);

# collect blogs
$sql="SELECT blog_id from wp_blogs where deleted not like ‘1’";
$result=mysql_query($sql,$link);
if (!$result) {
die(‘Invalid query: ‘ . mysql_error());
}

$i = "0";
$emailarray = array();

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
$blog_id = $row[0];
$table = "wp_" . $blog_id . "_options";

# Find email adresses to admins in each blog in the system
$sql2="SELECT option_value FROM $table WHERE option_name = ‘admin_email’";
$result2=mysql_query($sql2,$link);
while ($row2 = mysql_fetch_array($result2, MYSQL_NUM)) {
$emailarray[$i] = $row2[0];
}
$i++;
}

# sort the email array and find unique emails
asort($emailarray);
$emailarray = array_unique($emailarray);

$emails = "";
foreach ($emailarray as $email) {
$emails .= $email.",";
}

$emails = substr($emails,0,-1);

# Could be smart to see who you will be sending your email to
echo $emails;

# open subject and body text files
$subject = file_get_contents($arguments["s"]);
$body = file_get_contents($arguments["b"]);
$from = $arguments["r"];

# Use this one to test before you actuelly send to $emails, which is a variabel that might contain hundreds of emails.
# You really would like to test first
$bcc = "user1@something.com,user2@something.net";
//$bcc = $emails; # uncomment this one, when you are ready to go

$headers = "From: noreply@something.com" . "\r\n" .
"Reply-To: noreply@something.com" . "\r\n" .
"Bcc: $bcc" . "\r\n" .
"X-Mailer: PHP/" . phpversion();
$header_ = ‘MIME-Version: 1.0’ . "\r\n" . ‘Content-type: text/plain; charset=UTF-8’ . "\r\n";

mail(”, ‘=?UTF-8?B?’.base64_encode($subject).’?=’, $body, $header_ . $headers);
mysql_close($link);
?>

[/code]

Postgresql 8.1 database bash dump script

Helge on

I’ve wrote a simple bash script to dump databases from a Postgresql 8.1 database server
which uses the pg_dump and pg_dumpall.


#!/bin/bash
## This scripts dumps all the databases in a Postgres 8.1 server, localhost
## 2 dump files are made for each database. One with Inserts another without.
## some variables, change them to fit yours
 LOGFILE="/var/lib/pgsql/data/pg_log/pgsql_dump.log"
 HOSTNAME=`hostname`
 MAILLIST="name1@something.com,name2@something.com"
 BACKUP_DIR="/backup/postgresql_dumps"

# Date and time variables
 DATE=`date +%Y-%m-%d`
 TIME=`date +%k:%M:%S`
 TODAY=$DATE"T"$TIME
# only postgres can run this script!
 if [ `whoami` != "postgres" ]; then
   echo "pgsql_dump tried to run, but user is not postgres!" >> $LOGFILE
   echo "You are not postgres, can not run."
   echo "Try: su -c ./pgsql_dump.sh postgres"
   exit;
 fi

# clean up old dumps! (find all types which are files, with the name that ends with .sql,
# their date older than 7 days, and execute the command "rm" (remove) )
 find $BACKUP_DIR -type f -name '*.sql' -mtime +7 -exec rm {} \;

# Check if there any backup files.
# Action: find in folder BACKUP_DIR all files with file-extension .sql.
# Count the number of files with wc (word count, option -l, which counts the numbers of lines.
# If this number is 0, then there are no files, and clearly something is wrong,
# because you don't have any backups!
if [ `find $BACKUP_DIR -type f -name '*.sql' | wc -l` -eq 0 ]; then
 echo "There are no pgsql dumps for the last 2 days at $HOSTNAME. Something is wrong!" | mail -s "[PGSQLDUMP ERROR] $HOSTNAME" $MAILLIST
fi

# Create the log-file if it doesn't exist
if [ ! -e $LOGFILE ]; then
 touch $LOGFILE
fi

# Find which databases you have in your Postgresql server
# Action: list out all the databases, remove unwanted lines and characters, extract wanted line with awk (line 1),
# strip away white empty lines with the command 'sed':
DATABASES=`psql -q -c "\l" | sed -n 4,/\eof/p | grep -v rows | grep -v template0 | awk {'print $1'} | sed -e '/^$/d'`
# Dump the databases in individual files
 for i in $DATABASES; do
   FILENAME="$i-$TODAY.sql"
   pg_dump -D $i > /backup/postgresql_dumps/"$FILENAME"
 done

# we also like a dump with copy statements
 for i in $DATABASES; do
   FILENAME="$i-cp-$TODAY.sql"
   pg_dump $i > /backup/postgresql_dumps/"$FILENAME"
 done

# full backup is also necessary
 pg_dumpall -D > /backup/postgresql_dumps/"all-databases $TODAY".sql
 pg_dumpall > /backup/postgresql_dumps/"all-databases $TODAY".sql

# Finally vacuum the database
 vacuumdb -a -f -z -q