Getting a quick summary on your MySQL Database

Summary of yer tables

Sometimes, you just want to see what’s going on from a high level. “HOW CAN I GET A SUMMARY OF MY TABLES?!” you may have asked yourself…

To do this, you’ll need to do a simple query into the TABLES table in the information_schema database. Like this:

It’s a habit for me, but I always like to describe the table so I know what I’m looking at:

Now, we can see what we’re looking for. Here’s what I generally like to do:

Yeah, even my schema names are trendy and clever.</sarcasm>

Anyways, now you can sort and/or select however you want! Grok yer data, amigo. =]

I realize my code blocks are not blocking well enough. Will fix.

Greppin’ Baller Style

So you wanna find something in yer code? Yep. Grep.

There comes a time when you really just want to find something, but either the grep is going to take way too long or you just like being efficient down to the microsecond. Either way, I’m about to show you how I grep. /flex

Anytime I grep, this is generally how I roll:
grep -IirFn "What I'm looking for" .
Let’s break it down now! We’re grepping, but what are all those options?

  • -I – ignore binary files (ie: don’t grep through my images, bro)
  • -i – case insensitive. I know I grepped for “What I’m looking for”, but maybe it’s not capitalized
  • -r – recursively grep through this directory and each one inside it… recursively. Ahem. Bad joke.
  • -F – search for my string literally, no regex!
  • -n – prepend each found line with the corresponding line number – such help! =]
  • "What I'm looking for" . – so what I’m looking for and the dot at the end is to tell grep to start looking here (and sneaky tip, include hidden files!)

Ludicrous speed, ENGAGE

Nerd Remix! Betcha Picard wished he said that once in a while. #nerdage

So let’s say this is all well and good, but we gotta keep on rollin’ through a huuuuuge codebase with lots and lots of stuuuuuuff. What then?!

This is generally when I like to FIND the context of the files I can get at and only grep through those:
find . -name "*.php" -type f -exec grep -FnH "DOING_AJAX" {} \;
Wait… that’s different…. and the arguments aren’t the same. What just happened?! D=

All great questions! Thank you for asking, me! =D

  • find . – find is the command, the dot here is for the same reason (start looking here and include hidden files!)
  • -name "*.php" – this tells find to go find files with a .php extension in the name. Keep in mind, if you want to not bother with case sensitivity, you can replace “-name” with “-iname” =]
  • -type f – this tells find to only worry about FILES (as opposed to directories, etc…)
  • -exec blah blah – this is pretty cool. You can tell find to execute any command upon the matched item. So here we told it to execute a grep. Make sure you end your exec with a fully escaped semicolon too!
  • grep -FnH – We know about the -Fn already, but the -H allows our grep to also prepend the matched grep with the filename also. So -nH gives us the filename:linenumber: match.

I hope that helps you get to what you’re looking for just a little quicker =]

Quick way to check for php syntax errors via CLI

Need a quick way to run through all php files in a directory (recursively) and see which ones have syntax errors? Here you are my friend:
find . -name "*.php" -type f -print | xargs -I file php -l file | grep -v "No syntax errors detected"

Another way to do this (which if you’re as familiar with find as I am — ie: not very) you could do this as well:
find . -name "*.php" -type f -exec php -l {} \; | grep -v "No syntax errors detected"

Set Default Username and Password for your MySQL DB

Tired of always having to enter your username and password to do anything on your mysql database? I would only suggest using this on your local dev box for security purposes, but admittedly, it’s a nice timesaver =]

Warning: this assumes you can vim. If you’d prefer, you can replace any instance of “vim” with “nano”

From the *nix CLI:
vim ~/.my.cnf
If it’s blank, feel free to insert the following (with your user credentials of course!):
[client]
user = 'user_name'
password = 'secure_password-moreso_than_that_pls...well...this_is_ok'

Save that puppy and now from your *nix CLI you shouldn’t have to worry about those precious few keystrokes when interacting with mysql anymore… on second thought, if your password looks like that above, use this. It’ll save you buckets of time =\

Quick way to remove all tables in MySQL DB through *nix CLI

Tablenator!

Here’s a quick time saver (if you dev like me, it’ll come in handy!) for when you just want to drop all of the tables in a database. Of course, test it first so you know what you’ll be dropping!

Test First:

Code:

Rundown

The basic formula is a BASH for loop that grabs all of the tables in the desired database, filters the output using a combination of AWK and GREP so we can use just a simple list, then drops each table separately.

USE THIS CODE AT YOUR OWN RISK! BACKUP YOUR DATABASE FIRST AND HAVE A RECOVERY PLAN IN PLACE! (trust a man known to fat finger things)

Make a Public SSH Key from your Private Key

Once you’ve found the private key you want to make a public key for (typically in ~/.ssh/ ), here’s the quick and easy command (in this example, we’re making a .pub for our id_rsa key):

ssh-keygen -y -f ~/.ssh/id_rsa > ~/.ssh/id_rsa.pub

Exporting a MySQL Database

For a MySQL database export:

mysqldump -u username -p database_name > /tmp/dump.sql
^^ from the *nix command line

For a particular table in a database

mysqldump -u username -p database_name table_name > /tmp/dump.sql
^^ also from the *nix command line

You will then be prompted for the password for the specified user, shortly thereafter, you’ll have your db export (or as some folks like to call it, a “db dump” — gah! Hate that name… gross!)

Tired of always typing in your username/password when using mysql via the CLI? Check out how to set up a .my.cnf!

Import MySQL DB via CLI

mysql -u account_name -p db_name < /path/to/import/file/mysql.sql
^^ from the *nix command line

Sometimes you need to specify the host (typically it's either 127.0.0.1 or localhost) like this:

mysql -u account_name -p -h localhost db_name < /path/to/import/file/mysql.sql
^^ from the *nix command line

Now enter the specified account's mysql password and you're on your way!

In the instance you're having issues with your import, another way to import (will require access and proper privs), sometimes you just want it to run and not worry about errors (for instance when an insert or two in a dump are greater than the buffer). The handy function to just "make it work" (ie: NOT FOR PRODUCTION USE!!!) is source in the mysql cli -- check it out:

mysql> source /path/to/db/export.sql;
^^ from the mysql command line

That'll treat the file as a list of commands and run each one individually. If a few of them don't work, it keeps moving on and finishes everything it can... now that's BLAMO

SQL Adding a Column to an Existing Table

Since I can never remember the syntax to add a column to a table, here it is:

alter table foo_tablename add column foo_column int(10) default null

  • alter table – the actual command
  • foo_tablename – the table name we’re altering
  • add column – telling sql to add a column instead of some other alteration
  • foo_column – name of the column to add
  • int(10) default null – the column’s qualities (it’s an int up to 10 digits long, can be null and defaults to null

Tada!!!