User Comments

Handy mysql log rotation script. For those not
using any chroot environements, comment out both
lines in #chroot section. Set MYSQL_HOME QUERYLOG
SLOWLOG ERRLOG appropiately.

#!/bin/sh
###############################################
# MySQL log rotation
# [email protected]
###############################################

# chroot
VIRTUAL="/virtual"
VIRTUAL_HOME="${VIRTUAL}/mysql"

# mysql
MYSQL_HOME="${VIRTUAL_HOME}/usr/local/mysql"
DATADIR="${MYSQL_HOME}/var"
LOGDIR="${MYSQL_HOME}/log"
QUERYLOG="${LOGDIR}/querylog"
SLOWLOG="${LOGDIR}/slowlog"
ERRLOG="${LOGDIR}/errlog"

# most universal method for calculating
yesterday's date in YYYYMMDD format
DATE=`/usr/bin/perl -e
"@a=localtime(time-86400);printf('%02d%02d%02d',@a[5]+1900,@a[4]+1,@a[3])"`

PID_FILE=$DATADIR/`/bin/hostname`.pid

if ! [ -s ${PID_FILE} ]; then
echo " Error: pid file not found."
exit 1;
fi

PID=`cat $PID_FILE`

echo -n "Rotating logs: "

if [ -e ${QUERYLOG} ]; then
echo -n "querylog "
/bin/mv ${QUERYLOG} ${QUERYLOG}.${DATE}
fi
if [ -e ${SLOWLOG} ]; then
echo -n "slowlog "
/bin/mv ${SLOWLOG} ${SLOWLOG}.${DATE}
fi
if [ -e ${ERRLOG} ]; then
echo -n "errlog "
/bin/mv ${ERRLOG} ${ERRLOG}.${DATE}
fi

/bin/kill -1 $PID
</PRE>
Run from cron at midnight.

To answer the above question, in the current version, replication supports parallel processing for reads, but you have to be extremely careful for writes.

There is, however, a way around the write limitations in most application situations.

Let's say you have two websites: Each site _could_ be (and in many default situations is) hosted on its own server with its own cpu and MySQL database.

The problems inherent in that situation are reliability (uptime), waste of system resources and lack of flexibility with system resources.

So instead you setup both websites to run on both servers. (Details of IP level load balancing are beyond the scope of this post, but there are lots of options available.) Now, if you've limited your application appropriately, you could setup both servers to write to each other, but I personally wouldn't recommend that you accept that limitation, as it can be fraught with hidden dangers.

Besides, in most applications, the vast majority of the load is reads, not writes.

The language your application is written in is probably implemented with pools of database connectors to service application threads. Using that model, you would setup a pool of read threads on each server to balance their reads from their local MySQL database and the replicated one on the other server.

For writes, you would setup your connectors on both servers to use the master MySQL database for writes, then setup a different pool of connectors for writes to use the slave database. You'll have to handle the error at your application level, but when the master is unavailable, then you switch your application to start writing to the slave instead.

You'll need to write some explicit error handling to tell the slave it's now the master and prevent the original master from being used for reads or writes until it has become the slave in turn and refreshed itself from the slave.

Writing a record to the database immediately after a transition from slave to master to use as a locking mechanism can help ensure that your application always knows what state the two (or more) MySQL databases are in with regards to which pool of connectors writes should be sent to.

A cleaner solution, but much more expensive option in terms of hardware, would be to use two database servers with a hardware IP level load balancer between them and the application servers. In that case, use the same method of using different connection pools for reads and writes, but configure them to hit one IP for each, then configure the load balancer to send the read IP address to both database servers, while only sending the write IP address to one database server at a time. The other database server(s) would be configured to only have traffic sent to them for the write IP address if the original has failed.

You could then safely chain multiple databases to each other for circular writing, but still ensure that all writes only originate in the correct sequence because unless there is a database failure, they are only performed on one database server.

Of course, before you try any replication scheme, be sure to read http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html and the rest of this section of the manual looking for gotchas!

Im testing replication structured master to cascade of slave servers but i got sync problem if a client use a slave to insert/update data.
Waiting for a master-2-master replication i solved this issue by a little cron in php:

$db = mysql_connect("master-host","master-user","master-pwd") or $db = false;
if ($db!=false) {
mysql_close($db);
$db = mysql_connect("localhost","local-user","local-pwd") or die ("Could not connect to MySQL");
mysql_select_db("my_database",$db);
mysql_query("stop slave; DROP DATABASE my_database; load data from master; start slave;");
mysql_close($db);
}

If you are using InnoDB tables, remember that nothing is replicated until the transaction is completed, so if you need to read information to complete the transaction that is dependent upon what has been written eg the value of an auto_increment field, you need to read it from the master.

For anyone looking to add databases (not just slaves) to running replication, I wrote the following script which works pretty well:
http://rdkls.blogspot.com.au/2011/04/adding-new-database-to-running-mysql.html

It's useful in a number of scenarios, but for me mainly if
1. Your server has many DBs, and you want to bring slave DBs online one-by-one while having replication continue to run
2. One database on the slave gets out of sync with the master and it is faster to recreate from backups

Add your own comment.