MySQL协议-从0实现一个MySQL客户端连接器

1.介绍

hzmysql项目时一款mysql客户端连接器，可以实现对mysql服务器的连接和各种查询等，样式和unix下的mysql客户端相似。

使用了最原始的socket进行连接，通过wireshake抓包和mysql官方文档进行分析数据的结构而进行的实现。

2.效果演示

root@ubuntu:/home/wyf/clion/hzmysql# ./hzmysql 
usage: ./hzmysql --password=string [options] ... 
options:
  -h, --host        this is the host of the remote address. (string [=127.0.0.1])
  -P, --port        this is the port of the remote address. (int [=3306])
  -u, --user        this is the user for database connection. (string [=root])
  -p, --password    this is the password for database connection. (string)
  -d, --database    this is the database to connect to. (string [=mysql])
  -?, --help        print this message

root@ubuntu:/home/wyf/clion/hzmysql# ./hzmysql -h 192.168.1.6 -p 123456
[2024-02-04 03:54:22] [ThreadID:140462242596672] send Auth Info Successfully!
Connected successfully!
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 130 .
Server version: 8.0.34 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql >

我们可以看到当我们执行这个程序时，首先会与mysql服务器进行三次握手，紧接着mysql服务器会发送自己的相关信息，这些会在后面进行详细介绍。

客户端将自己的认证信息发送给服务器，服务器验证并给客户端相关响应。

相关接口以及演示

  void registerFun() {
        registerCommand("select", &HzMySQL::query);
        registerCommand("use", &HzMySQL::changeDB);
        registerCommand("show", &HzMySQL::show);
        registerCommand("insert", &HzMySQL::insert);
        registerCommand("update", &HzMySQL::update);
        registerCommand("delete", &HzMySQL::deleteInfo);
        registerCommand("drop", &HzMySQL::drop);
        registerCommand("create", &HzMySQL::drop);
    }


bool HzMySQL::executeCommand(std::string &command_string) {
    command_string.resize(command_string.size() - 1);
    size_t spacePos = command_string.find(' ');

    std::string command = (spacePos != std::string::npos) ? command_string.substr(0, spacePos) : command_string;

    std::transform(command.begin(), command.end(), command.begin(), ::tolower);
    auto it = commandHandlers_.find(command);
    if (it != commandHandlers_.end()) {
        return it->second(*this, command_string);
    }
    return false;
}

1.展示数据库

mysql >show databases;
Database 
dc 
information_schema 
mysql 
performance_schema 
sys 
test

2.切换数据库

mysql >use test;
Database changed

3.查询

mysql >select * from info;
id name age 
1 wyf 17 
2 jzh 18

4.插入

mysql >insert into info(name,age) values('test2024-02-04',19);
[2024-02-04 04:13:37] [ThreadID:140462242596672] Field 'id' doesn't have a default value
mysql >insert into info values(3,'test-2024-02-04',20);
Query OK, 1 row affected
mysql >select * from info;
id name age 
1 wyf 17 
2 jzh 18 
3 test-2024-02-04 20

5.删除

mysql >delete from info where name ='wyf';
Query OK, 1 row affected
mysql >select * from info;
id name age 
2 jzh 18 
3 test-2024-02-04 20

6.修改

mysql >update info set age=19 where name ='jzh';
Query OK, 1 row affected
mysql >select * from info where name ='jzh';
id name age 
2 jzh 19

7.退出

mysql >exit;
root@ubuntu:/home/wyf/clion/hzmysql#

3.end

图示是mysql的一个连接周期。

后期我将会详细推进该连接器的实现。

从0实现MySQL连接协议