高性能 MySQL

1. MySQL 数据库逻辑架构图

img

每个虚线框为一层,总共三层。

第一层,服务层(为客户端服务):为请求做连接处理,授权认证,安全等。

第二层,核心层:查询解析,分析,优化,缓存,提供内建函数;存储过程,触发器,视图。

第三层,存储引擎层,不光做存储和提取数据,而且针对特殊数据引擎还要做事务处理。

2. 事务ACID特性

原子性:(atomicity)

一致性:(consistency)

隔离性:(isolation)

持久性:(durability)

3. 隔离级别

ASNI SQL 隔离级别

隔离级别 脏读可能性 不可重复读可能性 幻读可能性 加锁读
read uncommitted(未提交读) yes yes yes no
read committed(提交读) no yes yes no
repeatable read(可重复读) no no yes no
serializable(可串行化) no no no no

注意事务隔离级别越低通常可执行更高的并发

4. MySQL 中存储引擎

1. 事务存储引擎

  1. InnoDB
  2. NDB Cluster
  3. 第三方: XtraDB 、PBXT 等

注意: MySQL 默认开启 AUTOCOMMIT,若不显示开启一个事务,则每个查询被当作一个事务执行提交操作

1
2
3
4
5
6
7
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.52 sec)

查询表相关信息: 每个表对应一个 .frm 文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

mysql> show table status like 'user' \G;
*************************** 1. row ***************************
Name: user
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 7
Avg_row_length: 2340
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2018-07-07 16:32:53
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

2. MySQL 中主要存储引擎

  1. InnoDB:采用MVCC (多版本并发控制)支持高并发,基于聚簇索引建立,事务型存储引擎

  2. MyISAM:MySQL 5.1 及以前版本默认使用,不支持事务和行级锁,不支持崩溃后安全恢复

  3. MySQL内建其他存储引擎

    • Archive 引擎: 只支持 INSERT 和 SELECT 操作,5.1 之前不支持索引,可实现高并发插入,

      适合 日志和数据采集类应用,或某些需要快速INSERT才注意的场合

    • Blackhole引擎:没有实现任何存储机制,适用于复制架构日志审核

    • CSV引擎: 可以将普通CSV文件作为MySQL的表来处理,可做为一种数据交换机制

    • Federated引擎: 联合引擎,访问MySQL服务器的一个代理

    • Memory引擎: 数据保存在内存中,速度非常快,重启数据丢失,支持hash索引

    • Merge引擎: MyISAM引擎的一个变种,用于日志和数据仓库类应用

    • NDB集群引擎: SQL 和 NDB 原生协议之间的接口

  4. 第三方存储引擎

    • OLTP 类引擎: XtraDB 、 PBXT、 TokuDB(一种大数据存储引擎)、RethinkDB

      Falcon存储引擎

    • 面向列的存储引擎: MySQL默认面向行,但大数据量处理,面向列效率会更高。

      例如 Infobright,应用于数据分许和数据仓库,数据高度压缩,不支持索引

      InfiniDB,可在一组机器集群间做分布式查询

  5. 社区存储引擎

5. Schema 与 数据类型优化

  1. 整数类型

    两种类型的数字:整数(whole number)和实数(real number)

    若要存储整数:tinyint、smallin 、 mediumint、int、bigint

    分别使用 8 16 24 32 64 位存储空间

    unsigned 属性表示不允许负值,使正数的上限提升了一倍

    注意: 能选择的都是存储类型,计算时会使用64位 bigint计算

  2. 实数类型

    带有小数部分的数字。

    floutdouble 支持使用标准浮点运算进行近似运算

    decimal 用于存储精确小数,支持精确计算(5.0 之后MySQL 自身实现)

    可指定小数点前后所允许的最大位数

    注意: 计算时,MySQL 使用double进行计算

  3. 字符串类型

    varchar 与 char, 在磁盘和内存中的存储形式主要更存储引擎有关

    • varchar :变长字符串,需要额外使用 1 个或 2 个字节(列最大长度大于255字节)

      来记录字符串的长度,但由于变成,在进行UPDATE操作是易造成碎片

    • char:定长字符串,适用于存放短字符串,且MySQL会删除字符串末尾空格

      注意: char 、varchar 是以字符为单位

    • binary

    • varbinary

      存储二进制字符串,存储的是字节码,使用 ‘\0’ 进行填充

    • blob:二进制方式存取很大数据

      tinyblob、smallblob、(blob)、mediumblob、longblob、

    • text:字符当时存取很大数据

      tinytext、smalltext、(text)、mediumtext、longtext

  4. 日期和时间类型

    • datatime:能保存大范围的值,精度为s,把日期和时间封装到格式为YYYYMMDDHHMMSS

      的整数中,使用 8 个字节的存储空间。例如 ”2019-01-06 19:41:25“

    • timestamp:保存了从1970-01-01午夜到现在的秒数,使用4个字节的存储空间(1970-2048)

      其值与时区有关

  5. 位数据类型

    • bit:5.0 之前是tinyint 的同义词,5.0之后为一个新数据类型

      MySQL 将其当作字符串类型,而非数字类型

    • set:MySQL内部是以一系列打包的为的集合来表示

6. 加快 ALTER TABLE 操作速度

MySQL 执行大部分修改表结构的操作的方法是用新的结构创建一个空表,从旧表中查出所有数据插入到

新表中,然后删除旧表。

例如:

1
2
mysql> alter table sakila.film
-> modify column rental_duration tinyint(3) not null default 5;

而实际上,MySQL ALTER TABLE操作允许使用 ALTER COLUMN 、MODIFY COLUMN、CHANGE COLUMN语句

修改列,则上述SQL 语句可以修改为

1
2
mysql> alter table sakila.film
-> alter column rental_duration set default 5;

此语句会直接修改 .frm 文件而不涉及表数据

7. 创建高性能索引

索引在MySQL 中也叫做 “键(key)”, 是存储引擎用于快速找到记录的一种数据结构

注意:索引是在存储引擎层,而非服务器层,不同存储引擎索引工作方式并不相同

1. 索引的类型

  • B-Tree 索引

    若不特别指明类型,一般所说的索引都是B-Tree索引,使用B-Tree 数据结构进行存储,

    但不同存储引擎也可能使用不同的存储结构。适用于 全键值、键值范围和键值前缀查找

  • 哈希索引

    基于哈希表实现,只有精确匹配索引所有的列的查询才有效,每一列对应一个不同的哈希码,

    哈希索引将所有的哈希码存储在哈希索引中,同时在哈希表中保存执行每个数据行的指针。

    注意: 只有Memory 引擎显式支持哈希索引,且支持非唯一哈希索引

    InnoDB引擎可以在B-Tree索引基础上自动创建“自适应哈希索引”

  • 空间数据索引(R-Tree)

    MyISAM 表支持空间索引,可以用作地理数据存储。空间索引会从所有维度来索引数据

  • 全文索引

    查找的是文本中的关键字,而非直接比较索引中的值。全文索引适用于 MATCH AGAIINST

    操作,而不是普通的WHERE条件查询

  • 其他索引类别

    TokuDB 的 形树索引、ScaleDB的Patricia tries等

2. 索引的优点

  1. 大大减少了服务器需要扫描的数据量
  2. 帮助服务器避免排序和临时表
  3. 将随机IO变为顺序IO

8. MySQL 高级特性

MySQL 从 5.0 到 5.1 版本开始引入了很多新特性,例如分区、触发器等。

  1. 分区表

    对于用户而言,分区表是一个独立的逻辑表,但底层由多个物理子表组成。

    分区表实际上是一组对底层表的句柄对象的封装。对分区表的请求,都会通过句柄

    对象转化为对存储引擎的接口调用。 分区表主要目的是将数据按照一个较粗的力度

    分布在不同表中,将相关数据存放在一起,也适用于批量删除整个分区的数据。

  2. 视图

    5.0 版本之后引入,MySQL 在很多地方对应视图和表是同等对待的,当然也有不同,

    例如不能对视图创建触发器、不能对视图使用 drop table命令等。

    1
    2
    3
    4
    5
    6
    mysql> create view oceania as 
    -> seelct * from county where continent = 'Oceania'
    -> with check option;


    mysql> select code, name from oceania where name = 'Australia';

    MySQL 实现视图主要是使用临时表算法合并算法。 若视图中包含 GROUP BY、DISTINCT、 任何聚集函数、UNION、子查询等,MySQL 都会使用临时表算法来实现视图,且此类视图都无法被更新。

  3. 在MySQL 内部存储代码

    MySQL 允许通过触发器、存储过程、函数的形式来存储代码, 5.1 开始,还可以在定时任务中存放代码, 也被称为 “事件”

    • 存储过程和函数:可以替代一些小查询,解析和网络开销明显降低
    • 触发器:在执行 insert、update、delete 操作前后(同是只能选择一个)可以执行一些特定的操作。触发器无返回值,可以实现一些强制限制,简化应用逻辑,提高性能。MySQL 触发器“基于行的触发”设计。
    • 事件:事件在一个独立事件调度线程中被初始化,且该线程会在执行技术后被销毁,不会放到线程缓存。
    • 游标:MySQL 在服务器端提供只读、单向的游标,只能在存储过程或更底层的客户端api中使用。
  4. 用户自定义函数(UDF)

    可以使用支持C语言调用约定的任何编程语言来实现用户自定义函数,适合用作计算或与外界交互。

  5. MySQL 中 字符集 (默认 latin1 及 ISO-8859-1)

    只有基于字符的值才真正的“有”字符集的概念。校对规则和字符集总是一起使用。

    • 创建对象时的默认设置(character_set_server)

      默认设置逐层继承:数据库 -> 表 -> 列 真正存放数据的是列,只有在创建列没有为列指定字符集时,

      表的默认字符集才有用。

    • 服务器和客户端通信时的设置(character_set_client)

      character_set_client -> character_set_connection -> character_set_result

    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
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    149
    150
    151
    152
    153
    154
    155
    156
    157
    158
    159
    160
    161
    162
    163
    164
    165
    166
    167
    168
    169
    170
    171
    172
    173
    174
    175
    176
    177
    178
    179
    180
    181
    182
    183
    184
    185
    186
    187
    188
    189
    190
    191
    192
    193
    194
    195
    196
    197
    198
    199
    200
    201
    202
    203
    204
    205
    206
    207
    208
    209
    210
    211
    212
    213
    214
    215
    216
    217
    218
    219
    220
    221
    222
    223
    224
    225
    226
    227
    228
    229
    230
    231
    232
    233
    234
    235
    236
    237
    238
    239
    240
    241
    242
    243
    244
    245
    246
    247
    248
    249
    250
    251
    252
    253
    254
    255
    256
    257
    258
    259
    260
    261
    262
    263
    264
    265
    266
    267
    268
    269
    270
    271
    272
    273
    274
    275
    276
    mysql> SHOW COLLATION;
    +--------------------------+----------+-----+---------+----------+---------+
    | Collation | Charset | Id | Default | Compiled | Sortlen |
    +--------------------------+----------+-----+---------+----------+---------+
    | big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
    | big5_bin | big5 | 84 | | Yes | 1 |
    | dec8_swedish_ci | dec8 | 3 | Yes | Yes | 1 |
    | dec8_bin | dec8 | 69 | | Yes | 1 |
    | cp850_general_ci | cp850 | 4 | Yes | Yes | 1 |
    | cp850_bin | cp850 | 80 | | Yes | 1 |
    | hp8_english_ci | hp8 | 6 | Yes | Yes | 1 |
    | hp8_bin | hp8 | 72 | | Yes | 1 |
    | koi8r_general_ci | koi8r | 7 | Yes | Yes | 1 |
    | koi8r_bin | koi8r | 74 | | Yes | 1 |
    | latin1_german1_ci | latin1 | 5 | | Yes | 1 |
    | latin1_swedish_ci | latin1 | 8 | Yes | Yes | 1 |
    | latin1_danish_ci | latin1 | 15 | | Yes | 1 |
    | latin1_german2_ci | latin1 | 31 | | Yes | 2 |
    | latin1_bin | latin1 | 47 | | Yes | 1 |
    | latin1_general_ci | latin1 | 48 | | Yes | 1 |
    | latin1_general_cs | latin1 | 49 | | Yes | 1 |
    | latin1_spanish_ci | latin1 | 94 | | Yes | 1 |
    | latin2_czech_cs | latin2 | 2 | | Yes | 4 |
    | latin2_general_ci | latin2 | 9 | Yes | Yes | 1 |
    | latin2_hungarian_ci | latin2 | 21 | | Yes | 1 |
    | latin2_croatian_ci | latin2 | 27 | | Yes | 1 |
    | latin2_bin | latin2 | 77 | | Yes | 1 |
    | swe7_swedish_ci | swe7 | 10 | Yes | Yes | 1 |
    | swe7_bin | swe7 | 82 | | Yes | 1 |
    | ascii_general_ci | ascii | 11 | Yes | Yes | 1 |
    | ascii_bin | ascii | 65 | | Yes | 1 |
    | ujis_japanese_ci | ujis | 12 | Yes | Yes | 1 |
    | ujis_bin | ujis | 91 | | Yes | 1 |
    | sjis_japanese_ci | sjis | 13 | Yes | Yes | 1 |
    | sjis_bin | sjis | 88 | | Yes | 1 |
    | hebrew_general_ci | hebrew | 16 | Yes | Yes | 1 |
    | hebrew_bin | hebrew | 71 | | Yes | 1 |
    | tis620_thai_ci | tis620 | 18 | Yes | Yes | 4 |
    | tis620_bin | tis620 | 89 | | Yes | 1 |
    | euckr_korean_ci | euckr | 19 | Yes | Yes | 1 |
    | euckr_bin | euckr | 85 | | Yes | 1 |
    | koi8u_general_ci | koi8u | 22 | Yes | Yes | 1 |
    | koi8u_bin | koi8u | 75 | | Yes | 1 |
    | gb2312_chinese_ci | gb2312 | 24 | Yes | Yes | 1 |
    | gb2312_bin | gb2312 | 86 | | Yes | 1 |
    | greek_general_ci | greek | 25 | Yes | Yes | 1 |
    | greek_bin | greek | 70 | | Yes | 1 |
    | cp1250_general_ci | cp1250 | 26 | Yes | Yes | 1 |
    | cp1250_czech_cs | cp1250 | 34 | | Yes | 2 |
    | cp1250_croatian_ci | cp1250 | 44 | | Yes | 1 |
    | cp1250_bin | cp1250 | 66 | | Yes | 1 |
    | cp1250_polish_ci | cp1250 | 99 | | Yes | 1 |
    | gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 |
    | gbk_bin | gbk | 87 | | Yes | 1 |
    | latin5_turkish_ci | latin5 | 30 | Yes | Yes | 1 |
    | latin5_bin | latin5 | 78 | | Yes | 1 |
    | armscii8_general_ci | armscii8 | 32 | Yes | Yes | 1 |
    | armscii8_bin | armscii8 | 64 | | Yes | 1 |
    | utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
    | utf8_bin | utf8 | 83 | | Yes | 1 |
    | utf8_unicode_ci | utf8 | 192 | | Yes | 8 |
    | utf8_icelandic_ci | utf8 | 193 | | Yes | 8 |
    | utf8_latvian_ci | utf8 | 194 | | Yes | 8 |
    | utf8_romanian_ci | utf8 | 195 | | Yes | 8 |
    | utf8_slovenian_ci | utf8 | 196 | | Yes | 8 |
    | utf8_polish_ci | utf8 | 197 | | Yes | 8 |
    | utf8_estonian_ci | utf8 | 198 | | Yes | 8 |
    | utf8_spanish_ci | utf8 | 199 | | Yes | 8 |
    | utf8_swedish_ci | utf8 | 200 | | Yes | 8 |
    | utf8_turkish_ci | utf8 | 201 | | Yes | 8 |
    | utf8_czech_ci | utf8 | 202 | | Yes | 8 |
    | utf8_danish_ci | utf8 | 203 | | Yes | 8 |
    | utf8_lithuanian_ci | utf8 | 204 | | Yes | 8 |
    | utf8_slovak_ci | utf8 | 205 | | Yes | 8 |
    | utf8_spanish2_ci | utf8 | 206 | | Yes | 8 |
    | utf8_roman_ci | utf8 | 207 | | Yes | 8 |
    | utf8_persian_ci | utf8 | 208 | | Yes | 8 |
    | utf8_esperanto_ci | utf8 | 209 | | Yes | 8 |
    | utf8_hungarian_ci | utf8 | 210 | | Yes | 8 |
    | utf8_sinhala_ci | utf8 | 211 | | Yes | 8 |
    | utf8_german2_ci | utf8 | 212 | | Yes | 8 |
    | utf8_croatian_ci | utf8 | 213 | | Yes | 8 |
    | utf8_unicode_520_ci | utf8 | 214 | | Yes | 8 |
    | utf8_vietnamese_ci | utf8 | 215 | | Yes | 8 |
    | utf8_general_mysql500_ci | utf8 | 223 | | Yes | 1 |
    | ucs2_general_ci | ucs2 | 35 | Yes | Yes | 1 |
    | ucs2_bin | ucs2 | 90 | | Yes | 1 |
    | ucs2_unicode_ci | ucs2 | 128 | | Yes | 8 |
    | ucs2_icelandic_ci | ucs2 | 129 | | Yes | 8 |
    | ucs2_latvian_ci | ucs2 | 130 | | Yes | 8 |
    | ucs2_romanian_ci | ucs2 | 131 | | Yes | 8 |
    | ucs2_slovenian_ci | ucs2 | 132 | | Yes | 8 |
    | ucs2_polish_ci | ucs2 | 133 | | Yes | 8 |
    | ucs2_estonian_ci | ucs2 | 134 | | Yes | 8 |
    | ucs2_spanish_ci | ucs2 | 135 | | Yes | 8 |
    | ucs2_swedish_ci | ucs2 | 136 | | Yes | 8 |
    | ucs2_turkish_ci | ucs2 | 137 | | Yes | 8 |
    | ucs2_czech_ci | ucs2 | 138 | | Yes | 8 |
    | ucs2_danish_ci | ucs2 | 139 | | Yes | 8 |
    | ucs2_lithuanian_ci | ucs2 | 140 | | Yes | 8 |
    | ucs2_slovak_ci | ucs2 | 141 | | Yes | 8 |
    | ucs2_spanish2_ci | ucs2 | 142 | | Yes | 8 |
    | ucs2_roman_ci | ucs2 | 143 | | Yes | 8 |
    | ucs2_persian_ci | ucs2 | 144 | | Yes | 8 |
    | ucs2_esperanto_ci | ucs2 | 145 | | Yes | 8 |
    | ucs2_hungarian_ci | ucs2 | 146 | | Yes | 8 |
    | ucs2_sinhala_ci | ucs2 | 147 | | Yes | 8 |
    | ucs2_german2_ci | ucs2 | 148 | | Yes | 8 |
    | ucs2_croatian_ci | ucs2 | 149 | | Yes | 8 |
    | ucs2_unicode_520_ci | ucs2 | 150 | | Yes | 8 |
    | ucs2_vietnamese_ci | ucs2 | 151 | | Yes | 8 |
    | ucs2_general_mysql500_ci | ucs2 | 159 | | Yes | 1 |
    | cp866_general_ci | cp866 | 36 | Yes | Yes | 1 |
    | cp866_bin | cp866 | 68 | | Yes | 1 |
    | keybcs2_general_ci | keybcs2 | 37 | Yes | Yes | 1 |
    | keybcs2_bin | keybcs2 | 73 | | Yes | 1 |
    | macce_general_ci | macce | 38 | Yes | Yes | 1 |
    | macce_bin | macce | 43 | | Yes | 1 |
    | macroman_general_ci | macroman | 39 | Yes | Yes | 1 |
    | macroman_bin | macroman | 53 | | Yes | 1 |
    | cp852_general_ci | cp852 | 40 | Yes | Yes | 1 |
    | cp852_bin | cp852 | 81 | | Yes | 1 |
    | latin7_estonian_cs | latin7 | 20 | | Yes | 1 |
    | latin7_general_ci | latin7 | 41 | Yes | Yes | 1 |
    | latin7_general_cs | latin7 | 42 | | Yes | 1 |
    | latin7_bin | latin7 | 79 | | Yes | 1 |
    | utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 |
    | utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 |
    | utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 |
    | utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 |
    | utf8mb4_latvian_ci | utf8mb4 | 226 | | Yes | 8 |
    | utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 8 |
    | utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 8 |
    | utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 8 |
    | utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 |
    | utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 8 |
    | utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 8 |
    | utf8mb4_turkish_ci | utf8mb4 | 233 | | Yes | 8 |
    | utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 8 |
    | utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 8 |
    | utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 8 |
    | utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 8 |
    | utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 8 |
    | utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 8 |
    | utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 |
    | utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 8 |
    | utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 8 |
    | utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 8 |
    | utf8mb4_german2_ci | utf8mb4 | 244 | | Yes | 8 |
    | utf8mb4_croatian_ci | utf8mb4 | 245 | | Yes | 8 |
    | utf8mb4_unicode_520_ci | utf8mb4 | 246 | | Yes | 8 |
    | utf8mb4_vietnamese_ci | utf8mb4 | 247 | | Yes | 8 |
    | cp1251_bulgarian_ci | cp1251 | 14 | | Yes | 1 |
    | cp1251_ukrainian_ci | cp1251 | 23 | | Yes | 1 |
    | cp1251_bin | cp1251 | 50 | | Yes | 1 |
    | cp1251_general_ci | cp1251 | 51 | Yes | Yes | 1 |
    | cp1251_general_cs | cp1251 | 52 | | Yes | 1 |
    | utf16_general_ci | utf16 | 54 | Yes | Yes | 1 |
    | utf16_bin | utf16 | 55 | | Yes | 1 |
    | utf16_unicode_ci | utf16 | 101 | | Yes | 8 |
    | utf16_icelandic_ci | utf16 | 102 | | Yes | 8 |
    | utf16_latvian_ci | utf16 | 103 | | Yes | 8 |
    | utf16_romanian_ci | utf16 | 104 | | Yes | 8 |
    | utf16_slovenian_ci | utf16 | 105 | | Yes | 8 |
    | utf16_polish_ci | utf16 | 106 | | Yes | 8 |
    | utf16_estonian_ci | utf16 | 107 | | Yes | 8 |
    | utf16_spanish_ci | utf16 | 108 | | Yes | 8 |
    | utf16_swedish_ci | utf16 | 109 | | Yes | 8 |
    | utf16_turkish_ci | utf16 | 110 | | Yes | 8 |
    | utf16_czech_ci | utf16 | 111 | | Yes | 8 |
    | utf16_danish_ci | utf16 | 112 | | Yes | 8 |
    | utf16_lithuanian_ci | utf16 | 113 | | Yes | 8 |
    | utf16_slovak_ci | utf16 | 114 | | Yes | 8 |
    | utf16_spanish2_ci | utf16 | 115 | | Yes | 8 |
    | utf16_roman_ci | utf16 | 116 | | Yes | 8 |
    | utf16_persian_ci | utf16 | 117 | | Yes | 8 |
    | utf16_esperanto_ci | utf16 | 118 | | Yes | 8 |
    | utf16_hungarian_ci | utf16 | 119 | | Yes | 8 |
    | utf16_sinhala_ci | utf16 | 120 | | Yes | 8 |
    | utf16_german2_ci | utf16 | 121 | | Yes | 8 |
    | utf16_croatian_ci | utf16 | 122 | | Yes | 8 |
    | utf16_unicode_520_ci | utf16 | 123 | | Yes | 8 |
    | utf16_vietnamese_ci | utf16 | 124 | | Yes | 8 |
    | utf16le_general_ci | utf16le | 56 | Yes | Yes | 1 |
    | utf16le_bin | utf16le | 62 | | Yes | 1 |
    | cp1256_general_ci | cp1256 | 57 | Yes | Yes | 1 |
    | cp1256_bin | cp1256 | 67 | | Yes | 1 |
    | cp1257_lithuanian_ci | cp1257 | 29 | | Yes | 1 |
    | cp1257_bin | cp1257 | 58 | | Yes | 1 |
    | cp1257_general_ci | cp1257 | 59 | Yes | Yes | 1 |
    | utf32_general_ci | utf32 | 60 | Yes | Yes | 1 |
    | utf32_bin | utf32 | 61 | | Yes | 1 |
    | utf32_unicode_ci | utf32 | 160 | | Yes | 8 |
    | utf32_icelandic_ci | utf32 | 161 | | Yes | 8 |
    | utf32_latvian_ci | utf32 | 162 | | Yes | 8 |
    | utf32_romanian_ci | utf32 | 163 | | Yes | 8 |
    | utf32_slovenian_ci | utf32 | 164 | | Yes | 8 |
    | utf32_polish_ci | utf32 | 165 | | Yes | 8 |
    | utf32_estonian_ci | utf32 | 166 | | Yes | 8 |
    | utf32_spanish_ci | utf32 | 167 | | Yes | 8 |
    | utf32_swedish_ci | utf32 | 168 | | Yes | 8 |
    | utf32_turkish_ci | utf32 | 169 | | Yes | 8 |
    | utf32_czech_ci | utf32 | 170 | | Yes | 8 |
    | utf32_danish_ci | utf32 | 171 | | Yes | 8 |
    | utf32_lithuanian_ci | utf32 | 172 | | Yes | 8 |
    | utf32_slovak_ci | utf32 | 173 | | Yes | 8 |
    | utf32_spanish2_ci | utf32 | 174 | | Yes | 8 |
    | utf32_roman_ci | utf32 | 175 | | Yes | 8 |
    | utf32_persian_ci | utf32 | 176 | | Yes | 8 |
    | utf32_esperanto_ci | utf32 | 177 | | Yes | 8 |
    | utf32_hungarian_ci | utf32 | 178 | | Yes | 8 |
    | utf32_sinhala_ci | utf32 | 179 | | Yes | 8 |
    | utf32_german2_ci | utf32 | 180 | | Yes | 8 |
    | utf32_croatian_ci | utf32 | 181 | | Yes | 8 |
    | utf32_unicode_520_ci | utf32 | 182 | | Yes | 8 |
    | utf32_vietnamese_ci | utf32 | 183 | | Yes | 8 |
    | binary | binary | 63 | Yes | Yes | 1 |
    | geostd8_general_ci | geostd8 | 92 | Yes | Yes | 1 |
    | geostd8_bin | geostd8 | 93 | | Yes | 1 |
    | cp932_japanese_ci | cp932 | 95 | Yes | Yes | 1 |
    | cp932_bin | cp932 | 96 | | Yes | 1 |
    | eucjpms_japanese_ci | eucjpms | 97 | Yes | Yes | 1 |
    | eucjpms_bin | eucjpms | 98 | | Yes | 1 |
    | gb18030_chinese_ci | gb18030 | 248 | Yes | Yes | 2 |
    | gb18030_bin | gb18030 | 249 | | Yes | 1 |
    | gb18030_unicode_520_ci | gb18030 | 250 | | Yes | 8 |
    +--------------------------+----------+-----+---------+----------+---------+
    222 rows in set (0.00 sec)

    mysql> show character set;
    +----------+---------------------------------+---------------------+--------+
    | Charset | Description | Default collation | Maxlen |
    +----------+---------------------------------+---------------------+--------+
    | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
    | dec8 | DEC West European | dec8_swedish_ci | 1 |
    | cp850 | DOS West European | cp850_general_ci | 1 |
    | hp8 | HP West European | hp8_english_ci | 1 |
    | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
    | latin1 | cp1252 West European | latin1_swedish_ci | 1 |
    | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
    | swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
    | ascii | US ASCII | ascii_general_ci | 1 |
    | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
    | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
    | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
    | tis620 | TIS620 Thai | tis620_thai_ci | 1 |
    | euckr | EUC-KR Korean | euckr_korean_ci | 2 |
    | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
    | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
    | greek | ISO 8859-7 Greek | greek_general_ci | 1 |
    | cp1250 | Windows Central European | cp1250_general_ci | 1 |
    | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
    | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
    | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
    | utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
    | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
    | cp866 | DOS Russian | cp866_general_ci | 1 |
    | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
    | macce | Mac Central European | macce_general_ci | 1 |
    | macroman | Mac West European | macroman_general_ci | 1 |
    | cp852 | DOS Central European | cp852_general_ci | 1 |
    | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
    | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
    | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
    | utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
    | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
    | cp1256 | Windows Arabic | cp1256_general_ci | 1 |
    | cp1257 | Windows Baltic | cp1257_general_ci | 1 |
    | utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
    | binary | Binary pseudo charset | binary | 1 |
    | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
    | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
    | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
    | gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
    +----------+---------------------------------+---------------------+--------+
    41 rows in set (0.00 sec)
  6. 全文索引

    开始只有MyISAM 存储引擎支持,MySQL 5.6 中InnoDB开始支持。

    其实要做全局索引,完全可以使用其他解决方案, 例如:Sphinx、Lucene、Solr、Groonga、Xaoian、Senna

  7. 分布式事务(XA)

    是一种在多个服务器之间同步数据的方法

    • 第一阶段:XA事务协调器需要保证所有事务参与者都完成了准备工作
    • 第二阶段:协调器收到所有参与者发来的消息,告诉所有是事务可以提交
  8. 查询缓存

    MySQL 查询缓存保存查询返还的完整结果。当查询命中该缓存时,会立刻返回结果,跳过解析、优化和执行阶段。根据经验来看,在高并发的情况下,查询缓存会导致系统的性能下降。若希望有更高的缓存效率,建议使用memcahced 等其他类似解决方案来进行替代。

9. 复制

1. 概述

MySQL 内建的复制功能是构建基于MySQL 的大规模、高性能应用的基础,这类应用使用所谓的 “水平扩展”的架构,通过为服务器配置一个或多个备库的方式来实现数据同步。

MySQL 支持两种复制方式:基于行的复制和基于语句的复制

  • 基于语句的复制(逻辑复制):在MySQL 3.23 版本中就存在
  • 基于行的复制:5.1 版本中才被引入。

通过在主库上记录二进制日志,在备库重放日志的形式来实现异步的数据复制。所以同一时间,主库与从库数据可能会存在不一致的现象。

注意: MySQL 复制大部分是向后兼容,新版本服务器可以作为老版本服务器的备库,但是反过来往往不行。

2. 复制解决的问题

  • 数据分布

    在不同地理位置来实现分布数据备份

  • 负载均衡

    将读操作分布到多个服务器上,实现读写分离

  • 备份

    作为数据备份的一项技术补充

  • 高可用和故障切换

    帮助应用程序避免MySQL单点失败

  • MySQL升级测试

    使用一个高版本的MySQL作为备库,保证在升级全部实例前,查询能够在备库按照预期执行。

3. 基于Docker 的MySQL 主从复制测试

由于资源有限,虚拟机、mysql等配置繁琐,而Docker容器之间相互独立,有独立ip,互不冲突,本着学无止境的精神,故选择在本地已经配置好Docker 的 一 CentOS 虚拟机学习搭建MySQL 主从服务器。

  • 首先拉取docker镜像,我们这里使用5.7.24版本的mysql:

    1
    2
    3
    4
    5
    6
    7
    8
    # 查找 mysql 镜像, 去docker hub可以查看镜像版本
    docker search mysql

    # 拉取 mysql 5.7.24 镜像到本地
    docker pull mysql:5.7.24

    # 查看本地镜像
    docker images
  • 使用此镜像启动两个 MySQL 实例容器

    1
    2
    3
    4
    5
    # 主(master)
    docker run -p 3339:3306 --name mysql_master -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7.24

    # 从(slave)
    docker run -p 3340:3306 --name mysql_slave1 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7.24

    -p 外部端口:内部端口 端口映射

    – name 自定义启动容器名字

    -e MYSQL_ROOT_PASSWORD=123456 定义mysql 容器root密码为123456

    -d 容器后台运行

    mysql:5.7.24 运行mysql:5.7.24 镜像

    因为docker容器是相互独立的,每个容器有其独立的ip,所以不同容器使用相同的端口并不会冲突

    1
    2
    3
    4
    [root@localhost ~]# docker ps
    CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
    fc8b11595f99 mysql:5.7.24 "docker-entrypoint..." 4 minutes ago Up 4 minutes 33060/tcp, 0.0.0.0:3340->3306/tcp mysql_slave1
    0bc8050bd989 mysql:5.7.24 "docker-entrypoint..." 6 minutes ago Up 6 minutes 33060/tcp, 0.0.0.0:3339->3306/tcp mysql_master
  • 配置主库Master

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    docker exec -it mysql_master /bin/bash
    # 切换到 /etc/mysql 目录
    cd /etc/mysql
    # 对 my.cnf 进项配置
    vim my.cnf
    # 提示。。。。。。好吧。。。
    root@0bc8050bd989:/etc/mysql# cat /etc/issue
    Debian GNU/Linux 9 \n \l
    # 这玩意基于 Debian 啊。好吧!
    apt-get uddate
    apt-get install vim
    # 软件源速度比较慢。。。静静等待。。。
    vim my.cnf

    在my.cnf 内进进行如下配置

    1
    2
    3
    4
    5
    [mysqld]
    ## 同一局域网内注意要唯一
    server-id=100
    ## 开启二进制日志功能,可以随便取(关键)
    log-bin=mysql-bin
    1
    2
    3
    4
    # 重启mysql 服务,使配置文件生效
    service mysql restart
    # 重启docker 容器
    docker start mysql_master
  • 在master数据库创建数据同步用户,授予用户 slave REPLICATION SLAVE权限和REPLICATION CLIENT权限,用于在主从库之间同步数据。

    1
    2
    3
    4
    5
    mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
    Query OK, 0 rows affected

    mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
    Query OK, 0 rows affected
  • 配置从库Slave

    和配置Master(主)一样,在Slave配置文件my.cnf中添加如下配置:

    1
    2
    3
    4
    5
    6
    7
    [mysqld]
    ## 设置server_id,注意要唯一
    server-id=101
    ## 开启二进制日志功能,以备Slave作为其它Slave的Master时使用
    log-bin=mysql-slave-bin
    ## relay_log配置中继日志
    relay_log=edu-mysql-relay-bin

    配置完成后也需要重启mysql服务和docker容器,操作和配置Master(主)一致。

  • 链接Master(主)和 Slave(从)

    Master 进入mysql 执行 show master status

    1
    2
    3
    4
    5
    6
    7
    mysql> show master status;
    +------------------+----------+--------------+------------------+-------------------+
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000001 | 609 | | | |
    +------------------+----------+--------------+------------------+-------------------+
    1 row in set

    Slave 进入 mysql 执行

    1
    2
    3
    4
    5
    6
    7
    8
    mysql> change master to master_host='172.17.0.2',
    master_user='slave',
    master_password='123456',
    master_port=3306,
    master_log_file='mysql-bin.000001',
    master_log_pos= 609,
    master_connect_retry=30;
    Query OK, 0 rows affected

    说明:

    master_host :Master的地址,指的是容器的独立ip,可以通过

    1
    docker inspect --format='{{.NetworkSettings.IPAddress}}' 容器名称|容器id

    查询容器的ip

    1
    2
    3
    4
    [root@localhost ~]# docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql_master
    172.17.0.2
    [root@localhost ~]# docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql_slave1
    172.17.0.3

    master_port:Master的端口号,指的是容器的端口号

    master_user:用于数据同步的用户

    master_password:用于同步的用户的密码

    master_log_file:指定 Slave 从哪个日志文件开始复制数据,即上文中提到的 File 字段的值

    master_log_pos:从哪个 Position 开始读,即上文中提到的 Position 字段的值

    master_connect_retry:如果连接失败,重试的时间间隔,单位是秒,默认是60秒

    Slave 端执行 show slave status \G 查看主从同步状态

    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
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    mysql> show slave status \G
    *************************** 1. row ***************************
    Slave_IO_State:
    Master_Host: 172.17.0.2
    Master_User: slave
    Master_Port: 3306
    Connect_Retry: 30
    Master_Log_File: mysql-bin.000001
    Read_Master_Log_Pos: 609
    Relay_Log_File: edu-mysql-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: 609
    Relay_Log_Space: 154
    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
    Master_UUID:
    Master_Info_File: /var/lib/mysql/master.info
    SQL_Delay: 0
    SQL_Remaining_Delay: NULL
    Slave_SQL_Running_State:
    Master_Retry_Count: 86400
    Master_Bind:
    Last_IO_Error_Timestamp:
    Last_SQL_Error_Timestamp:
    Master_SSL_Crl:
    Master_SSL_Crlpath:
    Retrieved_Gtid_Set:
    Executed_Gtid_Set:
    Auto_Position: 0
    Replicate_Rewrite_DB:
    Channel_Name:
    Master_TLS_Version:
    1 row in set (0.00 sec)

    SlaveIORunning 和 SlaveSQLRunning 都是No,因为我们还没有开启主从复制过程。

    使用start slave开启主从复制过程,然后再次查询主从同步状态show slave status \G;

    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
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    mysql> start slave;
    Query OK, 0 rows affected (0.19 sec)

    mysql> show slave status \G
    *************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: 172.17.0.2
    Master_User: slave
    Master_Port: 3306
    Connect_Retry: 30
    Master_Log_File: mysql-bin.000001
    Read_Master_Log_Pos: 609
    Relay_Log_File: edu-mysql-relay-bin.000002
    Relay_Log_Pos: 320
    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: 609
    Relay_Log_Space: 531
    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: 100
    Master_UUID: a370118a-fd4e-11e8-ad2f-0242ac110002
    Master_Info_File: /var/lib/mysql/master.info
    SQL_Delay: 0
    SQL_Remaining_Delay: NULL
    Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
    Master_Retry_Count: 86400
    Master_Bind:
    Last_IO_Error_Timestamp:
    Last_SQL_Error_Timestamp:
    Master_SSL_Crl:
    Master_SSL_Crlpath:
    Retrieved_Gtid_Set:
    Executed_Gtid_Set:
    Auto_Position: 0
    Replicate_Rewrite_DB:
    Channel_Name:
    Master_TLS_Version:
    1 row in set (0.00 sec)

    SlaveIORunning 和 SlaveSQLRunning 都是Yes,说明主从复制已经开启。此时可以测试数据同步是否成功。

  • 测试主从复制

    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
    # Master 创建 test 数据库
    mysql> create database test;
    Query OK, 1 row affected

    mysql> show databases;
    +--------------------+
    | Database |
    +--------------------+
    | information_schema |
    | mysql |
    | performance_schema |
    | sys |
    | test |
    +--------------------+
    5 rows in set


    # Slave从库查看数据库
    mysql> show databases;
    +--------------------+
    | Database |
    +--------------------+
    | information_schema |
    | mysql |
    | performance_schema |
    | sys |
    | test |
    +--------------------+
    5 rows in set

    至此两个数据库同步成功!

4. 从另一台服务器开始复制

上述测试为两台新服务器配置主从复制,而通常情况下是一个已经运行了一段时间的主库,然后用一台新安装的备库与之同步。下面是一些从其他数据库备份数据库到从库的方法:

  • 冷备份:关闭主库,将数据复制到备库

  • 热备份:若仅使用MyISAM 表,可以在运行时使用mysqlhotcopy 或 rsync 来复制数据

  • 使用mysqldump:若仅适用InnoDB表,可以使用

    mysqldump --single-transaction --all-databases --master-data=1--host=master | mysql --host=slave

  • 使用快照或备份:只需知道对应的二进制日志坐标,就可以使用主库的快照或备份来初始化从库

  • 使用 Percona Xtrabackup:一款开源的热备份工具

  • 使用另外的从库

5. 复制的原理

1. 基于语句的复制

MySQL 5.0 之前版本只支持基于语句的复制(逻辑复制)。主库记录那些造成更改的查询,当备库读取并重放这些事件时,实际上是将主库执行的SQL再执行一遍。 优点就是实现比较简单,日志文件比较紧凑。缺点是无法考虑其他因素,例如若主库执行语句使用了当前时间戳或CURRENT_USER() 函数、触发器、存储过程等,可能就无法完整复现。还有串行化的更新使用更多的锁,造成性能下降。

2. 基于行的复制

MySQL 5.1 开始支持基于行的复制。将实际数据记录在二进制日志中,所以无需重放主库数据的查询,效率与基于语句的复制相比会更高一些。当时由于基于行的复制记录的是实际数据,在做一些简单的update操作时会将全表更新,意味着日志文件会写入全表数据,使得日志记录效率降低。

所以:

实际上MySQL 会在来年两种复制模式之间动态切换。默认情况下使用的是基于语句的复制方式,但如果发现语句无法被正确复制,就会切换到基于行的复制复制模式。

3. 复制文件

开启复制后除了 二进制日志文件中继日志文件,还会使用其他一些文件。

mysql-bin.index: 这个文件每一行都包含了二进制文件的文件名

mysql-relay-bin-index: 中继日志文件索引文件

master.info: 保存备库连接到主库所需的信息(纯文本)

relay-log.info: 此文件包含了当前备库复制的二进制日志和中继日志坐标

5. 发送复制事件到其他备库

log_slave_udpates 选项可以让备库变成其他服务器的主库

6. 复制过滤

  • 主库过滤记录到二进制文件
  • 备库过滤记录到中继日志文件

7. 复制拓扑

  • 一个MySQL 备库实例只能有一个主库(MySQL 不支持多主复制)
  • 每个备库必须有一个唯一的服务器ID
  • 一个主库可以有多个备库
  • 若打开了 log_slave_updates 选项,一个备库可以将其主库上的数据变化传播到其他备库