DEVELOPER ZONE ::
Login / Register
Table of Contents
This chapter describes the various replication features provided by MySQL. It introduces replication concepts, shows how to set up replication servers, and serves as a reference to the available replication options. It also provides a list of frequently asked questions (with answers), and troubleshooting advice for solving replication problems.
MySQL Enterprise. The MySQL Network Monitoring and Advisory Service provides numerous advisors that provide immediate feedback about replication-related problems. For more information see http://www.mysql.com/products/enterprise/advisors.html.
For a description of the syntax of replication-related SQL statements, see Section 13.6, “Replication Statements”.
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
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 it's own server with it's 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 refeshed 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://www.mysql.com/doc/en/Replication_HOWTO.html and the rest of this section of the manual looking for gotchas!
When you change the hostname of your server (linux "hostname" command), MySQL replication stops next time MySQL is restarted with an error because it can't figure out what happened.
Error:
050112 22:54:45 Failed to open the relay log './oldhost-relay-bin.000087' (relay_log_pos 394502776)
050112 22:54:45 Could not find target log during relay log initialization
How I worked around the problem:
> mysqlbinlog oldhost-relay-bin.000087 --position=394502776 | mysql -p
^ This applied the last binlogs to the database from the point replication stopped. I got the position from the error message or the "Show slave status;" query.
> vi relay-log.info
^ In relay-log.info, I changed the relay file to ./newhost-relay-bin.000001, the second line to 0 (which is the position of the binlog)
Then I logged back into the server and sent the "start slave;" command. Voila! :)
I hope this helps someone because it took me about an hour to figure this out. :P
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.
When moving to InnoDB, we found out that our replication setup script no longer works; after switching to mysqldump, it took the initial replication around two hours to complete (our databases contain around one million rows.)
To solve this, instead of mysqldump, we use normal mysql client, with arguments -h $master --skip-column-names --batch -e "SELECT * from $table" and redirect the output into a temporary file.
And then we import with mysql again, using -h 127.0.0.1 -e "LOAD DATA INFILE $tempfile REPLACE INTO TABLE $table'
The two hours were shortened to around 40 seconds. That's much better!
I actually found a simpler workaround for the hostname/relay-log name change problem than the one described above. Error log shows: 'failed to open relay log' and 'Could not find target log during relay log initialization'
Stop the mysql instance, and simply prepend the contents of 'old.host.name-relay-bin.index' to the 'new.host.name-relay-bin.index' like so:
cat new.host.name-relay-bin.index >> old.host.name-relay-bin.index
mv old.host.name-relay-bin.index new.host.name-relay-bin.index
and then start mysql.
(The problem itself is discussed in bug 2122:
http://bugs.mysql.com/bug.php?id=2122)
Add your own comment.