1.1 MySQL服务结构

MySQL是一个典型的c/s模式,服务端与客户端两部分组成。

服务端程序 mysqld

客户端程序 mysql自带客户端(mysql、mysqladmin、mysqldump等) 第三方客户端  API接口(php-mysql)

1.2 MySQL连接方式

TCP/IP 连接   网络连接串(通过用户名 密码 IP 端口进行连接)

mysql -uroot -p123 -h 127.0.0.1 -P 3306socket 连接   网络套接字(用户名 密码  socket文件)

mysql -uroot -p123 -S /application/mysql/tmp/mysql.sock 在linux中使用mysql命令不加其他的参数连接方式即

mysql -uroot -poldboy123使用的是套接字文件方式登录的1.3 MySQL在启动过程

启动后台守护进程,并生成工作线程

预分配内存结构供MySQL处理数据使用

实例就是MySQL的后台进程+线程+预分配的内存结构

2.1 连接层

通讯协议为:tcp/ip 或 socket

连接线程 为连接的数量

用户验证 为通过用户名 密码验证进行通讯协议

sql即结构化的查询语句(数据库内部逻辑语言)sql92 sql99

DDL 数据库定义语言

DCL 数据库控制语言

DML 数据库操作语言

DQL 数据查询语言

SQL层的功能: select * from wordpress.user;

1、判断语法、语句、语义

判断语句类型

2、数据库不能直接响应sql语句

必须明确的知道数据在哪个磁盘

3、数据库对象授权情况判断

授权失败不继续

4、解析(解析器)

将sql语句解析成执行计划,运行执行计划,生成找数据的方式

5、优化 (优化器)

运行执行计划5.6之后 基于代价的算法,从执行计划中选择代价最小的交给"执行器"

6、"执行器"    运行执行计划

最终生产如何去磁盘找数据方式

7、将取数据的方式,交由下层(存储引擎层)进行处理8、最终将取出的数据抽象成管理员或用户能看懂的方式(表),展现在用户面前

9、查询缓存: 缓存之前查询的数据。

假如我们查询的表是一个经常有变动的表,查询缓存不要设置太大

SQL层处理流程

2.3 存储引擎层

由上层决定存储方式

存储引擎是充当不同表类型的处理程序的服务器组件。

存储引擎层功能:

存数据、取数据

数据的不同存储方式

不同的管理方式:

事务(增、删、改)

备份恢复

高级功能(高可用的架构、读写分离架构)

2.4 依赖于存储引擎的功能

存储引擎是充当不同表类型的处理程序的服务器组件。

存储引擎用于:

存储数据、检索数据、通过索引查找数据

存储介质、

事务功能、

锁定、备份和恢复、优化

特殊功能:

全文搜索、引用完整性、空间数据处理

双层处理:

上层包括SQL解析器和优化器、下层包含一组存储引擎

SQL 层不依赖于存储引擎:

引擎不影响SQL处理

2.5 MySQL的逻辑构成(数据库内部结构)

逻辑构成是为了用户能够读懂数据出现的,让你更好的理解数据。

管理数据的一种方式。

库中包含表,在Linux中以目录表示

表中有列结构与行记录,在Linux中以多个文件表示

切换库mysql> use mysql;查看表mysql> show tables;查看列的信息(记录)mysql> desc user;

2.6 MySQL的存储方式

程序文件随数据目录一起存储在服务器安装目录下。执行各种客户机程序、管理程序和实用程序时将创建程序可执行文件和日志文件。首要使用磁盘空间的是数据目录。

服务器日志文件和状态文件:

包含有关服务器处理的语句的信息。日志可用于进行故障排除、监视、复制和恢复。

InnoDB 日志文件: (适用于所有数据库)驻留在数据目录级别。

InnoDB 系统表空间:  包含数据字典、撤消日志和缓冲区。

每个数据库在数据目录下均具有单一目录(无论在数据库中创建何种类型的表)。数据库目录存储以下内容:

数据文件:

特定于存储引擎的数据文件。这些文件也可能包含元数据或索引信息,具体取决于所使用的存储引擎。

格式文件 (.frm):

包含每个表和/或视图结构的说明,位于相应的数据库目录中。

触发器:

与某个表关联并在该表发生特定事件时激活的命名数据库对象。

数据目录的位置取决于配置、操作系统、安装包和分发。典型位置是 /var/lib/mysql。

MySQL 在磁盘上存储系统数据库 (MySQL)。

MySQL包含诸如用户、特权、插件、帮助列表、事件、时区实现和存储例程之类的信息。

2.7 MySQL体系结构小结

sql 优化相关的理念

解析器 : 执行计划  数据库执行sql的一种方式

优化器 : 知道基本规则,直接影响将来选择哪个执行计划

查询缓存 : 生产环境中,一般会用redis memcached 来代替

逻辑结构

库    就是一个目录,为了存放多张表

表    在相应的库中,用多个文件来表示

myisam表 3个文件:(.myd数据文件  .myi索引文件 frm表 结构定义文件)

innodb:  2个或者一个,共享表空间(ibdata1 基表 元数据)、独立表空间(5.6以后默认的表存储方式)

如何使用磁盘

1、多个库多个目录,目录下存放了多个表的存储文件

2、auto.cnf  db02.err  db02.pid  ibdata1  ib_logfile0  ib_logfile1等

3.1 连接管理:mysql

[root@db02 ~]# mysql --help-u <user_name> 或 --host=<user_name>-p<password>-h <host_name> 或 --host=<host_name>--protocol=<protocol_name> -P <port_number> 或 --port=<port_number> -S <socket_name> 或 --socket=<socket_name>常用的连接方式:

套接字:  mysql -uroot -p123  -S /application/mysql/tmp/mysql.sockTcp/Ip: mysql -uroot -p123 -h 10.0.0.52 -P 33063.2 数据库的启动流程

mysqld 文件属性[root@db02 ~]# cd /application/mysql/bin/[root@db02 bin]# file mysqldmysqld: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, not strippedmysql.server文件属性[root@db02 bin]# file ../support-files/mysql.server ../support-files/mysql.server: POSIX shell script text executablemysqld_safe文件属性[root@db02 data]# file /application/mysql/bin/mysqld_safe /application/mysql/bin/mysqld_safe: POSIX shell script text executable

可以看出mysql.server文件与mysqld_safe文件都是脚本文件,最后都调用mysqld二进制文件进行启动。

3.3 MySQL的关闭方法

推荐使用的方法:

1、mysqladmin -uroot -p123 shutdown2、servive mysqld stop尽量避免使用kill命令

mysqladmin命令关闭MySQL

[root@db02 bin]# mysqladmin shutdown -uroot -p123Warning: Using a password on the command line interface can be insecure.perror命令

[root@db02 bin]# perror 13OS error code  13:  Permission deniedperror命令是MySQL自带命令,能够查询MySQL错误代码的含义。

3.4 my.cnf 配置文件说明

功能 :

1、影响到服务器进程的启动

2、影响到客户端程序

配置my.cnf

使用不同的"标签"去明确指定影响哪部分功能

服务器端

[server]    [mysqld] -----> 一般设置此项    [mysqld_safe][client]     ----> 为了方便设置此项    [mysql]     [mysqladmin]    [mysqldump]

my.cnf文件配置实例

[root@db02 data]# cat /etc/my.cnf[mysqld]basedir=/application/mysqldatadir=/application/mysql/datasocket=/tmp/mysql.socklog-error=/var/log/mysql.logport=3307[mysql]socket=/tmp/mysql.sockuser=rootpassword=123检查进程信息,可以看到与my.cnf配置的相同

[root@db02 data]# ps -ef |grep [my]sql root       3411   1918  0 15:52 pts/1    00:00:00 /bin/sh /application/mysql/bin/mysqld_safemysql      3548   3411  0 15:52 pts/1    00:00:00 /application/mysql/bin/mysqld --basedir=/application/mysql --datadir=/application/mysql/data --plugin-dir=/application/mysql/lib/plugin --user=mysql --log-error=/var/log/mysql.log --pid-file=/application/mysql/data/db02.pid --socket=/tmp/mysql.sock --port=3307my.cnf文件的配置

3.5 配置文件的读取过程

/etc/my.cnf --> /etc/mysql/my.cnf --> $MYSQL_HOME/my.cnf --> defaults-extra-file=/tmp/oldboy.txt --> ~/.my.cnf注:假设4个配置文件都存在,同时使用--defaults-extra-file指定了参数文件,如果这时有一个 "参数变量"在5个配置文件中都出现了,那么后面的配置文件中的参数变量值会覆盖前面配置文件中的参数变量值,就是说会使用 ~/.my.cnf 中设置的值。

如果使用./bin/mysqld_safe 守护进程启动MySQL数据库时,使用了 --defaults-file=<配置文件的绝对路径>参数,这时只会使用这个参数指定的配置文件。

4.1 什么是MySQL多实例?

简单地说,MySQL多实例就是在一台服务器上同时开启多个不同的服务端口(如:3306/3307)同时运行多个MySQL服务进程,这些服务进程通过不同的socket监听不同的服务端口来提供服务。

这些MySQL多实例共用一套MySQL安装程序,使用不同的my.cnf(也可以相同)配置文件、启动程序(也可以相同)和数据文件。在提供服务时,多实例MySQL在逻辑上看来是各自独立的,他们根据配置文件对应设定值,获得服务器响应数量的资源。

4.2 MySQL多实例的作用与问题

有效利用服务器资源

当单个服务器资源有剩余时,可以充分利用剩余的资源提供更多的服务,且可以实现资源的逻辑隔离

节约服务器资源

当公司资金紧张,但是数据库又需要各自尽量独立地提供服务,而且,需要主从复制等技术时,多实例就再好不过了。

MySQL多实例有它的好处,但也有弊端,比如,会存在资源互相抢占的问题。当某个数据库实例并发很高或者有SQL慢查询时,整个实例会消耗大量的系统CPU、磁盘I/O等资源,导致服务器上的其他数据库实例提供服务的质量一起下降。

4.3 多实例配置思路

1 多套配置文件

2 多套 数据

3 多个socket

4 多个端口

5 多个日志文件

6 多个启动程序

4.4 多实例配置过程

第一个里程碑:多实例配置文件准备

[root@db02 /]# tree /data//data/├── 3306│   ├── my.cnf│   └── mysql└── 3307    ├── my.cnf    └── mysql3306端口my.cnf配置文件

[root@db02 /]# cat data/3306/my.cnf [client]port            = 3306socket          = /data/3306/mysql.sock[mysqld]user    = mysqlport    = 3306socket  = /data/3306/mysql.sockbasedir = /application/mysqldatadir = /data/3306/datalog-bin = /data/3306/mysql-binserver-id = 6[mysqld_safe]log-error=/data/3306/mysql_3306.errpid-file=/data/3306/mysqld.pid3307端口my.cnf配置文件

[root@db02 /]# cat  /data/3307/my.cnf [client]port            = 3307socket          = /data/3307/mysql.sock[mysqld]user    = mysqlport    = 3307socket  = /data/3307/mysql.sockbasedir = /application/mysqldatadir = /data/3307/datalog-bin = /data/3307/mysql-binserver-id = 7[mysqld_safe]log-error=/data/3307/mysql_3307.errpid-file=/data/3307/mysqld.pid编写管理脚本3306

1 [root@db02 /]# cat data/3306/mysql  2 #!/bin/sh 3 #3306 start scripts 4 #init 5 port=3306 6 mysql_user="root" 7 CmdPath="/application/mysql/bin" 8 mysql_sock="/data/${port}/mysql.sock" 9 mysqld_pid_file_path=/data/3306/3306.pid10 start(){11     if [ ! -e "$mysql_sock" ];then12          printf "Starting MySQL...\n"13         /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf --pid-file=$mysqld_pid_file_path 2>&1 > /dev/null &14         sleep 315     else16         printf "MySQL is running...\n"17         exit 118     fi19 }20 stop(){21     if [ ! -e "$mysql_sock" ];then22         printf "MySQL is stopped...\n"23         exit 124     else25         printf "Stoping MySQL...\n"26         mysqld_pid=`cat "$mysqld_pid_file_path"`27          if (kill -0 $mysqld_pid 2>/dev/null)28            then29              kill $mysqld_pid30              sleep 231          fi32     fi33 }34 35 restart(){36     printf "Restarting MySQL...\n"37     stop38     sleep 239     start40 }41 42 case "$1" in43     start)44         start45     ;;46     stop)47         stop48     ;;49     restart)50         restart51     ;;52     *)53         printf "Usage: /data/${port}/mysql {start|stop|restart}\n"54 esac编写管理脚本3307

1 [root@db02 /]# cat data/3307/mysql  2 #!/bin/sh 3 #3307 start scripts 4 #init 5 port=3307 6 mysql_user="root" 7 CmdPath="/application/mysql/bin" 8 mysql_sock="/data/${port}/mysql.sock" 9 mysqld_pid_file_path=/data/3307/3307.pid10 start(){11     if [ ! -e "$mysql_sock" ];then12          printf "Starting MySQL...\n"13         /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf --pid-file=$mysqld_pid_file_path 2>&1 > /dev/null &14         sleep 315     else16         printf "MySQL is running...\n"17         exit 118     fi19 }20 stop(){21     if [ ! -e "$mysql_sock" ];then22         printf "MySQL is stopped...\n"23         exit 124     else25         printf "Stoping MySQL...\n"26         mysqld_pid=`cat "$mysqld_pid_file_path"`27          if (kill -0 $mysqld_pid 2>/dev/null)28            then29              kill $mysqld_pid30              sleep 231          fi32     fi33 }34 35 restart(){36     printf "Restarting MySQL...\n"37     stop38     sleep 239     start40 }41 42 case "$1" in43     start)44         start45     ;;46     stop)47         stop48     ;;49     restart)50         restart51     ;;52     *)53         printf "Usage: /data/${port}/mysql {start|stop|restart}\n"54 esac注意脚本要给继续权限才能够正常使用。

第二个里程碑:创建数据目录并授权

[root@db02 /]# mkdir /data/{3306,3307}/data -p[root@db02 /]# 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.40特殊性:需要创建错误日志文件

touch /data/3306/mysql_3306.errtouch /data/3307/mysql_3307.err第五个里程碑:启动多实例

[root@db02 scripts]# /data/3306/mysql startStarting MySQL...[root@db02 scripts]# /data/3307/mysql startStarting MySQL...检查MySQL状态

[root@db02 scripts]# ps -ef |grep [my]sql root       4341      1  0 16:46 pts/1    00:00:00 /bin/sh /application/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf --pid-file=/data/3306/3306.pidmysql      4526   4341 13 16:46 pts/1    00:00:03 /application/mysql/bin/mysqld --defaults-file=/data/3306/my.cnf --basedir=/application/mysql --datadir=/data/3306/data --plugin-dir=/application/mysql/lib/plugin --user=mysql --log-error=/data/3306/mysql_3306.err --pid-file=/data/3306/3306.pid --socket=/data/3306/mysql.sock --port=3306root       4549      1  0 16:46 pts/1    00:00:00 /bin/sh /application/mysql/bin/mysqld_safe --defaults-file=/data/3307/my.cnf --pid-file=/data/3307/3307.pidmysql      4734   4549 51 16:46 pts/1    00:00:08 /application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf --basedir=/application/mysql --datadir=/data/3307/data --plugin-dir=/application/mysql/lib/plugin --user=mysql --log-error=/data/3307/mysql_3307.err --pid-file=/data/3307/3307.pid --socket=/data/3307/mysql.sock --port=3307检查端口信息

[root@db02 scripts]# netstat  -lntup |grep mysql tcp        0      0 :::3306                     :::*                        LISTEN      4526/mysqld         tcp        0      0 :::3307                     :::*                        LISTEN      4734/mysqld至此MySQL的多实例就配置完成。

4.5 多实例MySQL的使用

本地连接方式

mysql -S /data/3306/mysql.sockmysql -S /data/3307/mysql.sock4.6 关于MySQL多实例的选择

1、资金紧张性公司的选择

资金紧张,公司业务访问量又不大,但又希望不同的业务的数据库服务各自尽量独立

2、并发访问不是特别大的业务

当公司业务访问量不太大的时候,服务器的资源基本都是浪费的,这就适合多实例的使用

3、门户网站应用MySQL多实例场景

配置硬件好的服务器,可以节省IDC机柜空间,跑多实例也不会减少硬件资源不慢的浪费

一般是从库多实例,例如:某部门使用IBM服务器为48核CPU,内存96GB、一台服务器跑3~4个实例:此外,sina网也是用的多实例,内存48GB左右。企业环境中一般将多实例应用在测试环境。

第一步:将服务停掉

/etc/init.d/mysqld stop

第二步:加参数启动服务

cd /application/mysql/bin/ mysqld_safe --skip-grant-table --user=mysql --skip-networking &

这种模式下

无密码登陆    网络用户无法登陆    只能本地登陆    和授权有关的命令都无法执行

第三步:修改密码

[root@db02 3306]# mysqlmysql> update mysql.user set password=password('123') where user='root' and host='localhost';mysql> flush privileges;MySQL5.7 修改密码修改字段  authentication_string

第四步:退出重启服务

/etc/init.d/mysqld restart第五步:登录验证

mysql -uroot -p123至此密码修改成功。

本文内容来自于 老男孩Linux云计算运维优秀学员课后笔记整理

老男孩教育作为率先开展Linux和Python培训的领跑者,Linux云计算&&安全运维工程师2019重磅升级,持续领跑高端IT教育:课程更新7大亮点:1、增加Python自动化CMDB平台实战项目(约4-10天);2、增加大量网络安全课程(约10-20天);3、增加阿里云企业级云服务实战环境项目实践(约2-4天);4、增加个人成长、职场高薪、成为管理者职场思想课(约10-20天);5、增加Docker、K8S企业级实战实战项目(约5-10天);6、增加ELK日志收集项目实战(约2天);7、全程提供教学教材、习题、考试,学生人手多本;将Linux运维工程师、网络安全工程师、云计算工程师、运维开发、高级架构师、数据库管理员全运维类技术岗位一网打尽。

老男孩IT教育【Linux云计算运维】课程,开设有全日制脱产班、周末班和网络在线班,以帮助更多有需要的盆友们提升技能,若是想要了解具体课程内容的话,那就点击本文左下方“阅读原文”来咨询吧~

看完本文有收获?那就分享给更多朋友吧~

万水千山总是情,点个【好看】行不行?