IntroductionMaster-slave replication is the use of MySQL replication mechanism to replicate data to another or more MySQL server, the replicated server is called the primary server, the replicated server is called from the server. is generally a master more from. The main benefits of master-slave replication are data backup, load balancing (read/write separation), high availability, and so on.Relay LogThe relay log is applied to master-slave replication, which writes events to the relay log file from the server when it receives a primary server modification event, and the trunk log is like a queue in which events are queued for receiving from the server. As with the binary log index file, there is a trunk log index file that lists all the trunk log files, which are text and therefore can be viewed directly, and the last line of the file is the trunk log file that is currently in use. The default base name for the trunk log file is Hostname-relay-bin, the base file name plus a set of sequential numbers 000001, 000002 ... is the trunk log file name, such as localhost-relay-bin.000001. Each time the server is started, the Refresh Log command (flush logs) is executed, and the trunk log file reaches the maximum length, the server generates the next trunk log file in sequential order, and the parameter max_relay_log_size determines the maximum length of the trunk log. The trunk log index file name defaults to the trunk log base file name Plus. Index. Parameters Relay_log, Relay_log_index can modify the trunk log and the trunk log index file name.Working principleWhen the replication relationship is formed, the state and data of the database to be replicated must be fully consistent on the master-slave server, after which the data modification of the master server is correctly copied to the slave server. The binary log is responsible for passing data between the master and slave servers, so the primary server must turn on the binary logging feature. Each slave server must have the right to connect to the primary server and request data replication, from the server tells the primary server where the last connection was replicated to the binary log, where the primary server transfers data from the end of the last replication to the slave server, and when all data is copied from the server, it enters the wait state. When the primary server changes data again, it will continue to be logged in the binary log for transmission to the slave server. Each connection from the server also consumes the maximum number of connections configured for the Max_connections parameter of the primary server.semi-synchronous replicationThe general master-slave replication is asynchronous,The primary server immediately returns the result to the client after executing the client-submitted request, and does not care if it has been received and processed from the primary server. Asynchronous replication can cause the primary server to be inconsistent with data from the server for a short time. Usually this time is determined by the network and the system. If there is inconsistency, while the primary server is down, then the data from the server is incomplete, so there is a semi-synchronous replication. Semi-synchronous replication is when the primary server does not immediately return to the client after executing a client-submitted request, but waits for at least one received from the server and written to the relay log to return to the client. The advantage is that the master-slave data is consistent, the disadvantage is that the request latency of the main library increases, the throughput decreases, this delay is at least a TCP/IP round trip time. Therefore, semi-synchronous replication is best used in low latency networks. MySQL supports semi-synchronous replication in the form of plug-ins.Parallel ReplicationMaster-slave replication, there are three types of threads involved: Binlog Dump (Master), I/O thread (from), SQL thread (from). Before MySQL5.6, there were only two threads from the server, the I/O thread was responsible for receiving modification events from the primary server and writing them to the relay log, and the SQL thread was responsible for reading the events of the relay log and executing them, removing it after each trunk log was processed. These two threads do not interfere with each other. Even if data is restored from multiple databases on the server, there is only one SQL thread. MySQL starts with 5.6, I/O threads and SQL threads become coordinator threads and worker threads, coordinator threads contain I/O and SQL thread functionality, and worker threads are similar to SQL threads. The coordinator thread will determine whether to allow parallelism, perform a data restore if it is not allowed, and distribute to multiple worker threads for parallel restores if allowed. However, 5.6 of parallel replication is for multiple databases, i.e. one worker thread per database, so that if only one database needs to be replicated, it cannot be restored in parallel. MySQL5.7 start, can be called true parallel replication. 5.7 introduced the parameter slave-parallel-type, with the optional Values database (default, library-based parallel replication) and Logical_clock (parallel replication based on group submissions). Parameter Slave_parallel_workers sets the number of worker threads that are executed in parallel. Parallel replication can be turned on by configuring Slave-parallel-type=logical_clock from the server and slave_parallel_workers greater than 1. Parallel replication can significantly reduce master-slave latency. But not slave_parallel_workers the bigger the better, slave_parallel_workers too large and too small, can bring negative performance effects, such as causing coordinator thread judgment, distribution and other costs increased. Therefore needs to adapt to local conditions. The server_id must be unique, with a value between 1 and 2^32-1. The Master-info-repository parameter value determines how the primary server-related replication state is stored from the server. Master-info-repository=file, the Master.info file is generated from the server data directory, if Master-info-repository=table, The information will be present from the server's Mysql.slave_master_info table. Do not move or edit the related files and tables, you want to change the configuration by executing the changes master to statement again, the changes are automatically saved to the relevant files and tables. The Relay_log_info_repository parameter determines how the server-related replication state is stored, and when Relay_log_info_repository=file is generated from the server data directory Relay-log.info,relay_log_info_repository=table will save the information inthe Mysql.slave_relay_log_info table. Relay_log_recovery indicates that the secondary log is corrupted, causing some relay logs to not be processed, and automatically discarding all the unregistered trunk logs and retrieving them back from the primary server, which guarantees the integrity of the trunk log. Default is not turned on. The replicate_ignore_db parameter can exclude the default database that is not being made, replicate_do_db the opposite, it is only the default database that is specified is copied. The default database is the database specified by the use parameter. Therefore, these two parameters cross the library will have a problem, such as REPLICATE_IGNORE_DB=DB1 or REPLICATE_DO_DB=DB2, first use DB1, then update Db2.table1 ..., then update db2.table1 ... will be ignored. Skip-slave-start prevents the replication process from starting with the start of the server.ConfigurationPrimary server Configuration[Mysqld]server_id=1Log_binFrom server configuration[Mysqld]server_id=2Slave-parallel-type=logical_clockslave-parallel-workers=8master_info_repository=tablerelay_log_info_repository=tableRelay_log_recovery=onREPLICATE_DO_DB=DB1REPLICATE_DO_DB=DB2Skip-slave-startCreate user ' slave1 ' @ ' 192.168.22.133 ' identified by ' slave1 ' GRANT REPLICATION SLAVE on * * to ' slave1 ' @ ' in the primary server created with copy permissions 192.168.22.133 ' View the primary server's binary log file name and the next replication start location flush TABLES; SHOW MASTER STATUS; File positionlocalhost-bin.000001 297 First manually transfer the database of the primary database to the slave database, to ensure that when the master-slave replication is turned on, the state and data of the database to be replicated must be fully consistent on the master-slave server. You can take advantage of mysqldump, assuming that there are 2 databases to synchronize, namely DB1 and DB2, The primary server IP is 192.168.22.1, from the database IP to 192.168.22.133, the port is 3306, execute the following command to transfer DB1 and DB2 to the slave server, mysqldump-h 192.168.22.1-u root-p-- Databases DB1 | Mysql-h 192.168.22.133-u root-p. Of course, you can also use other backup methods. Set the primary server connection and replication information from the server change Master to--primary Server Ipmaster_host = ' 192.168.22.1 ',--primary server port Master_port = 3306,-- The primary server has replication permissions for users master_user = ' slave1 ',--user password Master_password = ' slave1 ',--primary server binary log filename master_log_file = ' localhost-bin.000001 ',--primary server binary log next replication start position master_log_pos = 297; Start the replicator from the server start SLAVE; Shut down the replicator from the server stop SLAVE; View from server status show Processlist; SHOW SLAVE STATUS;Remove from serverStop Slave;reset slave all;
MySQL5.7 Master-slave replication
