ENARRATIO MySQL Binlog: core technology ad meliorem database reliability

ENARRATIO MySQL Binlog: core technology ut amplio database reliability

2024-07-12

1. Introductio

1.1 Quid MySQL Bin Log？

MySQL Bin LogBinary Log, binarii stipes) est fasciculus stipes MySQL datorum, qui omnes modificationes ad datorum gestas refertDMLoperationes (ut INDO, UPDATE, DELETE, etc.), sedExcludit inhaerentiam accidentis SELECTmanereDQL Read-tantum operandi. Bin Log instrumentum magni momenti est pro MySQL ad consequendam replicationem, recuperationem et auditionem. For singularia vide;DDL, DML, DQL et DCL in MySQL

1.2 Munus et applicationem missiones Bin Log

Functiones principales Bin Log includunt: replicatio domini, data recuperatio, notitia tergum, et notitia subscriptionis

Application missiones	conceptum	Casus
dominus servus replicatio	Magister bibliothecam in binlogo ad bibliothecam ancillae cinematographicam renovationem mittit, et servus bibliothecam binlogum legit et declarationes SQL exequens.	E-commerce suggestum: Summa database records ordo operationes ad binlog, et synchronizat ordinem data e datorum ad conservandam omnium nodis constantiam.
Data Recuperatio	Usus binlog monumentis vicissim exsequi SQL dicta recuperare notitia	Systema pecuniarium: Magistratus tabulas transactiones casu deleverunt recuperant per parsing binlog ut integritatem datam curent et damna oeconomicorum evitent.
notitia tergum	Binlog ponitur pro tergum incrementi, salvis tergum temporis et spatii sumptibus	Socialis instrumentorum suggestus: binlogorum lima tergum sursum regulariter in dies singulos ut cito ad novissimum statum restituat sine toti database ut plene resupinetur.
Data subscriptione	Utere binlog ad monitor database update res in ipsa hora	Analysis realis-tempus turmarum grossorum: Per parsing binlog, operatio venditionis record insertio capitur et transfertur ad analysin suggestum in reali tempus pro venditionibus analysi inclinatio.

Per hos casus specificos clarius perspicere possumus momentum et diversitatem MySQL binlog in applicationibus practicis. Proprius usus functionis binlogi multum emendare potest systematis fidem, recuperare facultatem, ac celeritas negotii responsionis.

2. Bin Log notiones fundamentales

2.1 Quomodo Bin Log opera

Cum MySQL server dat munus Bin Log, omnes operationes modificationis datorum eruntres recordum ad bin lima stipes. Hae eventus in ordine exsecutionis reponuntur, continuam operationem consequentiam efficiunt. Hae eventus reddi possunt ad statum datorum recreandum, cum notitiae necessitates restituendae vel transcribendae sunt.

2.2 Tres formas Bin Log

MySQL Bin Log tres habet formas recordum:
Subsequens est tres formas recordorum MySQL Bin Log in formam tabulae constitutos:

recordum forma	describere	commodum	defectus
Statement-Substructio Logging (SBL)	Commemorare supplicium SQL dicitur ipsum	Log volumen parvum est et operationibus simplicibus SQL aptum.	Data constantia in aliquibus casibus praestari non potest, sicut functiones non determinatae (ut nunc ())
Row-Substructio Logging (RBL)	Adnotent specifica mutationes cuiusque ordinis notitia	Accuratior, apta ad multiplices operationes et exscribendas	Magnum volumen stipes, orbis magnus et supra caput retis
Mixta Logging (ML)	Commutatio inter modum enuntiationis et Row secundum circumstantias specificas.	attentis commodis utriusque	Complexum relativum ad efficiendum et administrandum

3. Configure et administrare Bin Log

3.1 Admitte bin Log

mysql> show variables like "%log_bin%";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
6 rows in set, 1 warning (0.00 sec)


1
2
3
4
5
6
7
8
9
10
11
12
13
14

Ut bin Log, debes configurare fasciculi MySQL configurationem (plerumque"my.cnfor *my.ini) et sequenti configuratione praestare :

[mysqld]
log-bin=mysql-bin
server-id=1
1
2
3

Post configurationem mutato, sileo mysql.implementSHOW VARIABLES LIKE 'log_bin'; Valor DE.

mysql> show variables like "%log_bin%";
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: *** NONE ***

+---------------------------------+--------------------------------------------------------------------------+
| Variable_name                   | Value                                                                    |
+---------------------------------+--------------------------------------------------------------------------+
| log_bin                         | ON                                                                       |
| log_bin_basename                | C:UsershiszmMySQL5.7.26databinlog       |
| log_bin_index                   | C:UsershiszmMySQL5.7.26databinlog.index |
| log_bin_trust_function_creators | OFF                                                                      |
| log_bin_use_v1_row_events       | OFF                                                                      |
| sql_log_bin                     | ON                                                                       |
+---------------------------------+--------------------------------------------------------------------------+
6 rows in set, 1 warning (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

3.2 Configure Bin Log parametri

Communis Bin Log configuration parametri includit:

log_bin: Admitte Bin Log.
server_id: Servo singulari identifier, replicandi usus est.
binlog_format: Forma Bin Log (SENTENTIA, ROW, MIXTA).
expire_logs_daysPone numerum dierum pro automatic expiratione et deletione Bin Stipes lima.
max_binlog_size: Pone maximam magnitudinem unius Bin Log lima.

3.3 Curo Bin Log files

Commune imperium administrandi Bin Log files:

View Bin Log file album：
```
SHOW BINARY LOGS;
1
```
View Bin Log lima amplitudo：
```
SHOW MASTER STATUS;
1
```

Delere vetus Bin Log files：

PURGE BINARY LOGS TO 'mysql-bin.000010';
1

vel:

PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
1

3.4 View Bin Log content


-- 创建一个名为 'simple_table' 的表
CREATE TABLE `simple_table` (
  `item_id` int(11) NOT NULL,  -- 项目编号
  `value` int(11) DEFAULT NULL,  -- 值
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- 最后更新时间
  PRIMARY KEY (`item_id`),  -- 设置项目编号为主键
  KEY `value_index` (`value`),  -- 为值字段创建索引
  KEY `update_time_index` (`last_updated`)  -- 为最后更新时间字段创建索引
) ENGINE=InnoDB;  -- 使用InnoDB存储引擎

-- 插入数据，将日期设置为当前日期
insert into `simple_table` values(1, 1, '2024-07-07');
insert into `simple_table` values(2, 2, '2024-07-07');
insert into `simple_table` values(3, 3, '2024-07-07');
insert into `simple_table` values(4, 4, '2024-07-07');
insert into `simple_table` values(5, 5, '2024-07-07');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

Utere mysqlbinlog instrumentum videre contenta Bin Log:

mysql> show binary logs;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    4
Current database: *** NONE ***

+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |      2411 |
+---------------+-----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

Si directe aperias, interpolata erit.

mysql> show binlog events in 'binlog.000001' from 0 limit 0,4G;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    6
Current database: *** NONE ***

*************************** 1. row ***************************
   Log_name: binlog.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.26-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: binlog.000001
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info:
*************************** 3. row ***************************
   Log_name: binlog.000001
        Pos: 154
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 219
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
   Log_name: binlog.000001
        Pos: 219
 Event_type: Query
  Server_id: 1
End_log_pos: 765
       Info: use `d`; -- 创建一个名为 'simple_table' 的表
CREATE TABLE `simple_table` (
  `item_id` int(11) NOT NULL,  -- 项目编号
  `value` int(11) DEFAULT NULL,  -- 值
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- 最后更新时间
  PRIMARY KEY (`item_id`),  -- 设置项目编号为主键
  KEY `value_index` (`value`),  -- 为值字段创建索引
  KEY `update_time_index` (`last_updated`)  -- 为最后更新时间字段创建索引
) ENGINE=InnoDB
4 rows in set (0.01 sec)

ERROR:
No query specified

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

3.5 Usura mysqlbinlog instrumentum

mysqlbinlog est instrumentum lineae mandatum ad parse Bin Stipes lima. Optiones communes includunt:

--start-datetime: Specificare tempus quo incipit parsing.
--stop-datetime: Specificare tempus cum parsing finit.
--start-position: Specificare locum ubi parsing incipit.
--stop-position: Specificare loco ubi parsing finit.

Exempli gratia, videre Bin Log pro certo tempore;

mysqlbinlog --start-datetime="2024-07-01 00:00:00" --stop-datetime="2024-07-01 12:00:00" binlog.000001
1

3,6 Parsing et replaying Bin Log

Bin Log certe replayed in MySQL servo pro notitia recuperatio:

mysqlbin logbinlog.000001 | mysql -u root -p
1

4. Applicationem Bin Log in replicando

4.1 Dominus-servus replicationis principium

Praecipuum replicationis MySQL domini-servi principium est quod dominus datorum scribit Bin Log, et servus datorum legit ac remittit haec omnia ad synchronizationem datam consequendam. Imprimis gradus sunt haec:

Bibliotheca principalis operationes scribendas praestat et has operationes ad Bin Log commemorat.
Servus bibliothecam principali bibliothecae coniungit, Bin Log legit, eamque suo proprio datam applicat.

4.2 Configurare dominus servi replicationem

Configurare pelagus bibliotheca：

[mysqld]
log-bin=mysql-bin
server-id=1
1
2
3

Configurare bibliothecam servi：

[mysqld]
server-id=2
relay-log=relay-bin
1
2
3

User replicationem creare primaria database：

CREATE USER 'replica'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
1
2

Replicationem configurare in servo database：

CHANGE MASTER TO
    MASTER_HOST='主库IP',
    MASTER_USER='replica',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='binlog.000001',
    MASTER_LOG_POS=0;
START SLAVE;
1
2
3
4
5
6
7

Reprehendo replicationem status：
```
SHOW SLAVE STATUSG
1
```

4.3 vigilantia et administrandi replicatio

Replicatio status in hoc mandato viverra potest:

SHOW SLAVE STATUSG
1

Communis status agri explicationes:

Slave_IO_Running: IO stamina spectatur.
Slave_SQL_Running: SQL status filum.
Seconds_Behind_Master: tempus bibliothecae servi pignerat bibliothecae magistri.

4.4 Incremental tergum

Incrementale tergum significat tergum sursum omnes mutationes cum ultima plena tergum vel ultimum incrementi tergum. Incrementale tergum effici potest utendo Bin Log.

Praestare plenum tergum：

mysqldump --all-databases --master-data=2 > full_backup.sql
1

Log Bin ad hodiernam recordarentur situ：

Quaerite sequentes lineas in plena lima tergum:
```
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=12345;
1
```

Tergum Bin Log：

mysqlbinlog --start-position=12345 binlog.000001 > incremental_backup.sql
1

4.5 bin Log recuperatio-fundatur

Restituere plena tergum：
```
mysql < full_backup.sql
1
```
Applicare incremental tergum：
```
mysql < incremental_backup.sql
1
```

5. scribe in fine

MySQL Bin Log Instrumentum validum est quod late in missionibus adhibetur ut receptae notitiae, replicationis et auditionis.Per recte Vestibulum ac utensBin Log , quae firmitatem et promptitudinem database MySQL in melius emendare possunt. In applicationibus practicis, pendet pro administratoribus datorum et machinis ut peritias et optimizationes modos Bin Log vincat.

Technology sharing