MySQL的基本介绍和多实例

连接方式

MySQL是基于C/S架构的程序,通常客户端连接MySQL有两种连接方式:

  • TCP/IP
  • socket(UNIX Domain Socket)
  1. TCP/IP形式

    通过IP地址的形式连接访问数据库,下面的为通过TCP/IP连接的形式:

    #当同时指定了-h和-S参数,则TCP/IP形式优先
    mysql -uoot -p[password] -h[ip]
    
  2. socket形式

    #默认情况下,通过socket形式连接数据库
    #通过UNIX Domain Socket实现进程间通信
    #因为没有3次握手,故效率比TCP/IP套接字要高
    #但受限于只能在本机的进程间通信
    
    mysql -uroot -p[password] -hlocalhost		#也为socket
    mysql -uroot -p[password] 
    

结构

在了解MySQL具体的结构之前,先了解以下两个概念:

实例
  • 一个进程和多个线程以及预分配的内存空间即为一个实例
多实例
  • 多个进程和其对应的多个线程以及预分配的内存空间即为多实例

程序结构

Mysql三层结构

连接层

  • 用户身份认证

  • 提供两种进程间通信的方式

    • TCP/IP
    • SOCKET
  • 负责处理用户与SQL层的交互

    注:socketTCP/IP协议快,因为没有三次握手

SQL层

  • 处理连接层的SQL语句
  • 语法检查
  • 语义验证
    • DML
    • DDL
    • DCL
    • DQL
  • 解析器
    • 解析处理用户的SQL语句,生成执行计划
  • 优化器
    • 接受解析器生成的执行计划,选取最优的执行计划(处理时间最快、最优的)
  • 执行器
    • 执行优化器选择出的最优计划
      • 创建与存储引擎层交互的线程
      • 将执行语句交给存储引擎层,取数据,接收存储引擎层结构化成为表的数据
  • 若前端部署了Redis或其他缓存数据库,则进行写缓存
  • 记录日志,如果开了的话(binlog)

存储引擎层

  • 处理来自SQL层的语句
  • 与磁盘交互,取数据时将数据结构化成表的形式返回给SQL
  • 建立与SQL交互的线程(通常这一步在执行器执行时已经建立)

附:SQL层流程图

SQL层

物理结构

MySQL中,一个数据库对应我们操作系统的一个对应的目录,库中的对应该目录内的文件,我们的真实数据就按照一定的规则存储在这些文件中。不同的存储引擎创建的表文件存储形式不同,生成的真实数据文件数量也不同。

逻辑结构

  • 在库中含有很多

  • 在表中记录有元数据真实数据行

元数据

  • 列和属性,主要是行数、占用空间大小、权限信息

  • 名称、数据类型及大小、非空、唯一、主键、非负数、自增长、默认值

其他

  • 一个段由多个区构成,段即

  • 一个区由多个页构成

  • 页为数据库中最小的单位:$1Page=16KB$

分区表

  • 由一个区构成的表即为分区表

扩展:部署多实例

在部署多实例之前,我们需要对目录进行规划,创建多套配置文件机及数据存储目录并进行初始化,在部署多实例时需要指定不同的TCP/IP监听端口,否则实例将无法启动;下面是目录基础结构和项目要求。

基本要求:

通过3307,3308,3309端口访问到不同的实例,这些实例都有自己独立的配置文件和数据目录

#目录结构,为了方便区分,均以端口号命名
/mydata/
├── 3307
│   └── my.cnf
├── 3308
│   └── my.cnf
└── 3309
    └── my.cnf

配置文件定义

对于3307实例

[mysqld]											#根据个人习惯可做调整
basedir=/usr/local/mysql							#可执行文件目录		
datadir=/mydata/3307/data							#指定实例数据存储目录
port=3307											#指定该实例使用的端口
socket=/mydata/3307/mysql.sock						#指定该实例套接字存放的位置
log-error=/mydata/3307/data/mysql.err				#指定该实例的错误日志位置
log-bin=/mydata/3307/data/mysql.bin					#指定binlog存放位置
server_id=7											#指定server_id以区分不同实例

将该文件放到对应实例的目录下,不同的实例其对应的内部配置也不同,需要稍作调整

对于3308实例

[mysqld]											#根据个人习惯可做调整
basedir=/usr/local/mysql							#可执行文件目录		
datadir=/mydata/3308/data							#指定实例数据存储目录
port=3308											#指定该实例使用的端口
socket=/mydata/3308/mysql.sock						#指定该实例套接字存放的位置
log-error=/mydata/3308/data/mysql.err				#指定该实例的错误日志位置
log-bin=/mydata/3308/data/mysql.bin					#指定binlog存放位置
server_id=8											#指定server_id以区分不同实例

对于3309实例

[mysqld]											#根据个人习惯可做调整
basedir=/usr/local/mysql							#可执行文件目录		
datadir=/mydata/3309/data							#指定实例数据存储目录
port=3309											#指定该实例使用的端口
socket=/mydata/3309/mysql.sock						#指定该实例套接字存放的位置
log-error=/mydata/3309/data/mysql.err				#指定该实例的错误日志位置
log-bin=/mydata/3309/data/mysql.bin					#指定binlog存放位置
server_id=9											#指定server_id以区分不同实例

初始化各个实例

由于是新的实例,默认没有任何基础表和库;所以我们需要初始化这些库,初始化依然是执行mysql_install_db这个脚本,如下代码:

#依次初始化这三个目录
#/usr/local/mysql这个目录并不固定,可根据个人具体情况做调整

#初始化3307实例
/usr/local/mysql/scripts/mysql_install_db --defaults-file=/mydata/3307/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/mydata/3307/data

#初始化3308实例
/usr/local/mysql/scripts/mysql_install_db --defaults-file=/mydata/3308/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/mydata/3308/data

#初始化3309实例
/usr/local/mysql/scripts/mysql_install_db --defaults-file=/mydata/3309/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/mydata/3309/data

启动多实例

启动多实例使用mysqld_safe命令,执行时需指定默认的配置文件,由于是由人工启动的,启动后该终端会进入阻塞状态,无法退出,必须加&符号将该进程调入后台,如下:

#依次启动3个实例
mysqld_safe --defaults-file=/mydata/3307/my.cnf &
mysqld_safe --defaults-file=/mydata/3308/my.cnf &
mysqld_safe --defaults-file=/mydata/3309/my.cnf &

注: 因为是手动启动的,未经过systemd,运行时间久了服务可能会出问题

设置密码

#依次设置3个实例的root账户密码
#通过套接字连接的数据库
mysqladmin -S /mydata/3307/mysql.sock password '123'
mysqladmin -S /mydata/3308/mysql.sock password '123'
mysqladmin -S /mydata/3309/mysql.sock password '123'

登陆不同的实例

#通过不同的套接字连接,也可通过不同的端口进行连接
mysql -uroot -S /mydata/3307/mysql.sock -p
mysql -uroot -S /mydata/3308/mysql.sock -p
mysql -uroot -S /mydata/3309/mysql.sock -p

区分不同实例

登陆后可以使用如下SQL语句以区分不同的实例:

show variables like 'server_id';

不进入数据库执行:

mysql -uroot -p123 -S /mydata/3307/mysql.sock -e "show variables like 'server_id';"

扩展:恢复数据库用户授权表

mysql.user表被清空导致无法登陆

在此情况下需进行停服务操作,注意数据安全。

  1. 停止mysqld服务

    systemctl stop mysqld
    #或
    /etc/init.d/mysqld stop
    
  2. 手动启动mysql服务

    #需要跳过权限检查和禁止TCP/IP登陆
    #跳过检查为了接下来恢复mysql.user表做准备
    #禁止TCP/IP登陆防止外部的非法访问,因为跳过的权限检查导致的无密码情况
    mysqld_safe --skip-grant-tables --skip-networking &
    
  3. 登陆数据库

  4. 刷新授权表

    flush privileges;
    
  5. 添加用户并授权

    grant all on *.* to 'root'@'localhost' identified by '123';
    #默认没有grant_priv权限,无法创建用户,执行下面的更新权限语句
    update mysql.user set Grant_priv="Y"  where user="root" and host="localhost";
    
  6. 重启服务即可。

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×