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:
- Tell everyone that you think you are directly connected to MySQL? In fact, it's not. You're directly connected to MySQL_Proxy
- Node and partition concept
- Interpretation of Proxy configuration file
- 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.
