MySQL - formatting output

Hey!
If you need to preserve fancy table output for your mysql queries, use the following switch: -t.

--table, -t
Display output in table format. This is the default for interactive use, but can be used to produce table output in batch mode.

Reference:
http://stackoverflow.com/questions/9745547/get-mysqls-fancy-table-format-as-stdout

MySQL in scripting - using timeouts

If you are using any cron jobs with which connect to MySQL and do some work it will be wise decision to think about what will happen when MySQL daemon stops. I'll tell you! Your scripts will not be able to connect to database. And they will run until database returns.  They may run forever!
Just look into man of mysqld.

      ·   connect_timeout
           The number of seconds before connection timeout. (Default value is
           0.)

See that?! Default value is 0 - it means infinity!

No need to have such risk.

Hence quickly add the following parameter to your scripts (example below use 5 seconds timeout):

--connect_timeout=5

Correct MySQL permissions for backup user

Backups are rule #1 for any DBA. If you are using mysqldump utility you should take care of correct privileges for your backup user:

> GRANT SHOW DATABASES, SHOW VIEW, SELECT, LOCK TABLES, RELOAD ON *.* to 'backup_user'@'localhost' IDENTIFIED BY 'backup_password';

It is expanded list of privileges found in the wonderful MySQL permissions for backup entry.

MySQL - Finding tables without primary keys

It is always important to have primary keys when you're in SQL world. I have found a nice way find to find those culprit tables who don't have it. And just modified a bit added "engine" column. So here it is:

SELECT table_catalog, table_schema, table_name, engine
  FROM information_schema.tables
    WHERE (table_catalog, table_schema, table_name) NOT IN
          (SELECT table_catalog, table_schema, table_name
               FROM information_schema.table_constraints
               WHERE constraint_type = 'PRIMARY KEY')
      AND table_schema NOT IN ('information_schema', 'pg_catalog');

Handly shell programming using MySQL

A very handy options to use MySQL queries results in your shell scripts:

       ·   --batch, -B
           Print results using tab as the column separator, with each row on a
           new line. With this option, mysql does not use the history file.

           Batch mode results in nontabular output format and escaping of
           special characters. Escaping may be disabled by using raw mode; see
           the description for the --raw option.

       ·   --skip-column-names, -N

           Do not write column names in results.

Feel the difference!

> mysql -h localhost -e 'show status like "uptime"'

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Uptime        | 2612  |

+---------------+-------+

> mysql -h localhost  -B -N -e 'show status like "uptime"'

Uptime 2619

Refrerences:

http://www.webhostingskills.com/articles/using_mysql_in_shell_scripts

http://dev.mysql.com/doc/refman/5.5/en/mysql-command-options.html