MySQL/MariaDB

MySQL是一个广泛使用的开源关系型数据库管理系统，其历史可以追溯到1979年，由Michael Widenius（也被称为Monty）开发了一个名为UNIREG的内部数据库工具。MySQL这个名字来源于Monty的女儿“My”，而数据库的海豚标志名为“Sakila”，这个名字是由一个竞赛中选出的。

MySQL的发展历史上有几个重要的里程碑：

1995年，MySQL AB公司在瑞典成立，由Monty Widenius、David Axmark和Allan Larsson共同创建，并发布了MySQL数据库的第一个版本。
2000年，MySQL决定开源，并根据GPL的条款发布软件，这导致公司收入大幅下降，但随后逐渐恢复。
2008年，MySQL AB被Sun Microsystems以大约10亿美元的价格收购。
2009年，Oracle收购了Sun Microsystems，从而也将MySQL纳入其产品线。
2010年，MySQL 5.5发布，InnoDB成为默认的存储引擎。
2016年，MySQL 8.0.0版本发布，引入了多项新特性和性能改进。

在Oracle收购MySQL之后，由于对MySQL未来发展的担忧，Monty Widenius创建了MariaDB，这是一个MySQL的分支，旨在完全兼容MySQL，同时提供一些改进和新特性。MariaDB使用XtraDB存储引擎代替了MySQL的InnoDB，并由开源社区维护。

MySQL的成功部分归功于其开源的特性，它允许全球开发者参与改进，形成了一个强大的生态系统。Monty Widenius坚信开源是软件开发的最佳方式，因为它可以集合全球的意见和力量来发现和解决问题。此外，MySQL的普及也得益于互联网的兴起和对高效、稳定数据库的需求。

除了MariaDB之外，还有其他一些基于MySQL的分支或自研存储引擎，例如Percona Server，它是基于InnoDB存储引擎的增强版，提供了XtraDB存储引擎以提升性能和易管理性。此外，国内也有一些基于MySQL的分支或自研存储引擎，如网易的InnoSQL和极数云舟的ArkDB 。

MySQL的发音为“my ess cue el”，是一个因其体积小、速度快、总体拥有成本低而受到许多中小型网站青睐的数据库系统。随着技术的发展和市场需求的变化，MySQL及其衍生产品继续在数据库领域扮演着重要角色。

https://www.mysql.com/cn/https://mariadb.org/zh/

install

mysqlmysql-ce download

sudo yum localinstall mysql-community-client-plugins-8.0.28-1.el8.x86_64.rpm
sudo yum localinstall mysql-community-libs-8.0.28-1.el8.x86_64.rpm
sudo yum localinstall mysql-community-common-8.0.28-1.el8.x86_64.rpm
sudo yum localinstall mysql-community-icu-data-files-8.0.28-1.el8.x86_64.rpm
sudo yum localinstall mysql-community-client-8.0.28-1.el8.x86_64.rpm
sudo yum localinstall mysql-community-server-8.0.28-1.el8.x86_64.rpm

sudo yum list installed | grep mysql
# Repository extras is listed more than once in the configuration
# mysql-community-client.x86_64            8.0.28-1.el8                        @@commandline
# mysql-community-client-plugins.x86_64    8.0.28-1.el8                        @@commandline
# mysql-community-common.x86_64            8.0.28-1.el8                        @@commandline
# mysql-community-icu-data-files.x86_64    8.0.28-1.el8                        @@commandline
# mysql-community-libs.x86_64              8.0.28-1.el8                        @@commandline
# mysql-community-server.x86_64            8.0.28-1.el8                        @@commandline
#

virtual container

$ docker pull mysql:8.0.28
$ docker run -d --name mysql -p 3306:3306 -v ~/var/lib/mysql/my.cnf:/etc/my.cnf -v ~/var/lib/mysql/my.cnf.d:/etc/my.cnf.d -v ~/var/lib/mysql/data:/var/lib/mysql -e MYSQL_ALLOW_EMPTY_PASSWORD=yes mysql:8.0.28

mariadbmariadb download mysql vs mariadb mariadb repo

$ sudo apt install mariadb-client-core=1:11.4.2+maria~deb10
$ sudo apt install mariadb-client=1:11.4.2+maria~deb10
$ sudo apt install mariadb-server-core=1:11.4.2+maria~deb10
$ sudo apt install mariadb-server=1:11.4.2+maria~deb10

virtual container

$ nerdctl pull mariadb:11.4.2

$ nerdctl run -d --name mariadb -p 3306:3306 -v ~/var/lib/mysql/my.cnf:/etc/my.cnf -v ~/var/lib/mysql/my.cnf.d:/etc/my.cnf.d -v ~/var/lib/mysql/data:/var/lib/mysql -e MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=yes mariadb:11.4.2

Authentication, Authorization, Audition

mysql> CREATE DATABASE wp;
mysql> CREATE USER wp;
mysql> ALTER USER 'wp'@'%' IDENTIFIED WITH 'mysql_native_password' BY '123456';
mysql> GRANT INSERT,DELETE,UPDATE,SELECT,CREATE ON wp.* TO 'wp'@'%';
mysql> FLUSH PRIVILEGES;
mysql> SHOW GRANT FOR 'wp'@'%';

mariadb> SET PASSWORD FOR 'wp'@'%' =PASSWORD('123456');

MySQL client

根据服务状态划分：

冷备份数据库服务关闭，拷贝存储目录下的文件。
热备份数据库服务在线，对数据库进行备份。

热备份划分：

物理备份通过文件系统的快照功能对数据库所在磁盘分区进行备份（LVM）
逻辑备份使用数据库系统提供的 dump 工具进行备份

参考：https://blog.csdn.net/qq_34556414/article/details/106781973

mysqldump mysqlbinlog

# 热备份 全量备份
# mysqldump
# 常用选项
# myisam 存储引擎备份选项
# --lock-all-tables
# innodb 存储引擎备份选项
# --single-transaction
# 指定库
# --databases
# 仅表结构
# --no-data
# 去除 drop statement
# --skip-add-drop-table 
# 去除 create statment 
# --no-create-info
# 库中存在大表
# --quick
# 创建新的 binlog
# --flush-logs
# --master-data=2
# 1: 每个库生成一个日志文件
# 2：仅生成一个日志文件

# 表结构与数据共同备份
# 数据库备份
$ mysqldump -u root -p --databases wp --single-transaction > ~/var/lib/mysql/wp-bak/all-wp-202407221700.sql

# 备份并压缩
# statement 格式输出的文件占用空间较大压缩可减少大量占用
$ mysqldump -u root -p --databases wp --single-transaction --flush-logs --master-data=2 | gzip > ~/var/lib/mysql/wp-bak/all-wp-202407221700.sql.gz


# 指定表备份
$ mysqldump -u root -p wp wp_users wp_usermeta --single-transaction > ~/var/lib/mysql/wp-bak/wp-users-202407221700.sql


# 其他格式
# xml
$ mysqldump -u root -p wp > ~/var/lib/mysql/data-wp-202407221801.sql --xml
# csv
$ mysqldump -u root -p wp --tab=/var/lib/mysql-files --fields-terminated-by=',' --fields-enclosed-by='\"'
# 查看文件操作许可目录参数
mysql> SHOW VARIABLES LIKE 'secure_file_priv';

# 还原备份
$ mysql -h 192.168.1.10 -P 3306 -u root -p < ~/var/lib/mysql/all-wp-202407221700.sql

# 交互式
mysql> source ~/var/lib/mysql/all-wp-202407221700.sql

# 压缩还原
$ gunzip < ~/var/lib/mysql/wp-bak/all-wp-202407221700.sql.gz | mysql -u root -p

# 增量备份
# mysqlbinlog

# 需要开启 binlog 功能
mysql> SHOW VARIABLES LIKE '%log_bin%';
# +---------------------------------+-----------------------------+
# | Variable_name                   | Value                       |
# +---------------------------------+-----------------------------+
# | log_bin                         | ON                          |
# | log_bin_basename                | /var/lib/mysql/binlog       |
# | log_bin_index                   | /var/lib/mysql/binlog.index |
# | log_bin_trust_function_creators | OFF                         |
# | log_bin_use_v1_row_events       | OFF                         |
# | sql_log_bin                     | ON                          |
# +---------------------------------+-----------------------------+
$ sudo vim /etc/my.cnf
# [mysqld] 下添加配置
server-id=1
log-bin=binlog

# 当前日志文件
mysql> SHOW MASTER STATUS;
mysql> SHOW BINLOG EVENTS IN 'binlog.000010';

# 日志查看
$ mysqlbinlog --base64-output=decode-rows --verbose /var/lib/mysql/binlog.000010 > mysql

# 根据日志文件还原
$ mysqlbinlog --server-id=1 --database=wp /var/lib/mysql/binlog.000010 | mysql -u root -p

# 基于时间点恢复
# --start-data="2024-08-01 9:00:00" 
# --stop-datatime="2024-08-01 10:00:00"

# 基于端点恢复
# --start-posistion="315"
# --stop-position="890"

Master Slave

Data Mock

mysql> CREATE TABLE person(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
person_id TINYINT NOT NULL COMMENT '用户id',
person_name VARCHAR(200) COMMENT '用户名称',
gmt_create DATETIME COMMENT '创建时间',
gmt_modified DATETIME COMMENT '修改时间' ) COMMENT '人员信息表';

mysql> INSERT INTO person VALUES(1, 1,'user_1', NOW(), NOW());

mysql> SET @i=1;  

# 笛卡尔积伪列写入。数据量根据 2^n 增加，n 为执行次数。
mysql> INSERT INTO person(id, person_id, person_name, gmt_create, gmt_modified)
SELECT
@i:=@i+1,
LEFT(rand()*100, 2) AS person_id,
CONCAT('user_', @i%2048),
DATE_ADD(gmt_create, INTERVAL + CAST(RAND()*1000 AS SIGNED) SECOND),
DATE_ADD(gmt_modified,INTERVAL + CAST(RAND()*10000 as SIGNED) SECOND)
FROM person;

# 计算表大小
mysql> SELECT CONCAT(ROUND(SUM((data_length+index_length)/1024/1024), 4), 'MB') FROM information_schema.tables WHERE table_schema='wp' AND table_name='person';

Storage Engine

segment 段 由一个或多个区组成，区在文件系统时一个连续分配的空间在 innodb 中式连续的 64 页，不过在段中不要求区与区之间式相邻的。段是数据库中的分配单位，不同数据类型的数据库对象以不同的段形式存在。当我们创建数据表、索引的时候，就会相应创建对应的段，比如将建一张表时会创建一个表段，创建一个索引时会创建一个索引段。

extent 区 innodb 中，一个区会分配 64 个连续的页。因为 innodb 中页大小默认是 16 KB，所以一个区的大小是64*16KB=1MB。在任何情况下每个区大小都为 1MB，为了保证页的连续性，innodb 每次会从磁盘申请 4~5 个区。 SHOW VARIABLES LILE 'innodb_page_size';

page 页 页是 innodb 最小管理单位。每页默认 16 KB；innodb 从 1.2.x 开始可以通过参数 innodb_page_size 将页设置为 4KB、8KB、16KB。设置完成后不可再修改。

page type

B-tree Node
Undo Log Page
System Log Page
Transaction System Page
InsertBufferBitmap
InsertBuffer Free List
UncompressedBLOB Page (BLOB,Binary large object)
CompressedBLOB Page

https://www.runoob.com/w3cnote/mysql-different-nnodb-myisam.html innodb-storage-engine myisam-storage-engine innodb-row-format

Data Type

技巧

单字段拆分多行

sql

-- substring_index(str,delim,n)
-- delim: delimiter 分隔符
-- n: 正数从左边分隔，负数从右边分隔。
select
	a.help_topic_id,
	b.s,
	substring_index(substring_index(b.s,',',a.help_topic_id + 1),',',-1),
	substring_index(b.s,',',a.help_topic_id+1)
from
	mysql.help_topic a
join (select '2020,2021,2022,2023' as s)B
where 
	a.help_topic_id < (length(b.s) - length(replace(b.s,',','')) + 1)

指定加密插件

# /etc/my.cng
# [mysqld]
# anthentication_policy=mysql_native_password

表名大小写

# /etc/my.cnf
# [mysqld]
# lower_case_table_names=0

# 0 表名存储为给定的大小写，比较时区分大小写。
# 1 表名存储为小写，比较时不区分大小写。
# 2 表名存储为给定的大小写，比较时是小写。

cli 免密

# ~/.my.cnf mysql-cli,mariadb-cli 当前用户免密 

[client]
user=
password=

[mariadb-dump]
user=
password=

[mysqldump]
user=
password=

指定导入路径

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

# /etc/mysql/my.cnf
secure-file-priv=/path/to/dir

窗口函数

窗口函数与普通聚合函数不同，它不会将多行合并为一行，而是为每一行返回一个值，同时保留原始数据行。

CUME_DIST 累积分布

SQL

CUME_DIST() OVER (
 PARTITION BY expr, ...
 ORDER BY expr [ASC | DESC], ...
)

DENSE_RANK 分配排名

SQL

DENSE_RANK() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)

FIRST_VALUE 取帧

SQL

FIRST_VALUE(expression) OVER (
        [partition_clause]
        [order_clause]
        [frame_clause]
)

MySQL/MariaDB ​

install ​

Authentication, Authorization, Audition ​

MySQL client ​

Master Slave ​

Data Mock ​

Storage Engine ​

Data Type ​

技巧 ​

单字段拆分多行 ​

指定加密插件 ​

表名大小写 ​

cli 免密 ​

指定导入路径 ​

窗口函数 ​