I've uploaded MySQL Replication Manager's (mysqlreplicationmanager) screenshot and Screencast

Screenshot

Video / Screencast


You can download a full size video from here.

read more

A well thought backup saved my skin last Saturday.
It's a simple setup: many copies. Using MySQL replication, the master is for writes.
Four slaves for reads. One slave for backups only (*). In a different server room. In a different building. (**)
The backup slave has a  cron job, which stops the slave, makes a dump, removes the oldest one, and resumes replication.
The same job works hourly (keeps 30 dumps), daily (keeps 7 dumps), and weekly (keeps 8 dumps).
The disaster occurred yesterday. A colleague who was working too much (***) made a destructive query on the wrong server. He thought he was using the development server, but it turned out to be the master. Fortunately, nobody else was working on a Saturday, so there weren't any changes, besides his. I zeroed the database on the master and reloaded the latest hourly dump. No suffering. No bad …

The slave failed with the error that the relay binlog is corrupt. It had copied close to 12 binlogs from the master and they were yet to be applied. Unfortunately those binlogs have been purged on the master. Now to sync up cleanly we might have to refresh data from the master which can be costly since it was a 290 GB database. We had the option of shutting down the server. We thought we can try our luck with a crazy hack. We shutdown the server. Tried reading the binlog using mysqlbinlog utility from the corrupt position. It failed as expected. Then we tried reading from the next immediate position and it went through fine. Now we had a proof that our hack might work. We opened the relay-log.info and incremented the second row by a value of one. Then we started the server. Boom, the slave started running and we were saved from a great pain of resyncing the slave.

PS : We might have missed one transaction in this hack, but that was ok for …

I wonder what the best way is to get a feature request more visibility (convert a feature request to an actual work item).We use replicate-do-db on all our slave servers , so after many, many, restarts of our slave servers, I checked the bug list for any feature requests surrounding this, and about 6 months ago, one was opened: replicate-* rules should be dynamically configurableSadly,

Welcome to Tip #1 in MySQL Replication Series. In this tip we will go over what to do when you only want to replicate certain data to slave(s). Most general way to tell what is replicated to which slave is to include following configuration directive in my.cnf file depending on your environment and your goals. We will start with slave server side options since you have more flexibility on slave on what to replicate and what not to.

Slave server options:

• replicate-do-db = dbname (or) replicate-do-db = dbname1, dbname2, …, dbnameN
  This option is used on slave server to tell the server to only replicate dbname db on this particular host. You would want to use this if you have a master which is replicating to multiple slaves and each slave may contain different database for read performance reasons.
• replicate-ignore-db = dbname (or) …

Setting up replication is not hard and can be done by following simple directions. You can learn setting up two type of replication MySQL offers here: master-master replication and master-slave replication. Once you have replication set up, you can start playing with it without doing any more changes to the configuration. But let us face it, using replication in production environment and for sites which are either going live or are live, requires deeper knowledge of how replication can be used and what it can (or can not) do. We will start this “MySQL Replication Series” with briefly going over how MySQL replication works. Later in the series I will be posting some tips on …

I know I’m a little late to the discussion, but Brian Aker posted a thought-provoking piece on the imminent death of MySQL replication to scale reads.  His premise is that memcached is so cool and scales so much better, that read replication scaling is going to become a think of the past.  Other MySQL community people, like Arjen and Farhan, chimed in too.

Now, I love memcached.  We use it as a vital layer in our datacenters, and we couldn’t live without it.  But it’s not a total solution to all …

Today is the first day at the conference (aside from the tutorials, which were yesterday). Here’s what I went to:

New Subquery Optimizations in 6.0

By Sergey Petrunia. This was a similar session to one I went to last year. MySQL has a few cases where subqueries are badly optimized, and this session went into the details of how this is being addressed in MySQL 6.0. There are several new optimization techniques for all types of subqueries, such as inside-out subqueries, materialization, and converting to joins. The optimizations apply to scalar subqueries and subqueries in the FROM clause. Performance results are very good, depending on which data you choose to illustrate. The overall point is that the worst-case subquery nastiness should be resolved. I’m speaking of WHERE NOT IN(SELECT…) and friends. It remains to be seen how this shakes out as 6.0 matures, and what edge cases will pop up.

The Lost Art Of the Self …

Properties:

Brian Aker has found general agreement with his post: "The Death of Read Replication".

Arjen Lentz says "I think Brian is right...", and Frank Mash confirmed: "what Brian says about replication, caching and memcached is very true".

Just like Video killed the Radio Star it looks like maybe Memcached killed the Replication Hierarchy!

But of course, Brian and others are talking about replication for scaling reads.

In my session on PBXT next week at the conference I will be talking about how we plan …