Tag: Replication

Impressions from MySQL conf 2011, part III: BoF Replication

MySQL

This post continues my impressions from some of the talks I’ve been to. I’ll dedicate this post to a single session.

Wednesday

  • BoF: Replication, Lars Thalmann, Mat Keep (Oracle)

Lars Thallman presented the MySQL 5.5 replication features, as well as the expected 5.6 features. Among other features, one could notice parallel replication, binlog checksums, sub-second resolution and more. There was an open discussion about these features, asking for comments; looking for new ideas and suggestion from the audience.

I can’t possibly cover it all. I’ll note two discussion I participated in, and which have interested me. This also serves for noting down to myself my ideas and thoughts. Continue reading » “Impressions from MySQL conf 2011, part III: BoF Replication”

Recovering a MySQL `root` password: the fourth solution

MySQL

Have just read Darren Cassar’s Recovering a MySQL `root` password – Three solutions. There’s a fourth solution: using an init-file, which leads to just one restart of the database instead of two. It also avoids the security issue involved with using skip-grant-tables.

I’ve written all about it before on Dangers of skip-grant-tables.

Darren’s 1st advice (look for password ini files, scripts, etc.) is a very good one. One password that can always be looked up in files is the replication’s password.

Replication’s password is easily forgotten: you only set it once and never use it again; never script it nor manually login with. When setting up new slaves, though, you suddenly need it.

Apparently not many realize that the replication password is written in plaintext in the master.info file. This file tells the slave all about it’s master connection: host, port, user & password are all there for you to read.

Upgrading to Barracuda & getting rid of huge ibdata1 file

MySQL

Some of this is old stuff, but more people are now converting to InnoDB plugin, so as to enjoy table compression, performance boosts. Same holds for people converting to Percona’s XtraDB. InnoDB plugin requires innodb_file_per_table. No more shared tablespace file.

So your ibdata1 file is some 150GB, and it won’t reduce. Really, it won’t reduce. You set innodb_file_per_table=1, do ALTER TABLE t ENGINE=InnoDB (optionally ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8), and you get all your tables in file-per-table .ibd files.

But the original ibdata1 file is still there. It has to be there, don’t delete it! It contains more than your old data.

InnoDB tablespace files never reduce in size, it’s an old-time annoyance. The only way to go round it, if you need the space, is to completely drop them and start afresh. That’s one of the things so nice about file-per-table: an ALTER TABLE actually creates a new tablespace file and drops the original one.

The procedure

The procedure is somewhat painful:

openark-kit (rev. 170): new tools, new functionality

Development MySQL

I’m pleased to announce a new release of the openark kit. There’s a lot of new functionality inside; following is a brief overview.

The openark kit is a set of utilities for MySQL. They solve everyday maintenance tasks, which may be complicated or time consuming to work by hand.

It’s been a while since the last announced release. Most of my attention was on mycheckpoint, building new features, writing documentation etc. However my own use of openark kit has only increased in the past few months, and there’s new useful solutions to common problems that have been developed.

I’ve used and improved many tools over this time, but doing the final cut, along with proper documentation, took some time. Anyway, here are the highlights:

New tool: oak-hook-general-log

oak-hook-general-log hooks up a MySQL server and dumps the general log based on filtering rules, applying to query role or execution plan. It is possible to only dump connect/disconnect entries, queries which make a full table scan, or use temporary tables, or scan more than X number of rows, or…

I’ll write more on this tool shortly.

New tool: oak-prepare-shutdown

This tool makes for an orderly and faster shutdown by safely stopping replication, and flushing InnoDB pages to disk prior to shutting down (keeping server available for connections even while attempting to flush dirty pages to disk). A typical use case would be:

oak-prepare-shutdown --user=root --ask-pass --socket=/tmp/mysql.sock && /etc/init.d/mysql stop

New tool: oak-repeat query

oak-repeat-query repeats executing a given query until some condition holds. The condition can be:

  • Number of given iterations has been reached
  • Given time has elapsed
  • No rows have been affected by query

The tool comes in handy for cleanup jobs, warming up caches, etc. Continue reading » “openark-kit (rev. 170): new tools, new functionality”

MMM for MySQL single reader role

MySQL

The standard documentation and tutorials on MMM for MySQL, for master-master replication setup, suggest one Virtual IP for the writer role, and two Virtual IPs for the reader role. It can be desired to only have a single virtual IP for the reader role, as explained below.

The two IPs for the reader role

A simplified excerpt from the mmm_common.conf sample configuration file, as can be found on the project’s site and which is most quoted: Continue reading » “MMM for MySQL single reader role”

Table refactoring & application version upgrades, Part II

Development MySQL

Continuing Table refactoring & application version upgrades, Part I, we now discuss code & database upgrades which require DROP operations. As before, we break apart the upgrade process into sequential steps, each involving either the application or the database, but not both.

As I’ll show, DROP operations are significantly simpler than creation operations. Interestingly, it’s the same as in life.

DROP COLUMN

A column turns to be redundant, unused. Before it is dropped from the database, we must ensure no one is using it anymore. The steps are:

  1. App: V1 -> V2. Remove all references to column; make sure no queries use said column.
  2. DB: V1 -> V2 (possibly failover from M1 to M2), change is DROP COLUMN.

DROP INDEX

A possibly simpler case here. Why would you drop an index? Is it because you found out you never use it anymore? Then all you have to do is just drop it.

Or perhaps you don’t need the functionality the index supports anymore? Then first drop the functionality:

  1. (optional) App: V1 -> V2. Discard using functionality which relies on index.
  2. DB: V1 -> V2 (possibly failover from M1 to M2), change is DROP INDEX. Check out InnoDB Plugin here. Continue reading » “Table refactoring & application version upgrades, Part II”

Table refactoring & application version upgrades, Part I

Development MySQL

A developer’s major concern is: How do I do application & database upgrades with minimal downtime? How do I synchronize between a DB’s version upgrade and an application’s version upgrade?

I will break down the discussion into types of database refactoring operations, and I will limit to single table refactoring. The discussion will try to understand the need for refactoring and will dictate the steps towards a successful upgrade.

Reader prerequisites

I will assume MySQL to be the underlying database. To take a major component out of the equation: we may need to deal with very large tables, for which an ALTER command may take long hours. I will assume familiarity with Master-Master (Active-Passive) replication, with possible use of MMM for MySQL. When I describe “Failover from M1 to M2“, I mean “Make the ALTER changes on M2 (passive), then switch your application from M1 to M2 (change of IPs, VIP, etc.), promoting M2 to active position, then apply same changes on M1 (now passive) or completely rebuild it”.

Phew, a one sentence description of M-M usage…

I also assume the reader’s understanding that a table’s schema can be different on master & slave, which is the basis for the “use replication for refactoring” trick. But it cannot be too different, or, to be precise, the two schemata must both support the ongoing queries for the table.

A full discussion of the above is beyond the scope of this post.

Types of refactoring needs

As I limit this discussion to single table refactoring,we can look at major refactoring operations and their impact on application & upgrades. We will discuss ADD/DROP COLUMN, ADD/DROP INDEX, ADD/DROP UNIQUE INDEX, ADD/DROP FOREIGN KEY, ADD/DROP TABLE.

We will assume the database and application are both in Version #1 (V1), and need to be upgraded to V2 or greater. Continue reading » “Table refactoring & application version upgrades, Part I”

Replication configuration checklist

MySQL

This post lists the essential and optional settings for a replication environment.

It does not explain how to create replicating slaves. See How To Setup Replication for that. However, not all configuration options are well understood, and their roles in varying architectures can change.

Here are the settings for a basic Master/Slave(s) replication architecturee.

Essential

  • log-bin: enable binary logs on the master. Replication is based on the master logging all modifying queries (INSERT/CREATE/ALTER/GRANT etc.), and the slaves being able to replicate them.
  • server-id: each machine must have a unique server-id. A slave will not replay queries originating from a server with the same server-id as its own.
  • GRANT: grant a user with REPLICATION SLAVE. The host list must include all replication slave hosts.
  • expire-logs-days: automatically clean up master’s binary logs older than given value. By default, binary logs are never removed.

When working with Master/Slaves replication, one should be prepared to master failure and slave promotion to master. It may be desirable to identify a particular slave as primary candidate for promotion.

Continue reading » “Replication configuration checklist”

Quick reminder: avoid using binlog-do-db

MySQL

Nothing new about this warning; but it’s worth repeating:

Using binlog-do-db is dangerous to your replication. It means the master will not write to binary logs any statement not in the given database.

Ahem. Not exactly. It will not write to binary logs any statement which did not originate from the given database.

Which is why a customer, who was using Toad for MySQL as client interface to MySQL, and by default connected to the mysql schema, did not see his queries being replicated. In fact, he later on got replication errors. If you do:

USE test;
INSERT INTO world.City VALUES (...)

Then the statement is assumed to be in the test database, not in the world database.

Slightly better is using replicate-do-db on the slave machines. At least we allow the master to write everything. But still, for the same reasons, slaves may fail to repeat a perfectly valid query, just because it has been issued in the context of the wrong database. replicate-ignore-db is somewhat safer yet, but the trap is still there.

My advice is that replication should replicate everything. Make sure you and everyone else you work with understand the implications of binlog-do-db and replicate-do-db before implementing it.

Defined your MySQL backup & recovery plan recently?

MySQL

Following up on Ronald Bradford‘s Checked your MySQL recovery process recently? post, I wish to add a prequel.

To see whether you have a clear definition of your backup requirements, ask yourself these questions:

  • Is there a backup/restore plan?
  • Is there a written backup/restore plan?
  • How fast do you need to recover a backup? What’s the longest downtime you will allow from the point of failure to the point of restored, functional database?
  • How much data are you willing to lose in case of crash? A second’s worth of data? An hour’s worth? A day’s worth? None?
  • Are you willing to allow that the database becomes read-only when taking the backup? Or completely down?
  • Are you willing to take the risk that the backup will not be 100% compatible with the data? (Backing up your slave holds this risk)
  • Is your manager willing to all that you are willing?
  • Is the backup plan known to the management team, or do they just know that “the database has backups“?

The above checklist is by no means complete.

I have a vivid memory of a very good sys admin who failed on the last two points. He had some very sour days when recovering a lost file from tape took much longer than was affordable to some contract.

I found that technical people rarely share the same views as marketing/management. Make sure to consult with the management team; they will have a clearer view on what the company can afford and what it cannot afford.