Run queries on all DB instances

Assumes there is a text file, “instances.txt” that has the IP or symbolic name of the server where the instances are Just put the query in quotes at the end of the script (such as pasting the below into runonall.sh)

params=$@
           
for i in `cat /home/rbyrd2/bin/instances.txt` ; do echo "$i:  " ; mysql -h $i -N -B -e "$params" ; done  

Tags

Related Posts

Share This

Multidimensional array sorts

Call it this way…

array_sort($array,'index1','index2',etc.);

You can place a ! in front of the index name (e.g., !index1) to indicate a reverse sort.

Ensure these two functions are available in the source or associated includes:

function array_sort_func($a,$b=NULL) {
   static $keys;
   if($b===NULL) return $keys=$a;
   foreach($keys as $k) {
      if(@$k[0]=='!') {
         $k=substr($k,1);
         if(@$a[$k]!==@$b[$k]) {
            return strcmp(@$b[$k],@$a[$k]);
         }
      }
      else if(@$a[$k]!==@$b[$k]) {
         return strcmp(@$a[$k],@$b[$k]);
      }
   }
   return 0;
}

function array_sort(&$array) {
   if(!$array) return $keys;
   $keys=func_get_args();
   array_shift($keys);
   array_sort_func($keys);
   usort($array,"array_sort_func");       
} 

Tags

Related Posts

Share This

Basic connectivity tests through firewalls

Possible telnet test output for another database and their meaning is listed below:

a) Result when firewall is blocking connection for a IP and port combination:
    >telnet 20.25.127.140 3306
    Trying 20.25.127.140...
    telnet: Unable to connect to remote host: Connection timed out

b) Result when firewall is allowing connections through for IP address and port number but listener on
 remote host is down:
    >telnet 20.25.127.140 3306
    Trying 20.25.127.140...
    telnet: Unable to connect to remote host: Connection refused

c) Result when firewall is allowing connections through for IP address and port number and listener on 
remote host is running:
    >telnet 32.82.12.177  3306
    Trying 32.82.12.177...
    Connected to 32.82.12.177.
    Escape character is '^]'.

Tags

Related Posts

Share This

Soundex stuff from iREIT

Various crap.

SELECT
  d.id,
  d.name
FROM ireit_analysis.v_ireit_house d,
  ireit_reference.dictionary_newage r
WHERE d.name REGEXP r.word
    AND ( r.word LIKE '%finance%' || r.definition LIKE '%finance%' || r.word LIKE '%bank%' || r.definition LIKE '%bank%' || r.word LIKE '%money%' || r.definition LIKE '%money%');
SELECT
  d.id,
  d.name
FROM ireit_analysis.v_ireit_house d
WHERE d.name STRCMP(SELECT
                      r.word
                    FROM ireit_reference.dictionary_newage r
                    WHERE r.word LIKE '%finance%');
SELECT
  d.id,
  d.name
FROM ireit_analysis.v_ireit_house d
WHERE d.name sounds LIKE 'b';
SELECT
  difference('gokarts','gocart');
SELECT
  d.id,
  d.name
FROM ireit_analysis.v_ireit_house d
WHERE INSTR(SUBSTR(SOUNDEX(CONCAT('Z',d.name)), 2), REPLACE(SUBSTR(SOUNDEX('Zbird'), 2), '0', '')) > 0;
SELECT
  d.id,
  d.name
FROM ireit_analysis.v_ireit_house d
WHERE INSTR(SUBSTR(SOUNDEX(d.name), 2), REPLACE(SUBSTR(SOUNDEX('auto'), 2), '0', '')) > 0;
SELECT
  SOUNDEX('z0portal.com'),
  SOUNDEX('zbird');

Tags

Related Posts

Share This

Show all grants

Useful when migrating. Point at source DB instance, retrieve grants using this script, pipe through the Add a semicolon in PERL script, and voila, recreate grants on destination instance.

#!/bin/bash 
( 
 mysql --batch --skip-column-names -e "SELECT user, host FROM user" mysql 
) | while read user host 
do 
  echo "# $user @ $host"
  mysql --batch --skip-column-names -e"SHOW GRANTS FOR '$user'@'$host'"
done 

Tags

Related Posts

Share This