MySQL5.6 Master-slave replication (based on Gtid and multithreading)

A: Gtid Work flow chart

II: What is Gtid

Three: Configure Master-slave replication

IV: Description

1.1 GTID Work Flow chart

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6F/94/wKiom1WhIrPzlen4AAE6CZfkVPc295.jpg "title=" 4.png " alt= "Wkiom1whirpzlen4aae6czfkvpc295.jpg"/>

2.1 what is Gtid

GTID : is a globally unique identifier, a 128-bit random character, combined with the ID number of a transaction to uniquely identify the identifier of a transaction on a host

In Binlog, the header of each transaction is written with the Gtid identity, so Gtid makes it easy to track and compare replication transactions, and to recover quickly from the crash

3.1 master and slave configuration and parameter description

"Master"

#数据目录位置datadir  = /MySQL_DATA  #端口port  = 3306  #server_idserver_id  = 1   #sock文件位置socket = /tmp/mysql.sock   #每张表一个表文件innodb_file_per_table =1  #区分大小写lower_case_ table_names=1  #PID文件位置pid_file =/tmp/master.pid  #二进制日志文件位置log_bin =/mysql_bin_log/master-bin # Binary Log index file log_bin_index=/mysql_bin_log/maste-bin.index  #二进制日志文件格式binlog_format =row  # When reading events from the log from the server to the local application, whether to log the event writes related operations to the local binary log, #为了GTID的复制安全, you need to enable this option log_slave_updates=true # Whether to enable the Gtid feature gtid_mode=on    #是否强制GTID具有其的一致性功能enforce_gtid_consistency =true  #在GTID模式下, Each from the server, in the primary server, you have to tell the main server, who is report_port=3306report_host=192.168.1.112   #master_info_repository, Relay_log_info_repository starts these two entries, which can be used to implement secure binary and security functions from the server   #用于让主服务器记录, each connection information from the server, # And each binary log file name that is copied from the server, its associated event location, and so on, #记录到文件中 (Master.info) or recorded in the table (Mysql.slave_master_info) master_info_repository= table    #让从服务器自己记录, who is the master server of his own, #主服务器哪一个二进制日志文件以及这个二进Location of the log file, #记录到文件中 (Relay-log.info) or in the table (Mysql.slave_relay_log_info) relay_log_info_repository=table  # Enable ensures that no information is lost, and each event synchronizes master_info_repository,relay_log_info_repository information to the table sync_master_info=1   # Set the number of SQL threads from the server, 0 to turn off multithreaded replication slave_parallel_workers=2   #binlog_checksum, master_verify_checksum, # Slave_sql_verify_checksum enable the replication of all the checksum function #binlog_checksum The primary server side at startup when the checksum Binlog check code, such as the main server crashes, #下次启动的时候, # If it finds out that this binlog checksum is different from the last time it was down, it will refuse to read the Binlog_checksum=crc32master_verify_checksum=1slave_sql_verify_ checksum=1   #启用可用于在二进制日志详细记录事件相关信息 to reduce the complexity of troubleshooting binlog_rows_query_log_events=1  # Data is not buffered in the binary log buffer and is written directly to the binary log file sync_binlog=1   #半同步配置rpl_semi_sync_master_enabled =1rpl_semi_sync_master_ timeout=1000    #SSL加密配置sslssl_ca   = /usr/local/mysql/ssl/cacert.pem        #指定CA文件位置ssl_cert = /usr/local/mysql/ssl/master.crt        #指定证书文件位置ssl_key   = /usr/local/mysql/ssl/master.key       #指定密钥所在位置 

"Slave"

#数据目录datadir  = /MySQL_DATA  #端口port  = 3306  #server_idserver_id  = 2   #sock文件位置socket  = /tmp/mysql.sock   #二进制日志log-bin=/mysql_bin_log/slave-bin # Binary log format binlog_format=row  #每张表一个表文件innodb_file_per_table =1# case-sensitive lower_case_table_names=1  #PID文件位置pid_ file=/tmp/slave.pid  #中继日志relay_log =/mysql_data/relay-log  #中继日志索引文件relay_log_index =/mysql_data/ relay-log.index   #当从服务器上从中继日志中读取事件在本地应用的时候, it is necessary to enable this option Log_slave if the relevant operation of this event write is logged to the binary log of this # ground #为了GTID的复制安全 _updates=true  #是否启用GTID的功能gtid_mode =on   #是否强制GTID具有其的一致性功能enforce_gtid_consistency =true # In Gtid mode, each slave server, in the host server, you have to tell the main server, who is report_port=3306report_host=192.168.1.113   #master_info_ Repository,relay_log_info_repository start these two items, #可用于实现在奔溃时保证二进制及从服务器的安全的功能   #用于让主服务器记录, each connection information from the server, # And each binary log file name that is copied from the server, its associated event location, and so on, #记录到文件中 (Master.info) or recorded in the table (Mysql.slave_master_info) master_info_repository= table   #让从服务器自己记录, who is the master server of his own, #The primary server which binary log file and where this binary log file is located, #记录到文件中 (Relay-log.info) or in the table (Mysql.slave_relay_log_info) relay_log_info_ repository=table   #启用可确保无信息丢失, each event is synchronized master_info_repository, #relay_log_info_repository的信息到表sync_ master_info=1  #binlog_checksum,master_verify_checksum,  #slave_sql_verify_checksum启用复制有关的所有校验功能 # Binlog_checksum The main server side at the start of the check Binlog check code, such as the main server crashes, #下次启动的时候, #如果它发现这个binlog的校验码和上一次宕机时候的校验码不一样的话, it will refuse to read Binlog_ checksum=crc32master_verify_checksum=1slave_sql_verify_checksum=1  #启用可用于在二进制日志详细记录事件相关信息, Reduces the complexity of troubleshooting binlog_rows_query_log_events=1   #半同步设置rpl_semi_sync_slave_enabled =1  # Set the read-only read_only=1   from the server #SSL配置sslssl_ca   = /usr/local/mysql/ssl/cacert.pemssl_cert=  /usr/local/mysql/ssl/slave.crtssl_key  = /usr/local/mysql/ssl/slave.key

3.2 connecting to the master server from the server

Mysql>change MASTER to, master_host= ' 192.168.1.112 ', master_user= ' Repluser ', master_password= ' Replpass ', Master_auto_position=1, master_ssl=1,->master_ssl_ca= '/usr/local/mysql/ssl/cacert.pem ',->master_ssl_cert= '/usr/local/mysql/ssl/slave.crt ',->master_ssl_key= '/usr/local/mysql/ssl/slave.key ';

4.1 Description

MySQL5.6 semi-synchronous replication, as well as configuring SSL and MySQL5.5

This article is from the "Everyman" blog, please make sure to keep this source http://caoyt.blog.51cto.com/9978141/1673297

MySQL5.6 Master-slave replication (based on Gtid and multithreading)