MySQL learning notes (28): copying


This article was updated on May 4, 2020, using MySQL 5.7 and operating system deepin 15.4.

  • Startup options
  • Management and maintenance

Replication refers to transferring DDL and DML operations of the master database to the slave database through binary logs, and then the slave database redoes these logs, so as to keep the data of the master database and the slave database synchronized.

The advantages of replication are:

  • If there is a problem with the master database, you can quickly switch to the slave database to provide services.
  • Query operation can be performed on the slave database to reduce the access pressure of the master database. Because MySQL implements asynchronous replication, the difference of data should be considered when querying from the slave database.
  • You can perform a backup on the secondary library to avoid affecting the service of the primary library during the backup.

MySQL completes data replication between master and slave databases through three threads: binlog dump thread runs on the master database, I / O thread and SQL thread run on the slave database. Can be passed throughSHOW PROCESSLISTView the status of the thread.

The replication of MySQL is that the master database actively pushes logs to the slave database. The replication process involves two types of very important log files: binary log and relay log. The format and content of relay log file is the same as that of binary log file. The difference is that it will be deleted automatically after execution.

In order to ensure that the I / O thread and SQL thread of the slave library can still know where to start copying after the slave library crashes, two log files will be created by default And relay- The progress of I / O thread reading the binary log of main database and the progress of SQL thread applying relay log are recorded respectively. adoptSHOW SLAVE STATUSYou can view the status of the current replication from the library.

MySQL supports four replication modes: SQL statement based replication (SBR), row data based replication (RBR), hybrid replication (using SBR for secure SQL and RBR for non secure SQL), and replication using global transaction IDS (gtids).

MySQL supports three log formats: SQL statement based log format (SBL), row data based log format (RBL), and hybrid format.

Common architectures for replication are:

  • One master multi slave replication architecture
  • Multi level replication architecture: the secondary master database storage engine can be set as blackhole
  • Dual master replication / dual master architecture: it is suitable for scenarios where master-slave switching is required. A dual master multi-level replication architecture can be built on this basis.

There are two ways of replication: asynchronous replication and semi synchronous replication.

Startup options

  • –Log slave updates: set whether the update operation of slave database will write binary log. If the slave is also the master, you need to turn this option on. It needs to be used with — log bin.
  • –Master connect retry: sets the interval between retries when the connection to the master database is lost.
  • –Read only: set the slave library to only accept the update operation of root.
  • –Replicate do DB: Specifies the database to replicate from the library.
  • –Replicate do table: Specifies the table to copy from the library.
  • –Replicate ignore DB: Specifies that replicated databases are ignored from the library.
  • –Replicate ignore table: Specifies that replicated tables are ignored from the library.
  • –Replicate wild go table: Specifies the table copied from the library as a wildcard.
  • –Replicate wild ignore table: specifies in a wildcard that replicated tables are ignored from the library.
  • –Slave skip errors: defines the error number that can be automatically skipped by the slave library during replication.

Management and maintenance

For the moment.