MySQL master-slave replication overview and configuration mysql5.7.10 for simple master-slave replication

2. What is master-slave replication:
   

   By copying data from one of the MySQL hosts to a different host, one server acts as the primary server (master) while one or more other servers act as slave servers (slave). When replicating, all writes to the data table must be performed on the primary server. Otherwise, because the primary server does not synchronize data from the server, it can cause master-slave data inconsistencies. MySQL's master-slave replication feature is the foundation for building high-performance, large application servers

   
   

4. The role of Master-slave replication:

   1. Support for backup of data

   2. Enabling high availability and offsite disaster recovery for data Services

   3. Implement multiple server load sharing

   
   

6. Master-slave Replication Implementation principle:

   The implementation of the entire replication process is mainly done by three threads, where the slave side two threads (SQL thread and IO thread), one thread (IO thread) on the master side
   

   1.master logging of database changes to binary logs (binary log)

   2.slave io thread asks master for events in binary log

   The IO thread of the 3.master server corresponds to the data requested by the IO thread of the slave server to the other party

   4.slave IO thread receives data and writes to the trunk log in sequence

   The 5.slave SQL thread detected a change in the trunk log, parsing the SQL statement that the master has changed to execute and executing it again

   
   

7. Master-Slave replication Category:

   1. Statement-based replication: Each statement that modifies the data is recorded in the binary of master. Slave The SQL thread will parse the same statement that was executed by Sing Woo original master at the time of the copy execution

   Pros: No need to record changes in each row of data, reduce the binary log volume, save the IO cost, improve performance.

   Cons: Because he is the execution statement of the record, so, in order for these statements to be executed correctly at the slave end, he must also record some relevant information of each statement at the time of execution, that is, contextual information, To ensure that all statements are executed at the slave end of the cup with the same results as they do at the master end. When some special functions are executed in one of the SQL statements of master, and then copied to slave, execution may result in a different value. For example, a field in the Master table inserts a select now (), and when the statement is copied to slave to execute, the resulting data cannot be copied correctly.
   

   
   

   2. Row-based replication: The binary log is recorded in the form of each row of data being modified, and then the same data is modified on the slave side.

   Advantage: The binary log can not record the execution of the SQL statement context-sensitive information, only need to record that one record has been modified, what to change. So rowlevel log content will be very clearly recorded in each row of data modification details, very easy to understand. And there will be no stored procedures, or functions, and the invocation of triggers and triggering problems that cannot be replicated correctly in certain situations

   Cons: All executed statements are recorded in the binarylog of each row, which can result in a large amount of log content, such as an UPDATE statement: Update user_info set sal=15000 where user _id >= 3, after execution, the log is not the event that corresponds to this update statement (MySQL records the Binarylog log as an event), but the change of each record updated by this statement, so that many records have been updated many events, Increases the IO burden.

   
   

   3. Hybrid mode: statement-based and row-based hybrid approach and the first two methods of the pros and cons, is a relatively ideal way to replicate

   This replication level of MySQL is determined by configuring the record format of the binary log.

   
   

9. MySQL master-slave replication configuration:

   Environment Description:

   Both master and slave data are initially not data-
   

   Master

   Operating system: centos6.7

   MySQL version: mysql5.7.10

   Host Address: 192.168.1.223

   Slave

   Operating system: centos6.7

MySQL version: mysql5.7.10

Host Address: 192.168.1.222

1.master Terminal configuration Work

Start binary Log
Choose a unique Server-id

VI/ETC/MY.CNF log-bin=/mydata/binlogs/master-bin binlog_format=mixed Server-id = 2

Restart MySQL Service

Service mysqld Restart

Log on to MySQL create a user with copy permission

Mysql-uroot-p ' password ' grant replication slave on * * to [e-mail protected] ' 192.168.1.222 ' identified by ' PASSW          Ord '; Flush privileges;

2.slave Terminal configuration Work

Modify Server-id
Enable the relay log

VI/ETC/MY.CNF Server-id = one relay-log =/mydata/relaylogs/relay-bin Log-bin=off

Restart MySQL Service

Service mysqld Restart

Log in to the MySQL connection master server

Mysql-uroot-p ' password ' change master to master_host= ' 192.168.1.223 ', master_user= ' repuser ', master_password= ' pas Sword

View slave working status

show slave status \g;

1. Row ***************************
Slave_io_state:
master_host:192.168.1.223
Master_user:repuser
master_port:3306
Connect_retry:60
Master_log_file:
Read_master_log_pos:4
relay_log_file:relay-bin.000001
Relay_log_pos:4
Relay_master_log_file:
Slave_io_running:no #现在io线程处于关闭状态
Slave_sql_running:no #sql线程也处于关闭状态
replicate_do_db:
replicate_ignore_db:
Replicate_do_table:
Replicate_ignore_table:
Replicate_wild_do_table:
Replicate_wild_ignore_table:
last_errno:0
Last_error:
skip_counter:0
exec_master_log_pos:0
relay_log_space:154
Until_condition:none
Until_log_file:
until_log_pos:0
Master_ssl_allowed:no
Master_ssl_ca_file:
Master_ssl_ca_path:
Master_ssl_cert:
Master_ssl_cipher:
Master_ssl_key:
Seconds_behind_master:null
Master_ssl_verify_server_cert:no
last_io_errno:0
Last_io_error:
last_sql_errno:0
Last_sql_error:
Replicate_ignore_server_ids:
master_server_id:0
Master_uuid:
Master_info_file:/mydata/master.info
sql_delay:0
Sql_remaining_delay:null
Slave_sql_running_state:
master_retry_count:86400
Master_bind:
Last_io_error_timestamp:
Last_sql_error_timestamp:
MASTER_SSL_CRL:
Master_ssl_crlpath:
Retrieved_gtid_set:
Executed_gtid_set:
auto_position:0
replicate_rewrite_db:
Channel_name:
Master_tls_version:

Start replication:

Start slave;

6. Verify the master-slave configuration results:

Log on to the master server to create a database

Mysql-uroot-p ' password ' CREATE database two_db; show databases;

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/7D/2F/wKiom1bhcsyjNzRCAAA_M4XFpW0303.png "title=" QQ picture 20160310211347.png "alt=" Wkiom1bhcsyjnzrcaaa_m4xfpw0303.png "/>

Log on to the slave server to see if it is synchronized

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/7D/2D/wKioL1bhcyWD52KuAAAxXhuKxS8012.png "title=" QQ picture 20160310211303.png "alt=" Wkiol1bhcywd52kuaaaxxhukxs8012.png "/>

MySQL simple master-slave copy is basically done

This article is from the "left-handed" blog, make sure to keep this source http://mofeihu.blog.51cto.com/1825994/1749732

MySQL master-slave replication overview and configuration mysql5.7.10 for simple master-slave replication