使用Clion调试MySQL Server源码

四月 22, 2022

环境

Mac OS (m1)

Clion

MySQL 8.0 源码 https://github.com/mysql/mysql-server

MySQL 8.0 boost_1_73_0 依赖，源码安装

Boost 下载安装

最新版并不是MySQL需要的，MySQL 8.0 默认需要的是boost_1_73_0，如果版本不符合要求，编译启动过程中会有相应的提示。

https://www.boost.org/users/history/version_1_73_0.html

https://www.boost.org/doc/libs/1_73_0/more/getting_started/unix-variants.html

正常来说下载，解压到指定目录即可，这里我们把boost解压到 /usr/local/ 目录下，追加 BOOST_ROOT 到 PATH 环境变量。

下载 MySQL-Server 源码

git clone git@github.com:mysql/mysql-server.git

Clion 配置Debug

用Clion打开MySQL Server 源码工程，配置cmake

Cmake 配置如下：

1 2	-DWITH_DEBUG=1 -DWITH_BOOST=/usr/local/boost_1_73_0

运行Cmake

Debug MySQL Server

选择 MySQL Server 启动入口

需要配置MySQL Server的相关目录，更多配置项 https://dev.mysql.com/doc/refman/8.0/en/data-directory-initialization.html

1 2	--basedir=/Users/timmy/mysql --datadir=/Users/timmy/mysql/data

然后点击Debug即可，如果我们本地安装有mysql client，这时候就可以通过客户端免密登录了。

1	mysql -u root --skip-password

选择配置 MySQL 指令，在clion中连接启动的mysqld （本地有mysql客户端可以不用）

通用开发环境搭建

八月 12, 2021

以下文件安装默认目录为~/dev

~/dev 后续路径为解压包后的完整路径

Java 开发环境搭建

Java SE Downloads

一般选择压缩包(Compressed Archive)安装, 这样可以定制化安装到指定目录。

如果是ARM架构，建议使用Zulu -CA-macos-aarch64 ARM 64-bit

环境变量

1
2
3

JAVA_HOME=~/dev/zulu8.56.0.23-ca-jdk8.0.302-macosx_aarch64/zulu-8.jdk/Contents/Home
PATH=$PATH:$JAVA_HOME/bin
CLASS_PATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar

MAVEN 安装及配置

下载tar.gz 解压即可，最好不要修改settings.xml里面的localRepository，因为idea内置maven，默认的目录为.m2，因此如果要修改settings.xml，需要一并修改。

环境变量

1 2	MAVEN_HOME=~/dev/apache-maven-3.8.1 PATH=$PATH:$MAVEN_HOME/bin

MySQL Client 安装

MySQL Server 在mac机器上一般使用docker(也有arm版了)安装，以便切换不同的mysql版本。

MySQL Client 则可以使用mysql的安装包里面工具。如果是ARM架构，可选择macos11-arm64 arm64.tar.gz

环境变量

1	PATH=$PATH:~/dev/mysql-8.0.26-macos11-arm64/bin

Github GitLab SSH keys 配置

ed25519加密解密很快,生成时间短而且安全性更高,rsa则加密解密稍慢,生成时间长,安全性没有ed25519高,只是rsa基本都是默认,所以用的人更多,但是建议转换为ed25519,网站软件现在基本都支持了.

Github (ed25519)

1	ssh-keygen -t ed25519 -C "your_email@example.com"

GitLab (rsa)

1	ssh-keygen -t rsa -C "your_email@example.com"

配置ssh config

#将上面生成的id_rsa 及 id_rsa.pub 分别移动到github和gitlab目录
# cat ~/.ssh/config
# gitlab
Host gitlab.*.com
    HostName gitlab.company.com
    PreferredAuthentications publickey
    IdentityFile ~/.ssh/gitlab/id_rsa

#github
Host github.com
    HostName github.com
    AddKeysToAgent yes
    UseKeychain yes
    PreferredAuthentications publickey
    IdentityFile ~/.ssh/github/id_ed25519

验证SSH是否配置成功

1 2	ssh -vT git@github.com ssh -vT git@gitlab.company.com

安装zsh及主题(Mac)

八月 11, 2021

Install Homebrew

1	/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

Install Oh My Zsh

1	sh -c "$(curl -fsSL https://raw.githubusercontent.com/ohmyzsh/ohmyzsh/master/tools/install.sh)"

Install Powerline fonts

# clone
git clone https://github.com/powerline/fonts.git --depth=1
# install
cd fonts
./install.sh
# clean-up a bit
cd ..
rm -rf fonts

配置 zsh 支持图标

MySQL-architecture

六月 30, 2021

MySQL is Relational Database Management system which is free Open Source Software Under GNU License. It is also supported by Oracle Company .It is fast , scalable, easy to use database management System. MySQL support many operation system like Windows, Linux, MacOS etc.

MySQL is Structured Query Language which is used to manipulate, manage and retrieve data with the help of various Queries.

MySQL is developed and supported by MySQL AB which is a Swedish Company and written in C and C++ programming language. It was developed by Michael Widenius and David Hughes .It is often used to say that MySQL is named after the name of daughter of the co-founder MIchael Widenius whose name is ‘My’.

Architecture of MySQL:

Architecture of MySQL describes the relation among the different components of MySQL System. MySQL follow Client-Server Architecture. It is designed so that end user that is Clients can access the resources from Computer that is server using various networking services. The Architecture of MySQL contain following major layer’s :

Client
Server
Storage Layer

Client Layer:
This layer is the topmost layer in the above diagram. The Client give request instructions to the Serve with the help of Client Layer .The Client make request through Command Prompt or through GUI screen by using valid MySQL commands and expressions .If the Expressions and commands are valid then the output is obtained on the screen. Some important services of client layer are :

Connection Handling.
Authentication.
Security.

Connection Handling :
When a client send request to the server and server will accept the request and the client is connected .. When Client is connected to the server at that time , a client get it’s own thread for it’s connection. With the help of this thread all the queries from client side is executed.

Authentication :
Authentication is performed on the server side when client is connected to the MySQL server. Authentication is done with the help of username and password.

Security :
After authentication when the client gets connected successfully to MySQL server, the server will check that a particular client has the privileges to issue in certain queries against MySQL server.

Server Layer:
The second layer of MySQL architecture is responsible for all logical functionalities of relational database management system of MySQL. This Layer of MySQL System is also known as “Brain of MySQL Architecture”. When the Client give request instructions to the Server and the server gives the output as soon as the the instruction is matched. The various sub components of MySQL server are:

Thread Handling –
When a client send request to the server and server will accept the request and the client is connected .. When Client is connected to the server at that time , a client get it’s own thread for it’s connection. This thread is provided by thread handling of Server Layer. Also the queries of client side which is executed by the thread is also handled by Thread Handling module.
Parser –
A Parser is a type of Software Component that built a data structure(parse tree) of given input . Before parsing lexical analysis is done i.e. input is broken into number of tokens . After the data is available in the smaller elements parser perform Syntax Analysis , Semantics Analysis after that parse tree is generated as output.
Optimizer –
As soon as the parsing is done , various types of optimization techniques are applied at Optimizer Block. These techniques may include rewriting the query, order of scanning of tables and choosing the right indexes to use etc.
Query Cache –
Query Cache stores the complete result set for inputted query statement. Eve before Parsing , MySQL Server consult query cache . When client write a query , if the query written by client is identical in the cache then the server simply skip the parsing, optimization and even execution, it just simply display the output from the cache.
Buffer and Cache –
Cache and will buffer store the previous query or problem asked by user. When User write a query then it firstly goes to Query Cache then query cache will check that the same query or problem is available in the cache. If the same query is available then it will provide output without interfering Parser, Optimizer.
Table Metadata Cache –
The metadata cache is a reserved area of memory used for tracking information on databases, indexes, or objects. The greater the number of open databases, indexes, or objects, the larger the metadata cache size.
Key Cache –
A key cache is an index entry that uniquely identifies an object in a cache. By default, edge servers cache content based on the entire resource path and a query string.

Storage Layer:
This Storage Engine Layer of MySQL Architecture make it’s unique and most preferable for developer’s . Due to this Layer MySQL layer is counted as the mostly used RDBMS and is widely used. In MySQL server , for different situations and requirement’s different types of storage engines are used which are InnoDB ,MyISAM , NDB ,Memory etc. These storage engines are used as pluggable storage enginer where tables created by user are plugged with them.

Features of MySQL:

MySQL language is easy to use as compared to other programming language like C,C++, Java etc. By learning with some basic command we can work , create and interact with Database.
MySQL consist of Data Security layer which protect the data from violator. Also, passwords are encrypted in MySQL.
MySQL follow Client-Server Architecture where Client request Commands and instructions and Server will produce output as soon as the instruction is matched.
MySQL is free to use under Community version of it. So we can download it from MySQL website and work on it freely.
MySQL use multithreading which makes it Scalable. It can handle any amount of data .The default file size limit is 4 GB, but we can increase it according to our need.
MySQL is considered as one of the fast database. It’s fastness is determined on the basis of large number of benchmark tests.
MySQL is very flexible because it supports large number of embedded systems.
MySQL is compatible to run on various operating system such as Windows, MacOS , Linux etc.
MySQL allow transactions to be rolled back, commit and cash recovery.
It has low memory leakage problem which increase its memory efficiency.
MySQL version 8.0 provide dual password support , one is a current password and another is secondary password. With the help of this we can create new password.
MySQL provide feature of Partitioning which improve performance of large databases.

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

Reference

architecture-of-MySQL

docker_net

一月 12, 2021

Docker 网络问题汇总

Docker’s networking subsystem is pluggable, using drivers. Several drivers exist by default, and provide core networking functionality:

bridge: The default network driver. If you don’t specify a driver, this is the type of network you are creating. Bridge networks are usually used when your applications run in standalone containers that need to communicate. See bridge networks.
host: For standalone containers, remove network isolation between the container and the Docker host, and use the host’s networking directly. See use the host network.
overlay: Overlay networks connect multiple Docker daemons together and enable swarm services to communicate with each other. You can also use overlay networks to facilitate communication between a swarm service and a standalone container, or between two standalone containers on different Docker daemons. This strategy removes the need to do OS-level routing between these containers. See overlay networks.
macvlan: Macvlan networks allow you to assign a MAC address to a container, making it appear as a physical device on your network. The Docker daemon routes traffic to containers by their MAC addresses. Using the macvlan driver is sometimes the best choice when dealing with legacy applications that expect to be directly connected to the physical network, rather than routed through the Docker host’s network stack. See Macvlan networks.
none: For this container, disable all networking. Usually used in conjunction with a custom network driver. none is not available for swarm services. See disable container networking.
Network plugins: You can install and use third-party network plugins with Docker. These plugins are available from Docker Hub or from third-party vendors. See the vendor’s documentation for installing and using a given network plugin.

常用类型

bridge 很容易打通各个容器之间的通信
host 复用宿主机网络

#创建my-net私网（各容器通过my-net这个路由器通信，容器不能直接打通宿主机）
docker network create my-net
#删除my-net私网
docker network rm my-net

jdb

八月 26, 2020

jdb官方

操作实例

import java.util.concurrent.TimeUnit;

public class Hello {
    public static void main(String[] args) throws InterruptedException {
        int wait = 20;
        if (args.length > 0) {
            wait = Integer.parseInt(args[0]);
        }
        int i = 0;
        while (i++ < wait) {
            System.out.println(i);
            TimeUnit.SECONDS.sleep(10);
        }
        System.out.println("exit...");
    }
}

编译

// com.sun.tools.example.debug.expr.ParseException: Name unknown: wait
// wait = null
javac -g Hello.java #没有-g参数，jdb print 变量 如上错误

执行

1 2	# win 系统指令有差异 java -Xdebug -Xrunjdwp:transport=dt_socket,address=8888,server=y,suspend=y Hello

IDEA Remote Debug

ClassLoader

七月 6, 2020

不同的 ClassLoader 之间也会有合作，它们之间的合作是通过 parent 属性和双亲委派机制来完成的。parent 具有更高的加载优先级。除此之外，parent 还表达了一种共享关系，当多个子 ClassLoader 共享同一个 parent 时，那么这个 parent 里面包含的类可以认为是所有子 ClassLoader 共享的。这也是为什么 BootstrapClassLoader 被所有的类加载器视为祖先加载器，JVM 核心类库自然应该被共享。

双亲委派

双亲委派规则可能会变成三亲委派，四亲委派，取决于你使用的父加载器是谁，它会一直递归委派到根加载器。

自定义加载器

class ClassLoader {

  // 加载入口，定义了双亲委派规则
  Class loadClass(String name) {
    // 是否已经加载了
    Class t = this.findFromLoaded(name);
    if(t == null) {
      // 交给双亲
      t = this.parent.loadClass(name)
    }
    if(t == null) {
      // 双亲都不行，只能靠自己了
      t = this.findClass(name);
    }
    return t;
  }
  
  // 交给子类自己去实现
  Class findClass(String name) {
    throw ClassNotFoundException();
  }
  
  // 组装Class对象
  Class defineClass(byte[] code, String name) {
    return buildClassFromCode(code, name);
  }
}

class CustomClassLoader extends ClassLoader {

  Class findClass(String name) {
    // 寻找字节码
    byte[] code = findCodeFromSomewhere(name);
    // 组装Class对象
    return this.defineClass(code, name);
  }
}

自定义类加载器不易破坏双亲委派规则，不要轻易覆盖 loadClass 方法。否则可能会导致自定义加载器无法加载内置的核心类库。在使用自定义加载器时，要明确好它的父加载器是谁，将父加载器通过子类的构造器传入。如果父类加载器是 null，那就表示父加载器是「根加载器」。

Class.forName vs ClassLoader.loadClass

这两个方法都可以用来加载目标类，它们之间有一个小小的区别，那就是 Class.forName() 方法可以获取原生类型的 Class，而 ClassLoader.loadClass() 则会报错。

Class<?> x = Class.forName("[I");
System.out.println(x);

x = ClassLoader.getSystemClassLoader().loadClass("[I");
System.out.println(x);

---------------------
class [I

Exception in thread "main" java.lang.ClassNotFoundException: [I
...

钻石依赖

项目管理上有一个著名的概念叫着「钻石依赖」，是指软件依赖导致同一个软件包的两个版本需要共存而不能冲突。

我们平时使用的 maven 是这样解决钻石依赖的，它会从多个冲突的版本中选择一个来使用，如果不同的版本之间兼容性很糟糕，那么程序将无法正常编译运行。Maven 这种形式叫「扁平化」依赖管理。

使用 ClassLoader 可以解决钻石依赖问题。不同版本的软件包使用不同的 ClassLoader 来加载，位于不同 ClassLoader 中名称一样的类实际上是不同的类。下面让我们使用 URLClassLoader 来尝试一个简单的例子，它默认的父加载器是 AppClassLoader。

$ cat ~/source/jcl/v1/Dep.java
public class Dep {
	public void print() {
		System.out.println("v1");
	}
}

$ cat ~/source/jcl/v2/Dep.java
public class Dep {
 public void print() {
  System.out.println("v1");
 }
}

$ cat ~/source/jcl/Test.java
public class Test {
	public static void main(String[] args) throws Exception {
		String v1dir = "file:///Users/qianwp/source/jcl/v1/";
		String v2dir = "file:///Users/qianwp/source/jcl/v2/";
		URLClassLoader v1 = new URLClassLoader(new URL[]{new URL(v1dir)});
		URLClassLoader v2 = new URLClassLoader(new URL[]{new URL(v2dir)});
		
    Class<?> depv1Class = v1.loadClass("Dep");
		Object depv1 = depv1Class.getConstructor().newInstance();
		depv1Class.getMethod("print").invoke(depv1);

		Class<?> depv2Class = v2.loadClass("Dep");
		Object depv2 = depv2Class.getConstructor().newInstance();
		depv2Class.getMethod("print").invoke(depv2);
	 
  System.out.println(depv1Class.equals(depv2Class));
 }
}

$ cd ~/source/jcl/v1
$ javac Dep.java
$ cd ~/source/jcl/v2
$ javac Dep.java
$ cd ~/source/jcl
$ javac Test.java
$ java Test
v1
v2
false

在这个例子中如果两个 URLClassLoader 指向的路径是一样的，下面这个表达式还是 false，因为即使是同样的字节码用不同的 ClassLoader 加载出来的类都不能算同一个类

我们还可以让两个不同版本的 Dep 类实现同一个接口，这样可以避免使用反射的方式来调用 Dep 类里面的方法。

1
2
3

Class<?> depv1Class = v1.loadClass("Dep");
IPrint depv1 = (IPrint)depv1Class.getConstructor().newInstance();
depv1.print()

ClassLoader 固然可以解决依赖冲突问题，不过它也限制了不同软件包的操作界面必须使用反射或接口的方式进行动态调用。Maven 没有这种限制，它依赖于虚拟机的默认懒惰加载策略，运行过程中如果没有显示使用定制的 ClassLoader，那么从头到尾都是在使用 AppClassLoader，而不同版本的同名类必须使用不同的 ClassLoader 加载，所以 Maven 不能完美解决钻石依赖。如果你想知道有没有开源的包管理工具可以解决钻石依赖的，我推荐你了解一下 sofa-ark，它是蚂蚁金服开源的轻量级类隔离框架。

Thread.contextClassLoader

class Thread {
  ...
  private ClassLoader contextClassLoader;
  
  public ClassLoader getContextClassLoader() {
    return contextClassLoader;
  }
  
  public void setContextClassLoader(ClassLoader cl) {
    this.contextClassLoader = cl;
  }
  ...
}

contextClassLoader「线程上下文类加载器」，这究竟是什么东西？

首先 contextClassLoader 是那种需要显示使用的类加载器，如果你没有显示使用它，也就永远不会在任何地方用到它。你可以使用下面这种方式来显示使用它

1	Thread.currentThread().getContextClassLoader().loadClass(name);

这意味着如果你使用 forName(string name) 方法加载目标类，它不会自动使用 contextClassLoader。那些因为代码上的依赖关系而懒惰加载的类也不会自动使用 contextClassLoader来加载。

其次线程的 contextClassLoader 默认是从父线程那里继承过来的，所谓父线程就是创建了当前线程的线程。程序启动时的 main 线程的 contextClassLoader 就是 AppClassLoader。这意味着如果没有人工去设置，那么所有的线程的 contextClassLoader 都是 AppClassLoader。

那这个 contextClassLoader 究竟是做什么用的？我们要使用前面提到了类加载器分工与合作的原理来解释它的用途。

它可以做到跨线程共享类，只要它们共享同一个 contextClassLoader。父子线程之间会自动传递 contextClassLoader，所以共享起来将是自动化的。

如果不同的线程使用不同的 contextClassLoader，那么不同的线程使用的类就可以隔离开来。

如果我们对业务进行划分，不同的业务使用不同的线程池，线程池内部共享同一个 contextClassLoader，线程池之间使用不同的 contextClassLoader，就可以很好的起到隔离保护的作用，避免类版本冲突。

如果我们不去定制 contextClassLoader，那么所有的线程将会默认使用 AppClassLoader，所有的类都将会是共享的。

mysql-xa

六月 15, 2020

MySQL XA

boolean logXaCommands = true;
   // 获得资源管理器操作接口实例 RM1
   Connection conn1 = DriverManager.getConnection("jdbc:mysql://localhost:33060/d1", "root", "pass");
   XAConnection xaConn1 = new MysqlXAConnection((JdbcConnection)conn1, logXaCommands);
   XAResource rm1 = xaConn1.getXAResource();

   // 获得资源管理器操作接口实例 RM2
   Connection conn2 = DriverManager.getConnection("jdbc:mysql://localhost:33060/d2", "root", "pass");
   XAConnection xaConn2 = new MysqlXAConnection((JdbcConnection)conn2, logXaCommands);
   XAResource rm2 = xaConn2.getXAResource();
   // AP请求TM执行一个分布式事务，TM生成全局事务id
   byte[] gtrid = "g12345".getBytes();
   int formatId = 1;
   try {
       // ==============分别执行RM1和RM2上的事务分支====================
       // TM生成rm1上的事务分支id
       byte[] bqual1 = "b00001".getBytes();
       Xid xid1 = new MysqlXid(gtrid, bqual1, formatId);
       // 执行rm1上的事务分支 One of TMNOFLAGS, TMJOIN, or TMRESUME.
       rm1.start(xid1, XAResource.TMNOFLAGS);
       // 业务1：插入user表
       PreparedStatement ps1 = conn1.prepareStatement("insert into t1 values (10,'ab')");
       ps1.execute();
       rm1.end(xid1, XAResource.TMSUCCESS);

       // TM生成rm2上的事务分支id
       byte[] bqual2 = "b00002".getBytes();
       Xid xid2 = new MysqlXid(gtrid, bqual2, formatId);
       // 执行rm2上的事务分支
       rm2.start(xid2, XAResource.TMNOFLAGS);
       // 业务2：插入user_msg表
       PreparedStatement ps2 = conn2.prepareStatement("insert into t2 values (20,'cd')");
       ps2.execute();
       rm2.end(xid2, XAResource.TMSUCCESS);

       // ===================两阶段提交================================
       // phase1：询问所有的RM 准备提交事务分支
       int rm1Prepare = rm1.prepare(xid1);
       int rm2Prepare = rm2.prepare(xid2);
       // phase2：提交所有事务分支
       boolean onePhase = false;
       //TM判断有2个事务分支，所以不能优化为一阶段提交
       if (rm1Prepare == XAResource.XA_OK && rm2Prepare == XAResource.XA_OK) {
           //所有事务分支都prepare成功，提交所有事务分支
           rm1.commit(xid1, onePhase);
           rm2.commit(xid2, onePhase);
       } else {
           //如果有事务分支没有成功，则回滚
           rm1.rollback(xid1);
           rm1.rollback(xid2);
       }
   } catch (XAException e) {
       // 如果出现异常，也要进行回滚
       e.printStackTrace();
   }

事务落在同一个MySQL

+---------------+------+----------------+-----------+-------------+----------------------------------------------+
| Log_name      | Pos  | Event_type     | Server_id | End_log_pos | Info                                         |
+---------------+------+----------------+-----------+-------------+----------------------------------------------+
| binlog.000001 |    4 | Format_desc    |         1 |         125 | Server ver: 8.0.20, Binlog ver: 4            |
| binlog.000001 |  125 | Previous_gtids |         1 |         156 |                                              |
| binlog.000001 |  156 | Anonymous_Gtid |         1 |         235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'         |
| binlog.000001 |  235 | Query          |         1 |         345 | XA START X'673132333435',X'623030303031',1   |
| binlog.000001 |  345 | Table_map      |         1 |         399 | table_id: 94 (d1.t1)                         |
| binlog.000001 |  399 | Write_rows     |         1 |         442 | table_id: 94 flags: STMT_END_F               |
| binlog.000001 |  442 | Query          |         1 |         550 | XA END X'673132333435',X'623030303031',1     |
| binlog.000001 |  550 | XA_prepare     |         1 |         598 | XA PREPARE X'673132333435',X'623030303031',1 |
| binlog.000001 |  598 | Anonymous_Gtid |         1 |         677 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'         |
| binlog.000001 |  677 | Query          |         1 |         787 | XA START X'673132333435',X'623030303032',1   |
| binlog.000001 |  787 | Table_map      |         1 |         841 | table_id: 96 (d2.t2)                         |
| binlog.000001 |  841 | Write_rows     |         1 |         884 | table_id: 96 flags: STMT_END_F               |
| binlog.000001 |  884 | Query          |         1 |         992 | XA END X'673132333435',X'623030303032',1     |
| binlog.000001 |  992 | XA_prepare     |         1 |        1040 | XA PREPARE X'673132333435',X'623030303032',1 |
| binlog.000001 | 1040 | Anonymous_Gtid |         1 |        1117 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'         |
| binlog.000001 | 1117 | Query          |         1 |        1228 | XA COMMIT X'673132333435',X'623030303031',1  |
| binlog.000001 | 1228 | Anonymous_Gtid |         1 |        1305 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'         |
| binlog.000001 | 1305 | Query          |         1 |        1416 | XA COMMIT X'673132333435',X'623030303032',1  |
+---------------+------+----------------+-----------+-------------+----------------------------------------------+

事务落在2个不同MySQL

MySQL 1

+---------------+-----+----------------+-----------+-------------+----------------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                         |
+---------------+-----+----------------+-----------+-------------+----------------------------------------------+
| binlog.000001 |   4 | Format_desc    |         1 |         125 | Server ver: 8.0.20, Binlog ver: 4            |
| binlog.000001 | 125 | Previous_gtids |         1 |         156 |                                              |
| binlog.000001 | 156 | Anonymous_Gtid |         1 |         235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'         |
| binlog.000001 | 235 | Query          |         1 |         345 | XA START X'673132333435',X'623030303031',1   |
| binlog.000001 | 345 | Table_map      |         1 |         399 | table_id: 101 (d1.t1)                        |
| binlog.000001 | 399 | Write_rows     |         1 |         442 | table_id: 101 flags: STMT_END_F              |
| binlog.000001 | 442 | Query          |         1 |         550 | XA END X'673132333435',X'623030303031',1     |
| binlog.000001 | 550 | XA_prepare     |         1 |         598 | XA PREPARE X'673132333435',X'623030303031',1 |
| binlog.000001 | 598 | Anonymous_Gtid |         1 |         675 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'         |
| binlog.000001 | 675 | Query          |         1 |         786 | XA COMMIT X'673132333435',X'623030303031',1  |
+---------------+-----+----------------+-----------+-------------+----------------------------------------------+

MySQL 2

+---------------+-----+----------------+-----------+-------------+----------------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                         |
+---------------+-----+----------------+-----------+-------------+----------------------------------------------+
| binlog.000001 |   4 | Format_desc    |         1 |         125 | Server ver: 8.0.20, Binlog ver: 4            |
| binlog.000001 | 125 | Previous_gtids |         1 |         156 |                                              |
| binlog.000001 | 156 | Anonymous_Gtid |         1 |         235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'         |
| binlog.000001 | 235 | Query          |         1 |         345 | XA START X'673132333435',X'623030303032',1   |
| binlog.000001 | 345 | Table_map      |         1 |         399 | table_id: 106 (d2.t2)                        |
| binlog.000001 | 399 | Write_rows     |         1 |         442 | table_id: 106 flags: STMT_END_F              |
| binlog.000001 | 442 | Query          |         1 |         550 | XA END X'673132333435',X'623030303032',1     |
| binlog.000001 | 550 | XA_prepare     |         1 |         598 | XA PREPARE X'673132333435',X'623030303032',1 |
| binlog.000001 | 598 | Anonymous_Gtid |         1 |         675 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'         |
| binlog.000001 | 675 | Query          |         1 |         786 | XA COMMIT X'673132333435',X'623030303032',1  |
+---------------+-----+----------------+-----------+-------------+----------------------------------------------+

binlog-protocol

六月 2, 2020

生成新的binlog

1	flush logs;

清空binlog

1	reset master;

格式

Binlog files start with a Binlog File Header followed by a series of Binlog Event

Header + Event + Event + Event

Header

A binlog file starts with a Binlog File Header [ fe 'bin' ]

Event

EventHeader + Event

Binlog Event header

The binlog event header starts each event and is either 13 or 19 bytes long, depending on the binlog version.

4              timestamp
1              event type
4              server-id
4              event-size
   if binlog-version > 1:
4              log pos
2              flags

+---------+---------+---------+------------+-------------+-------+
|timestamp|type code|server_id|event_length|next_position|flags  |
|4 bytes  |1 byte   |4 bytes  |4 bytes     |4 bytes      |2 bytes|
+---------+---------+---------+------------+-------------+-------+

Fields

timestamp (4) – seconds since unix epoch
event_type (1) – see Binlog Event Type
server_id (4) – server-id of the originating mysql-server. Used to filter out events in circular replication.
event_size (4) – size of the event (header, post-header, body)
log_pos (4) – position of the next event
flags (2) – see Binlog Event Flag

Table 14.5 Binlog Versions

Binlog version	MySQL Version
1	MySQL 3.23 - < 4.0.0
2	MySQL 4.0.0 - 4.0.1
3	MySQL 4.0.2 - < 5.0.0
4	MySQL 5.0.0+

目前市面上都是V4了，故EventHeader的长度可以默认为19

Binlog::FORMAT_DESCRIPTION_EVENT:

FORMAT_DESCRIPTION_EVENT 是binlog文件的第一个 Event

//https://sourcegraph.com/github.com/mysql/mysql-server/-/blob/libbinlogevents/src/binlog_event.cpp#L84:1
/**
   The method returns the checksum algorithm used to checksum the binary log.
   For MySQL server versions < 5.6, the algorithm is undefined. For the higher
   versions, the type is decoded from the FORMAT_DESCRIPTION_EVENT.

   @param buf buffer holding serialized FD event
   @param len netto (possible checksum is stripped off) length of the event buf

   @return  the version-safe checksum alg descriptor where zero
            designates no checksum, 255 - the orginator is
            checksum-unaware (effectively no checksum) and the actuall
            [1-254] range alg descriptor.
*/
enum_binlog_checksum_alg Log_event_footer::get_checksum_alg(const char *buf,
                                                            unsigned long len) {
  BAPI_ENTER("Log_event_footer::get_checksum_alg(const char*, unsigned long)");
  enum_binlog_checksum_alg ret = BINLOG_CHECKSUM_ALG_UNDEF;
  char version[ST_SERVER_VER_LEN];
  unsigned char version_split[3];
  BAPI_ASSERT(buf[EVENT_TYPE_OFFSET] == FORMAT_DESCRIPTION_EVENT);
  if (len > LOG_EVENT_MINIMAL_HEADER_LEN + ST_COMMON_HEADER_LEN_OFFSET + 1) {
    uint8_t common_header_len =
        buf[LOG_EVENT_MINIMAL_HEADER_LEN + ST_COMMON_HEADER_LEN_OFFSET];
    if (len >=
        static_cast<unsigned long>(common_header_len + ST_SERVER_VER_OFFSET +
                                   ST_SERVER_VER_LEN)) {
      memcpy(version, buf + common_header_len + ST_SERVER_VER_OFFSET,
             ST_SERVER_VER_LEN);
      version[ST_SERVER_VER_LEN - 1] = 0;

      do_server_version_split(version, version_split);
      if (version_product(version_split) < checksum_version_product)
        ret = BINLOG_CHECKSUM_ALG_UNDEF;
      else {
        size_t checksum_alg_offset =
            len - (BINLOG_CHECKSUM_ALG_DESC_LEN + BINLOG_CHECKSUM_LEN);
        ret =
            static_cast<enum_binlog_checksum_alg>(*(buf + checksum_alg_offset));
      }
    }
  }
  BAPI_RETURN(ret);
}

A format description event is the first event of a binlog for binlog-version 4. It describes how the other events are layed out.

Row Based Replication Events

In Row Based replication the changed rows are sent to the slave which removes side-effects and makes it more reliable. Now all statements can be sent with RBR though. Most of the time you will see RBR and SBR side by side.

00000004  87 77 d4 5e|0f|01 00 00  00|79 00 00 00|7d 00 00  |.w.^.....y...}..|
00000014  00|00 00|04 00|38 2e 30  2e 32 30 00 00 00 00 00  |.....8.0.20.....|
00000024  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00000034  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00000044  00 00 00 00 00 00 00|87  77 d4 5e|13|00 0d 00 08  |........w.^.....|
00000054  00 00 00 00 04 00 04 00  00 00|61|00 04 1a 08 00  |..........a.....|
00000064  00 00 08 08 08 02 00 00  00 0a 0a 0a 2a 2a 00 12  |............**..|
00000074  34 00 0a 28|01|63 c8 ff  8b                       |4..(.c....w.^#..|

Event Header

timestamp: 87 77 d4 5e #1590982535

event type: 0f # FORMAT_DESCRIPTION_EVENT

server-id: 01 00 00 00 #1

event-size: 79 00 00 00 #121

log pos: 7d 00 00 00 #125 position of the next event

flag: 00 00 #0

Event Body (FORMAT_DESCRIPTION_EVENT)

binlog-version: 04 00 #4

mysql-server version: 38 2e 30 2e 32 30 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 #8.0.20

create timestamp: 87 77 d4 5e #1590982535

event header length: 13 #19 固定值

event type header lengths： 00 0d 00 08 00 00 00 00 04 00 04 00 00 00 # 长度(15-1)

data length: 61 #97 ==> #check sum block = event-size - event header length - data length = 121-19-97 = 5

skip: 121(eventSize)-19(header)-2(binlogVersion)-50(serverVersion)-4(time)-1(headerLength)-14(typeHeaderLength)-1(dataLength)-5(checkSumBlock) = 25

check sum type: 01 #1 CRC32

例如：TABLE_MAP_EVENT

post-header:
    if post_header_len == 6 {
  4              table id
    } else {
  6              table id
    }
  2              flags

payload:
  1              schema name length
  string         schema name
  1              [00]
  1              table name length
  string         table name
  1              [00]
  lenenc-int     column-count
  string.var_len [length=$column-count] column-def
  lenenc-str     column-meta-def
  n              NULL-bitmask, length: (column-count + 8) / 7

+=====================================+
|        | fixed part (post-header)   |
| data   +----------------------------+
|        | variable part (payload)    |
+=====================================+

参考

mysql-server

六月 2, 2020

源码

1	https://github.com/mysql/mysql-server.git

1	git clone git@github.com:mysql/mysql-server.git

编译

cmake \
-DCMAKE_INSTALL_PREFIX=/tmp/mysql_data/mysql-8.0-rc \
-DMYSQL_DATADIR=/tmp/mysql_data/mysql-8.0-rc/data \
-DSYSCONFDIR=/tmp/mysql_data/mysql-8.0-rc \
-DMYSQL_UNIX_ADDR=/tmp/mysql_data/mysql-8.0-rc/data/mysql.sock \
-DWITH_DEBUG=1  \
-DDOWNLOAD_BOOST=1 \
-DWITH_BOOST=/tmp/boost_1_72