Backup mysql binary logs

It is always good to make a backup of all the log files you are about to delete. Alternatively if you take incremental backups then you should rotate the binary log by using FLUSH LOGS. This done, you need to copy to the backup location all binary logs which range from the one of the moment of the last full or incremental backup to the last but one. These binary logs are the incremental backup

Here is the bash script, which you can use to backup binary logs, all you need to do is change following param according to your needs and all yours. This script is not mine, I got the idea from here:

#
# This script backup binary log files
#

backup_user=dba
backup_password=xxxx
backup_port=3306
backup_host=localhost 
log_file=/var/log/binlog_backup.log
binlog_dir=/mnt/database/logs # Path to binlog
backup_dir=/mnt/archive/binlogs/tench # Path to Backup directory

PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin
export PATH

Log()
{
echo "`date` : $*" >> $log_file
}

mysql_options()
{
common_opts="--user=$backup_user --password=$backup_password"
if [ "$backup_host" != "localhost" ]; then
common_opts="$common_opts --host=$backup_host --port=$backup_port"
fi
}

mysql_command()
{
mysql $common_opts --batch --skip-column-names  -e "$2"
}

mysql_options

Log "[INIT] Starting MySQL binlog backup"

Log "Flushing MySQL binary logs (FLUSH LOGS)"

mysql_command mysql "flush logs"

master_binlog=`mysql_command mysql "show master status" 2>/dev/null | cut -f1`

Log "Current binary log is: $master_binlog"

copy_status=0

for b in `mysql_command mysql "show master logs" | cut -f1`
do
if [ -z $first_log ]; then
first_log=$b
fi
if [ $b != $master_binlog ]; then
Log "Copying binary log ${b} to ${backup_dir}"
rsync -av $backup_host:/$binlog_dir/$b $backup_dir >& /dev/null
if [ $? -ne 0 ]; then
copy_status=1
break
fi
else
break
fi
done

if [ $copy_status -eq 1 ]; then
Log "[ERR] Failed to copy binary logs cleanly...aborting"
exit 1
fi

NOW() function is not replication-safe

It says on mysql doc that NOW () function is replication-safe and they have given an example too to prove this fact that you will obtain the same result on slave as on the master.
http://dev.mysql.com/doc/refman/5.1/en/replication-features-functions.html

I'll try to prove it with an example that NOW()function is not replication-safe. Suppose Master is located in 'New york' and Slave is in 'London' and both servers are using local time. On Master you create table and insert a row as well as you explicitly set session time zone to 'SYSTEM'.

Note: Both Master/Slave are using identical version of MySQL, i.e. 5.1.47

mysql> CREATE TABLE test_now_func (mycol DATETIME);
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO test_now_func VALUES ( NOW() );
Query OK, 1 row affected (0.00 sec)

mysql> SET TIME_ZONE=’SYSTEM’;

mysql> SELECT * FROM test_now_func;
+---------------------+
| mycol |
+---------------------+
| 2009-09-01 12:00:00 |
+---------------------+
1 row in set (0.00 sec)

However if you do a SELECT on slaves copy you will see different result:
mysql> SELECT * FROM test_now_func;
+---------------------+
| mycol |
+---------------------+
| 2009-09-01 17:00:00 |
+---------------------+
1 row in set (0.00 sec)

The correct solution is recorded some where else, that is, the same system time zone should be set for both master and slave.
http://dev.mysql.com/doc/refman/5.1/en/replication-features-timezone.html

For example

[mysqld]
..
timezone=’America/New_York’

Key cache hit ratio

When we talk about key cache performance we usually look at these two status variables.

1. Key_read_requests
The number of requests to read a key block from the cache.

2. Key_reads
The number of physical reads of a key block from disk.

There is a good reason to examine Key_reads, because we know that disks are very slow relative to RAM but the Key_reads aren't always physical disk reads at all. If the requested block of data isn't in the operating system's cache, then a Key_read is a disk read, you will be lucky if it is cached, then it's just a system call. Having said that it is always good to minimize key_reads which may cause randon disk I/O.

The optimum solution is to keep the ratio Key_reads : Key_read_requests should be 1:100 and Key_writes / Key_write_requests should always be less than 1.

Finally I'll like to show you something partially useful you can do with Key_reads:

[xxxxxx ~]$ mysqladmin -uroot -p ext -ri10 | grep Key_reads
Enter password:
| Key_reads | 44605148 |
| Key_reads | 4 |
| Key_reads | 4 |
| Key_reads | 13 |
| Key_reads | 9 |
| Key_reads | 6 |
| Key_reads | 20 |
| Key_reads | 6 |
| Key_reads | 11 |

This server is doing approximately 7 Key_reads every ten seconds but it is hard to say that how many of them are random I/O and how many are just system calls (i.e. read from operrating system's cache)

upgrading to innodb plugin

As of MySQL 5.1.38, the InnoDB Plugin is included in MySQL 5.1 releases, in addition to the built-in version of InnoDB that has been included in previous releases. So all we need to do is just enable it, that is, add following options into my.cnf file

[mysqld]
..
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so

for further readings visit here http://dev.mysql.com/doc/refman/5.1/en/news-5-1-38.html

Assuming you have succesfully enabled Innodb Plugin, now what? would you automatically get benefits from all new Innodb plugin features?
The answer is yes/no, because Innodb Plugin supports new file format 'Barracuda', which introduces two such new data structures: compressed
, and long variable-length columns stored off-page. By default, the InnoDB Plugin does not create tables in a format that is incompatible with the built-in InnoDB in MySQL.
This means you won't get these two features automatically until you create new tables using new file format or convert exisiting tables to new format.

Innodb Plugin offers lots of other features too and these features are enabled by default, also you will automatically get benefit from these features such as fast index creation and lots of performance and scalability enhancments,
for full details click here
here http://www.innodb.com/doc/innodb_plugin-1.0/innodb-performance.html

Repair by keycache vs Repair by sorting

It is advised to disable keys on MYISAM/ARCHIVE tables before performing bulk insert operations should give a considerable speedup, especially when you have many indexes. And then enable keys to to re-create missing indexes, this task might take much longer than bulk insert operation, sometimes it may take days to finish.

One approach would be not to disable keys at first place but would this help at all? the answer is it may or may not. Personally I do not suggest this approach. If you find enable key task is taking longer then start new session with MySQL and check the state of enable keys command. If A SHOW PROCESSLIST reveal, not surprisingly, the dreaded "Repair with keycache", which seems to multiply indexing time by a factor of 20 to 30. If you want to see "repair by sort" to be used then theoretical max size of every single index must fit into myisam_max_sort_file_size.

Solution: increase myisam_max_sort_file_size

mysql> SET GLOBAL myisam_max_sort_file_size=100000 * 1024 * 1024

And restart enable keys

Similarly if your repair takes several hours then either use above solution or

you might think of using the utility 'myisamchk' with the -n (or --sort-recover) option.