MySQL Replibeertion

MySQL Replibeertion was the last scheduled session on Day 2, but not withstanding there was free beer (a lot of), there was a serious side with a Replication Discussion.

One of the first questions by Jeremy was “Are the any big replication users?” to which Sheeri quickly replied “Are you calling me fat again”.

This was a highly interactive session, here are some of the points from the audience.

Some Uses of Replication

  • Backup
  • Hot standby
  • Scaling
  • Data Warehousing
    • Slaves are larger then your database
  • For no special reason
  • Consolidation of multiple sources
  • Support for multiple indexes

Issues

  • Can break
  • replication lag
  • bi-directional replication is not supported
  • hard to setup replication/initialization upto point to run one command
  • hard to know when the slave is out of sync (working but broken) diagnose
  • does not manage binary logs for you (max-bin-logs )
  • no row level replication (5.1 row based replication, change based replication, good and bad )
  • Serialized execution on the slave
  • Master does not keep track of the slaves (to the master, the slave is just another connection) Jeremy comment “it really really sucks in production systems.”
  • No multi-master replication. A slave can not have more then one master.
  • ring replication. No idea when something breaks what is right.
  • no ability for delayed duplication.
  • no way to get binlogs back. (manually twink the info file)
  • Master doesn’t care what data is on the slave.
  • A replication backup is really only good for the restoration of that machine
  • Default reconnection timeout is way, way to high (default of 1 minute). It should be at most 1 second with exponential fallback. (master-connect-retry), no fallback, no max number of retries, logged in error log every time.

Feature Results (Things replication needs, what you want to see)

  • Delayed Replication
  • Registered Salves in the Master
  • Import Binlog
  • Checksum Table Events (Need ability for table checksum to be added to binlog periodically so it can be checked by the slave.)
  • Global Sequence Number
  • Connect Retry Exponential Backoff
  • Hetrogous Replication (Oracle to MySQL). Golden Gate Software has a commercial offering
  • Command Exclusion List (sql_log_bin=0)
  • Replication filters by data on the slave
  • Show upcoming queries, skip query
  • Multi-Master to one slave
  • piping mysqlbin log commands into the mysql client fails for some character sets.
  • Binlog index capability
  • Checksum of Binary Events to determine a command is valid
  • command line interface in mysqllog so you could go backwards and forwards, then execute commands.

Check out more at Google Code Blog.

Comments