目录

Life in Flow

知不知，尚矣；不知知，病矣。
不知不知，殆矣。

X

Mysql安装、用户管理、初始化、多实例

   
    0 0 410686931

下载

https://downloads.mysql.com/archives/community/

mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz

安装方式

安装方式优点缺点
rpm安装卸载简单可定制性差
glibc可定制性相比rpm包灵活些安装相比rpm包复杂些，需要手动初始化数据库
源码安装可定制性最强，根据需求和功能定制安装麻烦，需要手动初始化数据库
  • RPM: mysql-community-server-5.7.28-1.el7.x86_64.rpm ，需要在特定linux版本下安装。
  • glibc: mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz，依赖glibc库，可以安装在通用的Linux系统下
  • 源代码编译安装: mysql-5.7.31.tar.gz，通用的Linux下都可以编译安装

glibc方式安装

	# 解压软件
[root@localhost software]# ls
mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
[root@localhost software]# tar xf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
[root@localhost software]# mkdir /application
[root@localhost software]# mv mysql-5.7.26-linux-glibc2.12-x86_64 /application/mysql

# 创建用户、处理原始环境
[root@localhost software]# rpm -qa |grep mariadb
mariadb-libs-5.5.60-1.el7_5.x86_64
[root@localhost software]# yum remove mariadb-libs-5.5.60-1.el7_5.x86_64
[root@localhost software]# useradd -s /sbin/nologin mysql

# 设置环境变量
[root@localhost software]# vim /etc/profile
export PATH=/application/mysql/bin:$PATH
[root@localhost software]# source /etc/profile
[root@localhost software]# mysql -V
mysql  Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using  EditLine wrapper

# 创建数据路径并授权（与系统盘独立出来）
1. 添加一块新磁盘模拟数据盘
2. 格式化并挂载磁盘
[root@localhost software]# mkfs.xfs /dev/sdb
[root@localhost software]# mkdir /data
[root@localhost software]# blkid
[root@localhost software]# vim /etc/fstab 
UUID="b21ec3e0-e251-4ded-bc12-2d940f938dd5" /data xfs defaults 0 0
[root@localhost software]# mount -a
[root@localhost software]# df -h

# 授权
[root@localhost software]# chown -R mysql.mysql /application/*
[root@localhost software]# chown -R mysql.mysql /data

# 初始化数据（创建系统数据）
# 5.6 版本 初始化命令  /application/mysql/scripts/mysql_install_db 
# 5.7 版本
[root@localhost software]# mkdir /data/mysql/data -p 
[root@localhost software]# chown -R mysql.mysql /data
[root@localhost software]# yum install -y libaio-devel
[root@localhost software]# mysqld --initialize --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data
2021-04-26T09:01:01.470121Z 1 [Note] A temporary password is generated for root@localhost: kYf6tlpH=rx>

--initialize-insecure 参数：
无限制，无临时密码
[root@localhost software]# rm -rf /data/mysql/data/*
[root@localhost software]# mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data
2021-04-26T09:06:32.501985Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

# 配置文件准备
cat >/etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/data/mysql.log 
log_bin=/data/mysql/data/mysql-bin  
[mysql]
socket=/tmp/mysql.sock
EOF

# sysV(centos6)启动脚本
[root@localhost data]# cp /application/mysql/support-files/mysql.server  /etc/init.d/mysqld 
[root@localhost data]# service mysqld start
[root@localhost data]# lsof -i :3306

# systemd(centos7)启动脚本
[root@localhost data]# cat >/etc/systemd/system/mysqld.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://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
EOF
[root@localhost data]# systemctl start mysqld
[root@localhost data]# netstat -lnp | grep mysqld
tcp6       0      0 :::3306                 :::*                    LISTEN      16362/mysqld


# mysql启动失败错误日志查看
[root@localhost data]# cat /data/mysql/data/主机名.err 

# 管理员密码的设定（root@localhost）  之前没有密码
[root@localhost data]# mysqladmin -uroot -p password oldboy123
Enter password: 
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety

# 登录mysql
[root@localhost data]# mysqladmin -uroot -p password oldboy123

管理员用户密码怎么办？

# 关闭数据库
[root@db01 ~]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!

# 启动数据库到维护模式
[root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &

# 登录并修改密码
mysql> flush privileges;
mysql> alter user root@'localhost' identified by '1';
Query OK, 0 rows affected (0.01 sec)

# 正常启动验证
mysql -uroot -p1

一条SQL语句的执行过程

# 连接层
（1） 提供连接协议
	TCP/IP：mysql -uroot -poldboy123 -h 10.0.0.51 -P3306
	Socket：mysql -uroot -poldboy123 -S /tmp/mysql.sock
（2） 验证用户名（root@localhost）密码合法性，进行匹配专门的授权表。

（3） 派生一个专用连接线程（接收SQL，返回结果）
	  mysql> show processlist;

# SQL层
（1）验证SQL语法和SQL_MODE
（2）验证语义
（3）验证权限
（4）解析器进行语句解析，生成执行计划（解析树）
（5）优化器（各种算法，基于执行代价），根据算法，找到代价最低的执行计划。
	代价：CPU  IO  MEM
（6）执行器按照优化器选择执行计划，执行SQL语句，得出获取数据的方法。
（7）提供query cache(默认不开)，一般不开，会用redis
（8）记录操作日志（binlog），默认没开


# 存储引擎层
真正和磁盘打交道的一个层次
根据SQL层提供的取数据的方法，拿到数据，返回给SQL，结构化成表，再又连接层线程返回给用户。

逻辑结构

物理结构

# 宏观
库，存储在操作系统的目录中
表：
user表： MyISAM
user.frm     -----> 列的定义信息
user.MYD	 -----> 数据行 
user.MYI     -----> 索引信息

time_zone表：InnoDB
time_zone.frm  ---->列的定义信息
time_zone.ibd  ---->数据行和索引


# 微观
页：最小的存储单元，默认16k
区：64个连续的页，共1M
段：一个表就是一个段，包含一个或多个区

用户管理

作用：登录、管理数据库对象

原则：建用户，再授权。

用户的定义

用户名@'白名单'
白名单？
oldguo@'10.0.0.51'
oldguo@'10.0.0.%'
oldguo@'10.0.0.5%'	#%代表 0~9
oldguo@'10.0.0.0/255.255.254.0'
oldguo@'%'
oldguo@'oldguo.com'
oldguo@'localhost'
oldguo@'db01'

常用的：
oldguo@'10.0.0.%'
oldguo@'10.0.0.5%'
oldguo@'10.0.0.0/255.255.254.0'
oldguo@'localhost'

用户管理

# 创建用户
mysql> create user oldguo@'10.0.0.%' identified by '123';

# 查询用户
mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| oldguo        | 10.0.0.%  | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+---------------+-----------+-------------------------------------------+

# 删除用户
mysql> drop user oldguo@'10.0.0.%';

# 修改用户密码
mysql> alter user root@'localhost' identified by '123';

权限管理

控制用户登录之后能对Mysql对象做哪些命令。

权限

# ALL：普通管理员拥有的权限
SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, 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

# with grant option:超级管理员才具备的，给别的用户授权的功能

授权语法

# 授权
grant 权限   on     数据库名.表名      to    用户                   identified by '密码'	   
grant ALL    on     wordpress.*        to    wordpress@'10.0.0.%'   identified by '123';
grant select,update,insert,delete    on   wordpress@'10.0.0.%'   to   用户    identified by '密码'
grant select,update,delete,insert on zhihu.* to zhihu@'10.0.0.%' identified by '123';
grant all on *.* to oldguo@'10.0.0.%' identified by '123' with grant option; # oldguo成为超级管理员，能给别的用户授权

# 回收用户权限
mysql> revoke delete on zhihu.* from 'zhihu'@'10.0.0.%';

# 查看用户权限
mysql> show grants for zhihu@'10.0.0.%';
+-----------------------------------------------------------------+
| Grants for zhihu@10.0.0.%                                       |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhihu'@'10.0.0.%'                        |
| GRANT SELECT, INSERT, UPDATE ON `zhihu`.* TO 'zhihu'@'10.0.0.%' |
+-----------------------------------------------------------------+

MySQL中的权限是可以继承，多次授权是叠加的

oldboy@'10.0.0.%' 能对t1表具备什么权限？

  1. grant select,update on*.* to oldboy@'10.0.0.%';
  2. grant delete on wordpress.* to oldboy@'10.0.0.%';
  3. grant insert on wordpress.t1 to oldboy@'10.0.0.%';

连接管理

mysql 参数

-u	#用户名
-p	#密码
-S	#socket文件路径 mysql -uroot -poldboy123 -S /tmp/mysql.sock
-h	#主机地址/IP
-P	#端口
<	#导入运行sql脚本	# mysql -uroot -p <world.sql

启动方式

# sys-v
/etc/init.d/mysqld
service mysqld start
----> /application/mysql/bin/mysqld_safe
----> /application/mysql/bin/mysqld

# systemd
/etc/systemd/system/mysqld.service
systemctl start mysqld
---->/application/mysql/bin/mysqld

# 维护性的启动方式：
/application/mysql/bin/mysqld_safe  --skip-grant-tables --skip-networking &
/application/mysql/bin/mysqld &

初始化配置

作用

控制MySQL的启动
影响到客户端的连接

Mysql提供了三种初始化配置的方式

1、预编译：只能在编译安装中实现，硬编码配置到程序中


2、配置文件(my.cnf)，初始化配置文件默认读取位置：如果重复默认最后一个配置文件生效
/etc/my.cnf --》 /etc/mysql/my.cnf --》 /usr/local/mysql/etc/my.cnf  --》 ~/.my.cnf 

3、命令行参数 (仅限于 mysqld_safe mysqld)
mysqld_safe --defaults-file=/opt/my.cnf --socket=/tmp/mysql.socket&

初始化配置文件的作用范围

数据库的启动：mysqld  mysqld_safe  
客户端的连接：mysql  mysqldump  mysqladmin

初始化配置文件模版语法

服务端
[server]
[mysqld]
[mysqld_safe]

客户端 
[client]
[mysql]
[mysqldump]

[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/mysql/data 
socket=/tmp/mysql.sock 
server_id=6
port=3306
log_error=/data/mysql/data/mysql.log 
log_bin=/data/mysql/data/mysql-bin  
[mysql] 
socket=/tmp/mysql.sock

多实例

5.5.1 创建目录
mkdir -p /data/330{7,8,9}/data

5.5.2 准备配置文件

cat > /data/3307/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/data/3307/mysql-bin
EOF

cat > /data/3308/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/data/3308/mysql-bin
EOF

cat > /data/3309/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
port=3309
server_id=9
log_bin=/data/3309/mysql-bin
EOF

5.5.3 初始化三套数据
mv /etc/my.cnf /etc/my.cnf.bak
mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/application/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3308/data --basedir=/application/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/application/mysql

5.5.4 systemd管理多实例
cd /etc/systemd/system
cp mysqld.service mysqld3307.service
cp mysqld.service mysqld3308.service
cp mysqld.service mysqld3309.service


vim mysqld3307.service
ExecStart=/application/mysql/bin/mysqld  --defaults-file=/data/3307/my.cnf

vim mysqld3308.service
ExecStart=/application/mysql/bin/mysqld  --defaults-file=/data/3308/my.cnf

vim mysqld3309.service
ExecStart=/application/mysql/bin/mysqld  --defaults-file=/data/3309/my.cnf


5.5.5 授权
chown -R mysql.mysql /data/*

5.5.6 启动
systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service

5.5.7 验证多实例
netstat -lnp|grep 330
mysql -S /data/3307/mysql.sock -e "select @@server_id"
mysql -S /data/3308/mysql.sock -e "select @@server_id"
mysql -S /data/3309/mysql.sock -e "select @@server_id"
作者：Soulboy