DBA之路 2_MySQL_数据库的结构及基本管理

作者: Linux_淡忘 | 来源:发表于2019-06-19 08:03 被阅读0次

1.4逻辑结构

主要分为两大部分：库&表
库--->表--->数据行（记录），字段（列），属性（列约束，列名称，列的数据类型，表属性，权限）
表--->数据行+字段+元数据

逻辑结构

1.5物理结构（引入）

1.5.1宏观

库：存储在操作系统的目录中
表：MyISAM（一种存储引擎）
-rw-r----- 1 mysql mysql   10816 Jun 13 15:17 user.frm
-rw-r----- 1 mysql mysql     396 Jun 13 16:33 user.MYD
-rw-r----- 1 mysql mysql    4096 Jun 13 16:33 user.MYI
.frm存放列的定义信息
.MYD存放数据行
.MYI存放索引信息
InnoDB（默认的存储引擎,带有日志功能）
默认的类型，只要建表就是innoDB引擎，
-rw-r----- 1 mysql mysql    8636 Jun 13 15:17 time_zone.frm
-rw-r----- 1 mysql mysql   98304 Jun 13 15:17 time_zone.ibd
.frm存放列的定义信息
.idb存放数据行和索引信息
idb文件在MySQL层次上又进行了一次初始化，分成固定大小的页（16K默认）

面试题
请说MyISAM和InnoDB存储数据上的区别

1.5.2微观

段:即表，根据存储数据的大小可以自动扩容，一个段=256区=256*64个页
区:固定大小1M，根据page大小觉得里面有多少page
页:idb文件格式化后生成的大小相同且连续的存储空间，默认是16KB。
一个表就是一个段，MySQL分配空间时，至少会分配一个区，每个区默认是1M（64个page（页）），MySQL最小的IO单元是Page。
目的：顺序IO，一次读为一个IO,写为一个IO，一次读写一共两次IO。
而不分区后，是随机IO，会占用没必要的系统资源。按页为单元进行写入。idb文件是在表创建的时候对应生成的，他的作用可以理解为是该表对应的磁盘。

第二章基础管理

2.1 用户管理

2.1.1 作用

登录
管理数据库对象（逻辑结构等）

2.1.2 用户的定义

用户名@'白名单'
白名单：IP地址
oldzyl@'localhost'
oldzyl@'10.0.0.51' 允许oldzyl用户从51这个地址链接数据库
oldzyl@'10.0.0.%'
oldzyl@'10.0.0.5%'   50-59
oldzyl@'10.0.0.0/255.255.254.0'
oldzyl@'%'      
oldzyl@'oldzyl.com'   
oldzyl@'db01'    服务器名需要解析
常用：
oldzyl@'10.0.0.%'
oldzyl@'10.0.0.5%'   50-59
oldzyl@'10.0.0.0/255.255.254.0'

2.1.3 用户管理

创建用户
·······
mysql> create user oldguo@'10.0.0.%' identified by '123';
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 |
| oldguo        | 10.0.0.%  | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+---------------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)
删除用户
·······
mysql> drop user oldguo@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
修改用户
·······
mysql> alter user root@'localhost' identified by '123';

2.2权限管理

2.2.1 权限作用

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

2.2.2权限的定义

MySQL的权限定义就是SQL语句

常用权限介绍:

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
ALL : 以上所有权限，一般是普通管理员拥有的
with grant option：超级管理员才具备的，给别的用户授权的功能

8.0版本新特性
加入了role（角色）的概念

授权管理

语法：
grant 权限   on 范围  to 用户 identified by '密码'
权限：select,update,insert,delete
范围：
*.*             所有
wordpress.*     wordpress库底下所有表
wordpress.t1    wordpress库底下的t1表
此命令仅限于8.0版本以前

例子：

1.需求，从windows中的navicat软件管理MySQL数据库
2.创建 zhihu业务用户能够对知乎业库进行业务操作
(1)需求1：windows机器的navicat登录到linux中的MySQL，管理员用户。

mysql> grant all on *.* to root@'10.0.0.%' identified by '123';

(2)需求2：创建一个应用用户app用户，能从windows上登录mysql，并能操作app库

mysql> grant select ,update,insert,delete on app.* to app@'10.0.0.%' identified by '123';
业务用户权限：select update delete insert(插入)

(3)思考：问: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.%';
问:oldboy@'10.0.0.%'对t1表的权限是什么？
MySQL表中的权限是可以继承的，多次授权是可以叠加
所以想要取消某个权限，必须通过回收的方式实现，不能多次重复授权

(4)查看用户权限

将oldguo用户提升为超级管理员
mysql> grant all on *.* to oldguo@'10.0.0.%' identified by '123' with grant option;
mysql> show grants for root@'10.0.0.%';
+--------------------------------------------------+
| Grants for root@10.0.0.%                         |
+--------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.0.0.%' |
+--------------------------------------------------+
1 row in set (0.00 sec)

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.%' |
+-----------------------------------------------------------------+
2 rows in set (0.00 sec)

（5）回收权限

mysql>  revoke delete on zhihu.* from 'zhihu'@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)

面试题：
1.开发人员找DBA开用户，需要DBA和开发人员沟通什么？

（1）你要做哪些权限的操作
（2）你要从什么地址来登数据库
（3）要对什么对象进行管理操作

2.开发人员找DBA要管理员root用户的密码，作为DBA应该怎么处理（金融公司）

情况一：
（1）不给
（2）在金融公司严令禁止，举报！！！
情况二：（小公司）
root用户是乱用，学会保护自己
可以提意见，生成流程

3.MySQL连接管理

3.1自带的客户端工具

mysql参数
-u 用户名 
-P 端口号
-h IP
-p 密码
-S socket文件
-e 非交互式执行
< 导入SQL脚本

问题：怎么证明你的数据库是可用的

1）netstat -lnp|grep 330
      ps -ef|grep mysqld
2)   验证用户远程连接性
mysql -uroot -p123 -h 10.0.0.51 -e "show databases;"

3.2远程的客户端工具

navicat
 sqlyog

4.MySQL启动方式介绍

辅助脚本方式(普通的启动关闭):
sys-v：    /etc/init.d/mysqld
/etc/init.d/mysqld start ----->/application/mysql/bin/mysqld_safe----->/application/mysql/bin/mysqld
systemd:/etc/systemd/system/mysql.service
维护性的启动方式：
mysql_safe --skip-grant-tables --skip-networking &
mysqld &

5.MySQL初始化配置

5.1预编译时进行设置

只能在编译安装中实现
硬编码配置到程序中

5.2初始化配置文件（my.cnf）

5.2.1初始化配置文件默认读取位置

[root@hosue ~]# mysqld --help --verbose|grep "my.cnf"
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 
                      my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default

5.3命令行模式

mysqld_safe --defaults-file=/opt/my.cnf --socket=/tmp/aaa

5.4初始化配置文件应用

5.4.1作用

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

5.4.2书写格式

[程序名]
配置项=xxxx
配置项=xxxx
配置项=xxxx
配置项=xxxx

服务端
[server]
[mysqld]
[mysqld_safe]
客户端
[client]
[mysql]     
[mysqldump]

5.4.3 mysql初始化配置的常用参数(通用模板)

[mysqld]
user=mysql
datadir=/data/mysql/data
socket=/data/mysql/mysql.sock
basedir=/application/mysql
server_id=6
port=3307
log_error=/data/mysql/data/mysql.log
log_bin=/data/mysql/data/mysql_bin  
 ###二进制文件位置+文件名前缀，这里的
#为前缀,专门用来备份恢复和主从复制
[mysql]
socket=/tmp/mysql.sock

5.5MySQL多实例

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"

5.5.8最终结果

[root@hosue /data/mysql]# netstat -lnp|grep 330
tcp6       0      0 :::3307                 :::*                    LISTEN      11409/mysqld        
tcp6       0      0 :::3308                 :::*                    LISTEN      11469/mysqld        
tcp6       0      0 :::3309                 :::*                    LISTEN      11501/mysqld        
unix  2      [ ACC ]     STREAM     LISTENING     133929   11409/mysqld         /data/3307/mysql.sock
unix  2      [ ACC ]     STREAM     LISTENING     134411   11501/mysqld         /data/3309/mysql.sock
unix  2      [ ACC ]     STREAM     LISTENING     134304   11469/mysqld         /data/3308/mysql.sock
[root@hosue /data/mysql]# mysql -S /data/3307/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+
[root@hosue /data/mysql]# mysql -S /data/3308/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|           8 |
+-------------+
[root@hosue /data/mysql]# mysql -S /data/3309/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|           9 |
+-------------+
[root@hosue /data/mysql]#