MySQL Replication common SQL, application, file, process, Mode

MySQL Replication is commonly written when SQL, applications, files, processes, and modes are boring, after all, the built-in Replication is the pride of MySQL (I) SQL statement management of the master database section show master logs list the master database binary log show master status List the current master database binary log status show slave hosts list the slave database connected to the master database information show binlog events in 'Log _ name' lists the binary log index files for events in binary logs that are left empty by the reset master, create a new binary log purge master logs to 'Log _ name' purge master logs before 'date' to delete the binary log of the master database. We recommend that you delete the process: ① confirm the target log as follows: in the master database: show master logs in the slave Database: show slave status [each slave database is executed, capture the standby database with the maximum latency] ② back up ③ purge manage the standby database part change master to master _ ** tell the standby database how to connect to the primary database and play back its binary log parameters more complex, please refer to the manual reset slave to delete master.info, relay-log.info and all relay logs, and create a new relay log show slave status to view the current slave database status output more, the following describes Slave_IO_State, Slave_IO_Running, and Slave_ SQL _Running: the health status of the IO thread and the SQL thread. Master_Log_File: the name of the binary log of the master database that the IO thread is currently reading: in the binary log of the current master database, the position that the IO thread has read is Relay_Log_File: Relay_Log_Pos, the name of the relay log currently being read and executed by the SQL thread, where the SQL thread has read and executed Exec_Master_Log_Pos: where the binary log synchronized to the standby database can be computed with this output replication latency: Read_Master_Log_Pos-Exec_Master_Log_Pos: indicating the SQL thread latency, then, it indicates whether the master and slave nodes synchronize a point. The binary log coordinate is Position, minus Read_Master_Log_Pos: IO thread delay start slave Database SQL thread/IO thread stop slave Database SQL thread/IO thread (ii) Application Data Distribution → distribution of data hot backup to geographically isolated IDCs → copy is a technical supplement to backup, but it cannot replace backup read extension → Server Load balancer report analysis → without affecting the business of the master database, audit and report analysis at the end of the month can be put on the slave database for upgrade testing → use the latest mysql version for slave database failover → upgrade the slave database as the master database, minimize downtime (iii) file master.info records the information required to connect the slave database to the master database, such as the host, user name, password, and current binary log coordinate. At the same time, he can also tell the master database: "I need the content after a certain log location, please send me "relay-log.info to record the binlog index coordinates of the binary log and relay logs being copied by the current slave database to record binary log files on the master database disk relay log stores binary log events replicated by IO threads from the master database relay log index is used in the same way as binlog index (iv) Process (1) binary logs recorded in the master database: events are recorded in the order of transaction submission (2) the slave database copies the binary logs of the master database to the local relay log to start the IO thread and initiate the TCP/IP connection. The binlog dump thread is started in the master database to read the binary log IO thread records of the master database. read and replay binary log events from the relay log and master.info (3) slave database. (5) mode: STATEMENT and ROW are controlled by binlog_format. No mode can be used in any scenario. It is reasonable to say that MySQL can dynamically change the status in these two modes (binlog_format = 'mixed ') by default, STATEMENT is used for running. When the data cannot be correctly copied, the following lists their respective advantages and disadvantages. The advantages of the ROW mode are hardly the case where ROW-based replication cannot be processed, because the demand for strong serialization reduces CPU consumption, because it is not necessary to construct the SQL context information to better ensure that the quality of data copied to the slave database is faster, and to solve data inconsistency, such as when the modified row cannot be found, the ROW mode will stop the entire replication process, and STATEMENT will not be defective. You cannot determine what SQL black box is being executed, it is difficult to locate the fault location, occupying more disk space, more network bandwidth overhead, STATEMENT mode, advantages, row-based replication, the entire process is basically execution of SQL, which is easy to locate the problem. Disadvantages cannot be found. copy correctly, especially when stored procedures, triggers, functions, and so on are involved, this will lose the meaning of replication.