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
shell> mysqladmin flush-logs
But my choice goes to changing
/etc/my.cnf to an appropriate number (I like 30 days). You will have to bounce MySQL after updating
MySQL: Selecting a Random Record
From ~jk ORDER BY RAND()
SELECT col1, col2
FROM table JOIN
(SELECT CEIL(RAND() *
FROM table)) AS id
) AS r2
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)
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))