Automatic incremental MySQL backup with xtrabackup.

#!/bin/bash

now=`date +%Y-%m-%d_%H-%M-%S`
backup_directory="/media/data/backup"
current_backup=$backup_directory/$now"_full"

logfile="/var/log/db-backup/"$now"_full"

mysql_user="root"
mysql_pass=""


echo "========================================="
echo "        MySQL Data Backup (Full)"
echo "========================================="

#perform full backup
innobackupex --ibbackup=xtrabackup_55 --parallel=4 --user=$mysql_user --password=$mysql_pass --no-timestamp $current_backup 2> $logfile

# see if the backup completed sucsessfully
if ! tail $logfile -n 1 | grep completed.OK
then
        rm -R $current_backup
        echo "\nERROR: backup failed! (log: "$logfile")"
  exit 1
fi

echo $current_backup > $backup_directory/last_backup

echo "Backup Completed OK!"

MySQL: increasing query cache hit rate.

Part 1: Expecting SQL queries.

The most common mistakes:

1. Using different cases

Incoming queries are compared to those in the query cache before parsing, so the following two queries are regarded as different by the query cache:
    SELECT * FROM tbl_name
    Select * from tbl_name

Advice: use database abstraction layer or stick to single coding style.

2. Call to date/time functions at database side

A query cannot be cached if it contains any of the functions shown in the following list:

CONVERT_TZ() 

CURDATE() 

CURRENT_DATE() 

CURRENT_TIME() 

CURRENT_TIMESTAMP() 

CURTIME() 

NOW() 

SYSDATE() 

UNIX_TIMESTAMP() with no parameters 

Advice(UPDATED): Move date/time function to your application and cut off the seconds or/and  minutes  or/and hour part.

(If your database server and your application on the same, or both configured to same timezone, and using ntp servers).

3. Call to RAND(), FOUND_ROWS() functions at database side

Same for previos case: Queries which contain these function cannot be cached.

Advice: You could found "RAND()" analog at any programming language. Use SQL_CALC_FOUND_ROWS() function instead FOUND_ROWS()

4. Merging huge request with tables with high changing rate

If a table changes, all cached queries that use the table become invalid and are removed from the cache. This includes queries that use MERGE tables that map to the changed table. A table can be changed by many types of statements, such as INSERT, UPDATE, DELETE, TRUNCATE TABLE, ALTER TABLE, DROP TABLE, or DROP DATABASE.

Advice: Spit it to 2+ requests. 

Two queries(the first one which contains "huge part", but cached, and the second one for table with high change rate) will work faster then one merged.

For more information: http://dev.mysql.com/doc/refman/5.0/en/query-cache-operation.html

-- Vladimir Getmanshchuk aka vladget

spotscale diagram

src: http://mycld.googlecode.com/files/SpotScale.svg

Elastician: Comprehensive List of AWS Endpoints

endpoints cheat-sheet

Elastician: Comprehensive List of AWS Endpoints:

'via Blog this'