mysql replication介绍
本来想翻译一下的，但是想到自己的渣渣英语还是算了，能看个大概就行！
1.二进制日志格式
1.1 When using statement-based binary logging, the master writes SQL statements to the binary log. Replication of the master to the slave works by executing the SQL statements on the slave. This is called statement-based replication (often abbreviated as SBR), which corresponds to the standard MySQL statement-based binary logging format. Replication capabilities in MySQL version 5.1.4 and earlier used this format exclusively.
1.2 When using row-based logging, the master writes events to the binary log that indicate how individual table rows are changed. Replication of the master to the slave works by copying the events representing the changes to the table rows to the slave. This is called row-based replication (often abbreviated as RBR). In row-based replication, the master writes events to the binary log that indicate how individual table rows are changed.
1.3 You can also configure MySQL to use a mix of both statement-based and row-based logging, depending on which is most appropriate for the change to be logged. This is called mixed-format logging. When using mixed-format logging, a statement-based log is used by default. Depending on certain statements, and also the storage engine being used, the log is automatically switched to row-based in particular cases. Replication using the mixed format is often referred to as mixed-based replication or mixed-format replication. For more information, see Mixed Binary Logging Format.
1.4 mysql5.6默认使用的是statement格式；
1.5 You should note that MySQL Cluster Replication always uses row-based replication；
1.6 The logging format in a running MySQL server is controlled by setting the binlog_format server system variable.
1.7 To change the global or session binlog_format value, you must have the SUPER privilege.
1.8 If you are using InnoDB tables and the transaction isolation level is READ COMMITTED or READ UNCOMMITTED, only row-based logging can be used. It is possible to change the logging format to STATEMENT, but doing so at runtime leads very rapidly to errors because InnoDB can no longer perform inserts.
2.replication configuration
2.1 GTID即global transaction identifiers
2.1.1 When using GTIDs, each transaction can be identified and tracked as it is committed on the originating server and applied by any slaves;this means that it is not necessary when using GTIDs to refer to log files or positions within those files when starting a new slave or failing over to a new master, which greatly simplifies these tasks. You can use either statement-based or row-based replication with GTIDs ; however, for best results, we recommend that you use the row-based format.
1.1.2 A GTID is represented as a pair of coordinates, separated by a colon character (:), as shown here:
GTID = source_id:transaction_id
The source_id identifies the originating server. Normally, the server's server_uuid is used for this purpose. The transaction_id is a sequence number determined by the order in which the transaction was committed on this server;
2.1.3 This format is used to represent GTIDs in the output of statements such as SHOW SLAVE STATUS as well as in the binary log. They can also be seen when viewing the log file with mysqlbinlog --base64-output=DECODE-ROWS or in the output from SHOW BINLOG EVENTS.
2.1.4 The generation and lifecycle of a GTID consists of the following steps(GTID的生成和生命周期):
(1)A transaction is executed and committed on the master.
This transaction is assigned a GTID using the master's UUID and the smallest nonzero transaction sequence number not yet used on this server; the GTID is written to the master's binary log (immediately preceding the transaction itself in the log).
(2)After the binary log data is transmitted to the slave and stored in the slave's relay log (using established mechanisms for this process), the slave reads the GTID and sets the value of its gtid_next system variable as this GTID. This tells the slave that the next transaction must be logged using this GTID.
(3)The slave sets gtid_next in a session context.
The slave checks to make sure that this GTID has not already been used to log a transaction in its own binary log. If and only if this GTID has not been used, the slave then writes the GTID and applies the transaction (and writes the transaction to its binary log). By reading and checking the transaction's GTID first, before processing the transaction itself, the slave guarantees not only that no previous transaction having this GTID has been applied on the slave, but also that no other session has already read this GTID but has not yet committed the associated transaction. In other words, multiple clients are not permitted to apply the same transaction concurrently.
(4)Because gtid_next is not empty, the slave does not attempt to generate a GTID for this transaction but instead writes the GTID stored in this variable—that is, the GTID obtained from the master—immediately preceding the transaction in its binary log.
2.2 Setting Up Replication Using GTIDs
The key steps in this startup process for the simplest possible GTID replication topology—consisting of one master and one slave—are as follows:
(1)If replication is already running, synchronize both servers by making them read-only.
(2)Stop both servers.
(3)Restart both servers with GTIDs, binary logging, and slave update logging enabled, and with statements that are unsafe for GTID-based replication disabled. In addition, the servers should be started in read-only mode, and the slave SQL and I/O threads should be prevented from starting on the slave.
The mysqld options necessary to start the servers as described are discussed in the example that follows later in this section.
(4)Instruct the slave to use the master as the replication data source and to use auto-positioning, and then start the slave.
The SQL statements needed to accomplish this step are described in the example that follows later in this section.
(5)Disable read-only mode on both servers, so that they can once again accept updates.
For Example:
示例前提：
two servers are already running as master and slave, using MySQL's “classic” file-based replication protocol.
Most of the steps that follow require the use of the MySQL root account or another MySQL user account that has the SUPER privilege. mysqladmin shutdown requires either the SUPER privilege or the SHUTDOWN privilege.
Step 1: Synchronize the servers. Make the servers read-only.
mysql> SET @@global.read_only = ON;
Step 2: Stop both servers. Stop each server using mysqladmin；user having sufficient privileges to
shut down the server；
shell> mysqladmin -u username -p shutdown
Step 3: Restart both servers with GTIDs enabled；
shell> mysqld_safe --gtid_mode=ON --log-bin --log-slave-updates --enforce-gtid-consistency &
（Prior to MySQL 5.6.9, --enforce-gtid-consistency was named --disable-gtid-unsafe-statements）
Step 4: Direct the slave to use the master.
mysql> CHANGE MASTER TO
> MASTER_HOST = host,
> MASTER_PORT = port,
> MASTER_USER = user,
> MASTER_PASSWORD = password,
> MASTER_AUTO_POSITION = 1;
mysql> START SLAVE;
Neither the MASTER_LOG_FILE option nor the MASTER_LOG_POS option may be used with
MASTER_AUTO_POSITION set equal to 1
Step 5: Disable read-only mode.
mysql> SET @@global.read_only = OFF;
3.Replication and Binary Logging Options and Variables
3.1 Summary of Replication options and variables in MySQL 5.6
abort-slave-event-count： Option used by mysql-test for debugging and testing of replication；
binlog_gtid_simple_recovery：Controls how binary logs are iterated during GTID recovery；
Com_change_master：Count of CHANGE MASTER TO statements；
Com_show_master_status：Count of SHOW MASTER STATUS statements；
Com_show_new_master：Count of SHOW NEW MASTER statements；
Com_show_slave_hosts：Count of SHOW SLAVE HOSTS statements；
Com_show_slave_status：Count of SHOW SLAVE STATUS statements；
Com_slave_start：Count of START SLAVE statements；
Com_slave_stop： Count of STOP SLAVE statements；
disable-gtid-unsafe-statements：Replaced by --enforce-gtid-consistency in MySQL 5.6.9.
disable_gtid_unsafe_statements：Obsolete: Replaced by enforce_gtid_consistency in MySQL 5.6.9.
disconnect-slave-event-count：Option used by mysql-test for debugging and testing of replication；
enforce-gtid-consistency：Prevents execution of statements that cannot be logged in a transactionally
safe manner；
enforce_gtid_consistency：Prevents execution of statements that cannot be logged in a transactionally
safe manner；
gtid-mode：Controls whether GTID based logging is enabled and what type of transactions the logs can
contain；
gtid_done：Obsolete: Replaced by gtid_executed in MySQL 5.6.9.
gtid_executed：Global: All GTIDs in the binary log (global) or current transaction (session). Read-only.
gtid_lost：Obsolete: Replaced by gtid_purged in MySQL 5.6.9.
gtid_mode：Controls whether GTID based logging is enabled and what type of transactions the logs can
contain；
gtid_next：Specifies the GTID for the next statement to execute. See documentation for details；
gtid_owned：The set of GTIDs owned by this client (session), or by all clients, together with the
thread ID of the owner (global). Read-only.
gtid_purged：The set of all groups that have been purged from the binary log.
init_slave：Statements that are executed when a slave connects to a master；
log-slave-updates：This option tells the slave to log the updates performed by its SQL thread to its
own binary log；
log_slave_updates：Tells whether the slave should log the updates performed by its SQL thread to its
own binary log. Read-only; set using the --log-slave-updates server option.
master-info-file：The location and name of the file that remembers the master and where the I/O
replication thread is in the master's binary logs；
master-info-repository：Whether to write master status information and replication I/O thread location
in the master's binary logs to a file or table.
master-retry-count：Number of tries the slave will make to connect to the master before giving up；
master_info_repository：Whether to write master status information and replication I/O thread location
in the master's binary logs to a file or table.
relay-log：The location and base name to use for relay logs；
relay-log-index：The location and name to use for the file that keeps a list of the last relay logs；
relay-log-info-file： The location and name of the file that remembers where the SQL replication
thread is in the relay logs；
relay-log-info-repository：Whether to write the replication SQL thread's location in the relay logs to
a file or a table.
relay-log-recovery：Enables automatic recovery of relay log files from master at startup；
relay_log_basename：Complete path to relay log, including filename；
relay_log_index：The name of the relay log index file.
relay_log_info_file：The name of the file in which the slave records information about the relay logs.
relay_log_info_repository：Whether to write the replication SQL thread's location in the relay logs to
a file or a table.
relay_log_purge： Determines whether relay logs are purged；
relay_log_recovery：Whether automatic recovery of relay log files from master at startup is enabled;
must be enabled for a crash-proof slave.
relay_log_space_limit： Maximum space to use for all relay logs；
replicate-do-db：Tells the slave SQL thread to restrict replication to the specified database；
replicate-do-table：Tells the slave SQL thread to restrict replication to the specified table；
replicate-ignore-db：Tells the slave SQL thread not to replicate to the specified database；
replicate-ignore-table：Tells the slave SQL thread not to replicate to the specified table；
replicate-rewrite-db：Updates to a database with a different name than the original；
replicate-same-server-id：In replication, if set to 1, do not skip events having our server id；
replicate-wild-do-table：Tells the slave thread to restrict replication to the tables that match the
specified wildcard pattern；
replicate-wild-ignore-table：Tells the slave thread not to replicate to the tables that match the
given wildcard pattern；
report-host：Host name or IP of the slave to be reported to the master during slave registration；
report-password：An arbitrary password that the slave server should report to the master. Not the same
as the password for the MySQL replication user account；
report-port：Port for connecting to slave reported to the master during slave registration；
report-user：An arbitrary user name that a slave server should report to the master. Not the same as
the name used with the MySQL replication user account.
Rpl_semi_sync_master_clients：Number of semisynchronous slaves；
rpl_semi_sync_master_enabled：Whether semisynchronous replication is enabled on master；
Rpl_semi_sync_master_net_avg_wait_time：The average time the master waited for a slave reply；
Rpl_semi_sync_master_net_wait_time：The total time the master waited for slave replies；
Rpl_semi_sync_master_net_waits：The total number of times the master waited for slave replies；
Rpl_semi_sync_master_no_times：Number of times the master turned off semisynchronous replication；
Rpl_semi_sync_master_no_tx：Number of commits not acknowledged successfully；
Rpl_semi_sync_master_status： Whether semisynchronous replication is operational on master；
Rpl_semi_sync_master_timefunc_failures：Number of times the master failed when calling time functions；
rpl_semi_sync_master_timeout：Number of milliseconds to wait for slave acknowledgment；
rpl_semi_sync_master_trace_level：The semisynchronous replication debug trace level on the master；
Rpl_semi_sync_master_tx_avg_wait_time：The average time the master waited for each transaction；
Rpl_semi_sync_master_tx_wait_time：The total time the master waited for transactions；
Rpl_semi_sync_master_tx_waits：The total number of times the master waited for transactions；
rpl_semi_sync_master_wait_no_slave：Whether master waits for timeout even with no slaves；
Rpl_semi_sync_master_wait_pos_backtraverse：The total number of times the master waited for an event
with binary coordinates lower than events waited for previously；
Rpl_semi_sync_master_wait_sessions：Number of sessions currently waiting for slave replies；
Rpl_semi_sync_master_yes_tx： Number of commits acknowledged successfully；
rpl_semi_sync_slave_enabled：Whether semisynchronous replication is enabled on slave；
Rpl_semi_sync_slave_status：Whether semisynchronous replication is operational on slave；
rpl_semi_sync_slave_trace_level： The semisynchronous replication debug trace level on the slave；
rpl_stop_slave_timeout：Set the number of seconds that STOP SLAVE waits before timing out.；
server_uuid：The server's globally unique ID, automatically (re)generated at server start；
show-slave-auth-info：Show user name and password in SHOW SLAVE HOSTS on this master；
simplified_binlog_gtid_recovery：Controls how binary logs are iterated during GTID recovery；
skip-slave-start： If set, slave is not autostarted；
slave-checkpoint-group：Maximum number of transactions processed by a multi-threaded slave before a
checkpoint operation is called to update progress status. Not supported by MySQL Cluster.
slave-checkpoint-period：Update progress status of multi-threaded slave and flush relay log info to
disk after this number of milliseconds. Not supported by MySQL Cluster.
slave-load-tmpdir：The location where the slave should put its temporary files when replicating a LOAD
DATA INFILE statement；
slave-max-allowed-packet：Maximum size, in bytes, of a packet that can be sent from a replication
master to a slave; overrides max_allowed_packet；
slave_net_timeout：Number of seconds to wait for more data from a master/slave connection before
aborting the read；
slave-parallel-workers：Number of worker threads for executing events in parallel. Set to 0 (the
default) to disable slave multi-threading. Not supported by MySQL Cluster.
slave-pending-jobs-size-max：Maximum size of slave worker queues holding events not yet applied；
slave-rows-search-algorithms：Determines search algorithms used for slave update batching. Any 2 or 3
from the list INDEX_SEARCH, TABLE_SCAN, HASH_SCAN; the default is TABLE_SCAN,INDEX_SCAN；
slave-skip-errors：Tells the slave thread to continue replication when a query returns an error from
the provided list；
slave_checkpoint_group：Maximum number of transactions processed by a multi-threaded slave before a
checkpoint operation is called to update progress status. Not supported by MySQL Cluster；
slave_checkpoint_period:Update progress status of multi-threaded slave and flush relay log info to
disk after this number of milliseconds. Not supported by MySQL Cluster.
slave_compressed_protocol:Use compression on master/slave protocol;
slave_exec_mode:Allows for switching the slave thread between IDEMPOTENT mode (key and some other
errors suppressed) and STRICT mode; STRICT mode is the default, except for MySQL Cluster, where
IDEMPOTENT is always used;
Slave_heartbeat_period:The slave's replication heartbeat interval, in seconds;
slave_max_allowed_packet: Maximum size, in bytes, of a packet that can be sent from a replication
master to a slave; overrides max_allowed_packet;
Slave_open_temp_tables:Number of temporary tables that the slave SQL thread currently has open;
slave_parallel_workers:Number of worker threads for executing events in parallel. Set to 0 (the
default) to disable slave multi-threading. Not supported by MySQL Cluster;
slave_pending_jobs_size_max:Maximum size of slave worker queues holding events not yet applied;
Slave_retried_transactions:The total number of times since startup that the replication slave SQL
thread has retried transactions;
slave_rows_search_algorithms:Determines search algorithms used for slave update batching. Any 2 or 3
from the list INDEX_SEARCH, TABLE_SCAN, HASH_SCAN; the default is TABLE_SCAN,INDEX_SCAN;
Slave_running:The state of this server as a replication slave (slave I/O thread status);
slave_transaction_retries: Number of times the slave SQL thread will retry a transaction in case it
failed with a deadlock or elapsed lock wait timeout, before giving up and stopping;
slave_type_conversions:Controls type conversion mode on replication slave. Value is a list of zero or
more elements from the list: ALL_LOSSY, ALL_NON_LOSSY. Set to an empty string to disallow type
conversions between master and slave;
sql_slave_skip_counter: Number of events from the master that a slave server should skip. Not
compatible with GTID replication;
sync_binlog:Synchronously flush binary log to disk after every #th event;
sync_master_info: Synchronize master.info to disk after every #th event;
sync_relay_log:Synchronize relay log to disk after every #th event;
sync_relay_log_info: Synchronize relay.info file to disk after every #th event;
3.2 Summary of Binary Logging options and variables in MySQL 5.6
binlog-checksum：Enable/disable binary log checksums；
binlog-do-db：Limits binary logging to specific databases；
binlog_format：Specifies the format of the binary log；
binlog-ignore-db：Tells the master that updates to the given database should not be logged to the
binary log；
binlog-row-event-max-size：Binary log max event size；
binlog-rows-query-log-events：Enables logging of rows query log events when using row-based logging.
Disabled by default. Do not enable when producing logs for pre-5.6.2 slaves/readers.
Binlog_cache_disk_use：Number of transactions that used a temporary file instead of the binary log cache
binlog_cache_size：Size of the cache to hold the SQL statements for the binary log during a transaction
Binlog_cache_use：Number of transactions that used the temporary binary log cache；
binlog_checksum：Enable/disable binary log checksums；
binlog_direct_non_transactional_updates： Causes updates using statement format to nontransactional
engines to be written directly to binary log.
binlog_error_action：Controls what happens when the server cannot write to the binary log；
binlog_max_flush_queue_time：How long to read transactions before flushing to binary log；
binlog_order_commits：Whether to commit in same order as writes to binary log；
binlog_row_image：Use full or minimal images when logging row changes. Allowed values are full,
minimal, and noblob；
binlog_rows_query_log_events：When TRUE, enables logging of rows query log events in row-based logging
mode. FALSE by default. Do not enable when producing logs for pre-5.6.2 replication slaves or other
readers.
Binlog_stmt_cache_disk_use：Number of nontransactional statements that used a temporary file instead
of the binary log statement cache；
binlog_stmt_cache_size：Size of the cache to hold nontransactional statements for the binary log
during a transaction；
Binlog_stmt_cache_use：Number of statements that used the temporary binary log statement cache；
binlogging_impossible_mode：Deprecated and will be removed in a future version. Use the renamed
binlog_error_action instead；
Com_show_binlog_events：Count of SHOW BINLOG EVENTS statements；
Com_show_binlogs：Count of SHOW BINLOGS statements；
log-bin-use-v1-row-events：Use version 1 binary log row events；
log_bin_basename：Complete path to binary log, including filename；
log_bin_use_v1_row_events：Shows whether server is using version 1 binary log row events；
master-verify-checksum：Cause master to examine checksums when reading from the binary log；
master_verify_checksum：Cause master to read checksums from binary log；
max-binlog-dump-events：Option used by mysql-test for debugging and testing of replication；
max_binlog_cache_size：Can be used to restrict the total size used to cache a multi-statement
transaction；
max_binlog_size：Binary log will be rotated automatically when size exceeds this value；
max_binlog_stmt_cache_size：Can be used to restrict the total size used to cache all nontransactional
statements during a transaction；
slave-sql-verify-checksum：Cause slave to examine checksums when reading from the relay log；
slave_sql_verify_checksum：Cause slave to examine checksums when reading from relay log；
sporadic-binlog-dump-fail：Option used by mysql-test for debugging and testing of replication；
mysql官方关于replication的文档说明：
http://dev.mysql.com/doc/mysql-replication-excerpt/5.6/en/replication.html 