1 Mysql的体系结构和模型
Mysql 客户端/服务器工作模型 (C/S)
(1)本地scoket链接方式:
socket=/tmp/mysql.sock
mysql-S/tmp/mysgl.sock
说明:只能在本地使用,不依赖于IP和端口
(2)远程TCPIP链接方式mysql -u root -p 123 -h 10.0.0.51 -3306
mysqld 原理图:
服务器端:
实例:在内存工作的一种模式,mysqld+工作线程+预分配的内存结构。
功能:主要功能就是用来管理数据。
example:公司=boss+员工+办公室
mysqld的程序结构:
mysql的逻辑结构
linux
库:库名+库属性
目录:名字+属性
表:表名+表属性+表内容+列
文件:名字+属性+文件内容
mysql>select user,host from mysql.user;
mysql>show databases;
mysql>use mysql
mysql>show tables
mysql>desc 具体的表
mysql的物理结构
物理存储结构:
段:一个表就是一个段,可以一个区或者多个区组成
区:一个区(簇),默认是1m,由连续的64个pages组成
页:一个页,默认16kb,连续4个os block组成,最小的IO单元
磁头,磁盘盘片,扇区。
MYSQL基础管理
7.1 用户管理
7.1.1 用户的作用
Linux用户:
登录Linux系统
管理Linux对象: 文件
Mysql用户:
登录Mysql数据库
管理数据库对象:表
7.1.2用户的定义 (生发咋成?)
Linux用户:用户名
Mysql用户:用户名@‘白名单’
白名单:地址列表,允许白名单的IP登录Mysql,管理Mysql(在我这里有很大信任度的人或事物)。
example:
youngwu@‘localhost’ : youngwu用户能够通过本地登录Mysql(socket)
youngwu@‘10.0.0.8’ : youngwu用户能够通过10.0.0.8远程登录数据库mysql
youngwu@‘10.0.0.%’ : youngwu用户能够通过10.0.0.xx/24远程登录数据库mysql
youngwu@‘10.0.0.5%’ : youngwu用户能够通过10.0.0.50/59远程登录数据库mysql
youngwu@‘%’ : youngwu用户能够通过任何方式远程登录数据库(只要能相互ping 通,就能登录)
youngwu@‘10.0.0.0/255.255.254.0’ : youngwu用户能够通过在同一网段远程登录数据库。
7.1.3 用户管理:
查:mysql> select user,host,authentication_string from mysql.user;

增:
mysql> create user youngwu@'localhost';
mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| youngwu | localhost | |
+---------------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)
mysql> create user youngwu@'10.0.0.%' identified by '888';
mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| youngwu | localhost | |
| youngwu | 10.0.0.% | *41DDB5DFD213B288EE050BD64DC6AA36815A3486 |
+---------------+-----------+-------------------------------------------+
删:
mysql> drop user youngwu@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| youngwu | 10.0.0.% | *41DDB5DFD213B288EE050BD64DC6AA36815A3486 |
+---------------+-----------+-------------------------------------------+
改:msql> alter user youngwu@'localhost' identified by '123';
注意:Centos 8.0版本以前,是可以通过grant命令,建立用户+授权。
权限管理
8.1.1 作用
用户对数据库对象,有哪些管理能力。
8.1.2 权限的表现方式
具体的命令
mysql> show privilege
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege | Context | Comment |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter | Tables | To alter the table |
| Alter routine | Functions,Procedures | To alter or drop stored functions/procedures |
| Create | Databases,Tables,Indexes | To create new databases and tables |
| Create routine | Databases | To use CREATE FUNCTION/PROCEDURE |
| Create temporary tables | Databases | To use CREATE TEMPORARY TABLE |
| Create view | Tables | To create new views |
| Create user | Server Admin | To create new users |
| Delete | Tables | To delete existing rows |
| Drop | Databases,Tables | To drop databases, tables, and views |
| Event | Server Admin | To create, alter, drop and execute events |
| Execute | Functions,Procedures | To execute stored routines |
| File | File access on server | To read and write files on the server |
| Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess |
| Index | Tables | To create or drop indexes |
| Insert | Tables | To insert data into tables |
| Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) |
| Process | Server Admin | To view the plain text of currently executing queries |
| Proxy | Server Admin | To make proxy user possible |
| References | Databases,Tables | To have references on tables |
| Reload | Server Admin | To reload or refresh tables, logs and privileges |
| Replication client | Server Admin | To ask where the slave or master servers are |
| Replication slave | Server Admin | To read binary log events from the master |
| Select | Tables | To retrieve rows from table |
| Show databases | Server Admin | To see all databases with SHOW DATABASES |
| Show view | Tables | To see views with SHOW CREATE VIEW |
| Shutdown | Server Admin | To shut down the server |
| Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. |
| Trigger | Tables | To use triggers |
| Create tablespace | Server Admin | To create/alter/drop tablespaces |
| Update | Tables | To update existing rows |
| Usage | Server Admin | No privileges - allow connect only |
+-------------------------+---------------------------------------+-------------------------------------------------------+
31 rows in set (0.00 sec)
8.1.3 授权、回收权限操作
语法:
8.0以前:
grant 权限 on 对象 to 用户 identified by ‘密码’;
8.0+ :
create user 用户 identified by ‘密码’;
grant 权限 on 对象 to 用户;
grant 权限 on 对象 to 用户 identified by ‘密码’
grant 权限 on 对象 to 用户 identified by ‘密码’ with grant option;
权限:
ALL :管理员
all kinds of privileges :普通用户(业务用户,开发用户)
with grant option :给别的用户授权
对象范围:库,表
*.* :chomd -R 755 / 管理员
youngwu.* :chomd -R 755 /youngwu 普通用户
youngwu.t1 :chomd -R 755 /youngwu/t1 更小一级
例子:
1.创建并授权管理员用户youngwu,能够通过10.0.0.% 网段登录并管理数据库
mysql> grant all on *.* to y@'10.0.0.%' identified by '666' with grant option;
2.查询创建的用户
select user,host from mysql.user;
3.查询创建用户的权限
mysql> show grant for y@'10.0.0.%';
+-----------------------------------------------------------------+
| Grants for y@10.0.0.% |
+-----------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'y'@'10.0.0.%' WITH GRANT OPTION |
+-----------------------------------------------------------------+
mysql> select*from mysql.user\G
查询所有用户的权限
例子2
创建并授权一个x@’10.0.0.%‘业务用户,能够对x库下的所有对象进行增上改查
mysql>grant create,delete,update,select on x.* to x@'10.0.0.%' identified by '666'
mysql>show grants for x@'10.0.0.%'
拓展:
mysql授权表:
mysql库下:
1.user :*.*
2.db :x.*
3.tables_priv :x.t1
4.columns_priv :列
8.1.3 回收权限
Linux :
chomd -R 755 /y -------> chomd -R 644 /y
Mysql:
注意:mysql不能重复授权,只能先收回来再进行授权
例子:
mysql>revoke create on y.* from 'y'@'10.0.0.%'; 'y':这个双引号可加不可加
mysql>show grants for y@'10.0.0.%';
8.1.4 超级管理员密码忘记了,处理
--skip-grant-tables : 跳过授权表
--skip-networking : 跳过TCP/IP协议
关闭数据库
systemctl stop mysqld
使用安全模式启动
mysqld_safe --skip-grant-tables --skip-networking &
#或者
service mysqld start --skip-grant-tables --skip-networking
登录数据库并修改密码
mysql>alter user root@'localhost' identified by '密码'
mysql>

mysql>flush privileges #手动加载授权表
mysql>alter user root@'localhost' identified by '密码'
重启数据库到正常模式
[root@mysql ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
数据库连接
数据库连接原理图
mysql
参数列表
-u | 用户名 |
-p | 密码 |
-S | 本地socket连接 |
-h | 数据库IP地址 |
-P | 端口号 默认3306 |
-e | 免交互执行数据库命令 |
< | 导入数据库脚本 |
例子:
-e的应用:
mysqldump 备份工具
mysqladmin 管理工具
mysql 远程客户端程序 (开发工具)
前提:必须提前创建,可以远程连接的用户
10.1 方式
源码安装 :编译过程中设置的初始化参数
配置文件 :数据库启动之前,设定配置文件参数。 /etc/my.cnf
启动脚本命令行:mysqld_safe --skip-grant-tables --skip-netwirking &
10.2 配置文件应用
(1)配置文件读取顺序
[root@mysql ~]# mysqld --help --verbose |grep my.cnf
/etc/my.cnf /→etc/mysql/my.cnf →/usr/local/mysql/etc/my.cnf →~/.my.cnf
port=3306 port=3360
意外情况:
手工定制配置文件位置点:/opt/my.cnf ,/data/3306/my.cnf,/data/3307/my.cnf,/data/3308/my.cnf
mysqld --defaults-file=/opt/my.cnf &
mysqld_safe --defaluts-file=/opt/my.cnf &
(2)配置文件的格式
[root@mysql ~]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/app/database/mysql
datadir=/data/3306
server_id=6
port=3306
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock
【标签】
配置参数=xxx
标签是什么?
区分不同程序参数的。
【服务器端】:影响数据库服务端的运行
[mysqld]
[mysqld_safe]
[server]
[客户端] :影响本地客户端连接,不影响远程客户端
[mysql]
[mysqldump]
[client]
配置文件基础模板说明:
[root@mysql ~]# cat /etc/my.cnf
[mysqld] # 服务器端的标签
user=mysql #负责数据库管理的用户
basedir=/app/database/mysql #软件安装的位置
datadir=/data/3306 #数据存放的位置
server_id=6 #标识节点的唯一编号(主从有用)
port=3306 #端口号
socket=/tmp/mysql.sock #套接字文件
[mysql] #客户端标签
socket=/tmp/mysql.sock #读取socket文件的位置点
说明:
配置文件的端口是3306,而开启的时候使用命令mysqld_safe --port=3307 &
最后的端口为3307 因为命令行优先。
11.1 启动
systemctl start mysqld ------>mysql.server -------->mysqld_safe ------>mysqld
mysqld_safe 和mysqld ,可以在启动数据库时,加如自己执行的参数,例如
--skip-grant-tables
--skip-networking
--defaults-files=/opt/my.cnf
11.2 关闭
systemctl stop mysqld
service mysqld stop
/etc/init.dmysqld stop
mysqladmin -u root -p密码 shutdown
mysql -uroot -p密码 -e "shutdown"
- 初始化配置
- MYSQL的启动和关闭
12.1 同版本的多实例(一台机子上跑多个数据库)
(1)规划(条件)
配置文件 3份 :/data/330{7..9}/my.cnf
数据目录 3份 :/data/330{7..9}
初始化数据 3份 :
日志目录 3份 :/binlog/330{7..9}
socket 3份 :/tmp/mysql330{7..9}.sock
端口 3份 :port=330{7..9}
server_id 3份 :7,8,9
(2)创建配置文件:
cat > /data/3307/my.cnf <<EOF
[mysqld]
basedir=/app/database/mysql
datadir=/data/3307/data
socket=/tmp/mysql3307.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/binlog/3307/mysql-bin
EOF
cat > /data/3308/my.cnf <<EOF
[mysqld]
basedir=/app/database/mysql
datadir=/data/3308/data
socket=/tmp/mysql3308.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/binlog/3308/mysql-bin
EOF
cat > /data/3309/my.cnf <<EOF
[mysqld]
basedir=/app/database/mysql
datadir=/data/3309/data
socket=/tmp/mysql3309.sock
log_error=/data/3309/mysql.log
port=3309
server_id=9
log_bin=/binlog/3309/mysql-bin
EOF
(3)初始化数据:
mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/app/database/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/app/database/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/app/database/mysql
(4)准备启动脚本
cat >/etc/systemd/system/mysqld3307.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=/app/database/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3308.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=/app/database/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3309.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=/app/database/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
EOF
(5)准备启动脚本
systemctl start mysqld330{7..9}
[root@mysql /]# mysqld --initialize-insecure --user=mysql --datadir=/data/3307 --basedir=/app/database/mysql
2020-09-08T16:55:03.871613Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-09-08T16:55:03.873094Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2020-09-08T16:55:03.873122Z 0 [ERROR] Aborting
不同版本的多实例(一台机器上运行不同版本的数据库)
update user set host='%' where host='localhost' and user='root';更改数据库用户白名单。
查看某个数据库下的存储过程;
select name from mysql.proc where db='world';
select routine_name from information_schema.routines where routine_schema='world';
show procedure status where db='world';
存储过程
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc1 --name存储过程名
-> (IN parameter1 INTEGER)
-> BEGIN
-> DECLARE variable1 CHAR(10);
-> IF parameter1 = 17 THEN
-> SET variable1 = 'birds';
-> ELSE
-> SET variable1 = 'beasts';
-> END IF;
-> INSERT INTO table1 VALUES (variable1);
-> END
-> //
mysql > DELIMITER ;
call @porc1 调用存储过程。
show index from table