mysql

  • 咱们先来看一下MySQL的根本架构，从大的方面来讲，一个server层，一个引擎层。server层就像一个接口，能够对接任何符合规定的引擎。详细的细节能够参阅我之前写过的文章mysql的这些坑你踩过吗？快来看看怎么优化mysql
    谈谈mysql（1）——Binlog的复制方式和解析技巧

MySQL的存储引擎的简单解读

谈谈mysql（1）——Binlog的复制方式和解析技巧

  • 咱们看到，有的引擎是支撑业务的，有的引擎是不支撑业务的。可是咱们知道，MySQL的大部分引擎都支撑主从仿制的方法，比方myisam（依据句子），memory（依据句子），innodb(支撑依据行和依据句子),MySQL经过仿制主节点（master）的二进制日志（bin-log）来实现数据的同步。能够说bin-log是MySQL层面日志。

运用docker快速发动一个主备服务器

主库

  • 新建一个目录mysqla,进入目录履行以下指令,咱们把装备文件和数据目录都挂载到咱们宿主机上
docker run -d --name mysql-a -p 23307:3306 \
-v $(pwd)/conf:/etc/mysql/conf.d \
-v $(pwd)/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=root \
mysql
  • 在当时目录下的conf目录下，创立文件my.cnf 里边界说自己的日志称号目录和server-id，注意server-id不能重复
$ cat my.cnf
[mysqld]
server-id=1
log-bin=mysql-bin
  • 发动容器，创立一个slave用户用于仿制
$ CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
$ GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
$ FLUSH PRIVILEGES；

备库

  • 新建一个目录mysqlb,进入目录履行以下指令,咱们把装备文件和数据目录都挂载到咱们宿主机上
docker run -d --name mysql-b -p 23307:3306 \
-v $(pwd)/conf:/etc/mysql/conf.d \
-v $(pwd)/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=root \
mysql
  • 在当时目录下的conf目录下，创立文件my.cnf 里边界说自己的日志称号目录和server-id，注意server-id不能重复
$ cat ../../mysqlb/conf/my.cnf
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
server_id=100
log-bin=mysql-slave-bin
relay_log=edu-mysql-relay-bin
  • 检查主库和备库的容器ip
$ docker inspect -f '{{.Name}} - {{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' a4--这个是容器id
/mysql-b - 172.17.0.3
docker inspect -f '{{.Name}} - {{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' 52--这个是容器id
/mysql-a - 172.17.0.2
  • 我运用的mysql8 版别，要运用仿制用户恳求服务器公钥，在备库服务器上先履行mysql -uslave -p123456 -h172.17.0.2 --get-server-public-key，否则会报错error: Authentication requires secure connection. ,因为mysql8中caching_sha2_password 是默许的身份验证插件
  • 履行SQL设置主库的信息，注意：master_log_file 和master_log_pos是在主库履行 show master status;指令后能够获取到 
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= 157, master_connect_retry=30;
  • 检查slave的状况，假如有两个yes ，就代表成功了
    谈谈mysql（1）——Binlog的复制方式和解析技巧
  • 假如中间装备过错运用stop slave;reset slave;重置主库信息
  • 咱们在主库能够经过show processlist来检查备库的同步binlog的线程，比方我这个，线程端口45976 state说已经悉数同步到备库了，等候更多的更新。
    谈谈mysql（1）——Binlog的复制方式和解析技巧

binlog

  • 为什么会有binlog 咱们知道，MySQL服务器单机是有性能瓶颈的，特别是写比较多的时分，写锁一向被占用，导致读操作一向阻塞，最终导致恳求超时，为了处理这种问题，MySQL就要供给一种更大规模、高性能的服务，那么MySQL供给了一种水平扩展的架构，让一台服务器与其他服务器保持同步的功用，一台主库的数据能够同步到其他备用库上，备用库也能够装备成其他服务的主库。binlog处理了数据库之间数据同步的问题。

记载binlog的方法

有两种仿制方法，一种是依据句子的仿制（逻辑仿制），这种方法在MySQL版别3的时分就存在了。另一种便是依据行的仿制方法，这是在MySQL版别5提出来的。而详细支撑哪种方法和

依据句子的仿制

在这种方法下，binlog中记载的是那些形成数据更改的SQL，并在备库上重放这些SQL。这种方法的优点便是二进制日志里的事情更加紧凑，并且binlog的日志量会更小，比方咱们更新了几十万条记载，而日志里边只记载了一条update句子。

可是缺点也是显着的，比方主库和备库的履行时刻有可能会不一致，导致数据的时刻戳也不一样。第二点便是这种记载日志有必要是串行的履行，那么咱们可能就需要更多的锁来保证它是串行的。

依据行的仿制

MySQL5.1以后开始支撑行的仿制，这种方法便是将实践数据存储到binlog里边，这种方法能够保证主备库的数据完全一致。并且不需要逻辑的binlog，仿制数据的功率也更高。并且关于较为杂乱的sql来说，这种方法也更高效，因为你中间不论履行了多少sql，我日志只记载物理日志，可能便是一行数据。可是关于一些update操作，那么每行被更改的数据都得记载到binlog里边。并且binlog的可读性比较差，咱们不知道履行了哪些sql句子。

谈谈mysql（1）——Binlog的复制方式和解析技巧

假如声明是mixed，MySQL则动态切换的，依据句子的仿制履行不了的时分，就会采取行仿制。咱们也能够依据SHOW VARIABLES LIKE 'binlog_format'来检查当时的仿制方法。

同步的过程

谈谈mysql（1）——Binlog的复制方式和解析技巧

binlog的句子剖析以及开始和完毕的标记

  • 相关参阅增加链接描绘 咱们能够经过mysqlbinlog剖析binlog,其中最常用到两个参数–base64-output和–verbose

  • base64-output AUTO: 默许为AUTO方法，原始的记载binlog events的方法。假如要经过binlog恢复数据(mysqlbinlog log_file | mysql -h server_name)，有必要运用AUTO方法 NEVER: 不显现binlog statements，遇到ROW格局的binlog直接报错 DECODE-ROWS: 紧缩显现row格局events

  • verbose 将行方法下的binlog以注释的SQL句子的方法显现，在适用的情况下，还包括表的分区信息。即经过伪代码的方法重构出行数据改变的等价的SQL句子

接下来咱们经过履行SQL来生成一些binlog日志

row方法下的日志

  • 检查binlog是否敞开show variables like 'log_bin';

    谈谈mysql（1）——Binlog的复制方式和解析技巧

  • 检查binlog的记载方法show variables like 'binlog_format%';

    谈谈mysql（1）——Binlog的复制方式和解析技巧

  • 经过flush binary logs指令封闭当时运用的binary log，然后打开一个新的binary log文件，文件的序号加1

  • 履行一个insert操作

  • 在不加任何参数的情况下，咱们看到的日志是紧缩过的

    谈谈mysql（1）——Binlog的复制方式和解析技巧

  • 咱们经过mysqlbinlog mysql-bin.000005 --base64-output=DECODE-ROWS -vv 检查日志（v便是verbose参数简写）

    谈谈mysql（1）——Binlog的复制方式和解析技巧

  • 咱们履行一个update句子看一下日志是什么样子

mysql> update user set name='www.acurd.com' where id>2;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

咱们发现被解析成了两个update句子

谈谈mysql（1）——Binlog的复制方式和解析技巧

依据statement的binlog
  • 咱们改一下主库的my.cnf装备，声明binlog的格局是statement
$ cat my.cnf
[mysqld]
server-id=1
log-bin=mysql-bin
default_authentication_plugin=mysql_native_password
binlog_format=statement
  • 检查binlog的记载方法show variables like 'binlog_format%';
    谈谈mysql（1）——Binlog的复制方式和解析技巧
  • 刷新日志文件flush binary logs
  • 更新数据 update user set name='acurd' where id>1;
  • 经过mysqlbinlog检查日志mysqlbinlog mysql-bin.000008 ，日志中记载的便是咱们履行的SQL
    谈谈mysql（1）——Binlog的复制方式和解析技巧