Shulou(Shulou.com)06/01 Report--

Each binary log format has advantages and disadvantages. For most users, hybrid replication formats provide the best combination of data integrity and performance. However, if you want to take advantage of features specific to statement-based or row-based replication formats when performing certain tasks, you can use the information in this section, which summarizes their relative advantages and disadvantages to determine which best suits your needs

Advantages of statement-based replication Mature technologies write less data to log files. When updates or deletions affect many rows, this results in a significant reduction in the storage space required for log files. This also means that retrieval and recovery from backups can be done much faster. The log file contains all the statements that make any changes, so it can be used to audit the database. Disadvantages of statement-based replication Statements that are unsafe for SBR. Not all statements that modify data (such as INSERT, UPDATE, and REPLACE statements) can be replicated using statement-based replication. When using statement-based replication, any nondeterministic behavior is difficult to replicate. Examples of such data modification language (DML) statements include statements that depend on UDFs or nondeterministic stored procedures because the values returned by such UDFs or stored procedures depend on factors other than parameters supplied to them. (Row-based copy, however, simply copies the values returned by UDFs or stored programs, so its impact on table rows and data is the same on both master and slave.) For more information see Section 16.4.1.16, "Replication of Invoked Features" Delete and UPDATE statements without LIMIT clauses of ORDER BY are indeterminate. For more information, see Section 16.4.1.17, "Replication and LIMIT". Deterministic UDFs must be applied to slaves Using statement-based replication does not replicate statements correctly using any of the following methods: LOAD_FILE()UUID(), UUID_SHORT()USER()FOUND_ROWS()SYSDATE()(unless both the master and the slave are started with the --sysdate-is-now option)GET_LOCK()IS_FREE_LOCK()IS_USED_LOCK()MASTER_POS_WAIT()RAND()RELEASE_LOCK()SLEEP()VERSION() BUT YES, All other functions are copied correctly using statement-based copying, including NOW(), etc. For more information, see Section 16.4.1.15, "Replication and System Functions". Statements that do not replicate correctly using statement-based replication log a warning as shown below [Warning] Statement is not safe to log in statement format. In this case, a similar warning is issued to the client. The client can use SHOW WARNINGS to display it INSERT... SELECT requires more row-level locks than row-based replication UPDATE statements requiring table scans (because no indexes are used in the WHERE clause) must lock more rows than row-based replicationFor InnoDB: INSERT statements using AUTO_INCREMENT block other non-conflicting INSERT statements For complex statements, the statement must be evaluated and executed on slave before updating or inserting rows. For row-based replication, slave only needs to modify the affected rows, rather than executing the full statement. If errors occur in the evaluation of slaves, especially when executing complex statements, statement-based replication may gradually increase the error margin of affected rows over time, see Section 16.4.1.28, "Slave Errors During Replication Store function executes with the same NOW() value as the calling statement. However, the storage process is not like this. Table definitions must be (almost) identical on master and slave. For more information see Section 16.4.1.10, "Replication with Differing Table Definitions on Master and Slave" Benefits of row-based replication All changes can be replicated. This is the safest form of replication. Note: Statements that update information in mysql databases (such as GRANT, REVOKE, and trigger operations, stored routines (including stored procedures), and views) are copied onto slaves using statement-based replication. SELECT, the CREATE statement is generated from the table definition and copied using a statement-based format, and the row insert is copied using a row-based format. Fewer row locks on master are required for higher concurrency for statements of the following types INSERT... SELECT INSERT statements with AUTO_INCREMENTUPDATE or Delete statements with WHERE clauses that do not use keys or do not change most of the examined rows. Disadvantages of row-based replication RBR generates more data that must be logged. To replicate a DML statement, such as an UPDATE or Delete statement, statement-based replication writes only the statement to the binary log. Row-based replication, in contrast, writes each changed row to a binary log. Row-based replication may write more data to the binary log if the statement changes many rows; this is true even for rolled back statements. This also means that it may take more time to make and restore backups. In addition, binary logs are locked for longer periods of time to write data, which can cause concurrency problems. Use binlog_row_image = minimal to significantly reduce defects. Deterministic UDFs that generate large BLOB values using row-based replication take longer to replicate than statement-based replication. This is because BLOB column values are recorded, not statements that generate data. You cannot see statements received and executed from master on slave. However, you can use mysqlbinlog with the--base64-output = DECODE-ROWS and--verbose options to see what data has changed. Alternatively, use the binlog_rows_query_log_events variable, which, if enabled, adds the statement to the Rows_query event output by mysqlbinlog when using the-vv option. For tables that use the MyISAM storage engine, slaves of INSERT statements require stronger locks when they are applied to binary logs as row-based events rather than as statements. This means concurrent inserts on MyISAM tables are not supported when row-based replication is used.

Reference link: dev.mysql.com/doc/refman/5.7/en/replication-sbr-rbr.html

PREV:6: Implementation of Multi-Source Replication https://blog.51cto.com/itzhoujun/2353940

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.