mysql


You have a schema containing lots of MyISAM tables that may possibly be stale due to code no longer updating them. You can rank them by date of most recent update.

mysql -D PriceComparison -NBe"show table status like '%'" \
| \
cut -f1,2,11,12 \
| \
sort -k 5,6

I omitted a grep MyISAM because the InnoDB tables will be grouped together with NULL dates so aren’t a problem. The sneaky problem to avoid is that sort sees a datetime field as being two fields. To sort the list by creation date you would use sort -k 3,4.

Advertisements

For reporting-style stats we often need to round down ‘now’ to the nearest 5 mins. For instance, my current project requires the code to identify the current ‘rolling twenty minutes’ with such rounding. An easy way to do this that works even in MySQL v4.0 is the following.

SELECT FROM_UNIXTIME(300 * FLOOR( UNIX_TIMESTAMP()/300 ))

which gives the most recently-passed 5-min datetime, taking 13:52:50 down to 13:50:00.
Of course, if you need rounding to the nearest 15 mins then the 300 would be changed to 900.

If your tables are all using InnoDB then you can get a snapshot of the master with minimal locking using

schemata="one two three"
mysqldump --opt --no-data --routines --skip-triggers \
  --databases $schemata >schema__all.sql
mysqldump --opt --no-create-info --skip-routines --triggers \
  --flush-logs --single-transaction --master-data=2 \
  --databases $schemata >data__all.sql

Otherwise, if you have some non-transactional tables (eg MyISAM) then omit single-transaction and use table locking.
A comment in the dump file will tell you what settings to use for master.info but you’d be able to guess it anyway (thanks to flush-logs).

change master to master_log_file='..', master_log_pos=..  # probably 107

Very useful summary of changes required at pythian.com

Start the server with

mysqld_safe –skip-grant-tables &