This will tell you, sorted with most usage at top. for file in /proc/*/status ; do awk ‘/VmSwap|Name/{printf $2 ” ” $3}END{ print “”}’ $file; done | sort -k 2 -n..
Category : bash snippets
Stupid command line tricks: I often run SQL jobs that can take many days to fully execute, and I always pipe the error and stdout to a file. As such, you end up with lots of lines like this in the output:Query OK, 81218 rows affected (52.76 sec)Here’s a silly one-liner that will quickly total ..
Since this is an area not everyone has to deal with, I thought a quick one-liner was in order. MySQL can have one of three different binary log formats, which are used to facilitate replication. MySQL masters have binary logging enabled and the binary logs are streamed to all the replicas, where the logs are ..
The attached scripts simple reverse a repliation flow (e.g. serverA –> serverB becomes serverB –> serverA). automate..
#!/bin/bash # # Many third-party data sources do lazy character column definitions, making them varchar(65535), which is stoopid. # This little script will read in the DDL for a table, parse out the columns defined like this and then hork out a # SQL script that discovers the actual max length of the actual data ..
#SCHEMA=”${2}” STARTIME=`date` echo “Starting rowcount update at $STARTIME” # fivetran database DBASE=”fivetran” SCHEMALIST=”greenhouse hubspot jira mandrill marketo” # Truncate rollup table echo “Deleting from rollup table for ${DBASE} schema…” PGPASSWORD=xxxxxxxxxx psql -t -h warehouse.vacasa.services -p 5439 -Uvacasaroot -d${DBASE} -n -q -c “TRUNCATE TABLE admin.overall_rowcount;” # Iterate through schemata for SCHEMA in ${SCHEMALIST} do TABLES=`PGPASSWORD=xxxxxxxxxx psql ..
#!/bin/bash ################################################################################# # findlockblocks.sh # # Dead-stupid script that leverages existing RS queries and does a mashup that reports # the current running queries that are blocking others, sorted by time running. # # Nice, simple way to see if there’s actually a problem or if RS is just swamped. # # v1.01 2019-06-14 rlbyrd ..
#!/bin/bash ################################################################################# # do_analyze_by_batch.sh # # Using this query as the source… # # SELECT database, schema || ‘.’ || “table” AS “table”, stats_off # FROM svv_table_info # WHERE stats_off > 5 # ORDER BY 3 DESC, 2; # # …which displays all tables and their stats_off percentage in descending order. # # # Then ..
#!/bin/bash # Expects: # If no *.last exists, make one. # if one does, diff it and report # Set this manually. Everything else will branch off this. BASEDIR=”/data/backups/ddl/” DBUSER=”svc_dba_adhoc” DBPASS=”ADXEZKZLbgdvcB24nvPu” TZ=”America/Los_Angeles” EMAILTO=”richard.byrd@example.com,mark.butler@example.com,rlbyrd@rlbyrd.com” if [ “$2” == “” ] then echo ” ” echo “USAGE: ddldiff ” echo ” ” fi HOST=${1} SCHEMA=${2} TMPFILE=”/tmp/${HOST}-${SCHEMA}.tmp” /bin/rm ..
Another useful database auditor to quickly find possible issues. Expects user and pass on command line. Currently expects to be executed on the same server as the mysqld process. #!/bin/sh # Another useful database auditor to quickly find possible issues. Expects user and pass on command line. # Currently expects to be executed on the ..
Make sure you’re using GNU find and GNU sed. find . -type f -exec sed -i ‘s/OLDSTRING/NEWSTRI..
Useful if you feel you’re getting DDOS, flood, or other attacks: netstat -an | grep tcp | awk ‘{print $5}’|sed ‘s/::ffff://’|cut -f1 -d’:’| sort | uniq -c | ..
BASH Shell Redirect Output and Errors To /dev/null How do I redirect output and errors to /dev/null under bash / sh shell scripting? How do I redirect the output of stderr to stdout, and then redirect this combined output to /dev/null? You can send output to /dev/null, by using command >/dev/null syntax. However, this will ..
By now, we’re all aware that a utf8 charset and collation is the “right” thing to do, with most folks opting for utf8_general_ci or utf8_unicode_ci. However, lots of installations still default to some form of latin1, which is unfortunate. There are multiple ways to do this, all which functionally do the same thing. Be aware ..
#!/usr/bin/expect -f # catch the date passed on the command line and assign it to a variable set thedate [lindex $argv 0] # connect to remote server spawn scp “user@server:/backups/*$thedate*” /backups_archives ####################### expect { -re “.*es.*o.*” { exp_send “yesr” exp_continue } -re “.*sword.*” { exp_send “your_passwordrr” } } interact Read more about expect here: http://linux.die.net/man/1/expect ..
Basic rsyncing: #!/bin/bash rsync -avz –delete –exclude= –exclude=remotehost.com:/the/remote/directory/ /the/local/..
find . -name ‘*.txt’ -print0 | xargs -0 rm You can substitute the “rm” with any command you need to run on all the associated files. “print0” automagically escapes filenames with spaces or weird c..
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 ..
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 ..
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 ..
CONVERT ALL THE THINGS. Well, almost all the things. #!/bin/bash for database in aaaaa bbbbb ccccc ddddd eeee do thesetables=`mysql -N -B -e “SELECT CONCAT(TABLE_SCHEMA,’.’,TABLE_NAME) \ FROM information_schema.TABLES where TABLE_SCHEMA = \”$database\”” \ AND TABLE_SCHEMA NOT IN (‘mysql’,’information_schema’,’performance_schema’)` alltables=`echo $alltables $thesetables` # change the schema itself mysql -e “ALTER DATABASE $database CHARACTER SET utf8 COLLATE ..