Naming

Generic

Do

Use meaningful names.

Use lower_case_underscores.

The length of db name should be less than 42

The length of table name should be less than 48

The length of username should be less than 32

Avoid MySQL reserved keywords as a column‘s name

e.g., keyword or reserved words , like: name, status, type, data, description, level, host, port……

https://dev.mysql.com/doc/refman/8.0/en/keywords.html
dangerous words ,like : rm, truncate ,delete……
confusion words , like : master, slave ,( which is a MySQL Replication role.)

...

Posted by Wei on 2021-11-01, Last Modified on 2025-08-11

【MySQL】Logs - Redo Logs (Write-ahead Logging)

Background

我们都知道，事务的四大特性里面有一个是持久性（durability） ，具体来说就是

只要事务提交成功，那么对数据库做的修改就会被永久保存下来了，不可能因为任何原因再回到原来的状态 。

那么 MySQL 是如何保证一致性的呢？最简单的做法是在每次事务提交的时候，将该事务涉及修改的数据页全部刷新到磁盘中。但是这么做会有严重的性能问题，主要体现在两个方面：

因为 InnoDB 是以**页（page）**为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了！
一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，因而是随机 I/O（random I/IO），随机 I/O写入性能相对很差

因此 MySQL设计了 Redo Log， 具体来说就是只记录事务对数据页做了哪些修改，这样就能在尽可能的提高性能的同时，保证持久性（durability）。注意到，写入 Redo Log 写入位于磁盘中的 Redo Log file 是顺序 I/O（即通过append-only的方式把修改追加到 Redo Log file 中），而真正去磁盘更新存储的数据库数据是随机I/O。

...

Posted by Wei on 2021-10-31, Last Modified on 2025-08-11

【MySQL】Logs - Relay Logs

A replica server creates several repositories of information to use for the replication process:

The replica’s relay log, which is written by the replication I/O (receiver) thread, contains the transactions read from the replication source server’s binary log. The transactions in the relay log are applied on the replica by the replication SQL (applier) thread.
The replica’s connection metadata repository contains information that the replication receiver thread needs to connect to the replication source server and retrieve transactions from the source’s binary log. The connection metadata repository is written to the mysql.slave_master_info table.
The replica’s applier metadata repository contains information that the replication applier thread needs to read and apply transactions from the replica’s relay log. The applier metadata repository is written to the mysql.slave_relay_log_info table.

...

Posted by Wei on 2021-10-31, Last Modified on 2025-08-11

【MySQL】搭建基于 Binlog Position 的主从复制

Demo

在我的demo中，我是在docker 中创建了两个docker container。

# 这里：
# 1. --restart=unless-stopped：container 会自动启动
# 2. MYSQL_ROOT_HOST=%：root用户可以在任何host上登录
# 3. MYSQL_ROOT_PASSWORD=1234：root 密码为1234
# 4. 3307:3306：将本机的 3307 映射到 container 内部的 3306
$ docker run --name sw-mysql1 --restart=unless-stopped -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=1234 -p 3307:3306 -d mysql

$ docker run --name sw-mysql2 --restart=unless-stopped -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=1234 -p 3308:3306 -d mysql

值得一提的是，这两个container在默认情况下并不能相互通讯

$ docker network create myNetwork
$ docker network connect myNetwork sw-mysql1
$ docker network connect myNetwork sw-mysql2

...

Posted by Wei on 2021-10-30, Last Modified on 2023-03-29

Run the MySQL Server Process Properly

Make sure that the server is running. If it is not, clients cannot connect to it. For example, if an attempt to connect to the server fails with a message such as one of those following, one cause might be that the server is not running:

$> mysql
ERROR 2003: Can't connect to MySQL server on 'host_name' (111)
$> mysql
ERROR 2002: Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (111)

...

Posted by Wei on 2021-10-28, Last Modified on 2025-08-11

【MySQL】Install MySQL in Docker

Install

$ docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -p 3306:3306 -d mysql:tag

… where some-mysql is the name you want to assign to your container, my-secret-pw is the password to be set for the MySQL root user and tag is the tag specifying the MySQL version you want. See the list above for relevant tags.

# install the latest mysql with root's passord being 1234 and allowing remote access
$ docker run --name sw-mysql --restart=unless-stopped -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=1234 -p 3306:3306 -d mysql

# enter the container
$ docker exec -it sw-mysql sh

if you want to access your mysql server remotely, you need mapping the port 3306 of the container to port 3306 of the host machine.
If you wanna run MySQL in another port, use -p 3307:3306, meaning map the container-port 3306 on the prefered TCP port (of your server), in this case it is 3307
Set the environment variable MYSQL_ROOT_HOST with wildcards to allow root connections from other hosts

# access mysql from another host
$ mysql -u root -h 192.168.18.134 -p1234

...

Posted by Wei on 2021-10-26, Last Modified on 2025-08-11

【MySQL】Logs - Binary Logs

Binary Logs

The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data.

Purpose

The binary log has two important purposes:

For replication, the binary log on a replication source server provides a record of the data changes to be sent to replicas. The source sends the information contained in its binary log to its replicas, which reproduce those transactions to make the same data changes that were made on the source. See Section 17.2, “Replication Implementation”.
Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup. See Section 7.5, “Point-in-Time (Incremental) Recovery”.

The binary log is not used for statements such as SELECT or SHOW that do not modify data.

...

Posted by Wei on 2021-10-26, Last Modified on 2023-03-29

【Engineering】Single Source of Truth

Single source of truth

Definition 1

In information systems design and theory, single source of truth (SSOT) is the practice of structuring information models and associated data schema such that every data element is mastered (or edited) in only one place. Any possible linkages to this data element (possibly in other areas of the relational schema or even in distant federated databases) are by reference only. Because all other locations of the data just refer back to the primary “source of truth” location, updates to the data element in the primary location propagate to the entire system without the possibility of a duplicate value somewhere being forgotten.

Deployment of an SSOT architecture is becoming increasingly important in enterprise settings where incorrectly linked duplicate or de-normalized data elements (a direct consequence of intentional or unintentional denormalization of any explicit data model) pose a risk for retrieval of outdated, and therefore incorrect, information. A common example would be the electronic health record, where it is imperative to accurately validate patient identity against a single referential repository, which serves as the SSOT. Duplicate representations of data within the enterprise would be implemented by the use of pointers rather than duplicate database tables, rows, or cells. This ensures that data updates to elements in the authoritative location are comprehensively distributed to all federated database constituencies in the larger overall enterprise architecture.

...

Posted by Wei on 2021-10-26, Last Modified on 2025-08-11

【Database】冷热存储数据分离

背景

在海量大数据场景下，一张表中的部分业务数据随着时间的推移仅作为归档数据或者访问频率很低，同时这部分历史数据体量非常大，比如订单数据或者监控数据，降低这部分数据的存储成本将会极大的节省企业的成本。如何以极简的运维配置成本就能为企业极大降低存储成本。

...

Posted by Wei on 2021-10-24, Last Modified on 2025-08-11

【Grafana】Advance

...