MySQL（1）-CSDN博客

本文链接：https://blog.csdn.net/dreamnn/article/details/135829489

一、数据库管理系统种类

RDBMS

以多张二维表的方式来存储，又给多张表建立了一定的关系（关系型数据库）

NoSQL

nosql很多以json格式进行存储数据的（mongodb）

RDBMS与NoSQL对比

功能对比

	关系型数据库	非关系型数据库
强大的查询功能	√	×
强一致性	√	×
二级索引	√	×
灵活模式	×	√
扩展性	×	√
性能	×	√

特点对比

关系型数据库（RDBMS）的特点：
- 二维表
- 典型产品Oracle传统企业，MySQL互联网企业
- 数据存取是通过SQL（Structured Query Language结构化查询语言）
- 最大特点数据安全性方面强（ACID）
非关系型数据库（NoSQL：Not only SQL）的特点：
- 不是否定关系型数据库，而是做关系型数据库的补充

数据库市场

MySQL的市场应用

中、大型互联网公司
市场空间：互联网领域第一
趋势明显
同源产品：MariaDB、PerconaDB

类似产品

微软：SQLserver
- 微软和sysbase合作开发的产品，后来自己开发，windows平台
- 三、四线小公司，传统行业在用
IBM：DB2
- 市场占有量小
- 目前只有：国有银行（人行，中国银行，工商银行等）、中国移动应用
PostgreSQL
MongoDB
Redis

MySQL发展史

1979年，报表工具Unireg出现。
1985年，以瑞典David Axmark为首，成立了一家公司（AB前身），ISAM引擎出现。
1990年，提供SQL支持。
1999年-2000年，MySQL AB公司成立，并公布源码，开源化。
2000年4月BDB引擎出现，支持事务。
2008年1月16日 MySQL被Sun公司收购。
2009年4月20日Oracle收购Sun公司，MySQL转入Oracle门下

二、MySQL安装

MySQL安装方式

rpm、yum安装
- 安装方便、安装速度快、无法定制
二进制
- 不需要安装，解压即可使用，不能定制功能
编译安装
- 可定制，安装慢
- 四个步骤
  - 解压（tar）
  - 生成（./configure）cmake
  - 编译（make）
  - 安装（make install）

源码安装

一般初学者不建议源码安装，除非需要定制化，否则使用二进制安装

MySQL版本选择:
- https://downloads.mysql.com/archives/community/
- 5.6：GA（稳定版本）6-12个月小版本是偶数版是稳定版，奇数版是开发版
- 5.7：选择5.17版本以上，支持MGR（MySQL自带的高可用）
下载源码，并且配置编译环境

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.40.tar.gz
tar xzvf mysql-5.6.40.tar.gz
cd mysql-5.6.40
yum install -y ncurses-devel libaio-devel cmake gcc gcc-c++ glibc

创建mysql用户

useradd mysql -s /sbin/nologin -M

编译并安装

mkdir /application
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.40 \
-DMYSQL_DATADIR=/application/mysql-5.6.40/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.6.40/tmp/mysql.socket \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \ 
-DWITH_EXTRA_CHARSETS=all \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITH_ZLIB=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLE_DOWNLOADS=1 \
-DWITH_DEBUG=0
echo $?	# 检查上一条代码是否执行成功，为0表示执行成功
make	# 编译 -j，指定核数
make install

创建配置文件

ln -s /application/mysql-5.6.40/ /application/mysql	# 如果版本发生改变，直接重新创建一个软连接，不用修改原来目录的名字
cd /application/mysql/support-files/
cp my-default.cnf /etc/my.cnf	# 后者是linux底下自带的mysql文件
cp:是否覆盖"/etc/my.cnf"? y

创建启动脚本

cp mysql.server /etc/init.d/mysqld

初始化数据库

cd /application/mysql/scripts
yum -y install autoconf
./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data/

启动数据库

mkdir /application/mysql/tmp
chown -R mysql:mysql /application/mysql*
/etc/init.d/mysqld start

配置环境变量

vim /etc/profile.d/mysql.sh
export PATH="/application/mysql/bin:$PATH"

source /etc/profile

systemd 管理mysql

vim /usr/lib/systemd/system.mysqld.service
[Unit]
Descruption=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=Mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000

设置mysql开机自启动，并且开始mysql服务

systemctl daemon-reload
systemctl start mysqld
systemctl enable mysqld

设置mysql密码，并且登录测试

mysqladin -uroot password '123456'
mysql -uroot -p123456
mysql> show databases;
mysql> \q
Bye

二进制安装

当前运维和开发最常见的做法是二进制安装mysql

下载二进制包并且解压

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz
tar xzvf mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz

然后的步骤就和编译安装一样

useradd mysql -s /sbin/nologin -M
mkdir /application
mv mysql-5.6.40-linux-glibc2.12-x86_64 /application/mysql-5.6.40
ln -s /application/mysql-5.6.40/ /application/mysql	# 如果版本发生改变，直接重新创建一个软连接，不用修改原来目录的名字


cp my-default.cnf /etc/my.cnf	# 后者是linux底下自带的mysql文件
cp:是否覆盖"/etc/my.cnf"? y
cp mysql.server /etc/init.d/mysqld
cd /application/mysql/scripts
yum -y install autoconf
./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data/
vim /etc/profile.d/mysql.sh
export PATH="/application/mysql/bin:$PATH"

source /etc/profile

需要注意，官方编译的二进制包默认是在/usr/local目录下的，我们需要修改配置文件

sed -i 's#/usr/local#/application#g' /etc/init.d/mysqld /application/mysqld/bin/mysqld_safe

创建systemd管理文件，并且测试是否正常使用

vim /usr/lib/systemd/system.mysqld.service
[Unit]
Descruption=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000

vim /etc/my.cnf
basedir = /application/mysql
datadir = /application/mysql/data

systemctl daemon-reload
systemctl start mysqld
systemctl enable mysqld
mysqladmin -uroot password '123456'
mysql -uroot -p123456

三、MySQL体系结构管理

客户端与服务器模型

在这里插入图片描述

mysql是一个典型的C/S服务结构
- mysql自带的客户端程序（/application/mysql/bin）
- mysql
- mysqladmin
- mysqldump
mysqld一个二进制程序，后台的守护进程
- 单线程
- 多线程
应用连接MySQL方式
- TCP/IP的连接方式
  - mysql -uroot -p123456 -h127.0.0.1

mysql -uroot -p123456
mysql>
mysql -uroot -p123456 -h127.0.0.1
mysql>status
mysql -uroot -p123456 -hlocalhost
mysql>status
* 套接字连接方式
	* mysql -uroot -p123456 -S /tmp/mysql.sock

MySQL服务器构成

mysqld服务结构

实例=mysqld后台守护进程+Master Thread + 干活的Thread+预分配的内存
- 公司=老板+经理+员工+办公室
连接层
- 验证用户的合法性（ip，端口，用户名）
- 提供两种连接方式（socket，TCP/IP）
- 验证操作权限
- 提供一个与SQL层交互的专用线程
SQL层
- 接受连接层传来的SQL语句
- 检查语法
- 检查语义（DDL，DML，DQL，DCL）
- 解析器，解析SQL语句，生成多种执行计划
- 优化器，根据多种执行计划，选择最优方式
- 执行器，执行优化器传来的最优方式SQL
  - 提供与存储引擎交互的线程
  - 接收返回数据，优化成表的形式返回SQL
- 将数据存入缓存
- 将数据存入缓存
- 记录日志，binlog
存储引擎
- 接收上层的执行结构
- 取出磁盘文件和相应数据
- 返回给SQL层，结构化之后生成表格，由专用线程返回给客户端

mysql逻辑结构

MySQL的逻辑对象：做为管理人员或者开发人员操作的对象

库
表：列+其他属性（行数+占用空间大小+权限）
列：列名字+数据类型+其他约束（非空、唯一、主键、非负数、自增长、默认值）

最直观的数据：二维表，必须用库来存放

mysql逻辑结构与Linux系统对比

MySQL	Linux
库	目录
show databases;	ls -l /
use mysql;	cd /mysql
表	文件
show tables;	ls
二维表=元数据+真实数据行	文件=文件名+文件属性

mysql的物理结构

MySQL的最底层的物理结构是数据文件，也就是说，存储引擎层，打交道的文件，是数据文件。
存储引擎分为很多种类（Linux中的FS）
不同的存储引擎的区别：存储方式、安全性、性能

myisam：

mysql自带的表部分是使用的myisam

mysql> show create table mysql.user\G;
...
一个表由三个文件组成

innodb:

自己创建一个表，在编译的时候已经默认指定使用innodb

mysql> show create table test\G;
...
一个表由两个文件组成

段、区、页（块）

段：理论上一个表就是一个段，由多个区构成，（分区表是一个分区的一个段）
区：连续的多个页构成
页：最小的数据存储单一，默认是16k

四、Mysql用户权限管理

Mysql用户基础操作

Linux用户的作用
- 登录系统
- 管理系统文件
Linux用户管理
- 创建用户：useradd adduser
- 删除用户：userdel
- 修改用户：usermod
Mysql用户的作用
- 登录Mysql数据库
- 管理数据库对象
Mysql用户管理
- 创建用户：create user
- 删除用户：delete user drop user
- 修改用户：update
用户的定义
- username@‘主机域’
- 主机域：可以理解为是Mysql登录的白名单
- 主机域格式：
  - 10.1.1.12
  - 10.1.0.1%
  - 10.1.0.%
  - 10.1.%.%
  - %
  - localhost
  - 192.168.1.1/255.255.255.0
刚装完mysql数据库该做的事情
- 设定初始密码

mysqladmin -uroot password "123456"
* 忘记root密码
/etc/init.d/mysqld stop
mysqld_safe --skip-grant-tables --skip-networking
# 修改root密码
update mysql.user set password=PASSWORD('123456') where user='root' and host='localhost';
flush privileges;

用户管理

创建用户

mysql> create user user01@'192.168.64.%' identified by '123456';

查看用户

mysql> select user,host from mysql.user;

删除用户

mysql> drop user user01@'192.168.64.%';

修改密码

mysql> set password=PASSWORD('123456');
mysql> update mysql.user set password=PASSWORD('USER01') where user='root' and host='localhost';
mysql> grant all privileges on *.* to user01@'192.168.64.%' identified by '123456';

用户权限介绍

权限

INSERT,SELECT,UPDATE,DELETE,CREATE,DROP,RELOAD,SHUTDOWN,PROCESS,FILE,REFERENCES,INDEX,ALTER,SHOW DATABASE, SUPER, CREATE TEMPORARY TABLES,LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW,SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE

每次设定只能有一个属主，没有属组或其他用户的概念

grant	all privileges 	on 	*.* to user01@'192.168.64.%' identified by '123456';
		权限				 作用对象	归属									密码
后面加上with grant options，表示可以当前用户可以给别的用户授权

作用对象分解：

.[当前MySQL实例中所有库下的所有表]
wordpress.* [当前MySQL实例中wordpress库中所有表（单库级别）]
wordpress.user[当前MySQL实例中wordpress库中的user表（单表级别）]

企业中权限的设定

开发人员说：给我开一个用户
沟通
- 你需要对哪些库、表进行操作
- 你从哪里连接过来
- 用户名有没有要求
- 密码要求
- 发邮件
一般给开发创建用户权限

grant select,update,delete,insert on *.* to user01@'192.168.64.%' identified by '123456';

实验思考问题

# 创建wordpress数据库
create database wordpress;
# 使用wordpress库
use wordpress;
# 创建t1、t2表
create table t1 (id int);
create table t2 (id int);
# 创建blog库
create database blog;
# 使用blog库
use blog;
# 创建tb1表
create table tb1 (id int);

授权

1、grant select on *.* to wordpress@'10.0.0.5%' identified by '123';
2、grant insert,delete,update on wordpress.* to wordpress@'10.0.0.5%' identified by '123';
3、grant all on wordpress.t1 to wordpress@'10.0.0.5%' identified by '123';

一个客户端程序使用wordpress用户登录到10.0.0.51的MySQL后，
- 对t1表的管理能力？
- 对t2表的管理能力？
- 对tb1表的管理能力？
解
- 同时满足1，2，3，最终权限是1+2+3
- 同时满足了1，2两个授权，最终权限是1+2
- 只满足1授权，所以只能select
结论
- 如果在不同级别都包含某个表的管理能力时，权限是相加关系
- 但是我们不推荐在多级别定义重复权限。
- 最常用的权限设定方式是单库级别授权，即：wordpress.*

五、Mysql连接管理

MySQL自带的连接工具
- mysql
  - -u：指定用户
  - -p：指定密码
  - -h：指定主机
  - -P：指定端口
  - -S：指定socket
  - -e：指定SQL
第三方连接工具
- sqlyog
- navicat

六、MySQL启动关闭流程

启动

/etc/init.d/mysqld start -------> mysqld_safe -------> mysqld

关闭

/etc/init.d/mysqld stop
mysqladmin -uroot -p123456 shutdown
kill -9 pid ?
killall mysqld ?
pkill mysqld ?

出现问题：

1、如果在业务繁忙的情况下，数据库不会释放pid和sock文件

2、号称可以达到和oracal一样的安全性，但是并不能100%达到

3、在业务繁忙的情况下，丢数据（补救措施，高可用）

七、MySQL实例初始化配置

在启动一个实例的时候，必须要知道如下的问题
- 我不知道我的程序在哪？
- 我也不知道我将来启动后去哪找数据库？
- 将来我启动的时候启动信息和错误信息放在哪？
- 我启动的时候sock文件pid放在哪？
- 我启动，你们给了我多少内存？
- ……若干问题
预编译：cmake去指定，编码到程序当中去
在命令行设定启动初始化配置

--skip-grant-tables
--skip-networking
--datadir=/application/mysql/data
--basedir=/application/mysql
--defaults-file=/etc/my.cnf
--pid-file=/application/mysql/data/db01.pid
--socket=/application/mysql/data/mysql.sock
--user=mysql
--port=3306
--log-error=/application/mysql/data/db01.err

重写配置文件（/etc/my.cnf）
- 配置文件读取顺序：
  - /etc/my.cnf
  - /etc/mysql/my.cnf
  - $MYSQL_HOME/my.cnf（前提是在环境变量中定义了MYSQL_HOME变量）
  - defaults-extra-file（类似include）
  - ~/.my.cnf
  - 下面的会覆盖上面的配置文件
–defaults-file：默认配置文件
- 如果使用./bin/mysqld_safe守护进程启动mysql数据库时，使用了–defaults-file=<配置文件的绝对路径>参数，这时只会使用这个参数指定的配置文件。

# cmake:
socket=/application/mysql/tmp/mysql.sock
#命令行
--socket=/tmp/mysql.sock
#配置文件：
/etc/my.cnf中[mysqld]标签下：socket=/opt/mysql.sock
#default参数：
mysqld_safe --defaults-file=/tmp/a.txt &配置文件中[mysqld]标签下：socket=/tmp/test.sock
mysqld_safe --defaults-file=666.txt --server_id=888 &

优先级结论
- 命令行
- defaults-file
- 配置文件
- 预编译
初始化配置文件功能
- 影响实例的启动（mysqld）
- 影响到客户端
配置标签分类
- [client]所有客户端程序
- [server]所有服务器程序

vim /etc/my.cnf
[client]
port=3306
user=root
password=123456
host=192.168.64.129

mysql

八、MySQL多实例配置

一个服务器上会存在多个实例

多实例

感觉类似服务器的虚拟主机

多套后台进程+线程+内存结构
多个配置文件
- 多端口
- 多socket文件
- 多个日志文件
- 多个server_id
多套数据

实战配置

# 创建数据目录
mkdir -p /data/330{7..9}
# 创建配置文件
touch /data/330{7..9}/my.cnf
touch /data/330{7..9}/mysql.log
# 编辑3307配置文件
vim /data/3307/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
log-bin=/data/3307/mysql-bin
server_id=7
port=3307
[client]
socket=/data/3307/mysql.sock
# 编辑3308配置文件
vim /data/3308/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
log-bin=/data/3308/mysql-bin
server_id=8
port=3308
[client]
socket=/data/3308/mysql.sock
# 编辑3309配置文件
vim /data/3309/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
log-bin=/data/3309/mysql-bin
server_id=9
port=3309
[client]
socket=/data/3309/mysql.sock
# 初始化3307数据
/application/mysql/scripts/mysql_install_db \
--user=mysql \
--defaults-file=/data/3307/my.cnf \
--basedir=/application/mysql --datadir=/data/3307/data
# 初始化3308数据
/application/mysql/scripts/mysql_install_db \
--user=mysql \
--defaults-file=/data/3308/my.cnf \
--basedir=/application/mysql --datadir=/data/3308/data
# 初始化3309数据
/application/mysql/scripts/mysql_install_db \
--user=mysql \
--defaults-file=/data/3309/my.cnf \
--basedir=/application/mysql --datadir=/data/3309/data
# 修改目录权限
chown -R mysql.mysql /data/330*
# 启动多实例
mysqld_safe --defaults-file=/data/3307/my.cnf &
mysqld_safe --defaults-file=/data/3308/my.cnf &
mysqld_safe --defaults-file=/data/3309/my.cnf &
没有开启成功，查看报错日志，重新初始化一下
# 查看server_id
mysql -S /data/3307/mysql.sock -e "show variables like'server_id'"
mysql -S /data/3308/mysql.sock -e "show variables like'server_id'"
mysql -S /data/3309/mysql.sock -e "show variables like'server_id'"
# 进入单独的mysql实例
mysql -S /data/3307/mysql.sock -uroot
# 关闭实例
mysqladmin -S /data/3307/mysql.sock -uroot shutdown
mysqladmin -S /data/3308/mysql.sock -uroot shutdown
mysqladmin -S /data/3309/mysql.sock -uroot shutdown

九、客户端工具

mysql

作用
- 连接
- 管理
自带的命令

\h	或 help	或?		查看帮助
\G					 格式化查看数据（key：value）
\T 或 tee			 记录日志 \T /tmp/temp.log
\c（5.7可用ctrl+c）	   结束命令
\s 或 status			 查看状态信息
\. 或 source			 导入SQL数据
\u 或 use			 使用数据库
\q 或 exit 或 quit	退出
\! 或 system			 执行shell命令 \! ip a

接收用户的SQL语句

mysqladmin

"强制回应（Ping）"服务器
关闭服务器
创建和删除数据库
显示服务器和版本信息
显示或重置服务器状态变量
设置口令
重新刷新授权表
刷新日志文件和高速缓存
启动和停止复制

[root@localhost ~]# mysqladmin -uroot -p1 create hellodb
[root@localhost ~]# mysqladmin -uroot -p1 drop hellodb
[root@localhost ~]# mysqladmin -uroot -p1 ping 检查服务端的状态
[root@localhost ~]# mysqladmin -uroot -p1 status 服务器运行状态
[root@localhost ~]# mysqladmin -uroot -p1 status --sleep 2 --count 10 每两秒钟显示
一次服务器实时状态一共显示10次
uptime：是mysql正常运行的时间
Threads：指开启的会话数
Questions：服务器启动以来客户的问题（查询）数目	（应该是只要跟mysql作交互：不管你查询表，还是查询服务器状态都记问一次）。
Slow queries：按字面意思是慢查询的意思，不知道mysql认为多久才足够算为长查询，这个先放着
Opens：服务器已经打开的数据库的数量
Flush tables：服务器已经执行的flush...、refresh和reload命令的数量。
open tables：通过命令使用的数据库的表的数量，以服务器启动开始。
Queries per second avg：select语句平均查询时间
[root@localhost ~]# mysqladmin -uroot -p1 extended-status	显示状态变量
[root@localhost ~]# mysqladmin -uroot -p1 variables 显示服务器变量
[root@localhost ~]# mysqladmin -uroot -p1 flush-privileges 数据库重读授权表，等同于reload
[root@localhost ~]# mysqladmin -uroot -p1 flush-tables 关闭所有已经打开的表
[root@localhost ~]# mysqladmin -uroot -p1 flush-threads 重置线程池缓存
[root@localhost ~]# mysqladmin -uroot -p1 flush-status 重置大多数服务器状态变量
[root@localhost ~]# mysqladmin -uroot -p1 flush-logs 日志滚动。主要是实现二进制和中继日志滚动
[root@localhost ~]# mysqladmin -uroot -p1 flush-hosts 清除主机内部信息
[root@localhost ~]# mysqladmin -uroot -p1 kill 杀死线程
[root@localhost ~]# mysqladmin -uroot -p1 refresh 相当于同时执行flush-hosts flush-logs
[root@localhost ~]# mysqladmin -uroot -p1 shutdown 关闭服务器进程
[root@localhost ~]# mysqladmin -uroot -p1 version 服务器版本以及当前状态信息
[root@localhost ~]# mysqladmin -uroot -p1 start-slave 启动复制，启动从服务器复制线程
[root@localhost ~]# mysqladmin -uroot -p1 stop-slave 关闭复制线程

mysqldump

备份数据库和表的内容

[root@localhost ~]# mkdir -p /backup/mysqldump
[root@localhost ~]# mysqldump -uroot -p --all-databases > /backup/mysqldump/all.db
# 备份所有数据库
mysqldump -uroot -p test > /backup/mysqldump/test.db
# 备份指定数据库
mysqldump -uroot -p mysql db event > /backup/mysqldump/table.db
# 备份指定数据库指定表（多个表以空格间隔）
mysqldump -uroot -p test --ignore-table=test.t1 --ignore-table=test.t2 > /backup/mysqldump/test2.tb
# 备份指定数据库排除某些表

还原的方法

mysqladmin -uroot -p create db_name
mysql -uroot -p db_name < /backup/mysqldump/db_name.db
# 注：在导入数据库前，db_name如果没有，是需要创建的；而且与db_name.db中数据库名是一样的才可以导入。
mysql > use db_name;
mysql > source /backup/mysqldump/db_name.db;
# source也可以还原数据库