MySQL ReplicationLinux教学课件.ppt

马哥教育 MySQL Replication Include MySQL 5.6 MySQL Replication 4 多级复制 读写分离 垂直拆分 scale out 分库 scale on rid, price: amoeba lamp: PHP server-id MySQL Replication Common Use Cases More Advanced Use Cases MySQL 5.6 Advanced Replication Features MySQL Utilities Summary What is Replication? For the purposes of this paper we define “replication” as the duplication of data to one or more locations These can be co-located within a data center, or geographically distributed Replication enables a database to copy or duplicate changes from one physical location or system to another Replication Happens at MySQL level, not Storage EngineLevel(*NDB) Asynchronous!(Semi-syncavailablein5.5) A server can have only 1 master IO Thread: Fetches from master SQL Thread: Executes on slave Single Threaded Execution (Expect enhancements in 5.6) Different schema’s are possible between master and slave (Watchout!!): different indexes storage engines data types columns Binary Logs Set of files Contains all writes and schema changes != REDO/Transaction log Rotated when full (Set max_binlog_size) Incrementing numbers (000001, 000002, 000003, ...) Relay Logs are also binary logs 2 Formats: Statement Based (SBR) Row based (RBR, since mysql 5.1) mixed MySQL supports two kinds of replication: statement-based replication and row-based replication Statement-based (or “logical”) replication has been available since MySQL 3.23, and it’s what most people are using in production today Row-based replication is new in MySQL 5.1 Both kinds work by recording changes in the master’s binary log and replaying the log on the slave MySQL’s replication is mostly backward compatible. That is, a newer server can usually be a slave of an older server without trouble Replication is relatively good for scaling reads, which you can direct to a slave, but it’s not a good way to scale writes unless you design it right Attaching many slaves to a master simply causes the writes to be done many times, once on ea