MySQL 学习（三）建表及用户授权与权限撤销 、密码重置

DBA内容:
MySQL
一、搭建数据库服务器
二、MySQL数据库的基本服务
三、建表：mysql数据库类型、约束条件、键值、存储引擎
四、管理表记录：insert select update delete 匹配条件 多表查询
五、用户授权与权限撤销
六、数据备份与恢复
七、mysql主从同步
八、数据读写分离 （maxscale 软件 + 一主一从同步）
九、部署mysql集群 （MHA 软件 + mysql 主从同步）
十、mysql调优、Mysql多实例
十一、mysql视图和存储过程
十二、数据分片

NoSQL
非关系型数据库服务的使用：redis 和mongodb

一、多表查询
1.1复制表
作用：备份表 和快速建表
复制表结构的时候不需要表记录时，就加上一个不成立匹配条件，如where 1=2；
命令格式： create table 库.表 sql 查询结果；
重点：复制的内容由sql查询命令决定；不会复制源表字段的键值给新表，要自己新建；
mysql> use db3;
mysql> show tables;
mysql> create table db4.t01 select * from db3.usertab;
mysql> desc db4.t01;
mysql> create table db4.t02 select username,uid,shell from db3.usertab limit 5;
mysql> create table db4.t2 select id,username,uid,homedir from db3.usertab where 1=2；
mysql> desc db4.t2;
1.2 where 嵌套查询 ：把内层的查询结果作为外层的查询条件
命令格式：select 字段名列表 from 库.表 条件 （select 字段名列表 from 库.表 条件）；
例子：mysql> select username from db3.usertab
->where username in(select user from mysql.user where host= “localhost”);
1.3 多表查询
命令格式： select 字段列表 from 表名列表 where 匹配条件；
例子 ：mysql> create table t3
-> select username ,uid,shell ,homedir from db3.usertab limit 3;
mysql> create table t4 select username ,uid,gid from db3.usertab limit 5;
mysql> select * from t3,t4 where t3.username=t4.username;
mysql> select * from t3,t4 where t3.uid=t4.uid;
mysql> select t3.* from t3,t4 where t3.uid=t4.uid;
mysql> select t4.* from t3,t4 where t3.uid=t4.uid;
mysql> select t4.gid from t3,t4 where t3.uid=t4.uid;
mysql> select t3.username ,t4.username from t3,t4 where t3.uid=t4.uid;
mysql> select t3.username ,t4.username from t3,t4 where t3.uid=t4.uid
-> and t3.username is not null；
笛卡尔集：（ select 字段列表 from 表名列表 ）基本不用；
1.4 连接查询
左连接查询 ：当匹配条件成立时，以左表即A表为主显示查询记录；
select 字段名列表 from 表名A left join 表名B on 匹配条件；
右连接查询：当匹配条件成立时，以右表即B表为主显示查询记录；
select 字段名列表 from 表名A right join 表名B on 匹配条件；
例子：mysql> create table db4.t5
-> select username ,uid,gid,shell from db3.usertab where uid>=100 and uid<=500;
mysql> create table db4.t6
select username ,uid,gid,shell from db3.usertab where uid>=100 and uid<=500 limit 3;
mysql> select * from t5; mysql> select * from t6;
mysql> select * from t6 right join t5 on t6.uid = t5.uid;
mysql> select * from t6 left join t5 on t6.uid = t5.uid;
mysql> select t5.username,t6.username from t6 right join t5 on t6.uid=t5.uid;
mysql> select t5.username,t6.username from t6 left join t5 on t6.uid=t5.uid;
#二、安装数据库图形管理工具 phpmyadmin
2.1 管理数据库服务器的方式：命令行 、 图形（应用程序软件提供的图形界面、web页面）
2.2 在数据服务器上安装图形管理器工具phpmyadmin
准备软件的允许环境lamp/lnmp ；
#rpm -q httpd php php-mysql
#yum -y install httpd php php-mysql
#systemctl start httpd
#systemctl enable httpd
vim /var/www/html/test.php

<?php $x=mysql_connect("localhost","root","123456"); if($x){echo "ok";}else{echo "no";}; ?>

#yum -y install elinks
#elinks --dump http://localhost/test.php
#tar -xzf phpMyAdmin-2.11.11-all-languages.tar.gz -C /var/www/html/
#cd /var/www/html/
mv phpMyAdmin-2.11.11-all-languages phpmyadmin
cp config.sample.inc.php config.inc.php
安装软件：phpMyAdmin-2.11.11-all-languages.tar.gz ；
修改软件的配置文件定义管理的数据库的服务器；
#vim config.inc.php
17行 $cfg[‘blowfish_secret’] = ‘plj123’; (plj是自己随意取的)
31行 $cfg{[}^{\prime }Server{s}^{\prime }][$i][‘host’] = ‘localhost’; (指定数据库服务器的IP，本机就用localhost)
在客户端访问软件，管理数据库服务器
打开浏览器输入url地址 访问软件 ：firefox http://192.168.4.51/phpmyadmin &

#三、用户授权与权限撤销
3.0 管理员密码管理
恢复数据库管理员本机登陆密码；
#systemctl stop mysql
#vim /etc/my.cnf
([mysqld]
skip-grant-tables （添加改行，代表跳过授权表）
#validate_password_policy=0 （注释掉）
#validate_password_length=6 （注释掉）注释掉代表不需要密码登陆 )
#systemctl start mysqld
#mysql (登陆数据库)
mysql> update mysql.user
-> set authentication_string =password(“abc123”) (修改密码)
-> where host=“localhost” and user=“root”;
mysql> flush privileges; （让修改密码生效）
mysql> quit；
#vim /etc/my.cnf
([mysqld]
skip-grant-tables （去掉改行）
#validate_password_policy=0 （注释去掉）
#validate_password_length=6 （注释去掉）恢复密码登陆 )
#systemctl restart mysqld
#mysql -hlocalhost -uroot -p （输入密码abc123)
修改数据库管理员本机登陆密码；(需要指定当前登陆密码)
#mysqladmin -hlocalhost -uroot -p password "654321"
Enter password:当前登陆的密码
注意： 这里可能会报错：unable to change password; error: ‘Column count of mysql.user is wrong…Please use mysql_upgrade to fix this error.’ 这里提醒要升级一下：
解决办法：# mysql_upgrade -u root -p （密码是当前可登录的密码）
3.1 什么是用户授权：
在数据库服务器上添加连接用户，添加是可以设置用户的访问权限和连接的密码。
默认只允许数据库管理员root用户在本机登陆。默认只有数据库管理员root用户在本机登录授权才由授权权限。
3.2 用户授权命令的格式
mysql> grant 权限列表 on 数据库 to 用户名@“客户端地址” identified by “密码”
【with grant option】; (【with grant option】 是指赋予新添加的用户也有授权新用户的权限)
权限列表表示：all 所有权限 ；select ,insert ,update (字段名1，字段名2)
**数据库名的表示：. * 所有库所有表 ； 库名. :某个表 ；库名.表名 ：某张表；
用户名表示方式：连接数据库服务器时使用的名字，授权时自定义，要有标识性，
（名字存储在mysql库下的user表里）；
客户端地址的表示方式：%所有地址； 192.168.4.254 即指定ip地址 ；192.168.4.% 即网段；
pc254.tedu.cn 主机名（要求数据服务器可以解析主机名）；%.tedu.cn 域名；
indentified by “密码” 授权用户连接数据库服务器密码自定义即可，
with grant option 可选项，作用让新添加的授权用户有授权的权限。
例子：*允许客户端254主机可以使用root连接，连接密码是123456，连接后的访问权限是
对所有库所有表有完全访问权限，且有授权权限。
3.3 在客户端使用授权用户连接数据库服务器（客户端：192.168.4.52/53/54/55/56）
#ping -c 2 192.168.4.51
#which mysql
#yum -y install mariadb
#mysql -h数据库服务器ip地址 -u用户名 -p密码
在192.168.4.51主机上：mysql> grant all on . to root@“192.168.4.254”
-> idntified by “123456”
-> with grant option;
mysql> select user(); 显示连接用户名和客户端地址；
select @@hostname ；查看当前登录的主机名；
show grants; 登录用户查看自己的访问权限；
查看当前已有的授权用户 ：mysql> select user,host from mysql.user;
查看已有授权用户的访问权限：mysql> show grants for admin@"%";
授权用户连接数据库服务器修改自己的登陆密码：
SET PASSWORD=PASSWORD(“密码”) 不区分大小写
数据库管理员修改授权用户的登陆密码：
set password for 用户名@‘客户端地址’=password(“密码”)
52】#mysql -h192.168.4.51 -u root -p 123456
51】mysql> select @@hostname；
52】MySQL [(none)]> select @@hostname；
52】MySQL [(none)]> show grants;
52] MySQL [db3]> grant all on db3. to admin@"%" identified by “123456”;
(52主机授权给能连接51主机的任何主机以51的admin用户访问数据库服务器下的db库)
53] # mysql -h192.168.4.51 -u admin -p123456
53】MySQL [(none)]> select @@hostname;
53] MySQL [(none)]> show grants;
53] MySQL [(none)]> show databases;

3.4 数据库服务器使用授权库存储授权信息
51]mysql> use mysql; mysql> show tables;
information_schema 虚拟库：不占用物理存储空间，数据存储在内存里，存储已有库和表的信息。
是只读的，删除不了，没有操作权限。
mysql库 授权库：存储授权信息
user ： 存储授权用户的名及访问权限
desc mysql.user; select * from mysql.user; （查看用户权限）
db ： 存储授权用户对库的访问权限
tables_priv： 存储授权用户对表的访问权限
columns_priv： 存储授权用户对字段的访问权限
例子：52**】MySQL [(none)]> show databases; MySQL [(none)]> use home;MySQL [home]> show tables;
52】MySQL [home]> grant select,insert,update on home.gz to yaya@“localhost” identified
-> by “123456”;
51】# mysql -u yaya -p （ Enter password:123456）
mysql> select @@hostname; mysql> show databases; mysql> use home; mysql> show tables;
mysql> show grants; > select @@hostname;
52】MySQL [(none)]> use mysql; MySQL [mysql]> show tables;
MySQL [mysql]> desc user; MySQL [mysql]> select Host,db,user from db;
MySQL [mysql]> desc tables_priv; MySQL [mysql]> select Host,db,user from tables_priv;
MySQL [mysql]> select Host,db,user from columns_priv;
52】MySQL [mysql]> grant select ,update(name) on home.gz to yaya2@"%" identified by “123456”;
53】# mysql -h 192.168.4.51 -u yaya2 -p
MySQL [(none)]> show databases; > use home; > show tables; > select @@hostname;
> show grants; MySQL [home]> show grants

52] MySQL [mysql]> select Host,db,user from columns_priv;
MySQL [mysql]> select Host,db,user from tables_priv;
3.5 撤销用户权限命令的语法格式
重点：撤销的是用户的权限；用户对数据有过授权才可以撤销；
mysql > revoke 权限列表 on 数据库 from 用户名@"客户端地址"
drop user 用户名 @'客户端地址’； ------删除授权用户
例子：撤销52主机使用root用户连接时，授权权限。
51】mysql> select user,host from mysql.user;
mysql> show grants for admin@"%";
mysql> show grants for root@“192.168.4.52”;
mysql> revoke allsho on . from ‘root’@‘192.168.4.52’；
mysql> select * from mysql.user where user=“root” and host=“192.168.4.52”\G;
例子二：通过修改表记录的方式撤销用户的访问权限
mysql> show grants for root@‘192.168.4.52’;
mysql> update mysql.user
-> set select_priv=‘N’ 撤销查询权限
-> where user=‘root’ and host=‘192.168.4.52’；
mysql> flush privileges;
mysql> show grants for root@‘192.168.4.52’;
例子三： 撤销52主机使用root用户连接时的所有权限
51】mysql> revoke all on . from ‘root’@‘192.168.4.52’;
mysql> select * from mysql.user where host=‘192.168.4.52’\G; 权限都为’N’
mysql> select host,user from mysql.user; 用户仍然存在
mysql> show grants for root@‘192.168.4.52’; （USAGE 无权限）
52] MySQL [mysql]> show grants; 访问被拒绝
51】mysql> drop user root@‘192.168.4.254’; 删除授权用户
mysql> select host,user from mysql.user;

3.6 工作中如何授权
给管理者授权
给使用者授权
划重点：--------------
授权权限不是有授权权限就能授权的，要同时对Mysql库有insert权限。
且所授的权限得小于等于自身的权限。