数据库 day 02 mysql 基础管理

补：

数据初始化 (建系统库) ---（不安全的初始化方式，会生成一个随机登录数据库密码。登录时必须修改密码才能登录）
[root@db01 data_3306]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/mysql/data_3306

5.7 以前版本初始化方式:
/usr/local/mysql56/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql56 --datadir=/data/mysql/data_3306

show processlist; #查看当前线程用户登录数量

SQL层

1. 语法 
2. 语义
3. 权限
4. 解析   ---> 执行计划
5. 预处理 ---> 代价计算(cost)
6. 优化器 ---> 选择代价低的
7. 执行器 ---> 结果----> 段--->区---->页

查询QC(query_cache)  #将解析、预处理、优化器等重复操作保存在缓存中，提高效率。

day 2 第二章MySQL 基础管理

1.用户管理

登录
管理对象

1.2.1 用户名:    
不要太长 , 和业务有关
emp_user01
grant all on *.* to wordpress@'%' identified '123';
1.2.2 白名单?
user@'10.0.0.56'
user@'%'
user@'10.0.0.%'   255.255.255.0   24位
user@'10.0.0.0/255.255.254.0'     23位
user@'10.0.0.5%'    50-59
user@'localhost'  本地
1.2.3 常用
user@'10.0.0.%'
user@'10.0.0.0/255.255.254.0' 
user@'10.0.0.5%'    50-59
user@'localhost'  本地

创建用户:
mysql> CREATE USER  oldguo@'10.0.0.%' IDENTIFIED BY '123';
查询用户:
mysql> select user,host from mysql.user;
mysql> select user,host,authentication_string  from mysql.user;
修改用户的密码
mysql> alter user oldguo@'10.0.0.%' identified by '123456';
删除用户
mysql> drop user oldzhang@'10.0.0.%';

ps: 8.0+ 版本必须先创建用户再授权。
    8.0以前版本授权时同时可以创建用户。

2.权限管理

2.1 MySQL权限列表
mysql> show privileges;   #权限清单
2.2 # 权限语法格式
GRANT  权限   ON 权限作用范围   TO 用户   IDENTIFIED BY '123' with grant option;
权限作用范围 :
*.*       ====>  一般是管理员会设置的方法
oldguo.*  ====>  一般是业务用户会设置的方法
oldguo.t1 ====>  一般是业务用户户设置的方法 （具体到那个表）  

2.3 企业授权案例
(1)授权一个管理员用户oldguo,可以从10网段任意地址登录管理数据库 
GRANT  ALL  ON *.*    TO oldguo@'10.0.0.%'   IDENTIFIED BY '123'  with grant option;
(2)授权一个业务用户app,可以从10网段地址访问app库的所有表 
grant select,update,insert,delete ON app.* TO app@'10.0.0.%'   IDENTIFIED BY '123' ;
(3)授权一个开发用户dev,可以对dev库进行业务开发（开发的权限一般为除了server admin之外的）

2.4 root管理员密码忘记或被篡改如何处理?
(1) 关闭数据库,启动到"单用户"模式
[root@db01 data_3306]# systemctl stop mysqld
[root@db01 data_3306]# mysqld_safe  --skip-grant-tables  --skip-networking  &      #--skip-networking关闭远程tcp连接（不提供端口号）只从本地登录     --skip-grant-tables  不开启验证模块
(2) 无密码登录MySQL 
[root@db01 data_3306]# mysql
mysql> alter user root@'localhost' identified by '123456';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

mysql> flush privileges;   #手动将用户授权表加载到系统内存中
mysql> alter user root@'localhost' identified by '123456';

(3) 重启数据库到正常模式
[root@db01 data_3306]# systemctl restart mysqld

2.5 查询用户权限
mysql> show grants for oldshun@'10.0.0.%';

2.6 回收权限
mysql> revoke delete,drop   on *.* from 'oldshun'@'10.0.0.%';

3. MySQL的连接管理
3.1 自带客户端工具 
3.1.1 mysql 
-u  用户名 
-p  密码
-h  IP
-P  端口
-S  socket位置
-e  免交互
<   导入SQL脚本
例子: 
(1) TCP连接串远程登录
注:需要提前创建好远程用户
mysql> grant all on *.* to oldguo@'10.0.0.%' identified by '123';
[root@db01 data_3306]# mysql -uroot -p -h 10.0.0.51 -P 3306
(2) Socket连接方式
注:需要提前创建好localhost用户
mysql> grant all on *.* to oldguo@'localhost' identified by '123';
[root@db01 data_3306]# mysql -uoldguo -p -S /tmp/mysql.sock 
Enter password: 

如何验证一个用户是通过本地还是远程和登录的.
show processlist;

(3) 免交互执行命令
[root@db01 ~]# mysql -uroot -p -e "show processlist"
Enter password: 
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
|  5 | root | localhost | NULL | Query   |    0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+

(4) 导入SQL脚本 
[root@db01 ~]# mysql -uroot -p < t100w.sql
Enter password: 
mysql> source /root/world.sql

3.1.2 mysqladmin
(1) 修改密码
[root@db01 ~]# mysqladmin -uroot -p123456 password 123
[root@db01 ~]# mysql -uroot -p123

(2) 关闭数据库 
[root@db01 ~]# mysqladmin -uroot -p123  shutdown 

4. MySQL的启动关闭
systemctl ---> mysql.server start 
-----> mysqld_safe  ----> mysqld

5.1 初始化配置方法
源码安装定制    <    初始化配置文件  <    命令行启动时定制(优先级最高)

5.2 初始化配置文件
[root@db01 data_3306]# mysqld --help --verbose |grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 

先调用/etc/my.cnf 再调用/etc/mysql/my.cnf  /usr/local/mysql/etc/my.cnf      ~/.my.cnf   后面会复制前面

建议一个mysql实例一个配置文件

5.3 配置文件书写格式
[root@db01 data_3306]# cat /etc/my.cnf 
[mysqld]
user=mysql
port=3306
basedir=/usr/local/mysql57
datadir=/data/mysql/data_3306
server_id=6
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock

标签项  ====> [mysqld]
服务器端 [server]: [mysqld],[mysqld_safe]  ====> 影响到MySQL启动
客户端 [clinet] : [mysql] ,[mysqldump]    ====> 影响本地客户端程序
 
配置项  ====> key=value
5.4 自定制初识化配置文件位置
[root@db01 ~]# cat /opt/a.cnf 
[mysqld]
user=mysql
port=3306
basedir=/usr/local/mysql57
datadir=/data/mysql/data_3306
server_id=6
socket=/tmp/a.sock
[root@db01 ~]# pkill mysqld
[root@db01 ~]#  mysqld --defaults-file=/opt/a.cnf &   #启动数据库的方式
[root@db01 ~]# mysql  -S  /tmp/a.sock


6. 多实例的规划和配置
分布式架构中应用广泛
6.1 端口和目录
mkdir -p /data/mysql/data_{3307,3308,3309} 
2. 配置文件准备 
cat > /data/mysql/my3307.cnf <<EOF
[mysqld]
user=mysql
port=3307
basedir=/usr/local/mysql57
datadir=/data/mysql/data_3307
server_id=7
socket=/tmp/mysql3307.sock
EOF
cat > /data/mysql/my3308.cnf <<EOF
[mysqld]
user=mysql
port=3308
basedir=/usr/local/mysql57
datadir=/data/mysql/data_3308
server_id=8
socket=/tmp/mysql3308.sock
EOF
cat > /data/mysql/my3309.cnf <<EOF
[mysqld]
user=mysql
port=3309
basedir=/usr/local/mysql57
datadir=/data/mysql/data_3309
server_id=9
socket=/tmp/mysql3309.sock
EOF
3. 授权 
[root@db01 ~]# chown -R mysql.mysql /data/
4. 初始化数据 
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/mysql/data_3307
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/mysql/data_3308
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/mysql/data_3309
5. 启动多实例 
[root@db01 mysql]# mysqld --defaults-file=/data/mysql/my3307.cnf &
[root@db01 mysql]# mysqld --defaults-file=/data/mysql/my3308.cnf &
[root@db01 mysql]# mysqld --defaults-file=/data/mysql/my3309.cnf &
[root@db01 mysql]# netstat -tulnp


6. 使用 systemd 管理多实例
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=/usr/local/mysql57/bin/mysqld --defaults-file=/data/mysql/my3307.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=/usr/local/mysql57/bin/mysqld --defaults-file=/data/mysql/my3308.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=/usr/local/mysql57/bin/mysqld --defaults-file=/data/mysql/my3309.cnf
LimitNOFILE = 5000
EOF

pkill mysqld  
systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309

第三章 SQL 基础

1. SQL介绍
1.1 简介
结构化查询语言.  
1.2 SQL标准 
SQL89   SQL92  SQL99  SQL03  SQL05  
1.3 SQL_MODE
除数为零
日期
mysql> select @@sql_mode;
1.4 SQL 类型
DDL : 数据定义语言 : 库名,库属性,表名,表属性,列(列名,列属性)
DCL : 数据控制语言 : 权限
DML : 数据操作语言 : 数据行
DQL : 数据查询语言 : 数据行

1.5 SQL功能
管理,操作数据库对象: 
    库:  库名,库属性
    表:  表名,表属性,列(列名,列属性),数据行


2. MySQL规范性存储限制 

2.1 字符集  utf8   utf8mb4
utf8: 最大字符长度3个 ，其中中文三个字符  
utf8mb4 :  最大字节长度4个. 可以存储emoji表情字符.
2.2
# mysql 中支持的字符集
mysql> show charset;
# mysql 中的排序规则
show  collation;   #校对规则

默认是大小写不敏感.

2.3 数据类型
2.3.1 数字类型
tinyint（按需分派长度）   1字节长度数字=8字符  ===> 11111111  ===> 0-2^8-1 ===> -2^7-2^7-1 (3位)
int       4字节长度=32字节      ====> 0-2^32-1 ====> -2^31 - 2^31-1  (10位数)
bigint    8字节长度      ====> 0-2^64-1 ====> -2^63 - 2^63-1  (20位数)

2.3.2 字符串
char(10)  : 定长类型,最多10个字节,占用存储空间一定.最多存储255个字符.
varchar(10): 
    变长类型,最多10个字符,按需分配存储空间.
    需要额外1个字符或2个字符存储字符长度
    
因素: 变长的字符串列,90%几率都是varchar
具体原因是什么?
节省空间,还有没有别的原因?
遗留的问题..

enum('m','f')：枚举类型
      1   2 
      
2.3.3 时间类型
DATETIME 
范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。

TIMESTAMP 
1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。
timestamp会受到时区的影响
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 212,657评论 6赞 492
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,662评论 3赞 385
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 158,143评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,732评论 1赞 284
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,837评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 50,036评论 1赞 291
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,126评论 3赞 410
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,868评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,315评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,641评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,773评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,470评论 4赞 333
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,126评论 3赞 317
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,859评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,095评论 1赞 267
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,584评论 2赞 362
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,676评论 2赞 351

推荐阅读更多精彩内容