|
Planet MySQL
|
Planet MySQL - http://www.planetmysql.org/
|
-
Why MySQL replication is better than mysqlbinlog for recovery
You have a backup, and you have the binary logs between that backup and now. You need to do point-in-time recovery (PITR) for some reason. What do you do? The traditional answer is “restore the backup and then use mysqlbinlog to apply the binary logs.” But there’s a much better way to do it.
The better way is to set up a server instance with no data, and load the binary logs into it. I call this a “binlog server.” Then restore your backup and start the server as a replication slave of the binlog server. Let the roll-forward of the binlogs happen through replication, not through the mysqlbinlog tool.
Why is this better? Because replication is a more tested way of applying binary logs to a server. The results are much more likely to be correct, in my opinion. Plus, replication is easier and more convenient to use. You can do nice things like START SLAVE UNTIL, skip statements, stop and restart without having to figure out where you left off, and so on.
Replication also has the ability to correctly reproduce more types of changes than mysqlbinlog does. Try this with statement-based replication:
insert into tbl(col) values(connection_id());
That’ll work just fine through replication, because the SQL thread on the slave will change its connection ID to match the original. It won’t work through mysqlbinlog.
Related posts:MySQL disaster recovery by promoting a slaveProgress on High Performance MySQL Backup and Recovery chapterHow MySQL replication got out of syncHigh Performance MySQL, Second Edition: Backup and RecoveryHow to make MySQL replication reliable
-
Cassandra and Ganglia
I finally got some time to do some house cleaning. One of my nagging low-hanging fruit stuff was stop running jconsole on one screen to see the state of all my cassandra boxes. I created a ganglia script to graph what is above. Above I am showing all the cassandra servers and their total row read stages as a gauge. Meaning that basically I am graphing the delta of the change between ganglia script runs. This gives me the reads over time based on deltas between runs.How I have it set up is:All data exposed by JMX to produce tpstats and cfstats is graphed via ganglia. The pattern for each graph is as followscass_{stat_class}_{key}stat_class - tpc, tpp, tpa means complete, pending, active respectivelykey - would be message deserialization for instance.For column family stats I graph the keyspace stats as well as the specific column family stats exposed by cfstats. For instance below:If your interested in the scripts I'll send it to you or put it up on code.google.com, its written in perl OOP perl and takes the same approach of packaging that maatkit tool kit for mySQL by Xarb and crew does (puts all the "classes" in the file as the application).GmetricDelegate is the parent packageGmetricCassandra extends GmetricDelegate and overloads getData as well as defines what is an absolute stats vrs a gauge.As you can see the pattern I also haveGmetricInnoDBGmetricMySQLand so on.then on each server I run/usr/bin/perl -w /home/scripts/ganglia_gmetric.pl --module=GmetricCassandrathis then talks to Ganglia through gmetric to report the stats.
-
MySQL Cluster: 5 Steps to Getting Started, then 5 More to Scale for the Web
Join us for a live and interactive webinar session where we will demonstrate how to start an evaluation of the
MySQL Cluster database in 5 easy steps, and then how to expand your
deployment for web & telecoms-scale services.Just register here: http://www.mysql.com/news-and-events/web-seminars/display-566.htmlGetting Started will describe how to:
Get the softwareInstall itConfigure itRun itTest it
Scaling for HA and the web will describe how to:
Review the requirements for a HA configurationInstall the software on more serversUpdate & extend the configuration from a single host to 4Roll out the changesOn-line scaling to add further nodesWhen: Wednesday, September 08, 2010: 09:00 Pacific time (America)
Wed, Sep 08: 11:00 Central time (America)
Wed, Sep 08: 12:00 Eastern time (America)
Wed, Sep 08: 16:00 UTC
Wed, Sep 08: 17:00 Western European time
The presentation will be approximately 45 minutes long followed by Q&A.
-
dbbenchmark.com – configuring OpenBSD for MySQL benchmarking
Here are some quick commands for installing the proper packages and requirements for the MySQL dbbenchmark program.
export PKG_PATH="ftp://openbsd.mirrors.tds.net/pub/OpenBSD/4.7/packages/amd64/"
pkg_add -i -v wget
wget http://dbbenchmark.googlecode.com/files/dbbenchmark-version-0.1.beta_rev26.tar.gz
pkg_add -i -v python
Ambiguous: choose package for python
a 0:
1: python-2.4.6p2
2: python-2.5.4p3
3: python-2.6.3p1
Your choice: 2
pkg_add -i -v py-mysql
pkg_add -i -v mysql
pkg_add -i -v mysql-server
ln -s /usr/local/bin/python2.5 /usr/bin/python
gzip -d dbbenchmark-version-0.1.beta_rev26.tar.gz
tar -xvf dbbenchmark-version-0.1.beta_rev26.tar
cd dbbenchmark-version-0.1.beta_rev26
./dbbenchmark.py --print-sql
- login to mysql and execute sql commands
./dbbenchmark.py
-
Replication and “the lost binlog”
Unless you set sync_binlog = 1, a system crash on the master will likely fail any slave with an “Client requested master to start replication from impossible position” error. Generally, this kind of situation requires manual intervention. When we see this, we make sure things indeed failed “past the end” of a binlog (i.e. the bit that didn’t get to the physical platter before the crash), reposition the slave to the next binlog, and use the Maatkit tools to ensure the slave is properly synced.
sync_binlog=1 is a problem in itself, because it makes the server not just do one fsync per commit, but several and that’s serious overhead. sync_binlog is actually not a boolean but a “fsync binlog every N commits” where 0 meaning “never”. So you could set it to 10 (fsync every 10 commits) and thus reduce the loss a little bit while not doing too much harm to performance. But it’s not ideal and won’t always prevent the above error.
|
Whats Included