MySQL（四）、（五）、多实例

MySQL（四）、（五）、多实例

（一）关系型数据库基础

（二）MySQL安装

（三）管理数据库和表

（四）用户和权限管理

（五）函数，存储过程和触发器

（六）MySQL架构

（七）存储引擎

（八）MySQL服务器选项，系统和状态变量

（九）优化查询和索引管理

（十）锁和事务管理

（十一）日志管理

（十二）备份还原

（十三）MySQL集群

视图、函数、存储过程、触发器

视图

• 视图：VIEW,虚表，保存有实表的查询结果
• 创建方法：
  CREATE VIEW view_name [(column_list)]
  AS select_statement
  [WITH [CASCADED | LOCAL] CHECK OPTION]
• 查看视图定义：SHOW CREATE VIEW view_name
• 删除视图：
  DROP VIEW [IF EXISTS]
  view_name [, view_name] …
  [RESTRICT | CASCADE]
• 视图中的数据事实上存储于“基表”中，因此，其修改操作也会针对基表实现；其修改操作受基表限制

    视图：虚拟的表，对应于select的执行结果。视图是不存放数据的，只是
select语句的查询结果。

    例如：定义以下视图：
> create view view_practice_11 as select st.name,st.age,st.classid from students as st inner join (select classid as id,avg(age) as avg_age ,count(stuid) as count_student from students  group by classid) as ca on st.classid=ca.id where st.age>ca.avg_age and ca.count_student>2;

MariaDB [hellodb]>  show tables; ==>查看表的命令，但是也能显示视图
+-------------------+
| Tables_in_hellodb |
+-------------------+
| courses           |
| scores            |
| students          |
| view_practice_11  | ==>定义的视图，容易于表混淆
+-------------------+

==>视图是虚拟表，但是视图不是真正的表，因此不对应有对应数据库文件夹的磁
盘文件，只有视图的定义文件：view_practice_11.frm

MariaDB [hellodb]> show table status \G 查询当前库中所有表的状态
*************************** 9. row ***************************
           Name: user
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 2
 Avg_row_length: 8192
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 3
    Create_time: 2019-09-16 16:48:07
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 10. row ***************************
           Name: view_practice_11
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW

==>可见表和视图的差异。

注意：一般不会对视图进行增删改，因为视图只是select语句的执行结果。
    当视图是原表的一部分时：
    以insert为例：强行对视图进行insert有以下几种情况：
    ①insert即使能插入数据进去，若不满足视图的定义，也不能在视图中显示。
    ②若原表未显示字段不允许为null，insert执行失败。
    ③若select是多表查询时，insert执行失败，不知道insert的数据往哪加。

==>不要对视图进行增删改操作！！！

函数

• 函数：系统函数和自定义函数

（1）系统函数

（2）自定义函数 (user-defined function UDF)

• 自定义的函数均保存在mysql.proc表中

函数的定义于所在的数据库定义有关，不在定义的数据库中使用自定义的函数时:
    select db_name.func_name();

使用select * from mysql.proc \G 可以查看定义的函数--以及对应的db

• 创建UDF
  CREATE [AGGREGATE] FUNCTION function_name(parameter_name
  type,[parameter_name type,…])
  RETURNS {STRING|INTEGER|REAL}
  runtime_body

• 说明：
  参数可以有多个,也可以没有参数
  必须有且只有一个返回值

• 查看函数列表：
  SHOW FUNCTION STATUS;

• 查看函数定义
  SHOW CREATE FUNCTION function_name

• 删除UDF:
  DROP FUNCTION function_name

• 调用自定义函数语法:
  SELECT function_name(parameter_value,…)

示例1：带参数的UDF
    DELIMITER //
    CREATE FUNCTION deleteById(uid SMALLINT UNSIGNED) RETURNS 
    VARCHAR(20) 
    BEGIN
    DELETE FROM students WHERE stuid = uid;
    RETURN (SELECT COUNT(stuid) FROM students);
    END//
    DELIMITER;
示例1说明：
    因为SQL语句默认是以 ' ; ' 为结束标准的
    因此定义函数中直接使用SQL;==>mysql看见;默认会执行
    此时需要临时重新定义SQL语句的结束符
        即使用delimiter关键字

• 自定义函数中定义局部变量语法
  DECLARE 变量1[,变量2,… ]变量类型 [DEFAULT 默认值]
• 说明：局部变量的作用范围是在BEGIN…END程序中,而且定义局部变量语句必须在BEGIN…END的第一行定义

示例:
DELIMITER //
CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, y SMALLINT 
UNSIGNED) 
RETURNS SMALLINT
BEGIN
DECLARE a, b SMALLINT UNSIGNED;
SET a = x, b = y;
RETURN a+b;
END//
DELIMITER ;

为变量赋值语法
SET parameter_name = value[,parameter_name = value...]
SELECT INTO parameter_name

• 注意：10.2.27-MariaDB版本时创建函数的帮助：
  CREATE [AGGREGATE] FUNCTION function_name
  RETURNS {STRING|INTEGER|REAL|DECIMAL}
  SONAME shared_library_name
• 总述：函数在目前的使用不多了。

存储过程

• 存储过程优势
  存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,
  当需要时从数据库中直接调用,省去了编译的过程
  提高了运行速度
  同时降低网络数据传输量

• 存储过程与自定义函数的区别
  存储过程实现的过程要复杂一些,而函数的针对性较强
  存储过程可以有多个返回值,而自定义函数只有一个返回值
  存储过程一般可独立执行,而函数往往是作为其他SQL语句的一部分来使用

• 存储过程：存储过程保存在mysql.proc表中

• 创建存储过程
  CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter …]])routime_body
  proc_parameter : [IN|OUT|INOUT] parameter_name type
  其中IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；param_name表示参数名称；type表示参数的类型

• 查看存储过程列表
  SHOW PROCEDURE STATUS;

• 查看存储过程定义
  SHOW CREATE PROCEDURE sp_name

• 调用存储过程
  CALL sp_name ([ proc_parameter [,proc_parameter …]])
  CALL sp_name

• 说明:当无参时,可以省略"()",当有参数时,不可省略"()”

• 存储过程修改
  ALTER语句修改存储过程只能修改存储过程的注释等无关紧要的东西,不能修改存储过程体,所以要修改存储过程,方法就是删除重建

• 删除存储过程
  DROP PROCEDURE [IF EXISTS] sp_name

创建含参存储过程:包含IN参数和OUT参数
    delimiter //
    CREATE PROCEDURE deleteById(IN uid SMALLINT UNSIGNED, OUT num 
    SMALLINT UNSIGNED)
    BEGIN
    DELETE FROM students WHERE stuid >= uid;
    SELECT row_count() into num;
    END//
    delimiter ;
    call deleteById(2,@Line);
    SELECT @Line;
说明:创建存储过程deleteById,包含一个IN参数和一个OUT参数.调用时,传入删
除的ID和保存被修改的行数值的用户变量@Line,select @Line;输出被影响行数

@variable 为会话变量，有效范围为当前会话

• 存储过程和函数中可以使用流程控制来控制语句的执行
• 流程控制：
  IF：用来进行条件判断。根据是否满足条件，执行不同语句
  CASE：用来进行条件判断，可实现比IF语句更复杂的条件判断
  LOOP：重复执行特定的语句，实现一个简单的循环
  LEAVE：用于跳出循环控制
  ITERATE：跳出本次循环，然后直接进入下一次循环
  REPEAT：有条件控制的循环语句。当满足特定条件时，就会跳出循环语句
  WHILE：有条件控制的循环语句
• 汇总：目前在mysql数据库中，依据性能考虑已经不推荐使用存储过程、函数和视图给用户使用了，会影响服务器数据库性能。然而运维来说，有时可能会涉及写存储过程代替select语句。

触发器

• 触发器的执行不是由程序调用也不是手工启动，而是由事件来触发、激活从而执行。

eg:商品库中订单表中insert一条记录，那么库存表中就需要对应的更新。

• 创建触发器
  CREATE
  [DEFINER = { user | CURRENT_USER }]
  TRIGGER trigger_name
  trigger_time trigger_event
  ON tbl_name FOR EACH ROW（针对每行做触发条件）
  trigger_body
• 说明：
  trigger_name：触发器的名称
  trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发
  trigger_event：{ INSERT |UPDATE | DELETE }，触发的具体事件
  tbl_name：该触发器作用在表名

before 在触发器之前执行即使用定义的触发器的行为代替触发事件的操作。

示例：
建立以下两张表：
CREATE TABLE student_info (stu_id INT(11) NOT NULL AUTO_INCREMENT,stu_name VARCHAR(255) DEFAULT NULL,PRIMARY KEY (stu_id));

CREATE TABLE student_count (student_count INT(11) DEFAULT 0);
INS ERT INTO student_count VALUES(0);

创建触发器，在向学生表INSERT数据时，学生数增加，DELETE学生时，学生数减少
insert触发器: 
CREATE TRIGGER trigger_student_count_insert 
AFTER INSERT
ON student_info FOR EACH ROW
UPDATE student_count SET student_count=student_count+1;

delete触发器:
CREATE TRIGGER trigger_student_count_delete
AFTER DELETE
ON student_info FOR EACH ROW
UPDATE student_count SET student_count=student_count-1;

• 查看触发器
  SHOW TRIGGERS
  查询系统表information_schema.triggers的方式指定查询条件，查看指定的触发器信息。

触发器的信息存放在information_schema.triggers
> USE information_schema;
    Database changed
> SELECT * FROM triggers \G 查看所有触发器的定义
查看刚定义的触发器：
> SELECT * FROM triggers WHERE trigger_name='trigger_student_count_insert';

• 删除触发器
  DROP TRIGGER trigger_name;
• 汇总：运维实际工作中触发器一般也不使用。一般是通过（Java，php等）程序实现触发器的功能，而不是通过触发器实现。

MySQL（四）用户和权限管理

mysql用户

• 元数据数据库：mysql
  系统授权表：
  db, host, user
  columns_priv, tables_priv, procs_priv, proxies_priv
• 用户账号：
  ‘USERNAME’@‘HOST’
  @‘HOST’:
  主机名
  I P地址或Network
  通配符： % _
  示例：172.16.%.%

mysql用户管理

• 创建用户：CREATE USER
  CREATE USER ‘USERNAME’@‘HOST’ [IDENTIFIED BY ‘password’]；
  默认权限：USAGE

创建用户帮助：help create user

密码的形式：

  ①CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
  ②CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY PASSWORD '*90E462C37378CED12064BB3388827D2BA3A9B689';
    使用password()函数进行加密，一般使用第一种方式创建用户。

创建用户示例：
> create user 'test'@'192.168.38.%' identified by '12356';
查看用户列表：
> select user,host,password from mysql.user;
创建用户之后，监听端口监听，就可以基于网络远程连接：
centos6 ~]#mysql -utest -p12356 -h192.168.38.17
mysql>

    但是创建用户的默认权限：USAGE

• 用户重命名：RENAME USER
  RENAME USER old_user_name TO new_user_name;
• 删除用户：
  DROP USER ‘USERNAME’@‘HOST’

删除系统中默认的匿名用户：
> drop user ''@'localhost'  [from mysql.user];
> drop user ''@'centos7-17' [from mysql.user];

• 修改密码：
  ①SET PASSWORD FOR ‘user’@‘host’ = PASSWORD(‘password’);
  ②UPDATE mysql.user SET password=PASSWORD(‘password’) WHERE clause;
  ③mysqladmin命令方式修改密码
  #mysqladmin -u root -poldpass password ‘newpass’

> create user 'admin'@'192.168.38.%'; 创建用户时也可以不指定密码
然后结合修改密码的指令给其添加密码：
> set password for 'admin'@'192.168.38.%' = password('12356');

其中password()加密函数，默认密码加密时未加盐
>  select user,password from mysql.user where user='admin' or user='test';
+-------+-------------------------------------------+
| user  | password                                  |
+-------+-------------------------------------------+
| admin | *DC594838253636AA6E73A5366878F6F0502BDC5D |
| test  | *DC594838253636AA6E73A5366878F6F0502BDC5D |
+-------+-------------------------------------------+
==>可以看出admin和test密码相同

修改root密码：使用update修改：
> update mysql.user set password=password('336459') where user='root';

• mysql忘记管理员密码的解决办法：
  启动mysqld进程时，为其使用如下选项：
  vim /etc/my.cnf (mysql的配置文件：/etc/mysql/my.cnf也可能)
  [mysqld]
  skip-grant-tables
  skip-networking
  使用UPDATE命令修改管理员密码
  关闭mysqld进程，移除上述两个选项，重启mysqld

mysql忘记管理员密码时的解决办法：
    [mysqld]
    skip-grant-tables 
    skip-networking
重启mysql服务，然后可直接mysql登录（本地登录）。
然后使用update修改密码。

注：如果不加skip-networking
centos6 ~]#mysql -uroot  -h192.168.38.17
    远程主机也能实现mysql的免密码登录
    因此将skip-networking选项加上，安全

MySQL权限管理

• 权限类别：
  管理类
  程序类
  数据库级别
  表级别
  字段级别
• 管理类：
  CREATE TEMPORARY TABLES
  CREATE USER
  FILE
  SUPER
  SHOW DATABASES
  RELOAD
  SHUTDOWN
  REPLICATION SLAVE
  REPLICATION CLIENT
  LOCK TABLES
  PROCESS
• 程序类： FUNCTION、PROCEDURE、TRIGGER
  CREATE
  ALTER
  DROP
  EXCUTE
• 库和表级别：DATABASE、TABLE
  ALTER
  CREATE
  CREATE VIEW
  DROP
  INDEX
  SHOW VIEW
  GRANT OPTION：能将自己获得的权限转赠给其他用户
• 数据操作
  SELECT
  INSERT
  DELETE
  UPDATE
• 字段级别
  SELECT(col1,col2,…)
  UPDATE(col1,col2,…)
  INSERT(col1,col2,…)
• 所有权限
  ALL PRIVILEGES 或 ALL

授权

• 参考：https://dev.mysql.com/doc/refman/5.7/en/grant.html
• GRANT priv_type [(column_list)],… ON [object_type] priv_level TO ‘user’@‘host’ [IDENTIFIED BY ‘password’] [WITH GRANT OPTION];
  ①priv_type: ALL [PRIVILEGES]
  ②object_type:TABLE | FUNCTION | PROCEDURE
  ③priv_level: (所有库) | . | db_name. | db_name.tbl_name | tbl_name(当前库的表) | db_name.routine_name(指定库的函数,存储过程,触发器)
  ④ with_option: GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
• 回收授权
  REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] … ON [object_type] priv_level FROM user [, user] …
• 查看指定用户获得的授权
  Help SHOW GRANTS
  SHOW GRANTS FOR ‘user’@‘host’;
  HOW GRANTS FOR CURRENT_USER[()];
• 注意事项：
  MariaDB服务进程启动时会读取mysql库中所有授权表至内存
  1. GRANT或REVOKE等执行权限操作会保存于系统表中，MariaDB的服务进
     程通常会自动重读授权表，使之生效。
  2. 对于不能够或不能及时重读授权表的命令，可手动让MariaDB的服务进程
     刷新授权表：> FLUSH PRIVILEGES;让授权立即生效

授权示例：

>grant all on *.*  to admin;管理员，但是不能对其他用户授权

注意：`hellodb`.`students`  ``是反引号
>grant select(stuid,name) on `hellodb`.`students` to 'test'@'192.168.38.%';

使用远程连接：
centos6-16 ~]#mysql -utest -p12356 -h192.168.38.17
mysql> desc hellodb.students;只能看到两个字段
mysql> select * from hellodb.students;查看出错
    仅能看到授权的两个字段

    其中grant对系统中没有的用户授权相当于先创建用户，然后对用户授权。
    即创建用户和授权一起完成。一般使用这种方式创建用户并授权。
    因为创建用户的目的就是实现权限管理的。

实验：数据库的多实例

• 数据库的多实例：在一台主机上，同时启用多个数据库服务器，实验环境时可能出现此类用法。
• 每个实例实验不同的端口号

规划：3个实例，开启端口分别为3306，3307，3308 
规划的目录结构，存放对应各自mysql的数据文件、配置文件、服务启动文件、日
志文件、pid文件和socket文件.
/mysql/
├── 3306
│   ├── bin
│   ├── data
│   ├── etc
│   ├── log
│   ├── pid
│   └── socket
├── 3307
│   ├── bin
│   ├── data
│   ├── etc
│   ├── log
│   ├── pid
│   └── socket
└── 3308
    ├── bin
    ├── data
    ├── etc
    ├── log
    ├── pid
    └── socket
基于yum install mysql yum安装的mysql实现的多实例：

①创建目录：
# mkdir /mysql/330{6,7,8}/{etc,data,socket,log,bin,pid} -pv

②修改目录属主和数组
# chown -R mysql.mysql /mysql/

③生成各自的数据库文件：
# mysql_install_db --datadir=/mysql/3306/data --user=mysql
# mysql_install_db --datadir=/mysql/3307/data --user=mysql
# mysql_install_db --datadir=/mysql/3308/data --user=mysql

④生成配置文件  
/mysql/3306/etc：
# cp /etc/my.cnf /mysql/3306/etc/my.cnf
[mysqld]
port=3306
datadir=/mysql/3306/data
socket=/mysql/3306/socket/mysql.sock
symbolic-links=0

[mysqld_safe]
log-error=/mysql/3306/log/mariadb.log
pid-file=/mysql/3306/pid/mariadb.pid

/mysql/3307/etc/：
# cp /mysql/3306/etc/my.cnf /mysql/3307/etc/
# sed -i 's/3306/3307/' /mysql/3307/etc/my.cnf 

/mysql/3308/etc/：
# cp /mysql/3306/etc/my.cnf /mysql/3308/etc/
# sed -i 's/3306/3308/' /mysql/3308/etc/my.cnf

⑤生成对应的启动脚本文件：
# pstree -p | grep mysql
    ...
    |-mysqld_safe(8943)---mysqld(9085)-+-{mysqld}(9086)
由于mysqld是mysqld_safe的子进程，因此启动脚本对应启动的文件应该是
mysqld_safe进程
============================================================
vim mysqld.sh
启动和停止mysqld服务的简单脚本
#!/bin/bash

port=3306
mysql_user="root"
mysql_pwd="12346"
cmd_path="/usr/bin"
mysql_basedir="/mysql"
mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"

function_start_mysql()
{
    if [ ! -e "$mysql_sock" ];then
      printf "Starting MySQL...\n"
      ${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf  &> /dev/null  &
    else
      printf "MySQL is running...\n"
      exit
    fi
}
function_stop_mysql()
{
    if [ ! -e "$mysql_sock" ];then
       printf "MySQL is stopped...\n"
       exit
    else
       printf "Stoping MySQL...\n"
       ${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
   fi
}


function_restart_mysql()
{
    printf "Restarting MySQL...\n"
    function_stop_mysql
    sleep 2
    function_start_mysql
}

case $1 in
start)
    function_start_mysql
;;
stop)
    function_stop_mysql
;;
restart)
    function_restart_mysql
;;
*)
    printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esac
================================================================
# cp mysqld.sh /mysql/3306/bin/mysqld
# cp mysqld.sh /mysql/3307/bin/mysqld
# cp mysqld.sh /mysql/3308/bin/mysqld
修改对应mysqld文件下的port


启动对应的服务：
# /mysql/3306/bin/mysqld start
# /mysql/3307/bin/mysqld start
# /mysql/3308/bin/mysqld start

连接测试：
# mysql -S /mysql/3306/socket/mysql.sock 
# mysql -S /mysql/3307/socket/mysql.sock 
# mysql -S /mysql/3308/socket/mysql.sock 

设为开机启动：
将脚本路径放于/etc/rc.local文件中。
注：/etc/rc.local 默认在centos7上不具备执行权限