Mysql5.6 Master/Slave Parameters

Mysql5.6 Master/Slave is quite good, and many parameters are added to improve the security and efficiency of Master/Slave synchronization. The following is a detailed description of mysql5.6 Master/Slave parameters.

#########################################server_id = 2#binloglog-bin = /data1/mysql/binlog/binlogbinlog_format = ROWbinlog-row-image = minimal#gtid_mode = ON#enforce-gtid-consistency = truebinlog_cache_size = 4Mmax_binlog_size = 1Gmax_binlog_cache_size = 2Gsync_binlog = 1slave-skip-errors = 1062expire_logs_days = 3slave_parallel_workers = 4#relay logrelay-log = /data1/mysql/relaylog/relaylogmax_relay_log_size = 1Grelay_log_purge = 1relay_log_recovery = 1binlog_checksum = CRC32slave_allow_batching = 1master_verify_checksum = 1slave_sql_verify_checksum = 1binlog_rows_query_log_events = 1master_info_repository = TABLErelay_log_info_repository = TABLElog_slave_updates#########################################

Sync_binlog = 1

By default, the binlog is not synchronized with the hard disk each time it is written. Therefore, if the operating system or machine (not just the MySQL server) crashes, the last statement in binlog may be lost. To prevent this situation, you can use the sync_binlog global variable (1 is the safest value, but also the slowest) To synchronize the binlog with the hard disk after each N binlog writes. Even if sync_binlog is set to 1, the table content and binlog content may be inconsistent when a crash occurs. If the InnoDB table is used, the MySQL server processes the COMMIT statement, which writes the entire transaction to the binlog and submits the transaction to InnoDB. If a crash occurs between two operations, the transaction is rolled back by InnoDB at the time of restart, but it still exists in binlog. You can use the -- innodb-safe-binlog option to increase the consistency between InnoDB table content and binlog. (Note: In MySQL 5.1, -- innodb-safe-binlog is not required. Because XA transaction support is introduced, this option is voided.) This option provides greater security, synchronize the binlog (sync_binlog = 1) and (true by default) InnoDB logs of each transaction with the hard disk. This option is effective after the crash and restart, after the transaction is rolled back, the MySQL Server splits the InnoDB Transaction for rollback from the binlog. This ensures that the binlog reports the exact data of the InnoDB table and keeps the slave server synchronized with the master server (the rollback statement is not received ).

Server_id = 2: server-id = 2

Gtid_mode = ON

Enforce-gtid-consistency = true

These two parameters enable the UUID synchronization mode in mysql5.6. The two parameters must be enabled together. Otherwise, an error is reported. slave does not need to find the binlog and POS points during synchronous replication, directly change master to master_auto_position = 1 to automatically find the point for synchronization.

Limitations of GTID: In view of these limitations, use them with caution)

1. GTID synchronous replication is based on transactions. Therefore, Myisam tables are not supported, which may cause multiple gtids to be allocated to the same transaction.

2. the create table... SELECT statement is not supported. This statement is split into create table and insert transactions, and the two transactions are allocated with the same GTID. This will cause the insert statement to be ignored by the slave database.

3. create temporary table and drop temporary table operations are not supported.

Execute the synchronization statement after enabling:

Change master to MASTER_HOST = '60. 2.212.190 ', MASTER_PORT = 3306, MASTER_USER = 'repl', MASTER_PASSWORD = 'your rybckghmm _ P1XO', master_auto_position = 1;

If it is not started, the synchronization statement is the same as before:

Change master

Master_host = '192. 168.100.190 ',

Master_user = 'repl ',

Master_password = '000000 ',

Master_port = 3306,

Master_log_file = 'binlog. 000001 ',

Master_log_pos = 1287;

Start slave;

In version 5.6, the start master and slave nodes are: start slave, and you can also use slave start before.

Binlog-row-image = minimal. This option allows the application to only copy the image data of rows, regardless of whether the row has been DML. This improves the replication throughput of the Master/Slave machine and reduces the disk space, network resources, and memory occupied by binary logs.

Slave_parallel_workers = 4 The default value is 0. If this parameter is not enabled, the maximum number of concurrent threads is 1024. Master-slave replication enables four SQL threads to increase the throughput of slave servers and reduce latency. Concurrent SQL threads are used to concurrently apply events to different databases.

Binlog_checksum = CRC32

Slave_allow_batching = 1

Master_verify_checksum = 1

Slave_ SQL _verify_checksum = 1

Binlog_rows_query_log_events = 1

These four parameters enable binlog/relaylog verification to prevent log errors.

Relay_log_purge = 1

Relay_log_recovery = 1

The two enable the automatic repair function of relaylog to avoid log corruption due to external factors such as the network and stop the master and slave nodes.

Master_info_repository = TABLE

Relay_log_info_repository = TABLE

The two parameters will save master.info and relay.info In the table. The default value is Myisam engine.

Alter table slave_master_info engine = innodb;

Alter table slave_relay_log_info engine = innodb;

Alter table slave_worker_info engine = innodb;

Change to the Innodb engine to prevent the table from being damaged and then repair it by yourself.

This article from the "Wang Wei" blog, please be sure to keep this source http://wangwei007.blog.51cto.com/68019/1205298