Golang advanced, smashing database middleware, full of dry goods!

Required reading

Welcome to the official account of daydreaming (reply by programmers alike): database middleware.

You can get the annotated middleware source code package and use it out of the box ~

1, Installing MySQL for Centos7 and Mac

Note address: https://www.cnblogs.com/ZhuChangwu/p/12984153.html

Video streaming address: https://www.bilibili.com/video/BV19g411N7NR?p=2

2, Principle of master-slave replication

2.1. Binlog based_ filename + position

schematic diagram:

Note address: https://mp.weixin.qq.com/s/cSToNVQPK8QCpkjapxNoEw

Video streaming address: https://www.bilibili.com/video/BV19g411N7NR?p=3

2.2. Based on GTID

schematic diagram:

Note address: https://mp.weixin.qq.com/s/V5hU2ATeey871loWQIqHKg

Video streaming address: https://www.bilibili.com/video/BV19g411N7NR?p=4

3, my.cnf

[mysqld]

# port
port = 3306

# Data directory
datadir=/var/lib/mysql

# Error log
log-error=/var/log/mysqld.log

# Create a separate tablespace file for each table
# What exactly is a table space? What is a data table? https://mp.weixin.qq.com/s/CwxRjGI843UerF89G_WJ-Q
innodb_file_per_table=on
innodb_file_format = Barracuda

# binlog related configuration
# 1. What is the use of MySQL bin log? Where? Who wrote it? How to configure?
# https://mp.weixin.qq.com/s/DN1shuyxPJ6BkE_RLezAnA

# 2. Do you know the write mechanism of bin log? Talk about how you adjust parameters online!
# https://mp.weixin.qq.com/s/MtWzoiJtupso5M8z1KUaQQ

# 3. What are the formats of bin log? What's the difference? Advantages and disadvantages? What format is used online?
# https://mp.weixin.qq.com/s/ar-wVbDi4CYjPI1t6fTjVw
log_bin=mysql-bin
log-bin-index = mysql-bin.index
max_binlog_size = 256M
sync-binlog = 1000
binlog-format = ROW

# relaylog related configuration
relay_log_recovery = 1
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay-log = relay-log
relay-log-index = relay-log.index
sync_relay_log = 1000
max_relay_log_size = 256M

# Set the server ID to make the cluster unique
server-id=1

# pid,socket
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/lib/mysql/mysql.sock
symbolic-links=0

4, Test SQL

create database test;
 
use test;

CREATE TABLE `runoob_tbl` (
  `runoob_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `runoob_title` varchar(100) NOT NULL,
  `runoob_author` varchar(40) NOT NULL,
  `submission_date` date DEFAULT NULL,
  PRIMARY KEY (`runoob_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

 INSERT INTO runoob_tbl  (runoob_title, runoob_author, submission_date)
 VALUES("Welcome to wechat search:", "The same programmer", NOW());
CHANGE MASTER TO
    MASTER_HOST='10.4.7.103',
    MASTER_USER='MySQLsync',
    MASTER_PASSWORD='MySQLsync123',
    MASTER_PORT=3306,
    MASTER_AUTO_POSITION = 1;
      
CHANGE MASTER TO
    MASTER_HOST='10.4.7.103',
    MASTER_USER='mysqlsync',
    MASTER_PASSWORD='mysqlsync123',
    MASTER_PORT=3306,
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=434;
    
    CHANGE MASTER TO MASTER_AUTO_POSITION=0; 
 grant replication slave on *.* to MySQLsync@"%" identified by "MySQLsync123";
 
 grant replication slave on *.* to mysqlsync@"127.0.0.1" identified by "mysqlsync123";
 
 grant replication slave on *.* to mysqlsync@"%" identified by "mysqlsync123";

5, Middleware use and concept talk

Brain map:

Video address: https://www.bilibili.com/video/BV19g411N7NR?p=5

Understand these points:

  1. Tell everyone that you think you are directly connected to MySQL? In fact, it's not. You're directly connected to MySQL_Proxy
  2. Node and partition concept
  3. Interpretation of Proxy configuration file
  4. Demonstrate the use of Proxy

6, Overall startup process

  • Obtain the connection between master and slave libraries based on mysql protocol
  • Maintain the connection of the master-slave library into the connection pool
  • Exploring the active mechanism

Video address: https://www.bilibili.com/video/BV19g411N7NR?p=6

Notice: tell everyone that you think you are directly connected to MySQL? In fact, it's not. You're directly connected to MySQL_Proxy!

7, Implementation principle of authority management

Key concerns:

# server.go:381

// todo user whitelist verification. Only the specified user and ip can use Proxy
if allowConnect := conn.IsAllowConnect(); allowConnect == false {
		err := mysql.NewError(mysql.ER_ACCESS_DENIED_ERROR, "ip address access denied by kingshard.")
		conn.writeError(err)
		conn.Close()
		return
	}

Video address: https://www.bilibili.com/video/BV19g411N7NR?p=7

8, MySQL protocol - Handshake! Principle of mobile phone holding system

schematic diagram:

Key concerns:

server.go:388

  // todo establishes a handshake system based on MySQL protocol and client
  if err := conn.Handshake(); err != nil {
  	golog.Error("server", "onConn", err.Error(), 0)
 	 	conn.writeError(err)
 	 	conn.Close()
 	  return
  }

backend_conn.go:101


	// todo is actually the logic for establishing a connection between Proxy and MySQL Server
	// todo, it doesn't matter if you don't understand here, because if you want to understand here, you need to understand the MySQL protocol. I'll explain this to you in the following video
	// todo, you just need to know that after executing the code here, a Conn will be established between proxy and MySQL service,
	// todo and the Proxy will maintain the Conn. after the subsequent user's SQL calls, the Proxy will forward the user's SQL to the conn obtained here and let the MySQL engine really execute the SQL here

	// todo reads the handshake message sent from MySQL
	if err := c.readInitialHandshake(); err != nil {
		c.conn.Close()
		return err
	}
	// todo writes his own information (port, username, password, host)
	if err := c.writeAuthHandshake(); err != nil {
		c.conn.Close()

		return err
	}
	// todo reads the ok message sent by MySQL server
	if _, err := c.readOK(); err != nil {
		c.conn.Close()

		return err
	}

Video address: https://www.bilibili.com/video/BV19g411N7NR?p=8

9, Middleware continues to accept the principle of processing client SQL

Key concerns:

conn.go:279

// todo the following code continuously accepts the sql statements sent by the client in an infinite loop
	for {
		// todo parses the data packet according to the MySQL protocol and obtains the sql statements in the data packet
		data, err := c.readPacket()

		if err != nil {
			return
		}

		if c.configVer != c.proxy.configVer {
			err := c.reloadConfig()
			if nil != err {
				golog.Error("ClientConn", "Run",
					err.Error(), c.connectionId,
				)
				c.writeError(err)
				return
			}
			c.configVer = c.proxy.configVer
			golog.Debug("ClientConn", "Run",
				fmt.Sprintf("config reload ok, ver:%d", c.configVer), c.connectionId,
			)
		}
		// Use the dispatch method to continue processing packets
			if err := c.dispatch(data); err != nil {
			c.proxy.counter.IncrErrLogTotal()
			golog.Error("ClientConn", "Run",
				err.Error(), c.connectionId,
			)
			c.writeError(err)
			if err == mysql.ErrBadConn {
				c.Close()
			}
		}

		if c.closed {
			return
		}

		c.pkg.Sequence = 0
	}
}

Video address: https://www.bilibili.com/video/BV19g411N7NR?p=9

10, How does the middleware execute your select statement?

Key concerns:

conn.go:279

	// Use the dispatch method to continue processing packets
			if err := c.dispatch(data); err != nil {
			c.proxy.counter.IncrErrLogTotal()
			golog.Error("ClientConn", "Run",
				err.Error(), c.connectionId,
			)
			c.writeError(err)
			if err == mysql.ErrBadConn {
				c.Close()
			}
		}

conn.go:340

func (c *ClientConn) dispatch(data []byte) error {
	c.proxy.counter.IncrClientQPS()
	// todo MYSQL protocol stipulates that the data format sent by the client is cmd+data
	// cmd in todo is the type of sql. The types are enumerated below. You can understand it at a glance
	cmd := data[0]
	// The todo data section is the sql details
	data = data[1:]

	switch cmd {
	case mysql.COM_QUIT:
		c.handleRollback()
		c.Close()
		return nil
	case mysql.COM_QUERY: // todo select statement
		return c.handleQuery(hack.String(data))
	case mysql.COM_PING: // todo ping statement
		return c.writeOK(nil)
	case mysql.COM_INIT_DB:
		return c.handleUseDB(hack.String(data))
	case mysql.COM_FIELD_LIST:
		return c.handleFieldList(data)
	case mysql.COM_STMT_PREPARE:
		return c.handleStmtPrepare(hack.String(data))
	case mysql.COM_STMT_EXECUTE:// todo insert and update statements
		return c.handleStmtExecute(data)
	case mysql.COM_STMT_CLOSE:
		return c.handleStmtClose(data)
	case mysql.COM_STMT_SEND_LONG_DATA:
		return c.handleStmtSendLongData(data)
	case mysql.COM_STMT_RESET:
		return c.handleStmtReset(data)
	case mysql.COM_SET_OPTION:
		return c.writeEOF(0)
	default:
		msg := fmt.Sprintf("command %d not supported now", cmd)
		golog.Error("ClientConn", "dispatch", msg, 0)
		return mysql.NewError(mysql.ER_UNKNOWN_ERROR, msg)
	}

	return nil
}

Video address: https://www.bilibili.com/video/BV19g411N7NR?p=10

11, Implementation principle of read-write separation

Key concerns:

conn_pershard.go:97

	// todo obtains an available connection from the selected DB. If there is no open transaction and it is a read request, executeDB.IsSlave is generally true
	conn, err := c.getBackendConn(executeDB.ExecNode, executeDB.IsSlave)
	defer c.closeConn(conn, false)
	if err != nil {
		return false, err
	}

Video address: https://www.bilibili.com/video/BV19g411N7NR?p=11

12, Free Proxy source code

The annotated project has been uploaded to Baidu online disk. The project is based on vendor management dependency package and can be used out of the box ~

Official account of daydreaming (reply programmers), database middleware, you can get the current documents and source packages.

Tags: Go

Posted on Sun, 28 Nov 2021 04:58:14 -0500 by michaellunsford