MySQL: The Binary Log

From 5.2.3. The Binary Log, 6.2.2. Using Backups for Recovery, 6.3. Point-in-Time Recovery

Starting mysql like with --log-bin switched does this:

The binary log contains all statements that update data or potentially could have updated it (for example, a DELETE which matched no rows). Statements are stored in the form of “events” that describe the modifications. The binary log also contains information about how long each statement took that updated data.

This is near the equivalent of a transaction log in other DBMSes. You will notice a minimal (1%) loss in performance, but you’ll be able to restore your database to specified points in time.

To restore, run two commands:


shell> mysql < backup_sunday_1_PM.sql
shell> mysql mysqlbinlog host-bin.000007 host-bin.000008 | mysql


There are a few ways to deal with your logs from a maintenance standpoint.

You can periodically run this command:

PURGE MASTER LOGS

Or

shell> mysqladmin flush-logs

But my choice goes to changing expire_logs_days in /etc/my.cnf to an appropriate number (I like 30 days). You will have to bounce MySQL after updating /etc/my.cnf.

MySQL: Selecting a Random Record

From ~jk ORDER BY RAND()

SELECT col1, col2
  FROM table JOIN
       (SELECT CEIL(RAND() *
                    (SELECT MAX(id)
                       FROM table)) AS id
        ) AS r2
       USING (id);

MySQL: Full Text Search

From 11.8.1. Natural Language Full-Text Searches and MySQL SQL Syntax and Use

To examine your existing fulltext configuration directives:

show variables where variable_name like 'ft_%'

To change the minimum length of words to caught in your fulltext indexes, change the value of ft_min_word_len.

Example fulltext query:

SELECT * FROM apothegm
    WHERE MATCH(attribution, phrase)
    AGAINST('bell');

If you really want your fulltext searches to go to the next level, take a look at Stemming. This changes words into their “root” words so their variances can be found easier.

MySQL Closest GIS Location

With Latitude/Longitude stored in two seperate fields:

SELECT * FROM table
ORDER BY SQRT(POW(fieldLong - givenLong, 2) + POW(fieldLat - givenLat, 2))

Your response: