MySQL copy of AB

MySQL replication in the industry has called: MySQL synchronization, AB replication and so on. The professional name is called: Copy. Replication is one-way asynchronous replication, copying from one MySQL (Master) to another MySQL (Slave). Implementing the entire master-slave replication requires the IO process on the master server and the SQL process and IO processes on the salve server to be completed together.

To implement master-slave replication, you must first turn on the binary log (Bin-log) feature on the master side, because the entire MySQL replication process is actually slave to get the corresponding binary log files from the master side, Then, in accordance with the corresponding position number in their own slave end of the full execution log of the various operations, (binary log files are written in SQL statements, so you can directly execute directly on the slave machine).

The basic process of master-slave replication is as follows:

1, Mysql slave side of the IO process link on master, request to master to specify the log file at the specified location (or from the beginning of the log) after the contents of the log;

2. After master accepts the request from slave IO process, the IO process responsible for replication reads the corresponding log contents according to slave request information and returns the IO process to slave. and returns the Bin-log file name and location read by this request to the slave side.

3, the slave IO process after receiving the information, the received log content is written to the Relay-log file, and the read to the master side of the binary log file name position number is recorded in the Master-info file, Continue copying from Master the next time based on the log file name and position number of the record. To ensure consistency of data.

4, slave's SQL process detects the new additions in the relay-log, it immediately resolves the relay-log content and executes it on its own.

This traditional replication method because in the master side is multi-threaded write, and on the slave side is single-threaded replication, often cause very high latency, while in the configuration need to find Binlog and Pos point, and then change the master to point, not very experienced operation, Often find the wrong, resulting in master-slave synchronous replication error. These problems are largely addressed by the master-slave replication of the Gtid model, which is supported from Mysql5.6.

Tid:transaction ID, the ID number of the transaction: that is, every transaction in MySQL replication has its own ID number (random number)

Gtid:global Transaction ID, global transaction ID, every transaction ID number in the entire transaction schema is globally unique, not just on one node but in the entire master-slave replication schema, the ID number of every two transactions will not be the same. Is the token that MySQL has made for each transaction written to the binary log. Consists of the Server_uuid and the transaction ID.

Simply put, Gtid ensures that a data copy is made from the server to the other from the server and that data is integrated. A transaction corresponds to a unique ID, a gtid is executed only once on a single server, and the data is more secure and easier to failover than row replication.

Gtid also has its limitations, does not support the non-transactional engine, does not support the CREATE TABLE ... SELECT statement replication, and does not allow the simultaneous updating of a table of both the transaction engine and the non-transactional engine in one SQL, and in a replication group, it must be required to turn on the Ctid or close Gtid, turn on Dtid , the original traditional copy method is not used, and SQLslaveSkip_counter is not supported for Createtemporary table and drop temporary table statements.

MySQL is asynchronous replication by default, master does not know if slave has been accepted when writing an event to Binlon, and slave may lose transaction when Master is down. In this case, we can use semi-synchronous replication, where a thread commits a transaction on master that will be blocked until it learns that a slave that has been turned on for semi-synchronous replication has received all events for this transaction, or waits for a timeout.

Here are some simple configurations for MySQL master-slave replication

Traditional AB replication (MySQL version of rhel6.5 comes with MySQL):

On the master host:

VIM/ETC/MY.CNF server-id=1 log-bin=mysql-bin #启动二进制日志系统/etc/init.d/mysqld restartcd/var/lib/mysql

Binary log files can be plugged in by Mysqlbinlog instructions

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/83/5C/wKiom1dxLvLj25nhAAEsYuYxvxI130.png-wh_500x0-wm_3 -wmp_4-s_1495843346.png "title=" screenshot from 2016-06-27 14_40_20.png "alt=" Wkiom1dxlvlj25nhaaesyuyxvxi130.png-wh _50 "/>

Create a sync account and give permissions

Mysql-pmysql> GRANT REPLICATION SLAVE on * * to [e-mail protected] ' 172.25.254.3 ' identified by ' westos+123 ';

View Master Status

Show master status;

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M00/83/5B/wKioL1dxLySjsaJHAABSyyzoupU951.png-wh_500x0-wm_3 -wmp_4-s_3363063346.png "title=" screenshot from 2016-06-27 14_52_59.png "alt=" Wkiol1dxlysjsajhaabsyyzoupu951.png-wh _50 "/>

Records the values of file and position, which are used when the slave is configured.

On the slave host

See if you can remotely log in to the master host's MySQL

Mysql-h 172.25.254.2-uexample-pwestos+123

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M00/83/5B/wKioL1dxL0DiW433AADudw_0Duc472.png-wh_500x0-wm_3 -wmp_4-s_363857658.png "title=" screenshot from 2016-06-27 14_58_02.png "alt=" Wkiol1dxl0diw433aadudw_0duc472.png-wh_ "/>

vim /etc/my.cnf  server-id=2  #从服务器ID号, do not be the same as the primary ID and not the same as the other from the server/etc/init.d/mysqld  Restartmysql -pmysql> change master to    -> master_host= ' 172.25.254.2 ',  #指定主服务器ip地址     -> master_user= ' Example ',        #指定在主服务器上可以进行同步的用户名     -> master_password= ' westos+123 ',  # Sync user's password     -> master_log_file= ' mysql-bin.000003 ',  #上面看到的master的日志文件      -> master_log_pos=1007;          # Position value of Master machine mysql> start slave;    #开启同步mysql > show slave  status\g  #查看slave状态 

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M00/83/5C/wKiom1dxL4HD_G2JAAEBscc5G1U447.png-wh_500x0-wm_3 -wmp_4-s_2843864348.png "title=" screenshot from 2016-06-27 15_13_22.png "alt=" Wkiom1dxl4hd_g2jaaebscc5g1u447.png-wh _50 "/>

See the following two yes to indicate a successful sync

Slave_io_running:yes Slave_sql_running:yes

We can see if the synchronization is complete by whether the delay time in the slave state is 0 and the POS number is consistent with the master host.

。。。 Seconds_behind_master:0exec_master_log_pos:1007 ...

MySQL gtid replication: (MySQL for 5.7 Community edition)

On the master host:

VIM/ETC/MY.CNF server-id=1 log-bin=mysql-bin gtid-mode=on #开启gtid enforce-gtid-consistency=on #强制GTID的一致性/etc/ini T.d/mysqld restartmysql-pmysql> GRANT REPLICATION SLAVE on * * to [e-mail protected] ' 172.25.254.3 ' identified by ' Westo S+123 ';

vim /etc/my.cnf  server-id=2  log-slave-updates  #更新是否记入日志, When the slave host is a master host it must have   gtid-mode=on  enforce-gtid-consistency=on   slave-parallel-type=logical_clock  #MySQL  5.6 Multi-threaded synchronization only has a noticeable performance boost when there are multiple databases, mysql 5.7.2, You can avoid this limitation by configuring Slave-parallel-type to Logical_clock to .  slave-parallel-workers=16  #从服务器的SQL线程数, A database can have only one worker thread at a time to work. master_info_repository=table  #主服信息记录库 = table/File relay_log_info_repository=table  #中继日志信息记录库relay_log_ recovery=on    #在CRASH后自动放弃所有未执行的relay-log, and retrieve the log from master again, guaranteeing the full/etc/init.d/mysqld of Relay-log  restartmysql -pmysql> change master to     ->  Master_host= ' 172.25.254.2 ',     -> master_user= ' example ',     - > master_password= ' westos+123 ',    -> master_auto_position=1;mysql>  start slave;mysql> show processlist;  #可以看到开启了多个线程 

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M01/83/5B/wKioL1dxL5my7OwmAAF3oDNHirg950.png-wh_500x0-wm_3 -wmp_4-s_4271796188.png "title=" screenshot from 2016-06-27 16_00_17.png "alt=" Wkiol1dxl5my7owmaaf3odnhirg950.png-wh _50 "/>

This simple configuration is done, the method of detection is to create a database on the master host to see if the slave machine will be synchronized.

MySQL copy of AB