MySQL log detailed and practical

1.mysql master-Slave, master-master form

where the master-slave form, the main responsible for writing, from the responsible read, the main database if you hang up the other one from the database, change the main library slower

The main master form for the search engine, and the master-slave difference is that when there is data written, if the write database a succeeds then the default a master library, once write a exception is immediately replaced B library write, the B library is promoted to the main library, compared with the master-slave form, because there is no strict master-slave relationship, so the replacement of the The disadvantage is that both databases can change data, causing data disturbances (mainly in the form of a self-increasing primary key).

Multiple writes lead to self-incrementing primary key disorder can be modified by the self-increment primary key from the delta (if there are 2 databases, can be modified to 2, the specific method of modification I forgot, to use can Baidu),

Then different databases give a different initial primary key value, such as 2 databases, then one starts from 1 and one starts with 2, then their primary key will never be duplicated.

2. master-Slave database data replication method

Can be divided into asynchronous mode, semi-synchronous mode, synchronous mode.

As its implementation implies, the asynchronous pattern is to send write information to the library after the main library is written, and to continue its task once the message is complete

The synchronous mode is to send write information to the library after the main library is written, and return information to the main library after the library is written, only to receive the return information the main library will continue its task

The semi-synchronous mode is to send write information to the library after the main library is written, to notify the main library after receiving the information from the library, and then perform its own write operation, and the main library will continue its task after receiving the notification.

The speed of the three is naturally asynchronous > semi-synchronous > Synchronous

Three security is asynchronous < semi-synchronous < synchronous

3. Master-Slave database replication Policy

First, give two examples.

(1) Through the non-primary key, such as the user table in the email, query to a single user information, and modified, such as modified the phone. The SQL statement is

UPDATE users SET phone=13777777777 WHERE email= ' [email protected] '

(2) The Administrator batch modify male classmate for female classmate, then the SQL statement is

UPDATE users SET sex=1 WHERE sex=0

In order to meet the two different operating conditions can be good efficiency, give two different replication strategies

① by SQL statement, that is, the main library of what SQL statement operation, the statement is passed to the slave library, this way for example 2 is very efficient, but for example 1 is inefficient

② by row copy, that is, the main library operation of the rows of data, the operation of the row of data sent to the library, so example 1 of the data can use the primary key lookup, high efficiency, but for example 2 inefficient

Ideally, when SQL statements are inefficient, row replication is used, and when row replication is less efficient than SQL statements, the SQL statement is copied, and that little brother seems to recommend a library or a third-party tool, forgetting what to call ...

The copied action relies on the binlog of the main library, because the main library stores its SQL statements or row churn information in Binlog.

3. Database data backup and mis-operation restore

The first is a manual backup, usually a weekly full-scale backup, one incremental backup per day. Manual backups Most of the time can meet the rollback, restore requirements, but sometimes business requirements cannot be 1 days of data poor, so you will use MySQL own log to restore operations.

The MySQL log itself has many kinds, and InnoDB has the undo and redo logs, respectively, for rollback and roll forward. The redo log scenario is that when the transaction is halfway through MySQL suddenly hangs up, then MySQL reads the redo log and completes the unfinished transaction operation.

The primary reliance on backups is Binlog, as is the primary reliance on replication above.

First of all, the Binlog function needs to be opened manually, MySQL default is not open. As stated in the master-slave replication strategy, Binlog is divided into 3 modes, statement,row,mixed, all as the name implies, SQL statement records, Row Records and mixed records.

Binlog is a binary file that cannot be opened directly and needs to be viewed using the Mysqlbinlog tool.

The use of Binlog for data restoration effectively enhances timeliness and reduces the time to restore a large library backup by one or two days.

In this class, the main understanding of the database master-slave structure, MySQL in the role of Binlog can be said to be benefited.

9-22 MySQL log detailed and practical

MySQL log detailed and practical