MySQL master-slave replication, semi-synchronous replication, master-master Replication

The ultimate goal of data replication is to synchronize the data of one server with the data of another server, which can achieve data redundancy or load balancing of services. A master server can connect multiple slave servers, and the slave server can act as the master server in turn. The master-slave server can be located in different network topologies. Due to the powerful mysql replication function, the replication target can be all databases or some databases, even some tables in a database are copied.

MySQL supports two replication solutions:Statement-based replication and row-based Replication
Statement-based replication is based on Row replication. These two replication methods record any SQL statements in the binary log of the master server that may change the data in the database to the relay log, in addition, the SQL statement in the following relay logs is executed on the slave server to synchronize data with the master server. The difference is that when the master server executes a variable-based data and updates it to the database, such as the now () function, at this time, the entire syntax of the SQL statement is recorded during statement-based replication, while row-based replication records the value of now () updated to the database.
For example, run the following statement on the master server:
Mysql> update user set createtime = now () where sid = 16;
If now () returns the value 20:46:35
Statement-based replication records: update user set createtime = now () where sid = 16;
The row-based copy record will be: update user set createtime = '2017-04-16 20:46:35 'where sid = 16;

Three threads started for master-slave Replication
Binlog dump thread: sends binary log Content to slave server
I/O from the thread: Write the received data to the relay log
SQL thread: reads an SQL statement from the relay log at a time and runs it on the slave server.

1. Master-slave replication:
Preparations:
1. modify the configuration file (server_id must be modified)
2. Create a replication user
3. Start the slave service process of the slave server

Planning:
Master: IP Address: 172.16.4.11 version: mysql-5.5.20
Slave: IP Address: 172.16.4.12 version: mysql-5.5.20
Note that most of mysql replication is backward compatible,The slave server version must be higher than or equal to the master server version.
1. Master
Modify the configuration file and set it to the mysql master server.
# Vim/etc/my. cnf
Server_id = 11 # modify server_id = 11
Log_bin = mysql-bin # enable binary log
Sync_binlog = 1 # the binary file written to the disk immediately after any transaction is committed.
Innodb_flush_logs_at_trx_commit = 1 # write any transaction to the log file on the disk immediately after it is submitted.

Save and exit
# Service mysql reload # reload the mysql configuration file

2. Create a user on the Master and grant the copy permission.
Mysql> grant replication client, replication slave on *. * to repl@172.16.4.12 identified by '20140901 ';
Mysql> flush privileges;

3. Slave
Modify the configuration file and set it to a mysql slave server.
# Vim/etc/my. cnf
Server_id = 12 # modify server_id = 12
# Log-bin # comment out log-bin. The slave server does not need binary logs, so it is disabled.
Relay-log = mysql-relay # define the relay log name and enable the slave server relay log
Relay-log-index = mysql-relay.index # define relay log index name, enable slave server relay index
Read_only = 1 # Set the slave server to only perform read operations, not write operations

Save and exit
# Service mysql reload # reload the mysql configuration file

4. Verify that the relay log and server_id take effect on the Slave.
Mysql> show variables like 'relay % ';
+ ----------------------- + ----------------- +
| Variable_name | Value |
+ ----------------------- + ----------------- +
| Relay_log | relay-bin |
| Relay_log_index | relay-bin.index |
| Relay_log_info_file | relay-log.info |
| Relay_log_purge | ON |
| Relay_log_recovery | OFF |
| Relay_log_space_limit | 0 |
+ ----------------------- + ----------------- +
Mysql> show variables like 'server _ id ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Server_id | 12 |
+ --------------- + ------- +

5. Start the slave service process of the slave server
Scenario 1: If the master server and slave server are both newly created and no other data is added, run the following command:
Mysql> change master \
Master_host = '2017. 16.4.11 ',
Master_user = 'repl ',
Master_password = '000000 ';
Mysql> show slave status \ G
* *************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.4.11
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running:No
Slave_ SQL _Running:No
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: 25520
Relay_Log_Space: 2565465
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
Mysql> start slave;
Mysql> show slave status \ G
* *************************** 1. row ***************************
Slave_IO_State: Queueing master event to the relay log
Master_Host: 172.16.4.11
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running:Yes
Slave_ SQL _Running:Yes
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: 360
Relay_Log_Space: 300
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: 0
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: 11

1 2 3 4 Next Page