1 / 27

Liberating Your Data From MySQL: Cross-Database Replication to the Rescue!

Liberating Your Data From MySQL: Cross-Database Replication to the Rescue!. Robert Hodges and Linas Virbalas Continuent, Inc. Introductions What is Tungsten? A Word About MySQL Replication Tungsten’s MySQL to PostgreSQL Replication Demo! Questions and Answers. Agenda. Introductions.

urania
Download Presentation

Liberating Your Data From MySQL: Cross-Database Replication to the Rescue!

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Liberating Your Data From MySQL: Cross-Database Replication to the Rescue! Robert Hodges and Linas Virbalas Continuent, Inc.

  2. Introductions What is Tungsten? A Word About MySQL Replication Tungsten’s MySQL to PostgreSQL Replication Demo! Questions and Answers Agenda

  3. Introductions

  4. About Continuent Our Value: Make open source as robust as commercial databases Enterprise capability Cloud flexibility Open source cost Our Solution: Tungsten Database Clustering Our Company: Founded in Finland, HQ in USA Venture-backed US/European presence Deep experience in databases and distributed systems

  5. What is Tungsten and How Does It Work?

  6. What Is Tungsten? Tungsten creates data services using off-the-shelf open source databases to: Ensure reliable, fast access to data Simplify common management tasks Enable disaster recovery …Without changing applications

  7. Tungsten 1.3 Data Service Architecture DBMS DBMS DBMS Replicator Replicator Replicator Manager Manager Manager Apache/Mod_PHP Apache/Mod_PHP libmysqlclient.a libmysqlclient.a Connector Connector Master Slave Slave

  8. Technology: Replication Pipelines Pipeline Tungsten Replicator Process Stage Stage Extractor Filters Applier Extractor Filters Applier THL Master THL (via network) Slave DBMS Transaction History Log

  9. So Why Are We Here? Tungsten can replicate in real time from MySQL to PostgreSQL

  10. Why Would You Want to Do That? Feed MySQL merchant transactions to PostgreSQL applications Feed PG-based data warehouse and reporting Migrate MySQL data to PG with minimum downtime Plus Scott McNealy says it’s good to share PG App MySQL App 1 MySQL App 2 MySQL App 3

  11. A Word About MySQL Replication

  12. Writeable slaves Tungsten for MySQL You can both read and write to the DB operating in slave mode This, potentially, allows you to break consistency In some scenarios that is a plus Tungsten for PostgreSQL In Streaming Replication you can read from slaves Still, you cannot write to them In effect, you cannot have different data/structure on a slave

  13. Statement and Row Based Replication • Statement Based Replication • Row Based Replication CREATE TABLE t (id INT, name VARCHAR(12)); ALTER TABLE t ADD COLUMN id2 INT; INSERT INTO t VALUES (1, ‘Sun’, 999); UPDATE t SET name = ‘Moon’; CREATE TABLE t (id INT, name VARCHAR(12)); ALTER TABLE t ADD COLUMN id2 INT; { INSERT, t, (1, ‘Sun’, 999) }; { UPDATE, t, id == 1, (NULL, ‘Moon’, NULL) };

  14. Logical vs. Physical Replication

  15. TungstenMySQL -> PostgreSQLReplication

  16. MySQL -> PostgreSQL: Who’s Who? MySQL setup to run as MySQL master PostgreSQL setup to accept writes – Continuous Recovery is OFF Master DB Bin Logs (Binlogsenabled) Slave DB

  17. MySQL -> PostgreSQL: Tungsten Replicator Doing the Job Tungsten Replicator Tungsten Replicator Platform-Independent SQL Events Master DB Log Record JDBC SQL Requests Bin Logs (Binlogs enabled) Slave DB

  18. MySQL -> PostgreSQL: Tungsten Replicator Components Master Replicator Slave Replicator Platform-Independent SQL Events Transaction History Log Transaction History Log Filters Filters Master DB MySQL Extractor PostgreSQL Applier Log Record JDBC SQL Requests Bin Logs (Binlogs enabled) Slave DB

  19. MySQL -> PostgreSQL: Challenges Data Type Differences Default (Implicitly Defined) Schema Selection SQL Dialect Differences Statement Replication vs. Row Replication Character Sets and Binary Data

  20. MySQL -> PostgreSQL: Data Types • Note the type differences between MySQL and PG

  21. MySQL -> PostgreSQL: Default Schema • MySQL: Trivial to use `USE` • MySQL: Going without `USE` generates different events • PG: Extract the default schema from the event • PG: Set it before applying

  22. MySQL -> PostgreSQL: SQL Dialect • Differences between DDL and DML statement SQL dialects • Row Replication resolves issues rising from differences in DML, but still leaves DDL to handle • Tungsten Replicator Filters come to the rescue! • Simple to develop Java or JavaScript extensions • Event structure IN -> Filter -> Event structure OUT

  23. MySQL -> PostgreSQL: Charsets • Statement replication: MySQL syntax is “permissive” • Embedded binary / alternate charsets • Different charsets for different clients • Row replication: database/table/column charsets may differ • Answer: Stick with one character set throughout; use row replication if you need to move binary data

  24. MySQL-> PostgreSQL: What’s Left? We have covered the basics but there is more… Initial provisioning from MySQL to PostgreSQL Data transformation Making it go *really* fast (think parallel replication)

  25. DEMO

  26. Q & A

  27. Contact Information HQ and Americas 560 S. Winchester Blvd., Suite 500 San Jose, CA 95128 Tel (866) 998-3642 Fax (408) 668-1009 e-mail: sales@continuent.com EMEA and APAC Lars Sonckin kaari 1602600 Espoo, FinlandTel +358 50 517 9059Fax +358 9 863 0060 Continuent Web Site: http://www.continuent.com

More Related