MySQL repication
1MySQL repicationprinciple The write operation of the front-end user, or the database modification operation, is logged to the binary log file, save as an event, Master sends Binlog to slave MySQL server via port 3306, slave MySQL server will binlog save to relay log, Read the relay log and execute it again to write the data to disk
1.1 Master-slave 650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/83/98/wKioL1d3r0fSbTtVAABA_BllLwI578.png "border=" 0 "name=" "" 359 "height=" 236 ">
1.2 A master multi-slave, multi-level replication 650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M02/83/9A/wKiom1d3r0eQtqEhAACzu56aN8o705.png " Border= "0" name= "" "515" height= "339" style= "FONT-SIZE:13PX; line-height:1.5; " >
2MySQL repicationCongenital defects MySQL a query can only be executed on one CPU,If the master has multiple CPUs and executes multiple transactions at the same time, it is faster for multiple transactions to write to the database, but the binary logs are cached in memory and a single piece is synchronized from the cache to the binary log file after a period of time. At this point, the binary log will be synchronized to the top, salve from the log a read event, and execute, so that the main speed than from a lot faster
3mysql repication modeAsync: Default mode, Master local execution succeeds, that is success; Regardless of whether the binary logs are sent to slavesemi-synchronous: In a master multi-slave mode, Master sends a binary log to multiple slave, but only one slave receives success, which means that the semi-synchronous is successful, similar to synchronization. MySQL 5.5 supports semi-sync after. synchronization: Master executes a statement, the master binary log is synchronized to the slave trunk log, slave reads the trunk log file and executes successfully, and the return information informs Master that the execution was successful. sync mode, MySQL will be much slower and the front-end application will wait a long time.
4 MySQL Replication effect1, slave offline to do cold backup, hot preparation is more complex. 2, high availability, when the main failure, from a little processing can be online work3, remote disaster tolerance4, Scale out: share read load, a master more from, master write from Read
5 in MySQL master and slave, do not use the MySQL agent, how to implement master is responsible for writing, Slave is responsible for reading? (Take Discuz as an example) PHP module itself does not interact with MySQL, and MySQL interaction is discuz, let Discuz solve read and write problems; Dual Master Model, it can reduce the pressure of the server to read the data, cannot alleviate the write operation, and it is prone to repeat the binary log, so it is less used.
6 master and Slave, a master and many from, the two masters can not reduce the pressure of a server write; When a server cannot withstand write pressure: 1) scale on, increase its configuration 2) scale out, data splitting (vertical split, horizontal split) 650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M02/83/9A/wKiom1d3r0eT8FI1AAAb5QLy-ck709.png "border=" 0 "name=" "" 414 "height=" 183 " Style= "FONT-SIZE:13PX; line-height:1.5; " >650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/83/98/wKioL1d3r0fyoghMAAAuO7f5hiE554.png "border=" 0 "Style=" FONT-SIZE:13PX; line-height:1.5, "Name=" "" 267 "height=" 229 ">
7 MySQL read-write separation implementation MYSQL-PROXYAMOEBA8 data splitting cobar9 mysql log binary log bin LPG storage location: Data directory under MYSQL-BIN.XXXXXXXX
Scroll: 1 to reach maximum on line; 2 Flush logs;3 Server restartClear Binary Log command: Purgeformat: statement, statement-based, not recommended Row, line based, recommended for use Mixed, mixed mysql-bin.index: Binary log file Index fileView the binary log files currently used by MySQL: Show Master stattus;View binary log file contents: Show Binlog Events in "file";
View a list of binary log files: show binary logs;
Binary logging events contain elements: timestamp,position (offset), Event,server-id
Binary logs can be used for point-in-time restore, but it is not an alternative to data backup?
when recovering data using binary logs, there is no guarantee that the data will be exactly the same as before. Multiple CPUs process data in parallel, but log is written serially.
The transaction log error log, general query log relay logSlow Query Log
From for notes (Wiz)
MySQL master-slave replication-concepts and architecture
