ALIX Apache C Debian digitalfoo DIY Eclipse FreeBSD GIMP git GNOME HowTo Linux Mac OS X misc MySQL NanoBSD PHP Programming Python REST Soekris wireless

List all databases with row count and size

SELECT
    table_schema `DB`,
    SUM(table_rows) `Row Count`,
    SUM(data_length + index_length)/1024/1024 `Size (MB)`
FROM
    information_schema.tables
GROUP BY
    table_schema
ORDER BY
    table_schema;
+--------------------+-----------+--------------+
| DB                 | Row Count | Size (MB)    |
+--------------------+-----------+--------------+
| information_schema |      NULL |   0.00878906 |
| intellitype        |         2 |   0.01562500 |
| intellitypesite    |        35 |   0.37500000 |
| jobfoo             |      8048 |   3.34375000 |
| mysql              |      2059 |   0.64330387 |
| performance_schema |     23014 |   0.00000000 |
| tidenugget         |   2940747 | 263.10937500 |
+--------------------+-----------+--------------+

Obviously, you can add a WHERE table_schema=`YOUR_DATABASE_NAME` to filter on a database name...

List all tables in database with row count and size

SELECT
    table_name,
    table_rows,
    (data_length + index_length)/1024/1024 `Size (MB)`
FROM
    information_schema.tables
WHERE
    table_schema='tidenugget'
ORDER BY
    table_name;
+----------------------------+------------+--------------+
| table_name                 | table_rows | Size (MB)    |
+----------------------------+------------+--------------+
| auth_group                 |          0 |   0.03125000 |
| auth_group_permissions     |          0 |   0.06250000 |
| auth_permission            |         27 |   0.04687500 |
| auth_user                  |          0 |   0.03125000 |
| auth_user_groups           |          0 |   0.06250000 |
| auth_user_user_permissions |          0 |   0.06250000 |
| django_admin_log           |          0 |   0.04687500 |
| django_content_type        |          9 |   0.03125000 |
| django_session             |          0 |   0.03125000 |
| restapi_place              |       1881 |   0.35937500 |
| restapi_prediction         |    2938332 | 262.28125000 |
| restapi_region             |        279 |   0.06250000 |
+----------------------------+------------+--------------+
   

See what hosts are being blocked.

l@ln1:~$ sudo iptables -L 
Chain INPUT (policy ACCEPT)
target     prot opt source               destination         
fail2ban-ssh-ddos  tcp  --  anywhere             anywhere             multiport dports ssh
fail2ban-ssh  tcp  --  anywhere             anywhere             multiport dports ssh

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination         

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination         

Chain fail2ban-ssh (1 references)
target     prot opt source               destination         
DROP       all  --  188.127.225.85       anywhere            
DROP       all  --  219.138.203.198      anywhere            
DROP       all  --  server77-68-105-205.live-servers.net  anywhere            
DROP       all  --  essen107.server4you.net  anywhere            
RETURN     all  --  anywhere             anywhere            

Chain fail2ban-ssh-ddos (1 references)
target     prot opt source               destination         
RETURN     all  --  anywhere             anywhere 

Note the IP of the host.

l@ln1:~$ sudo iptables -L -n
Chain INPUT (policy ACCEPT)
target     prot opt source               destination         
fail2ban-ssh-ddos  tcp  --  0.0.0.0/0            0.0.0.0/0            multiport dports 22
fail2ban-ssh  tcp  --  0.0.0.0/0            0.0.0.0/0            multiport dports 22

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination         

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination         

Chain fail2ban-ssh (1 references)
target     prot opt source               destination         
DROP       all  --  188.127.225.85       0.0.0.0/0           
DROP       all  --  219.138.203.198      0.0.0.0/0           
DROP       all  --  77.68.105.205        0.0.0.0/0           
DROP       all  --  217.172.182.32       0.0.0.0/0           
RETURN     all  --  0.0.0.0/0            0.0.0.0/0           

Chain fail2ban-ssh-ddos (1 references)
target     prot opt source               destination         
RETURN     all  --  0.0.0.0/0            0.0.0.0/0          

Put the following in a file.

#!/bin/bash

if [ -z $1 ]; then
    echo "USAGE: $0  [<chain, defaults to fail2ban-ssh>]"
    exit 1
fi

host=$1
chain=$2

if [ -z $2 ]; then
    chain="fail2ban-ssh"
fi

sudo iptables -D $chain -s $host -j DROP

Run it.

l@ln1:~$ sh drop_from_fail2ban.sh 
USAGE: sh drop_from_fail2ban.sh <ipaddr> [<chain, defaults to fail2ban-ssh>] l@ln1:~$ sh drop_from_fail2ban.sh 77.68.105.205
   

I wrote the following little script to migrate some of my private git repositories to a different server.

The following assumes you can commit to the new host already. You might have to setup repositories in gitosis/gitolite first if you are using one of those.


First, create a text file containing a list of repositories you want to migrate.

lyle@localhost:/tmp/git$ cat repos.txt 
c__project1
c__progject2
django__project1
python__project1

Create the script to do the work. Or you can download here.

#!/bin/sh                                                                          
# lyle@digitalfoo.net                                                           
                                                                                   
file=$1                                                                            
from=$2                                                                            
to=$3                                                                              
                                                                                   
function usage {                                                                   
    echo "USAGE: $0  <user@fromhost> <user@tohost>"                    
    exit 1                                                                         
}                                                                                  
                                                                                   
if [ -z $file ] || [ -z $from ] || [ -z $to ]; then                                
    usage                                                                          
fi                                                                                 
                                                                                   
for repo in `cat $file`; do                                                        
    git clone $from:$repo                                                          
    cd $repo                                                                       
    git remote set-url origin $to:$repo                                            
    git push origin master                                                         
    cd ..                                                                          
done  

Run the script.

lyle@localhost:~$ mkdir gitmigrate
lyle@localhost:~$ cd gitmigrate
lyle@localhost:~/gitmigrate$ sh git_migrate.sh
USAGE: ./git_migrate.sh <user@fromhost> <user@tohost>

This should pull the repositories from user@fromhost:reponame, reconfigure the push URL to user@tohost:reponame, and push the repository (including previous history) to the new destination.