mysql架构解析

• 架构

　　mysql是cs架构，在服务端可以启动该mysqld服务，mysqld进程默认监听在tcp:3306。在客户端使用命令行工具mysql或者图形化工具navicat for mysql进行远程连接，连接信息包括user,password,host，连接池组件会到mysql.user表中验证用户和密码是否ok，登录host时候匹配。通过则登录成功。

　　在服务端，最外层是connector组件，用于外部应用程序访问数据。

　　下一层是连接池组件，用于验证等。管理服务和工具，SQL接口组件，

　　下面是插件式存储引擎，文件系统。

　　查看mysql在磁盘上的存储：

MariaDB [(none)]> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

[root@sqlm mysql]# ll -h
total 47M
-rw-rw---- 1 mysql mysql  16K Mar  3 10:03 aria_log.00000001
-rw-rw---- 1 mysql mysql   52 Mar  3 10:03 aria_log_control
-rw-rw---- 1 mysql mysql  26M Mar 20 19:58 ibdata1
-rw-rw---- 1 mysql mysql 5.0M Mar 20 19:58 ib_logfile0
-rw-rw---- 1 mysql mysql 5.0M Mar 20 19:58 ib_logfile1
-rw-rw---- 1 mysql mysql 1.5K Feb  8 15:46 master1-bin.000001
-rw-rw---- 1 mysql mysql 1.8M Feb 11 14:35 master1-bin.000002
-rw-rw---- 1 mysql mysql  264 Mar  3 10:03 master1-bin.000003
-rw-rw---- 1 mysql mysql 8.8M Mar  6 17:09 master1-bin.000004
-rw-rw---- 1 mysql mysql 1.8K Mar 19 21:04 master1-bin.000005
-rw-rw---- 1 mysql mysql  245 Mar 20 19:41 master1-bin.000006
-rw-rw---- 1 mysql mysql  126 Mar 20 19:41 master1-bin.index

　　

• 插件式存储引擎

　　　存储引擎是mysql区别于其他数据库的一个最重要特性。存储引擎是表级别的，可以针对不同功能的表，设定不同的存储引擎（每个存储引擎的特点不一样），以达到最优性能。我们应该深刻的意识到，存储引擎是mysql的核心。

　　　mysql提供了存储引擎接口，使我们可以编写自己的存储引擎。在大兴企业中，会有人员开发更加适合于自己业务的存储引擎。

　show engines命令显示mysql支持的存储引擎。innodb为默认引擎。

MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | CSV storage engine                                                         | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                                      | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                  | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                                      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                                     | NO           | NO   | NO         |
| FEDERATED          | YES     | FederatedX pluggable storage engine                                        | YES          | NO   | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                         | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                     | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)

 

• 配置文件

　　mysql的配置文件以参数集的方式体现，每个参数集下有很多parameter=value的形式体现。[mysqld]为daemon service，[mysqld_safe]为线程安全。这些配置在3大点中有所体现。

[root@sqlm ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin
server_id=1
log-basename=master1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

log-bin=mysql-bin