MySQL体系结构及多实例
MySQL客户端和服务器端模型
- MySQL是一个典型C/S,服务器端与客户端两部分组成
- 服务器端程序 mysqld
- mysql自带的客户端(mysql mysqladmin mysqldump等),API接口(php-mysql)
连接方式
通过网络连接串,TCP/IP:(用户名;密码;IP;port) mysql -uroot -p123 -h 10.0.0.52 -P 3306 通过套接字文件,SOCKET:(用户名;密码;Socket路径) mysql -uroot -p123 -S /application/mysql/tmp/mysql.sock
MySQL服务器构成(实例的构成)
实例
MySQL在启动过程
- 启动后台守护进程(mysqld),并生成工作线程(xxxx)
- 预分配内存结构供MySQL处理数据使用
- 线程是干活的,进程是分配线程的
- MySQL启动过程中,启动了一个守护进程(mysqld),并生成了许多线程
实例是什么:
MySQL的后台进程+线程+预分配的内存结构
mysqld服务的构成
分为三层:
- 连接层
- SQL层
- 存储引擎层
连接层
- 通信协议(tcp/ip socket)
- 连接线程
- 用户验证(合法性,让不让连接)
SQL层
sql层的处理流程图解:
SQL:结构化的查询语言(数据库内部的逻辑语言)又分为:
- DDL:数据库定义语言
- DCL:数据库控制语言
- DML:数据库操作语言
- DQL:数据查询语言
SQL层的功能
1、判断
语法、语句、语义
语句类型:DQL select
2、我们数据库不能真正理解SQL语句
必须明确的知道,数据在哪个磁盘的,哪个文件的哪个,哪个页上。
3、数据库对象授权情况进行检查。
4、解析(解析器)
将sql语句解析成N种执行计划
mysql没法直接执行sql语句,必须解析成执行计划,运行执行计划,最终生成如何区磁盘找数据的方式
5、优化(优化器)
根据不同执行计划,知道哪个代价是最低的
MySQL5.6以后学习了oracle的基于代价算法,从N种执行计划中,选择代价最小的交给“执行器”
6、“执行器”
运行执行计划
生成如何去磁盘找数据的方式
7、将取数据获取的方式,交由下层(存储引擎层)进行处理
8、最终,将取出的数据抽象成管理员或用户能看懂的方式(表),展现用户面前。
9、查询缓存:缓存之前查询的数据。
假如我们查询的表是一个经常有变动的表,查询缓存不要设置太大。
存储引擎层
存储引擎是充当不同表类型的处理程序的服务器组件。
功能:
存数据、取数据
数据的不同存储方式
不同的管理方式:
事务(增、删、改)
备份恢复
高级功能(高可用的架构、读写分离架构)
依赖于存储引擎的功能
- 存储介质
- 事务功能
- 锁定
- 备份和恢复
- 优化
- 特殊功能:
-
全文搜索 引用完整性 空间数据处理
数据库的逻辑结构(数据内部结构)
为了用户能够读懂数据出现的,更好的理解数据。
管理数据的一种方式:
对象包括库和表,库中包括多张表,表中包括行记录和列结构
MySQL如何使用磁盘
库是用目录表示的,表是库目录下的文件表示的
- .MYD类型文件:存储行数据信息
- .frm类型文件:存储列定义信息
- .MYI类型文件:存储索引
mysql的存储方式
程序文件随数据目录一起存储在服务器安装目录下。执行各种客户机程序、管理程序和实用程序时,将创建程序可执行文件和日志文件。首要使用磁盘空间的是数据目录。
- 服务器日志文件和状态文件:包含有关服务器处理的语句的信息。日志可用于进行故障排除、监视、复制和恢复。
- InnoDB 日志文件:(适用于所有数据库)驻留在数据目录级别。
- InnoDB 系统表空间:包含数据字典、撤消日志和缓冲区。
每个数据库在数据目录下均具有单一目录(无论在数据库中创建何种类型的表)。数据库目录存储以下内容:
- 数据文件:特定于存储引擎的数据文件。这些文件也可能包含元数据或索引信息,具体取决于所使用的存储引擎。
- 格式文件 (.frm):包含每个表和/或视图结构的说明,位于相应的数据库目录中。
- 触发器:与某个表关联并在该表发生特定事件时激活的命名数据库对象。
数据目录的位置取决于配置、操作系统、安装包和分发。典型位置是 /var/lib/mysql。
MySQL 在磁盘上存储系统数据库 (mysql)。mysql 包含诸如用户、特权、插件、帮助列表、事件、时区实现和存储例程之类的信息。
小结
MySQL的连接方式
1. 通过网络连接串,TCP/IP:(用户名;密码;IP;port) mysql -uroot -p123 -h 10.0.0.52 -P 3306
3. 通过套接字文件,SOCKET:(用户名;密码;Socket路径) mysql -uroot -p123 -S /application/mysql/tmp/mysql.sock
优化相关的理念(mysqld)
SQL解析(解析器):解析成 ,执行计划 mysql能够理解的,执行SQL的一种方式
将sql语句解析成N种执行计划
mysql没法直接执行sql语句,必须解析成执行计划,运行执行计划,最终生成如何
优化(优化器):直接影响选择哪个执行计划
基本规则:根据不同执行计划,知道哪个代价是最低的
MySQL5.6以后学习了oracle的基于代价算法,从N种执行计划中,选择代价最小的交给“执行器”
查询缓存:缓存之前所查询的数据
作为了解,生产中,一般会用类似于redis或memcache来代替
逻辑结构---物理结构对应关系
库 ----->data 就是一个目录,为了存放多张表
表 ----->data 在相应的库中,用多个文件来表示
myisam表:3个文件(.myd数据文件 .myi索引文件 .frm表(列)结构定义文件)
innodb:2个或者1个
如何使用磁盘的
- 多个库多个目录,目录下存放了多个表的存储文件
- 初始化生成的存储文件:auto.cnf、ibdataN、ib_logfileN、db02.err等
MySQL管理
连接管理:mysql
连接mysql可以使用的参数:
[root@db02 ~]# mysql --help -u <user_name> 或 --host=<user_name> # 登陆mysql的用户 -p<password> # 登陆mysql的密码 -h <host_name> 或 --host=<host_name> # 远程连接,后面跟连接mysql服务器的IP --protocol=<protocol_name> -P <port_number> 或 --port=<port_number> # 指定端口号 -S <socket_name> 或 --socket=<socket_name> # 指定sock文件路径
两种连接方式
mysql -uroot -p123 -S /application/mysql/tmp/mysql.sock mysql -uroot -p123 -h 10.0.0.52 -P 3306
参数文件配置/etc/my.cnf
功能:
1.影响到服务器进程的启动
2.影响到客户端程序
如何配置my.cnf
使用不同标签明确指定影响哪部分功能
服务器端:
[server]包括: [mysqld] ---- > 一般设置此项 [mysqld_safe]
客户端:
[client] ---- > 为了方便设置此项
[mysql]
[mysqldump]
[mysqladmin]
配置文件读取顺序
/etc/my.cnf ---- > /etc/mysql/my.cnf --- > $MYSQL_HOME/my.cnf ---- > defaults-extra-file --- > ~/.my.cnf 如果在启动mysql时利用--defaults-file指定了参数配置文件,那么直接读取指定的文件 /application/mysql/bin/mysqld_safe --defaults-file=/tmp/aa.txt
MySQL数据库的关闭
常规关闭:
mysqladmin shutdown
servive mysqld stop
还有使用kill杀进程的方式关闭,但是尽量不要使用kill方式关闭
可通过如下地址查看生产高并发环境野蛮粗鲁杀死数据库导致故障企业案例:
http://oldboy.blog.51cto.com/2561410/1431161
http://oldboy.blog.51cto.com/2561410/1431172
MySQL多实例
编辑要指定的my.cnf和启动脚本
my.cnf文件
实例1:3306
[root@db02 3306]# cat /data/3306/my.cnf [client] port = 3306 socket = /data/3306/mysql.sock [mysqld] user = mysql port = 3306 socket = /data/3306/mysql.sock basedir = /application/mysql datadir = /data/3306/data log-bin = /data/3306/mysql-bin server-id = 6 [mysqld_safe] log-error=/data/3306/my_3306.err pid-file=/data/3306/mysqld.pid
实例2:3307
[root@db02 3306]# cat /data/3307/my.cnf [client] port = 3307 socket = /data/3307/mysql.sock [mysqld] user = mysql port = 3307 socket = /data/3307/mysql.sock basedir = /application/mysql datadir = /data/3307/data log-bin = /data/3307/mysql-bin server-id = 7 [mysqld_safe] log-error=/data/3307/myy_3307.err pid-file=/data/3307/mysqld.pid
启动脚本
3306启动脚本:
[root@db02 3306]# cat /data/3306/mysql #!/bin/sh #init port=3306 mysql_user="root" CmdPath="/application/mysql/bin" mysql_sock="/data/${port}/mysql.sock" mysqld_pid_file_path=/data/3306/3306.pid start(){ if [ ! -e "$mysql_sock" ];then printf "Starting MySQL...\n" /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf --pid-file=$mysqld_pid_file_path 2>&1 > /dev/null & sleep 3 else printf "MySQL is running...\n" exit 1 fi } stop(){ if [ ! -e "$mysql_sock" ];then printf "MySQL is stopped...\n" exit 1 else printf "Stoping MySQL...\n" mysqld_pid=`cat "$mysqld_pid_file_path"` if (kill -0 $mysqld_pid 2>/dev/null) then kill $mysqld_pid sleep 2 fi fi } restart(){ printf "Restarting MySQL...\n" stop sleep 2 start } case "$1" in start) start ;; stop) stop ;; restart) restart ;; *) printf "Usage: /data/${port}/mysql {start|stop|restart}\n" esac
3307启动脚本:
[root@db02 3306]# cat /data/3307/mysql #!/bin/sh #init port=3307 mysql_user="root" CmdPath="/application/mysql/bin" mysql_sock="/data/${port}/mysql.sock" mysqld_pid_file_path=/data/3307/3307.pid start(){ if [ ! -e "$mysql_sock" ];then printf "Starting MySQL...\n" /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf --pid-file=$mysqld_pid_file_path 2>&1 > /dev/null & sleep 3 else printf "MySQL is running...\n" exit 1 fi } stop(){ if [ ! -e "$mysql_sock" ];then printf "MySQL is stopped...\n" exit 1 else printf "Stoping MySQL...\n" mysqld_pid=`cat "$mysqld_pid_file_path"` if (kill -0 $mysqld_pid 2>/dev/null) then kill $mysqld_pid sleep 2 fi fi } restart(){ printf "Restarting MySQL...\n" stop sleep 2 start } case "$1" in start) start ;; stop) stop ;; restart) restart ;; *) printf "Usage: /data/${port}/mysql {start|stop|restart}\n" esac
创建数据目录授权
mkdir /data/{3306,3307}/data -p chown -R mysql.mysql /data/
初始化数据
cd /application/mysql/scripts ./mysql_install_db --defaults-file=/data/3306/my.cnf --basedir=/application/mysql --datadir=/data/3306/data --user=mysql ./mysql_install_db --defaults-file=/data/3307/my.cnf --basedir=/application/mysql --datadir=/data/3307/data --user=mysql
授权执行权限并启动数据库
5.6.36特殊性:需要创建错误日志文件
touch /data/3306/my_3306.err touch /data/3307/my_3307.err
给启动脚本执行权限,启动后检查
chmod 700 /data/3306/mysql chmod 700 /data/3307/mysql [root@db02 3306]# /data/3306/mysql start Starting MySQL... [root@db02 3306]# /data/3307/mysql start Starting MySQL... [root@db02 3306]# netstat -lntup|grep 330 tcp 0 0 :::3306 :::* LISTEN 3180/mysqld tcp 0 0 :::3307 :::* LISTEN 3388/mysqld
多实例登陆
mysql -S /data/3306/mysql.sock mysql -S /data/3307/mysql.sock