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

2021-04-28

下载

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 方式安装

  1	# 解压软件
  2[root@localhost software]# ls
  3mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
  4[root@localhost software]# tar xf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
  5[root@localhost software]# mkdir /application
  6[root@localhost software]# mv mysql-5.7.26-linux-glibc2.12-x86_64 /application/mysql
  7
  8# 创建用户、处理原始环境
  9[root@localhost software]# rpm -qa |grep mariadb
 10mariadb-libs-5.5.60-1.el7_5.x86_64
 11[root@localhost software]# yum remove mariadb-libs-5.5.60-1.el7_5.x86_64
 12[root@localhost software]# useradd -s /sbin/nologin mysql
 13
 14# 设置环境变量
 15[root@localhost software]# vim /etc/profile
 16export PATH=/application/mysql/bin:$PATH
 17[root@localhost software]# source /etc/profile
 18[root@localhost software]# mysql -V
 19mysql  Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using  EditLine wrapper
 20
 21# 创建数据路径并授权（与系统盘独立出来）
 221. 添加一块新磁盘模拟数据盘
 232. 格式化并挂载磁盘
 24[root@localhost software]# mkfs.xfs /dev/sdb
 25[root@localhost software]# mkdir /data
 26[root@localhost software]# blkid
 27[root@localhost software]# vim /etc/fstab 
 28UUID="b21ec3e0-e251-4ded-bc12-2d940f938dd5" /data xfs defaults 0 0
 29[root@localhost software]# mount -a
 30[root@localhost software]# df -h
 31
 32# 授权
 33[root@localhost software]# chown -R mysql.mysql /application/*
 34[root@localhost software]# chown -R mysql.mysql /data
 35
 36# 初始化数据（创建系统数据）
 37# 5.6 版本 初始化命令  /application/mysql/scripts/mysql_install_db 
 38# 5.7 版本
 39[root@localhost software]# mkdir /data/mysql/data -p 
 40[root@localhost software]# chown -R mysql.mysql /data
 41[root@localhost software]# yum install -y libaio-devel
 42[root@localhost software]# mysqld --initialize --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data
 432021-04-26T09:01:01.470121Z 1 [Note] A temporary password is generated for root@localhost: kYf6tlpH=rx>
 44
 45--initialize-insecure 参数：
 46无限制，无临时密码
 47[root@localhost software]# rm -rf /data/mysql/data/*
 48[root@localhost software]# mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data
 492021-04-26T09:06:32.501985Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
 50
 51# 配置文件准备
 52cat >/etc/my.cnf <<EOF
 53[mysqld]
 54user=mysql
 55basedir=/application/mysql
 56datadir=/data/mysql/data
 57socket=/tmp/mysql.sock
 58server_id=6
 59port=3306
 60log_error=/data/mysql/data/mysql.log 
 61log_bin=/data/mysql/data/mysql-bin  
 62[mysql]
 63socket=/tmp/mysql.sock
 64EOF
 65
 66# sysV(centos6)启动脚本
 67[root@localhost data]# cp /application/mysql/support-files/mysql.server  /etc/init.d/mysqld 
 68[root@localhost data]# service mysqld start
 69[root@localhost data]# lsof -i :3306
 70
 71# systemd(centos7)启动脚本
 72[root@localhost data]# cat >/etc/systemd/system/mysqld.service <<EOF
 73[Unit]
 74Description=MySQL Server
 75Documentation=man:mysqld(8)
 76Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
 77After=network.target
 78After=syslog.target
 79[Install]
 80WantedBy=multi-user.target
 81[Service]
 82User=mysql
 83Group=mysql
 84ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
 85LimitNOFILE = 5000
 86EOF
 87[root@localhost data]# systemctl start mysqld
 88[root@localhost data]# netstat -lnp | grep mysqld
 89tcp6       0      0 :::3306                 :::*                    LISTEN      16362/mysqld
 90
 91
 92# mysql启动失败错误日志查看
 93[root@localhost data]# cat /data/mysql/data/主机名.err 
 94
 95# 管理员密码的设定（root@localhost）  之前没有密码
 96[root@localhost data]# mysqladmin -uroot -p password oldboy123
 97Enter password: 
 98mysqladmin: [Warning] Using a password on the command line interface can be insecure.
 99Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety
100
101# 登录mysql
102[root@localhost data]# mysqladmin -uroot -p password oldboy123
103
104

管理员用户密码怎么办？

 1# 关闭数据库
 2[root@db01 ~]# /etc/init.d/mysqld stop
 3Shutting down MySQL.. SUCCESS!
 4
 5# 启动数据库到维护模式
 6[root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &
 7
 8# 登录并修改密码
 9mysql> flush privileges;
10mysql> alter user root@'localhost' identified by '1';
11Query OK, 0 rows affected (0.01 sec)
12
13# 正常启动验证
14mysql -uroot -p1
15

一条 SQL 语句的执行过程

 1# 连接层
 2（1） 提供连接协议
 3	TCP/IP：mysql -uroot -poldboy123 -h 10.0.0.51 -P3306
 4	Socket：mysql -uroot -poldboy123 -S /tmp/mysql.sock
 5（2） 验证用户名（root@localhost）密码合法性，进行匹配专门的授权表。
 6
 7（3） 派生一个专用连接线程（接收SQL，返回结果）
 8	  mysql> show processlist;
 9
10# SQL层
11（1）验证SQL语法和SQL_MODE
12（2）验证语义
13（3）验证权限
14（4）解析器进行语句解析，生成执行计划（解析树）
15（5）优化器（各种算法，基于执行代价），根据算法，找到代价最低的执行计划。
16	代价：CPU  IO  MEM
17（6）执行器按照优化器选择执行计划，执行SQL语句，得出获取数据的方法。
18（7）提供query cache(默认不开)，一般不开，会用redis
19（8）记录操作日志（binlog），默认没开
20
21
22# 存储引擎层
23真正和磁盘打交道的一个层次
24根据SQL层提供的取数据的方法，拿到数据，返回给SQL，结构化成表，再又连接层线程返回给用户。
25
26
27

逻辑结构

物理结构

 1# 宏观
 2库，存储在操作系统的目录中
 3表：
 4user表： MyISAM
 5user.frm     -----> 列的定义信息
 6user.MYD	 -----> 数据行 
 7user.MYI     -----> 索引信息
 8
 9time_zone表：InnoDB
10time_zone.frm  ---->列的定义信息
11time_zone.ibd  ---->数据行和索引
12
13
14# 微观
15页：最小的存储单元，默认16k
16区：64个连续的页，共1M
17段：一个表就是一个段，包含一个或多个区

用户管理

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

原则：建用户，再授权。

用户的定义

 1用户名@'白名单'
 2白名单？
 3oldguo@'10.0.0.51'
 4oldguo@'10.0.0.%'
 5oldguo@'10.0.0.5%'	#%代表 0~9
 6oldguo@'10.0.0.0/255.255.254.0'
 7oldguo@'%'
 8oldguo@'oldguo.com'
 9oldguo@'localhost'
10oldguo@'db01'
11
12常用的：
13oldguo@'10.0.0.%'
14oldguo@'10.0.0.5%'
15oldguo@'10.0.0.0/255.255.254.0'
16oldguo@'localhost'

用户管理

 1# 创建用户
 2mysql> create user oldguo@'10.0.0.%' identified by '123';
 3
 4# 查询用户
 5mysql> select user,host,authentication_string from mysql.user;
 6+---------------+-----------+-------------------------------------------+
 7| user          | host      | authentication_string                     |
 8+---------------+-----------+-------------------------------------------+
 9| root          | localhost | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 |
10| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
11| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
12| oldguo        | 10.0.0.%  | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
13+---------------+-----------+-------------------------------------------+
14
15# 删除用户
16mysql> drop user oldguo@'10.0.0.%';
17
18# 修改用户密码
19mysql> alter user root@'localhost' identified by '123';
20

权限管理

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

权限

1# ALL：普通管理员拥有的权限
2SELECT,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
3
4# with grant option:超级管理员才具备的，给别的用户授权的功能

授权语法

 1# 授权
 2grant 权限   on     数据库名.表名      to    用户                   identified by '密码'	   
 3grant ALL    on     wordpress.*        to    wordpress@'10.0.0.%'   identified by '123';
 4grant select,update,insert,delete    on   wordpress@'10.0.0.%'   to   用户    identified by '密码'
 5grant select,update,delete,insert on zhihu.* to zhihu@'10.0.0.%' identified by '123';
 6grant all on *.* to oldguo@'10.0.0.%' identified by '123' with grant option; # oldguo成为超级管理员，能给别的用户授权
 7
 8# 回收用户权限
 9mysql> revoke delete on zhihu.* from 'zhihu'@'10.0.0.%';
10
11# 查看用户权限
12mysql> show grants for zhihu@'10.0.0.%';
13+-----------------------------------------------------------------+
14| Grants for zhihu@10.0.0.%                                       |
15+-----------------------------------------------------------------+
16| GRANT USAGE ON *.* TO 'zhihu'@'10.0.0.%'                        |
17| GRANT SELECT, INSERT, UPDATE ON `zhihu`.* TO 'zhihu'@'10.0.0.%' |
18+-----------------------------------------------------------------+

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

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

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

连接管理

MySQL 参数

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

启动方式

 1# sys-v
 2/etc/init.d/mysqld
 3service mysqld start
 4----> /application/mysql/bin/mysqld_safe
 5----> /application/mysql/bin/mysqld
 6
 7# systemd
 8/etc/systemd/system/mysqld.service
 9systemctl start mysqld
10---->/application/mysql/bin/mysqld
11
12# 维护性的启动方式：
13/application/mysql/bin/mysqld_safe  --skip-grant-tables --skip-networking &
14/application/mysql/bin/mysqld &

初始化配置

作用

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

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

11、预编译：只能在编译安装中实现，硬编码配置到程序中
2
3
42、配置文件(my.cnf)，初始化配置文件默认读取位置：如果重复默认最后一个配置文件生效
5/etc/my.cnf --》 /etc/mysql/my.cnf --》 /usr/local/mysql/etc/my.cnf  --》 ~/.my.cnf 
6
73、命令行参数 (仅限于 mysqld_safe mysqld)
8mysqld_safe --defaults-file=/opt/my.cnf --socket=/tmp/mysql.socket&

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

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

初始化配置文件模版语法

 1服务端
 2[server]
 3[mysqld]
 4[mysqld_safe]
 5
 6客户端 
 7[client]
 8[mysql]
 9[mysqldump]
10
11[mysqld]
12user=mysql
13basedir=/application/mysql
14datadir=/data/mysql/data 
15socket=/tmp/mysql.sock 
16server_id=6
17port=3306
18log_error=/data/mysql/data/mysql.log 
19log_bin=/data/mysql/data/mysql-bin  
20[mysql] 
21socket=/tmp/mysql.sock
22

多实例

 15.5.1 创建目录
 2mkdir -p /data/330{7,8,9}/data
 3
 45.5.2 准备配置文件
 5
 6cat > /data/3307/my.cnf <<EOF
 7[mysqld]
 8basedir=/application/mysql
 9datadir=/data/3307/data
10socket=/data/3307/mysql.sock
11log_error=/data/3307/mysql.log
12port=3307
13server_id=7
14log_bin=/data/3307/mysql-bin
15EOF
16
17cat > /data/3308/my.cnf <<EOF
18[mysqld]
19basedir=/application/mysql
20datadir=/data/3308/data
21socket=/data/3308/mysql.sock
22log_error=/data/3308/mysql.log
23port=3308
24server_id=8
25log_bin=/data/3308/mysql-bin
26EOF
27
28cat > /data/3309/my.cnf <<EOF
29[mysqld]
30basedir=/application/mysql
31datadir=/data/3309/data
32socket=/data/3309/mysql.sock
33log_error=/data/3309/mysql.log
34port=3309
35server_id=9
36log_bin=/data/3309/mysql-bin
37EOF
38
395.5.3 初始化三套数据
40mv /etc/my.cnf /etc/my.cnf.bak
41mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/application/mysql
42mysqld --initialize-insecure  --user=mysql --datadir=/data/3308/data --basedir=/application/mysql
43mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/application/mysql
44
455.5.4 systemd管理多实例
46cd /etc/systemd/system
47cp mysqld.service mysqld3307.service
48cp mysqld.service mysqld3308.service
49cp mysqld.service mysqld3309.service
50
51
52vim mysqld3307.service
53ExecStart=/application/mysql/bin/mysqld  --defaults-file=/data/3307/my.cnf
54
55vim mysqld3308.service
56ExecStart=/application/mysql/bin/mysqld  --defaults-file=/data/3308/my.cnf
57
58vim mysqld3309.service
59ExecStart=/application/mysql/bin/mysqld  --defaults-file=/data/3309/my.cnf
60
61
625.5.5 授权
63chown -R mysql.mysql /data/*
64
655.5.6 启动
66systemctl start mysqld3307.service
67systemctl start mysqld3308.service
68systemctl start mysqld3309.service
69
705.5.7 验证多实例
71netstat -lnp|grep 330
72mysql -S /data/3307/mysql.sock -e "select @@server_id"
73mysql -S /data/3308/mysql.sock -e "select @@server_id"
74mysql -S /data/3309/mysql.sock -e "select @@server_id"

作者：Soulboy