注意：学习的 MySQL 版本为 5.7，更高版本可能有些不同
1. 索引
1.1. 查看索引
SHOW INDEX FROM 数据库表名
1.2. 创建索引
-- 普通索引，如果是 CHAR,VARCHAR 类型，length 可以小于字段实际长度;
-- 如果是 BLOB 和 TEXT 类型，必须指定 length。
-- 下同。
CREATE INDEX indexName ON tableName(tableColumns(length));
-- 唯一索引
CREATE UNIQUE INDEX indexName ON tableName(tableColumns(length))
-- 主键索引一般在建表的时候指定
CREATE TABLE mytable(
id INT NOT NULL,
username VARCHAR(16) NOT NULL,
PRIMARY KEY(id)
);
1.3. 添加索引
-- 添加主键索引，索引值必须是唯一的，且不能为 NULL。columnList 用逗号分隔。
ALTER TABLE tableName ADD PRIMARY KEY (columnList)
-- 添加唯一索引，创建索引的值必须是唯一的（除了 NULL 外，NULL 可能会出现多次）。
ALTER TABLE tableName ADD UNIQUE indexName (columnList)
-- 添加普通索引，索引值可出现多次。
ALTER TABLE tableName ADD INDEX indexName (columnList)
-- 指定索引为 FULLTEXT，用于全文索引。
ALTER TABLE tableName ADD FULLTEXT indexName (columnList)
1.4. 删除索引
DROP INDEX [indexName] ON tableName;
1.5. 哪些情况需要创建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的应该创建索引
- 查询中与其他表关联的字段，外键关系建立索引
- WHERE 条件的高频字段可以创建索引
- 查询中排序的字段可以创建索引，将大大提高排序速度
- 查询中统计或者分组字段
1.6. 哪些情况不适合创建索引
- 频繁更新的字段不适合创建索引，因为更新字段不仅是更新记录还会更新索引
- WHERE 条件里用不到的字段
- 表记录太少也没必要建索引
- 经常增删改的表也不需要建，虽然能提升查询速度，但是会降低更新表（
INSERTUPDATEDELETE）的速度，在更新数据的同时还会更新索引文件 - 如果某个字段的值包含许多重复内容则建索引没有太大实际效果（比如性别）
1.7. 性能优化之 EXPLAIN
1.7.1. EXPLAIN 概念
EXPLAIN 会向我们提供一些 MySQL 是执行 sql 的信息：
- EXPLAIN 可以解释说明 SELECT, DELETE, INSERT, REPLACE 和 UPDATE 等语句
- 当 EXPLAIN 与可解释的语句一起使用时，MySQL 会显示一些来自于优化器的关于 sql 执行计划的信息。即 MySQL 解释它是如何处理这些语句的，和表之间是如何连接的。想获取更多关于 EXPLAIN 如何获取执行计划信息的
- 当 EXPLAIN 后面是一个会话的 connection_id 而不是一个可执行的语句时，它会展示会话的信息
- 对于 SELECT 语句，EXPLAIN 会产生额外的执行计划信息，这些信息可以用 SHOW WARNINGS 显示出来
- EXPLAIN 对于检查设计分区表的查询时非常有用
- FORMAT 选项可以用于选择输出格式，如果没有配置 FORMAT 选项，默认已表格形式输出。JSON 选项让信息以 json 格式展示
1.7.2. EXPLAIN 输出列信息
EXPLAIN 输出的字段信息:
第一列:列名
第二列:FORMAT = JSON 时输出中显示的等效属性名称
第三列：字段含义
| Column | JSON Name | Meaning |
|---|---|---|
| id | select_id | select 标识号 |
| select_type | None | select 类型 |
| table | table_name | 这一行数据是关于哪张表的 |
| partitions | partitions | 匹配的分区，对于未分区表，该值为空 |
| type | access_type | 使用的连接类别,有无使用索引 |
| possible_keys | possible_keys | MySQL 能使用哪个索引在该表中找到行 |
| key | key | MySQL 实际决定使用的键（索引） |
| key_len | key_length | MySQL 决定使用的键长度。如果键是 NULL，长度为 NULL |
| ref | ref | 与索引关联的列 |
| rows | rows | MySQL 认为执行 sql 时必须被校验的行数 |
| filtered | filtered | 表示此查询条件所过滤的数据的百分比 |
| Extra | None | 附加信息 |
1.7.2.1. id
SELECT 标识符。SELECT 在查询中的序列号，可以为空。
1.7.2.2. select_type
SELECT 类型，所有类型在下表中展示，JSON 格式的 EXPLAIN 将 SELECT 类型公开为 query_block 的属性，除非它是 SIMPLE 或 PRIMARY。 JSON 名称(不适用为 None)也显示在表中。
| select_type Value | JSON Name | Meaning |
|---|---|---|
| SIMPLE | None | 简单 SELECT(不使用 UNION 或子查询等) |
| PRIMARY | None | 嵌套查询时最外层的查询 |
| UNION | None | UNION 中的第二个或后面的 SELECT 语句 |
| DEPENDENT UNION | dependent (true) | UNION 中的第二个或以后的 SELECT 语句，且依赖于外部查询 |
| UNION RESULT | union_result | UNION 的结果 |
| SUBQUERY | None | 子查询中的第一个 SELECT 语句 |
| DEPENDENT SUBQUERY | dependent (true) | 子查询中的第一个 SELECT 语句，且依赖于外部查询 |
| DERIVED | None | 派生表（子查询中产生的临时表） |
| MATERIALIZED | materialized_from_subquery | 物化子查询 |
| UNCACHEABLE SUBQUERY | cacheable (false) | 无法缓存结果的子查询，必须对外部查询的每一行进行重新计算 |
| UNCACHEABLE UNION | cacheable (false) | UNION 中属于不可缓存子查询的第二个或以后的 SELECT 语句(请参考 UNCACHEABLE SUBQUERY) |
- SIMPLE：简单 SELECT(不使用 UNION 或子查询等)
- PRIMARY：嵌套查询时最外层的查询
- UNION：UNION 中的第二个或后面的 SELECT 语句
- DEPENDENT UNION：UNION 中的第二个或以后的 SELECT 语句，且依赖于外部查询
- UNION RESULT：UNION 的结果
- SUBQUERY：子查询中的第一个 SELECT 语句
- DEPENDENT SUBQUERY：子查询中的第一个 SELECT 语句，且依赖于外部查询
- DERIVED：派生表（子查询中产生的临时表）
1.7.2.3. table
显示这一行的数据是关于哪张表的，有时是真实的表名字，有时也可能是以下几种结果
- <unionM,N>: 指 id 为 M,N 行结果的并集
- <derivedN>: 该行是指 id 值为 n 的行的派生表结果。派生表可能来自例如 FROM 子句中的子查询
- <subqueryN>: 该行是指 id 值为 n 的行的物化子查询的结果
1.7.2.4. partitions
查询的记录所属于的分区，对于未分区表，该值为 NULL。
1.7.2.5. type
连接使用了哪种类别,有无使用索引,常用的类型有：system, const, eq_ref, ref, range, index, ALL（从左到右，性能越来越差），详情查看 EXPLAIN Output Format 中 EXPLAIN Join Types 部分。
NULL：MySQL 在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成
system：这个表（也可能是查询出来的临时表）只有一行数据 (system table). 是 const 中的一个特例
const：表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const 表很快，因为它们只读取一次！const 用于查询条件为 PRIMARY KEY 或 UNIQUE 索引并与常数值进行比较时的所有部分
SELECT * FROM tbl_name WHERE primary_key=1;
SELECT * FROM tbl_name
WHERE primary_key_part1=1 AND primary_key_part2=2;
- eq_ref：对于前几个表中的每一行组合，从该表中读取一行。除了 system 和 const，这是最好的连接类型。当连接使用索引的所有部分，并且索引是主键或唯一非空索引时，将使用它。eq_ref 可以用于使用
=操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
- ref 对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是 UNIQUE 或 PRIMARY KEY （换句话说，如果联接不能基于关键字查询结果为单个行的话），则使用 ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。ref 可以用于使用 = 或 <=> 操作符的带索引的列
SELECT * FROM ref_table WHERE key_column=expr;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
fulltext：使用 FULLTEXT 索引执行连接
ref_or_null：该联接类型 ref 类似，但是添加了 MySQL 可以专门搜索包含 NULL 值的行。在解决子查询中经常使用该联接类型的优化
SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;
index_merge：该联接类型表示使用了索引合并优化方法。在这种情况下，key 列包含了使用的索引的清单，key_len 包含了使用的索引的最长的关键元素
unique_subquery：该类型替换了下面形式的 IN 子查询的 ref：
value IN (SELECT primary_key FROM single_table WHERE some_expr)
unique_subquery 是一个索引查找函数，可以完全替换子查询，效率更高。
- index_subquery：该联接类型类似于 unique_subquery。可以替换 IN 子查询，但只适合下列形式的子查询中的非唯一索引：
value IN (SELECT key_column FROM single_table WHERE some_expr)
- range：只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引。key_len 包含所使用索引的最长关键元素。在该类型中 ref 列为 NULL。当使用 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符，用常量比较关键字列时，可以使用 range
SELECT * FROM tbl_name
WHERE key_column = 10;
SELECT * FROM tbl_name
WHERE key_column BETWEEN 10 and 20;
SELECT * FROM tbl_name
WHERE key_column IN (10,20,30);
SELECT * FROM tbl_name
WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
index：该联接类型与 ALL 相同，除了只有索引树被扫描。这通常比 ALL 快，因为索引文件通常比数据文件小。当查询只使用作为单索引一部分的列时，MySQL 可以使用该联接类型
ALL：对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记 const 的表，这通常不好，并且通常在其它情况下很差。通常可以增加更多的索引而不要使用 ALL，使得行能基于前面的表中的常数值或列值被检索出
1.7.2.6. possible_keys
possible_keys 列指出 MySQL 能使用哪个索引在该表中找到行。注意，该列完全独立于 EXPLAIN 输出所示的表的次序。这意味着在 possible_keys 中的某些键实际上不能按生成的表次序使用。
如果该列是 NULL，则没有相关的索引。在这种情况下，可以通过检查 WHERE 子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用 EXPLAIN 检查查询。
1.7.2.7. key
key 列显示 MySQL 实际决定使用的键（索引）。如果没有选择索引，键是 NULL。要想强制 MySQL 使用或忽视 possible_keys 列中的索引，在查询中使用 FORCE INDEX、USE INDEX 或者 IGNORE INDEX。
1.7.2.8. key_len
key_len 列显示 MySQL 决定使用的键长度。如果键是 NULL，则长度为 NULL。
使用的索引的长度。在不损失精确性的情况下，长度越短越好。
1.7.2.9. ref
ref 列显示使用哪个列或常数与 key 一起从表中选择行。
1.7.2.10. rows
rows 列显示 MySQL 认为它执行查询时必须检查的行数。
1.7.2.11. Extra
该列包含 MySQL 解决查询的详细信息：
Distinct：一旦 MYSQL 找到了与行相联合匹配的行，就不再搜索了
Not exists：MYSQL 优化了 LEFT JOIN，一旦它找到了匹配 LEFT JOIN 标准的行，就不再搜索了
Range checked for each：没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL 检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一
Using filesort：看到这个的时候，查询就需要优化了。MYSQL 需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
Using index：列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候
Using temporary：看到这个的时候，查询需要优化了。这里，MYSQL 需要创建一个临时表来存储结果，这通常发生在对不同的列集进行 ORDER BY 上，而不是 GROUP BY 上
Using where：使用了 WHERE 从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型 ALL 或 index，这就会发生，或者是查询有问题
1.8. 索引失效的场景
2. 慢查询日志
2.1. 查看是否开启慢查询日志记录
SHOW VARIABLES LIKE '%slow_query_log%';
5.7 默认是关闭状态，开启慢查询日志记录会对性能造成一定影响，不建议在生产环境长期开启。
第二行表示慢查询日志的文件路径记名字。
2.2. 开启慢查询日志记录
-- 1 表示开启，0 表示关闭，通过这种方式开启的只对本次会话有效，重启服务后就会失效，
-- 要想永久生效需要去修改配置文件(my.cnf/my.ini)
SET GLOBAL slow_query_log = 1;
2.3. 查看默认的“慢查询”时间阈值
SHOW VARIABLES LIKE '%long_query_time%';
默认时间为 10 秒，即执行时间超过 10 秒的 sql 语句才会被认为是慢查询。
2.4. 修改慢查询时间配置
-- 设置为 3 秒，根据实际情况调整，同样只对本次会话有效
SET GLOBAL long_query_time = 3;
注意：修改慢查询时间后关闭当前会话重新开一个，否则时间设置不会生效。
2.5. 测试是否能够记录
SELECT sleep(5); -- 故意延时 5 秒
3. MySQL 的锁
3.1. 锁的认识
3.1.1. 锁的解释
计算机协调多个进程或线程并发访问某一资源的机制。
3.1.2. 锁的重要性
在数据库中，除传统计算资源（CPU、RAM、I\O等）的争抢，数据也是一种供多用户共享的资源。如何保证数据并发访问的一致性，有效性，是所有数据库必须要解决的问题。锁冲突也是影响数据库并发访问性能的一个重要因素，因此锁对数据库尤其重要。
3.1.3. 锁的缺点
加锁是消耗资源的，锁的各种操作，包括获得锁、检测锁是否已解除、释放锁等 ，都会增加系统的开销。
3.1.4. 简单的例子
现如今网购已经特别普遍了，比如淘宝双十一活动，当天的人流量是千万及亿级别的，但商家的库存是有限的。系统为了保证商家的商品库存不发生超卖现象，会对商品的库存进行锁控制。当有用户正在下单某款商品最后一件时，系统会立马对该件商品进行锁定，防止其他用户也重复下单，直到支付动作完成才会释放（支付成功则立即减库存售罄，支付失败则立即释放）。
3.2. 锁的类型
3.2.1. 表锁（MyISAM 存储引擎的默认锁）
3.2.1.1. 种类
读锁（read lock），也叫共享锁（shared lock）：针对同一份数据，多个读操作可以同时进行而不会互相影响（SELECT）
写锁（write lock），也叫排他锁（exclusive lock）：当前操作没完成之前，会阻塞其它读和写操作（UPDATE、INSERT、DELETE）
3.2.1.2. 特点
- 对整张表加锁
- 开销小
- 加锁快
- 无死锁
- 锁粒度大，发生锁冲突概率大，并发性低
3.2.1.3. 结论
- 读锁会阻塞写操作，不会阻塞读操作
- 写锁会阻塞读和写操作
3.2.1.4. 建议
MyISAM 的读写锁调度是写优先，这也是 MyISAM 不适合做写为主表的引擎，因为写锁以后，其它线程不能做任何操作，大量的更新使查询很难得到锁，从而造成永远阻塞。
3.2.2. 行锁(InnoDB 存储引擎默认锁)
3.2.2.1. 种类
读锁（read lock），也叫共享锁（shared lock）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
写锁（write lock），也叫排他锁（exclusive lock）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享锁和排他锁
意向共享锁（IS）：一个事务给一个数据行加共享锁时，必须先获得表的IS锁
意向排它锁（IX）：一个事务给一个数据行加排他锁时，必须先获得该表的IX锁
3.2.2.2. 特点
- 对一行数据加锁
- 开销大
- 加锁慢
- 会出现死锁
- 锁粒度小，发生锁冲突概率最低，并发性高
3.2.2.3. 事务并发带来的问题
- 更新丢失
解决：让事务变成串行操作，而不是并发的操作，即对每个事务开始---对读取记录加排他锁
- 脏读
解决：隔离级别为Read uncommitted
- 不可重读
解决：使用Next-Key Lock算法来避免
- 幻读
解决：间隙锁（Gap Lock）
3.2.3. 页锁
开销、加锁时间和锁粒度介于表锁和行锁之间，会出现死锁，并发处理能力一般（此锁不做多介绍）
3.3. 如何上锁？
3.3.1. 表锁
- 隐式上锁（默认，自动加锁自动释放）
SELECT -- 上读锁
INSERT、UPDATE、DELETE -- 上写锁
- 显式加/解锁（手动）
LOCK TABLE tableName READ; -- 读锁
LOCK TABLE tableName WRITE; -- 写锁
-- 解锁（手动）
UNLOCK TABLES; -- 所有锁表
| session01 | session02 |
|---|---|
| LOCK TABLE teacher READ; // 上读锁 | |
| SELECT * FROM teacher; // 可以正常读取 | SELECT * FROM teacher;// 可以正常读取 |
| UPDATE teacher SET name = 3 WHERE id =2;// 报错因被上读锁不能写操作 | UPDATE teacher SET name = 3 WHERE id =2;// 被阻塞 |
| UNLOCK TABLES;// 解锁 | |
| UPDATE teacher SET name = 3 WHERE id =2;// 更新操作成功 |
| session01 | session02 |
|---|---|
| LOCK TABLE teacher WRITE;// 上写锁 | |
| SELECT * FROM teacher; // 可以正常读取 | SELECT * FROM teacher;// 被阻塞 |
| UPDATE teacher SET name = 3 WHERE id =2;// 可以正常更新操作 | UPDATE teacher SET name = 4 WHERE id =2;// 被阻塞 |
| UNLOCK TABLES;// 解锁 | |
| SELECT * FROM teacher;// 读取成功 | |
| UPDATE teacher SET name = 4 WHERE id =2;// 更新操作成功 |
3.3.2. 行锁
- 隐式上锁（默认，自动加锁自动释放）
SELECT -- 不会上锁
INSERT、UPDATE、DELETE -- 上写锁
- 显式上锁（手动）
SELECT * FROM tableName LOCK IN SHARE MODE;//读锁
SELECT * FROM tableName FOR UPDATE;//写锁
- 解锁（手动）
1. 提交事务（commit）
2. 回滚事务（rollback）
3. kill 阻塞进程
| session01 | session02 |
|---|---|
| begin; | |
| SELECT * FROM teacher WHERE id = 2 LOCK IN SHARE MODE;// 上读锁 | |
| SELECT * FROM teacher WHERE id = 2 LOCK IN SHARE MODE;// 上读锁 | |
| UPDATE teacher SET name = 3 WHERE id =2;// 可以更新操作 | UPDATE teacher SET name = 5 WHERE id =2;// 被阻塞 |
| commit; | |
| UPDATE teacher SET name = 5 WHERE id =2;// 更新操作成功 |
| session01 | session02 |
|---|---|
| begin; | |
| SELECT * FROM teacher WHERE id = 2 FOR UPDATE;// 上写锁 | |
| SELECT * FROM teacher WHERE id = 2;// 可以正常读取 | |
| UPDATE teacher SET name = 3 WHERE id =2;// 可以更新操作 | UPDATE teacher SET name = 5 WHERE id =2;// 被阻塞 |
| rollback; | |
| UPDATE teacher SET name = 5 WHERE id =2;// 更新操作成功 |
为什么上了写锁，别的事务还可以读操作？
因为 InnoDB 有 MVCC 机制（多版本并发控制），可以使用快照读，而不会被阻塞。
3.4.行锁的实现算法
3.4.1. Record Lock 锁
单个行记录上的锁。
Record Lock 总是会去锁住索引记录，如果 InnoDB 存储引擎表建立的时候没有设置任何一个索引，这时 InnoDB 存储引擎会使用隐式的主键来进行锁定。
3.4.2. Gap Lock 锁
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时， InnoDB 会给符合条件的已有数据记录的索引加锁，对于键值在条件范围内但并不存在的记录。
优点：解决了事务并发的幻读问题
不足：因为 query 执行过程中通过范围查找的话，他会锁定争个范围内所有的索引键值，即使这个键值并不存在。间隙锁有一个致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成锁定的时候无法插入锁定键值范围内任何数据。在某些场景下这可能会对性能造成很大的危害。
3.4.3. Next-key Lock 锁
同时锁住数据+间隙锁 在 Repeatable Read 隔离级别下， Next-key Lock 算法是默认的行记录锁定算法。
3.4.4. 行锁的注意点
- 只有通过索引条件检索数据时，InnoDB才会使用行级锁，否则会使用表级锁(索引失效，行锁变表锁)
- 即使是访问不同行的记录，如果使用的是相同的索引键，会发生锁冲突
- 如果数据表建有多个索引时，可以通过不同的索引锁定不同的行
3.5. 如何排查锁？
3.5.1. 表锁
3.5.1.1. 查看表锁情况
SHOW OPEN TABLES;
5.1.2. 表锁分析
SHOW STATUS LIKE 'table%';
- table_locks_waited:
出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次值加1），此值高说明存在着较严重的表级锁争用情况
- table_locks_immediate:
产生表级锁定次数，不是可以立即获取锁的查询次数，每立即获取锁加1
3.5.2. 行锁
3.5.2.1. 行锁分析
SHOW STATUS LIKE 'innodb_row_lock%';
- innodb_row_lock_current_waits: 当前正在等待锁定的数量
- innodb_row_lock_time: 从系统启动到现在锁定总时间长度
- innodb_row_lock_time_avg: 每次等待所花平均时间
- innodb_row_lock_time_max: 从系统启动到现在等待最长的一次所花时间
- innodb_row_lock_waits: 系统启动后到现在总共等待的次数
3.5.2.2. information_schema 库
- innodb_lock_waits 表
- innodb_locks 表
- innodb_trx 表
3.5.2.3. 优化建议
- 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
- 合理设计索引，尽量缩小锁的范围
- 尽可能较少检索条件，避免间隙锁
- 尽量控制事务大小，减少锁定资源量和时间长度
- 尽可能低级别事务隔离
3.6. 死锁
3.6.1. 解释
指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象
3.6.2. 产生的条件
- 互斥条件：一个资源每次只能被一个进程使用
- 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放
- 不剥夺条件：进程已获得的资源，在没有使用完之前，不能强行剥夺
- 循环等待条件：多个进程之间形成的一种互相循环等待的资源的关系
3.6.3. 解决
- 查看死锁：SHOW ENGINE INNODB STATUS
- 自动检测机制，超时自动回滚代价较小的事务（INNODB_lock_wait_timeout 默认50s）
- 人为解决，kill阻塞进程（SHOW processlist）
- wait for graph 等待图（主动检测）
3.6.4. 如何避免
- 加锁顺序一致，尽可能一次性锁定所需的数据行
- 尽量基于 primary（主键）或 unique key 更新数据
- 单次操作数据量不宜过多，涉及表尽量少
- 减少表上索引，减少锁定资源
- 尽量使用较低的隔离级别
- 尽量使用相同条件访问数据，这样可以避免间隙锁对并发的插入影响
- 精心设计索引，尽量使用索引访问数据
- 借助相关工具：pt-deadlock-logger
3.7. 乐观锁与悲观锁
3.7.1. 悲观锁
3.7.1.1. 解释
假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。
3.7.1.2. 实现机制
表锁、行锁等。
3.7.1.3. 实现层面
数据库本身。
3.7.1.4. 适用场景
并发量大。
3.7.2. 乐观锁
3.7.2.1. 解释
假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。
3.7.2.2. 实现机制
提交更新时检查版本号或者时间戳是否符合。
3.7.2.3. 实现层面
业务代码。
3.7.2.4. 适用场景
并发量小。
4. MySQL 主从复制
4.1. binlog 是什么
MySQL 的 Binlog 日志是一种二进制格式的日志，Binlog 记录所有的 DDL 和 DML 语句(除了数据查询语句SELECT、SHOW等)，以 Event 的形式记录，同时记录语句执行时间。
Binlog 的主要作用有两个：
数据恢复：因为 Binlog 详细记录了所有修改数据的 SQL，当某一时刻的数据误操作而导致出问题，或者数据库宕机数据丢失，那么可以根据 Binlog 来回放历史数据。
主从复制：想要做多机备份的业务，可以去监听当前写库的 Binlog 日志，同步写库的所有更改。
Binlog 包括两类文件：
二进制日志索引文件(.index)：记录所有的二进制文件
二进制日志文件(.00000*)：记录所有 DDL 和 DML 语句事件
Binlog 日志功能默认是开启的，线上情况下 Binlog 日志的增长速度是很快的，在 MySQL 的配置文件 my.cnf 中提供一些参数来对 Binlog 进行设置。
# 设置此参数表示启用 binlog 功能，并制定二进制日志的存储目录
log-bin=/home/mysql/binlog/
# mysql-bin.* 日志文件最大字节（单位：字节）
# 设置最大 100 MB
max_binlog_size=104857600
# 设置了只保留 7 天 BINLOG（单位：天）
expire_logs_days = 7
# binlog 日志只记录指定库的更新
# binlog-do-db=db_name
# binlog 日志不记录指定库的更新
# binlog-ignore-db=db_name
# 写缓冲多少次，刷一次磁盘，默认0
sync_binlog=0
需要注意的是：
max_binlog_size ：Binlog 最大和默认值是 1G，该设置并不能严格控制 Binlog 的大小，尤其是 Binlog 比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性不可能做切换日志的动作，只能将该事务的所有 SQL 都记录进当前日志直到事务结束。所以真实文件有时候会大于 max_binlog_size 设定值。
expire_logs_days ：Binlog 过期删除不是服务定时执行，是需要借助事件触发才执行，事件包括：
- 服务器重启
- 服务器被更新
- 日志达到了最大日志长度
max_binlog_size - 日志被刷新
二进制日志由配置文件的 log-bin 选项负责启用，MySQL 服务器将在数据根目录创建两个新文件 mysql-bin.000001 和 mysql-bin.index，若配置选项没有给出文件名，MySQL 将使用主机名称命名这两个文件，其中 .index 文件包含一份全体日志文件的清单。
sync_binlog：这个参数决定了 Binlog 日志的更新频率。默认 0 ，表示该操作由操作系统根据自身负载自行决定多久写一次磁盘。
sync_binlog = 1 表示每一条事务提交都会立刻写盘。sync_binlog=n 表示 n 个事务提交才会写盘。
根据 MySQL 文档，写 Binlog 的时机是：SQL transaction 执行完，但任何相关的 Locks 还未释放或事务还未最终 commit 前。这样保证了 Binlog 记录的操作时序与数据库实际的数据变更顺序一致。
检查 Binlog 文件是否已开启：
mysql> SHOW VARIABLES LIKE '%log_bin%';
+---------------------------------+------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------+
| log_bin | ON |
| log_bin_basename | /usr/local/mysql/data/binlog |
| log_bin_index | /usr/local/mysql/data/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+------------------------------------+
6 rows in set (0.00 sec)
MySQL 会把用户对所有数据库的内容和结构的修改情况记入 mysql-bin.n 文件，而不会记录 SELECT 和没有实际更新的 UPDATE 语句。
如果你不知道现在有哪些 Binlog 文件，可以使用如下命令：
SHOW BINARY LOGS; -- 查看 binlog 列表
SHOW MASTER STATUS; -- 查看最新的 binlog
mysql> SHOW BINARY LOGS;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 179 | No |
| mysql-bin.000002 | 156 | No |
+------------------+-----------+-----------+
2 rows in set (0.00 sec)
Binlog 文件是二进制文件，强行打开看到的必然是乱码，MySQL 提供了命令行的方式来展示 Binlog 日志：
mysqlbinlog mysql-bin.000002 | more
mysqlbinlog 命令即可查看。
看起来凌乱其实也有迹可循。Binlog 通过事件的方式来管理日志信息，可以通过 SHOW BINLOG EVENTS IN 的语法来查看当前 Binlog 文件对应的详细事件信息。
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-----------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| mysql-bin.000001 | 125 | Previous_gtids | 1 | 156 | |
| mysql-bin.000001 | 156 | Stop | 1 | 179 | |
+------------------+-----+----------------+-----------+-------------+-----------------------------------+
3 rows in set (0.01 sec)
这是一份没有任何写入数据的 Binlog 日志文件。
Binlog 的版本是 V4，可以看到日志的结束时间为 Stop。出现 Stop event 有两种情况：
- 是 master shut down 的时候会在 Binlog 文件结尾出现
- 是备机在关闭的时候会写入 relay log 结尾，或者执行 RESET SLAVE 命令执行
本文出现的原因是我有手动停止过 MySQL 服务。
一般来说一份正常的 Binlog 日志文件会以 Rotate event 结束。当 Binlog 文件超过指定大小，Rotate event 会写在文件最后，指向下一个 Binlog 文件。
我们来看看有过数据操作的 Binlog 日志文件是什么样子的：
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-----------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| mysql-bin.000002 | 125 | Previous_gtids | 1 | 156 | |
+------------------+-----+----------------+-----------+-------------+-----------------------------------+
2 rows in set (0.00 sec)
上面是没有任何数据操作且没有被截断的 Binlog。接下来我们插入一条数据，再看看 Binlog 事件。
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| mysql-bin.000002 | 125 | Previous_gtids | 1 | 156 | |
| mysql-bin.000002 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 235 | Query | 1 | 323 | BEGIN |
| mysql-bin.000002 | 323 | Intvar | 1 | 355 | INSERT_ID=13 |
| mysql-bin.000002 | 355 | Query | 1 | 494 | USE `test_db`; INSERT INTO `test_db`.`test_db`(`name`) VALUES ('xdfdf') |
| mysql-bin.000002 | 494 | Xid | 1 | 525 | COMMIT /* xid=192 */ |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------+
7 rows in set (0.00 sec)
这是加入一条数据之后的 Binlog 事件。
我们对 event 查询的数据行关键字段来解释一下：
- Pos：当前事件的开始位置，每个事件都占用固定的字节大小，结束位置(End_log_position)减去Pos，就是这个事件占用的字节数
上面的日志中我们能看到，第一个事件位置并不是从 0 开始，而是从 4。MySQL 通过文件中的前 4 个字节，来判断这是不是一个 Binlog 文件。这种方式很常见，很多格式的文件，如 pdf、doc、jpg等，都会通常前几个特定字符判断是否是合法文件。
Event_type：表示事件的类型
Server_id：表示产生这个事件的 MySQL server_id，通过设置 my.cnf 中的 server-id 选项进行配置
End_log_position：下一个事件的开始位置
Info：包含事件的具体信息
4.2. Binlog 日志格式
针对不同的使用场景，Binlog 也提供了可定制化的服务，提供了三种模式来提供不同详细程度的日志内容:
- Statement 模式：基于 SQL 语句的复制(statement-based replication-SBR)
- Row 模式：基于行的复制(row-based replication-RBR)
- Mixed 模式：混合模式复制(mixed-based replication-MBR)
4.2.1. Statement 模式
保存每一条修改数据的SQL。
该模式只保存一条普通的SQL语句，不涉及到执行的上下文信息。
因为每台 MySQL 数据库的本地环境可能不一样，那么对于依赖到本地环境的函数或者上下文处理的逻辑 SQL 去处理的时候可能同样的语句在不同的机器上执行出来的效果不一致。
比如像 sleep()函数，last_insert_id()函数，等等，这些都跟特定时间的本地环境有关。
4.2.2. Row 模式
MySQL V5.1.5 版本开始支持 Row 模式的 Binlog，它与 Statement 模式的区别在于它不保存具体的 SQL 语句，而是记录具体被修改的信息。
比如一条 UPDATE 语句更新 10 条数据，如果是 Statement 模式那就保存一条 SQL 就够，但是 Row 模式会保存每一行分别更新了什么，有 10 条数据。
Row 模式的优缺点就很明显了。保存每一个更改的详细信息必然会带来存储空间的快速膨胀，换来的是事件操作的详细记录。所以要求越高代价越高。
4.2.3. Mixed 模式
Mixed 模式即以上两种模式的综合体。既然上面两种模式分别走了极简和一丝不苟的极端，那是否可以区分使用场景的情况下将这两种模式综合起来呢？
在 Mixed 模式中，一般的更新语句使用 Statement 模式来保存 Binlog，但是遇到一些函数操作，可能会影响数据准确性的操作则使用 Row 模式来保存。这种方式需要根据每一条具体的 SQL 语句来区分选择哪种模式。
MySQL 从 V5.1.8 开始提供 Mixed 模式，V5.7.7 之前的版本默认是 Statement 模式，之后默认使用 Row 模式， 但是在 8.0 以上版本已经默认使用 Mixed 模式了。
查询当前 Binlog 日志使用格式：
mysql> SHOW GLOBAL VARIABLES LIKE '%binlog_format%';
+---------------------------------+---------+
| Variable_name | Value |
+---------------------------------+---------+
| binlog_format | MIXED |
| default_week_format | 0 |
| information_schema_stats_expiry | 86400 |
| innodb_default_row_format | dynamic |
| require_row_format | OFF |
+---------------------------------+---------+
5 rows in set (0.01 sec)
4.3. 如何通过 mysqlbinlog 命令手动恢复数据
上面说过每一条 event 都有位点信息，如果我们当前的 MySQL 库被无操作或者误删除了，那么该如何通过 Binlog 来恢复到删除之前的数据状态呢？
首先发现误操作之后，先停止 MySQL 服务，防止继续更新。
接着通过 mysqlbinlog 命令对二进制文件进行分析，查看误操作之前的位点信息在哪里。
接下来肯定就是恢复数据，当前数据库的数据已经是错的，那么就从开始位置到误操作之前位点的数据肯定的都是正确的；如果误操作之后也有正常的数据进来，这一段时间的位点数据也要备份。
比如说：
误操作的位点开始值为 501，误操作结束的位置为 705，之后到 800 的位点都是正确数据。
那么从 0 - 500 ，706 - 800 都是有效数据，接着我们就可以进行数据恢复了。
先将数据库备份并清空。
接着使用 mysqlbinlog 来恢复数据：
0 - 500 的数据：
mysqlbinlog --start-position=0 --stop-position=500 bin-log.000003 > /root/back.sql;
上面命令的作用就是将 0 -500 位点的数据恢复到自定义的 SQL 文件中。同理 706 - 800 的数据也是一样操作。之后我们执行这两个 SQL 文件就行了。
4.4. Binlog 事件类型
上面我们说到了 Binlog 日志中的事件，不同的操作会对应着不同的事件类型，且不同的 Binlog 日志模式同一个操作的事件类型也不同，下面我们一起看看常见的事件类型。
首先我们看看源码中的事件类型定义：
源码位置：/libbinlogevents/include/binlog_event.h
enum Log_event_type
{
/**
Every time you update this enum (when you add a type), you have to
fix Format_description_event::Format_description_event().
*/
UNKNOWN_EVENT= 0,
START_EVENT_V3= 1,
QUERY_EVENT= 2,
STOP_EVENT= 3,
ROTATE_EVENT= 4,
INTVAR_EVENT= 5,
LOAD_EVENT= 6,
SLAVE_EVENT= 7,
CREATE_FILE_EVENT= 8,
APPEND_BLOCK_EVENT= 9,
EXEC_LOAD_EVENT= 10,
DELETE_FILE_EVENT= 11,
/**
NEW_LOAD_EVENT is like LOAD_EVENT except that it has a longer
sql_ex, allowing multibyte TERMINATED BY etc; both types share the
same class (Load_event)
*/
NEW_LOAD_EVENT= 12,
RAND_EVENT= 13,
USER_VAR_EVENT= 14,
FORMAT_DESCRIPTION_EVENT= 15,
XID_EVENT= 16,
BEGIN_LOAD_QUERY_EVENT= 17,
EXECUTE_LOAD_QUERY_EVENT= 18,
TABLE_MAP_EVENT = 19,
/**
The PRE_GA event numbers were used for 5.1.0 to 5.1.15 and are
therefore obsolete.
*/
PRE_GA_WRITE_ROWS_EVENT = 20,
PRE_GA_UPDATE_ROWS_EVENT = 21,
PRE_GA_DELETE_ROWS_EVENT = 22,
/**
The V1 event numbers are used from 5.1.16 until mysql-trunk-xx
*/
WRITE_ROWS_EVENT_V1 = 23,
UPDATE_ROWS_EVENT_V1 = 24,
DELETE_ROWS_EVENT_V1 = 25,
/**
Something out of the ordinary happened on the master
*/
INCIDENT_EVENT= 26,
/**
Heartbeat event to be send by master at its idle time
to ensure master's online status to slave
*/
HEARTBEAT_LOG_EVENT= 27,
/**
In some situations, it is necessary to send over ignorable
data to the slave: data that a slave can handle in case there
is code for handling it, but which can be ignored if it is not
recognized.
*/
IGNORABLE_LOG_EVENT= 28,
ROWS_QUERY_LOG_EVENT= 29,
/** Version 2 of the Row events */
WRITE_ROWS_EVENT = 30,
UPDATE_ROWS_EVENT = 31,
DELETE_ROWS_EVENT = 32,
GTID_LOG_EVENT= 33,
ANONYMOUS_GTID_LOG_EVENT= 34,
PREVIOUS_GTIDS_LOG_EVENT= 35,
TRANSACTION_CONTEXT_EVENT= 36,
VIEW_CHANGE_EVENT= 37,
/* Prepared XA transaction terminal event similar to Xid */
XA_PREPARE_LOG_EVENT= 38,
/**
Add new events here - right above this comment!
Existing events (except ENUM_END_EVENT) should never change their numbers
*/
ENUM_END_EVENT /* end marker */
};
这么多的事件类型我们就不一一介绍，挑出来一些常用的来看看。
4.4.1. FORMAT_DESCRIPTION_EVENT
FORMAT_DESCRIPTION_EVENT 是 Binlog V4 中为了取代之前版本中的 START_EVENT_V3 事件而引入的。它是 Binlog 文件中的第一个事件，而且，该事件只会在 Binlog 中出现一次。MySQL 根据 FORMAT_DESCRIPTION_EVENT 的定义来解析其它事件。
它通常指定了 MySQL 的版本，Binlog 的版本，该 Binlog 文件的创建时间。
4.4.2. QUERY_EVENT
QUERY_EVENT 类型的事件通常在以下几种情况下使用：
- 事务开始时，执行的 BEGIN 操作
- STATEMENT 格式中的 DML 操作
- ROW 格式中的 DDL 操作
比如上文我们插入一条数据之后的 Binlog 日志：
mysql> SHOW BINLOG EVENTS in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| mysql-bin.000002 | 125 | Previous_gtids | 1 | 156 | |
| mysql-bin.000002 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 235 | Query | 1 | 323 | BEGIN |
| mysql-bin.000002 | 323 | Intvar | 1 | 355 | INSERT_ID=13 |
| mysql-bin.000002 | 355 | Query | 1 | 494 | USE `test_db`; INSERT INTO `test_db`.`test_db`(`name`) VALUES ('xdfdf') |
| mysql-bin.000002 | 494 | Xid | 1 | 525 | COMMIT /* xid=192 */ |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------+
7 rows in set (0.00 sec)
4.4.3. XID_EVENT
在事务提交时，不管是 STATEMENT 还 是ROW 格式的 Binlog，都会在末尾添加一个 XID_EVENT 事件代表事务的结束。该事件记录了该事务的 ID，在 MySQL 进行崩溃恢复时，根据事务在 Binlog 中的提交情况来决定是否提交存储引擎中状态为 prepared 的事务。
4.4.4. ROWS_EVENT
对于 ROW 格式的 Binlog，所有的 DML 语句都是记录在 ROWS_EVENT 中。
ROWS_EVENT分为三种：
- WRITE_ROWS_EVENT
- UPDATE_ROWS_EVENT
- DELETE_ROWS_EVENT
分别对应 INSERT,UPDATE 和 DELETE 操作。
对于 INSERT 操作，WRITE_ROWS_EVENT 包含了要插入的数据。
对于 UPDATE 操作，UPDATE_ROWS_EVENT 不仅包含了修改后的数据，还包含了修改前的值。
对于 DELETE 操作，仅仅需要指定删除的主键（在没有主键的情况下，会给定所有列）。
对比 QUERY_EVENT 事件，是以文本形式记录 DML 操作的。而对于 ROWS_EVENT 事件，并不是文本形式，所以在通过 mysqlbinlog 查看基于 ROW 格式的 Binlog 时，需要指定 -vv --base64-output=decode-rows。
我们来测试一下，首先将日志格式改为 Rows：
mysql> SET binlog_format=ROW;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.01 sec)
然后刷新一下日志文件，重新开始一个 Binlog 日志。我们插入一条数据之后看一下日志：
mysql> SHOW BINLOG EVENTS in 'binlog.000008';
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
| binlog.000008 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| binlog.000008 | 125 | Previous_gtids | 1 | 156 | |
| binlog.000008 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000008 | 235 | Query | 1 | 313 | BEGIN |
| binlog.000008 | 313 | Table_map | 1 | 377 | table_id: 85 (test_db.test_db) |
| binlog.000008 | 377 | Write_rows | 1 | 423 | table_id: 85 flags: STMT_END_F |
| binlog.000008 | 423 | Xid | 1 | 454 | COMMIT /* xid=44 */ |
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
7 rows in set (0.01 sec)
4.5. 一主一从配置
以下转载自：MySQL主从配置详解
主服务器数据库的每次操作都会记录在其二进制文件 mysql-bin.xxx（该文件可以在 MySQL 目录下的 data 目录中看到）中，从服务器的I/O线程使用专用账号登录到主服务器中读取该二进制文件，并将文件内容写入到自己本地的中继日志 relay-log 文件中，然后从服务器的 SQL 线程会根据中继日志中的内容执行 SQL 语句。
4.5.1. MySQL 主从同步的作用
- 可以作为备份机制，相当于热备份
- 可以用来做读写分离，均衡数据库负载
4.5.2. 项目场景
1.主服务器 10.10.20.111，其中已经有数据库且库中有表、函数以及存储过程
- 从服务器 10.10.20.116，空的啥也没有
4.5.3. 准备工作
主从服务器需要有相同的初态
4.5.3.1. 将主服务器要同步的数据库加锁，避免同步时数据发生改变
mysql>USE db;
mysql>FLUSH TABLES WITH READ LOCK;
4.5.3.2. 将主服务器数据库中数据导出
mysql>mysqldump -uroot -pxxxx db > db.sql;
这个命令是导出数据库中所有表结构和数据，如果要导出函数和存储过程的话使用：
mysql>mysqldump -R -ndt db -uroot -pxxxx > db.sql
其他关于 MySQL 导入导出命令的戳这里
4.5.3.3. 备份完成后，解锁主服务器数据库
mysql>UNLOCK TABLES;
4.5.3.4. 将初始数据导入从服务器数据库
mysql>create DATABASE db;
mysql>USE db;
mysql>source db.sql;
好了，现在主从服务器拥有一样的初态了。
4.5.4. 主服务器配置
4.5.4.1. 修改 MySQL 配置
vi /etc/my.cnf
在 [mysqld] 中添加
# 主数据库端ID号
server_id = 1
# 开启二进制日志
log-bin = mysql-bin
# 需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
binlog-do-db = db
# 将从服务器从主服务器收到的更新记入到从服务器自己的二进制日志文件中
log-slave-updates
# 控制 binlog 的写入频率。每执行多少次事务写入一次(这个参数性能消耗很大，但可减小 MySQL 崩溃造成的损失)
sync_binlog = 1
# 这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
auto_increment_offset = 1
# 这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
auto_increment_increment = 1
# 二进制日志自动删除的天数，默认值为 0,表示“没有自动删除”，启动时和二进制日志循环时可能删除
expire_logs_days = 7
# 将函数复制到 slave
log_bin_trust_function_creators = 1
4.5.4.2. 重启MySQL，创建允许从服务器同步数据的账户
-- 创建slave账号account，密码123456
mysql>GRANT replication SLAVE on *.* to 'account'@'10.10.20.116' identified by '123456';
-- 更新数据库权限
mysql>FLUSH privileges;
4.5.4.3. 查看主服务器状态
mysql>SHOW MASTER STATUS\G;
***************** 1. row ****************
File: mysql-bin.000033 -- 当前记录的日志
Position: 337523 -- 日志中记录的位置
Binlog_Do_DB:
Binlog_Ignore_DB:
执行完这个步骤后不要再操作主服务器数据库了，防止其状态值发生变化。
4.5.5. 从服务器配置
4.5.5.1. 修改 MySQL 配置
vi /etc/my.cnf
在 [mysqld] 中添加
server_id = 2
log-bin = mysql-bin
log-slave-updates
sync_binlog = 0
# log buffer 将每秒一次地写入 log fil e中，并且 log file 的 flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作
innodb_flush_log_at_trx_commit = 0
# 指定 slave 要复制哪个库
replicate-do-db = db
# MySQL 主从复制的时候，当 Master 和 Slave 之间的网络中断，但是 Master 和 Slave 无法察觉的情况下（比如防火墙或者路由问题）。Slave 会等待 slave_net_timeout 设置的秒数后，才能认为网络出现故障，然后才会重连并且追赶这段时间主库的数据
slave-net-timeout = 60
log_bin_trust_function_creators = 1
4.5.5.2. 执行同步命令
-- 执行同步命令，设置主服务器 ip，同步账号密码，同步位置
mysql>change MASTER to master_host='10.10.20.111',master_user='account',master_password='123456',master_log_file='mysql-bin.000033',master_log_pos=337523;
-- 开启同步功能
mysql>START SLAVE;
4.5.5.3. 查看从服务器状态
mysql>SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.20.111
Master_User: account
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000033
Read_Master_Log_Pos: 337523
Relay_Log_File: db2-relay-bin.000002
Relay_Log_Pos: 337686
Relay_Master_Log_File: mysql-bin.000033
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
...
Slave_IO_Running 及 Slave_SQL_Running 进程必须正常运行，即 Yes 状态，否则说明同步失败。
若失败查看 MySQL 错误日志中具体报错详情来进行问题定位。
最后可以去主服务器上的数据库中创建表或者更新表数据来测试同步。
0 条评论