Category: Uncategorized

Installing Homebrew On A Mac

Advanced Mac users may appreciate using the Homebrew package manager, which greatly simplifies the process of installing command line software and tools on a Mac.

For example, if you want to easily install favorite command line tools on a Mac like cask, htop, wget, nmap, tree, irssi, links, colordiff, or virtually any other familiar unix command line utility, you can do so with a simple command. Homebrew downloads and builds the package for you.

This is obviously aimed at more technically savvy Mac users who spend a lot of time at the command line. While there’s no particular issue for novice users installing Homebrew on their Mac, the odds of novices finding it useful are slim, unless they intend to embark on learning the command line environment. Contrast that to power users who practically live in a terminal environment, whether longtime Mac users or migrating to the platform from the Windows or Linux world, who will immediately see the value of Homebrew.

Requirements for Installing Homebrew on Mac OS

prerequisites to installing Homebrew on a Mac include the following:

  • A Mac running Mac OS X 10.10 or later, though earlier versions are sort of supported
  • Command Line Tools must be installed on the Mac (either independently or through Xcode)
  • Knowledge of the command line and using bash or zsh

Assuming you’re interested in installing Homebrew and meet those requirements, then the rest is equally straight forward.

How to Install Homebrew on Mac OS

The simplest way to install Homebrew is through ruby and curl, accomplished with a single command. This approach is the same for installing Homebrew in all supported versions of Mac OS and Mac OS X.

  1. Open the “Terminal” application, found in /Applications/Utilities/
  2. Enter the following command into a single line of the terminal:

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

  1. Hit Return and you’ll see a series of lines about what the script will install and where, hit Return again to agree or hit Escape to cancel
  2. Enter the administrator password (required by sudo execution) to begin installation

Installation of Homebrew will take a while depending on the speed of your Mac and internet connection, as each necessary package is downloaded and installed by the script.

When complete, you will see an “Installation successful!” message.

Now you’re ready to install software packages through Homebrew, or you can read the help documentation with the following command:

brew help

Installing Software Packages through Homebrew on Mac

Installing packages with Homebrew is super easy, just use the following syntax:

brew install [package name]

For example, to install wget through Homebrew you could use the following syntax:

brew install wget

Simple, easy. Once complete you can run wget as usual.

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

MySQL – Sizing And Report Scripts

Various ways to get sizing, counts, etc.

-- Total rows and size for an entire server, excluding system schemata, ordered by size DESC 
SELECT table_schema,table_name,
 CONCAT(ROUND(SUM(table_rows) / 1000000,2),'M') rows,
 CONCAT(ROUND(SUM(data_length) / (1024 * 1024 * 1024),2), 'G') DATA,
 CONCAT(ROUND(SUM(index_length) / (1024 * 1024 * 1024), 2),'G') idx,
 CONCAT(ROUND(SUM(data_length + index_length) / (1024 * 1024 * 1024), 2),'G') total_size,
 ROUND(SUM(index_length) / SUM(data_length),2) idxfrac
 FROM information_schema.TABLES
 WHERE table_schema NOT IN ('mysql','performance_schema','information_schema') 
 GROUP BY table_schema,table_name
 ORDER BY SUM(data_length + index_length) DESC;
-- Total rows and size for an entire server, excluding system schemata, ordered by schema and table name 
 SELECT table_schema,table_name,
 CONCAT(ROUND(SUM(table_rows) / 1000000,2),'M') rows,
 CONCAT(ROUND(SUM(data_length) / (1024 * 1024 * 1024),2), 'G') DATA,
 CONCAT(ROUND(SUM(index_length) / (1024 * 1024 * 1024), 2),'G') idx,
 CONCAT(ROUND(SUM(data_length + index_length) / (1024 * 1024 * 1024), 2),'G') total_size,
 ROUND(SUM(index_length) / SUM(data_length),2) idxfrac
 FROM information_schema.TABLES
 WHERE table_schema NOT IN ('mysql','performance_schema','information_schema') 
 GROUP BY table_schema,table_name
 ORDER BY table_schema,table_name;

-- Total rows and size
 SELECT COUNT(*) TABLES,
 CONCAT(ROUND(SUM(table_rows) / 1000000,2),'M') rows,
 CONCAT(ROUND(SUM(data_length) / (1024 * 1024 * 1024),2), 'G') DATA,
 CONCAT(ROUND(SUM(index_length) / (1024 * 1024 * 1024), 2),'G') idx,
 CONCAT(ROUND(SUM(data_length + index_length) / (1024 * 1024 * 1024), 2),'G') total_size,
 ROUND(SUM(index_length) / SUM(data_length),2) idxfrac
 FROM information_schema.TABLES
-- Database wise report
 SELECT COUNT(*) TABLES, table_schema,
 CONCAT(ROUND(SUM(table_rows) / 1000000,2),'M') rows,
 CONCAT(ROUND(SUM(data_length) / (1024 * 1024 * 1024),2), 'G') DATA,
 CONCAT(ROUND(SUM(index_length) / (1024 * 1024 * 1024), 2),'G') idx,
 CONCAT(ROUND(SUM(data_length + index_length) / (1024 * 1024 * 1024), 2),'G') total_size,
 ROUND(SUM(index_length) / SUM(data_length),2) idxfrac
 FROM information_schema.TABLES
 GROUP BY table_schema
 ORDER BY SUM(data_length + index_length) DESC
-- Database wise report in MB
 SELECT COUNT(*) TABLES, table_schema,
 CONCAT(ROUND(SUM(table_rows) / 1000000,2),'M') rows,
 CONCAT(ROUND(SUM(data_length) / (1024 * 1024 ),2), 'M') DATA,
 CONCAT(ROUND(SUM(index_length) / (1024 * 1024 ), 2),'M') idx,
 CONCAT(ROUND(SUM(data_length + index_length) / (1024 * 1024 ), 2),'M') total_size,
 ROUND(SUM(index_length) / SUM(data_length),2) idxfrac
 FROM information_schema.TABLES
 GROUP BY table_schema
 ORDER BY SUM(data_length + index_length) DESC
-- Engine wise breakup
 SELECT ENGINE, COUNT(*) TABLES,
 CONCAT(ROUND(SUM(table_rows) / 1000000,2),'M') rows,
 CONCAT(ROUND(SUM(data_length) / (1024 * 1024 * 1024),2), 'G') DATA,
 CONCAT(ROUND(SUM(index_length) / (1024 * 1024 * 1024), 2),'G') idx,
 CONCAT(ROUND(SUM(data_length + index_length) / (1024 * 1024 * 1024), 2),'G') total_size,
 ROUND(SUM(index_length) / SUM(data_length),2) idxfrac
 FROM information_schema.TABLES
 GROUP BY engine ORDER BY sum(data_length+index_length) DESC
-- Top 30 tables
 SELECT table_schema, table_name, engine, table_rows,
 CONCAT(ROUND(data_length / (1024 * 1024),2),'MB') AS DATA,
 CONCAT(ROUND((data_length + index_length) / (1024 * 1024), 2),'MB') AS total_size
 FROM INFORMATION_SCHEMA.TABLES
 ORDER BY data_length DESC LIMIT 30
-- List of Full Text Indexes
 SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, CARDINALITY, NULLABLE
 FROM statistics
 WHERE index_type LIKE 'FULLTEXT%' ORDER BY TABLE_SCHEMA, TABLE_NAME
-- The number of columns for each datatype
 SELECT DATA_TYPE, COUNT(*) AS mycount
 FROM `COLUMNS`
 WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql')
 GROUP BY DATA_TYPE ORDER BY mycount DESC
-- The number of columns for each datatype with size (replace my_database)
 SELECT TABLE_SCHEMA, COLUMN_TYPE, count(*) FROM `COLUMNS`
 GROUP BY COLUMN_TYPE HAVING TABLE_SCHEMA = 'my_database'
-- Blob, Float and Double Data types details
 SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT,
 IS_NULLABLE, NUMERIC_PRECISION, COLUMN_TYPE, COLUMN_KEY
 FROM COLUMNS
 WHERE DATA_TYPE IN ('FLOAT','BLOB','DOUBLE')
 AND TABLE_SCHEMA != 'mysql'
 ORDER BY DATA_TYPE, COLUMN_TYPE
--All tables with no primary key
use INFORMATION_SCHEMA;
 select CONCAT(t.table_schema,".",t.table_name) as tbl
 from INFORMATION_SCHEMA.TABLES AS t
 LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c
 ON (t.TABLE_NAME=c.TABLE_NAME
 AND c.CONSTRAINT_SCHEMA=t.TABLE_SCHEMA
 AND constraint_name='PRIMARY')
 WHERE t.table_schema!="information_schema"
 AND constraint_name IS NULL;
-- All tables and their primary keys, if exist:
use INFORMATION_SCHEMA;
 select CONCAT(t.table_schema,".",t.table_name) as tbl,
 c.column_name,c.constraint_name
 from INFORMATION_SCHEMA.TABLES AS t
 LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c
 ON (t.TABLE_NAME=c.TABLE_NAME
 AND c.CONSTRAINT_SCHEMA=t.TABLE_SCHEMA
 AND constraint_name='PRIMARY')
 WHERE t.table_schema!="information_schema"
 order by constraint_name;
-- Foreign Key constraints for Database dbName
SELECT A.TABLE_SCHEMA AS FKTABLE_SCHEM, A.TABLE_NAME AS FKTABLE_NAME, A.COLUMN_NAME AS FKCOLUMN_NAME,
 A.REFERENCED_TABLE_SCHEMA AS PKTABLE_SCHEM, A.REFERENCED_TABLE_NAME AS PKTABLE_NAME,
 A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME, A.CONSTRAINT_NAME AS FK_NAME
 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A, INFORMATION_SCHEMA.TABLE_CONSTRAINTS B
 WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME
 AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND B.CONSTRAINT_TYPE IS NOT NULL
 HAVING PKTABLE_SCHEM IS NOT NULL
 and A.TABLE_SCHEMA = 'dbName'
 ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION limit 1000

Rename a MySQL Schema (Sort Of)

To rename a schema (emulating the now-dropped RENAME DATABASE functionality):

 SELECT concat('RENAME TABLE oldSchema.',table_name, ' TO newSchema.',table_name, ';')
 FROM information_schema.TABLES
 WHERE table_schema='oldSchema';

Execute the resultant sql, then drop the now-empty old schema.