linux系统mysql数据库权限和连接管理

一、MySQL5.6与MySQL5.7的区别

1.编译安装区别

#5.7在编译安装的时候多了一个boostorg库
[root@db02 mysql-5.7.20]# yum install -y gcc gcc-c++ automake autoconf
[root@db02 mysql-5.7.20]# yum install make cmake bison-devel ncurses-devel libaio-devel
[root@db02 mysql-5.7.20]#
wget httpss://dl.bintray.com/boostorg/release/1.65.1/source/boost_1_59_0.tar.gz
#登录boost.org下载也可以
[root@db02 mysql-5.7.20]# tar xf boost_1_59_0.tar.gz -C /usr/local/
[root@db02 mysql-5.7.20]#
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.7.20 \
-DMYSQL_DATADIR=/application/mysql-5.7.20/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.7.20/tmp/mysql.sock \
#开启BOOST库
-DDOWNLOAD_BOOST=1 \
#指定boost库位置
-DWITH_BOOST=/usr/local/boost_1_59_0 \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITH_ZLIB=bundled \
-DWITH_SSL=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLE_DOWNLOADS=1 \
-DWITH_DEBUG=0 

2.初识化时的区别

#5.6版本初识化
[root@db02 ~]# cd /usr/local/mysql/scripts/
[root@db02 scripts]# ./mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

#5.7版本初识化
[root@db02 scripts]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

--initialize  		   生成一个随机密码写到一个文件
--initialize-insecure   不生成随机密码

3.mysql5.7跟mysql5.6区别

1.mysql5.7 提供json格式数据
2.mysql5.7 支持多主一从
3.做高可用方式不同

二、mysql用户权限管理

	Linux系统	mysql数据库
用户的作用	1.登录系统 2.启动进程 3.文件权限	1.登录数据库 2.管理数据库
创建用户	1.useradd 2.adduser	1.grant 2.create user root@'localhost' 3.insert
删除用户	1.userdel -r	1.drop user root@'localhost' 2.delete
修改用户	1.usermod	1.update
查看用户	1.id 2.passwd	1.select user from mysql.user;

1.在MySQL中，用户是怎么定义的

#mysql中，定义一个用户是：
	用户名@'主机域'
	
#用户名写法：
	用户名如果是字符：
		mysql> create user root@'10.0.0.1';
	用户名是数字需要加引号：
		mysql> create user '123'@'10.0.0.1';

#主机域的写法：
	localhost
	127.0.0.1
	172.16.1.51
	db01
	172.16.1.%
	172.16.1.5%      #172.16.1.50-59
	172.16.%.%
	172.%.%.%
	%
	10.0.0.0/255.255.255.0
	10.0.0.0/24					#可以设置，但是不生效

2.用户的管理

1）创建用户

mysql> create user root@'localhost';
mysql> grant all on *.* to root@'localhost' identified by '123';
mysql> insert ...

2）查看用户

mysql> select user,host from mysql.user;

3）修改用户密码

1.命令行使用mysqladmin修改密码
[root@db02 ~]# mysqladmin -uroot -p123 password 123456

2.update修改用户密码
mysql> update mysql.user set password=PASSWORD('123') where user='root' and host='localhost';

3.修改当前用户密码
mysql> set password=password('123456');

4.grant修改密码
mysql> grant all on *.* to root@'localhost' identified by '123';
mysql> flush privileges;

4）删除用户

mysql> drop user qiudao@'10.0.0.0/24';

5）忘记root用户密码怎么办

1.停止数据库
systemctl stop mysqld

2.跳过授权表和网络启动
mysqld_safe --skip-grant-tables --skip-networking &

3.登录数据库
mysql

4.修改密码
mysql> flush privileges;
mysql> grant all on *.* to root@'localhost' identified by '123';
mysql> flush privileges;

5.退出重启数据库
mysqladmin -p123 shutdown
systemctl start mysql

3.权限的管理

1）授权命令

grant all on *.* to root@'localhost' identified by '123';
grant all privileges on *.* to root@'localhost' identified by '123';

grant 				#授权命令
all privileges 		#权限（所有权限）
on 					#在...上
*.* 				#所有库.所有表
to 					#给
root@'localhost' 	 #用户名@'主机域'
identified 			#设置密码
by 					#是
'123';				#'密码'

2）所有权限

#查看用户权限
mysql> show grants for lhd@'10.0.0.0/255.255.255.0';

#回收权限
mysql> revoke drop on *.* from lhd@'10.0.0.0/255.255.255.0';

#所有权限
SELECT, INSERT, UPDATE, DELETE, CREATE, 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, DROP, GRANT

3）作用对象

#授权root@'localhost'对所有库所有表拥有所有权限，密码是123
grant all on *.* to root@'localhost' identified by '123';		#所有库所有表授权

#授权root@'localhost'对wordpress库下所有表拥有所有权限，密码是123
grant all on wordpress.* to root@'localhost' identified by '123';		#单库授权

#授权root@'localhost'对wordpress库下所有表拥有查看，插入，修改的权限，密码是123（最常用）
grant select,insert,update on wordpress.* to root@'localhost' identified by '123';	#指定权限单库授权

#授权root@'localhost'用户对mysql库下的user表拥有查看，插入，修改的权限，密码是123
grant select,insert,update on mysql.user to root@'localhost' identified by '123';	#单表授权

#在企业中，单列授权被称为脱敏
grant select(user) on mysql.user.host to root@'localhost' identified by '123';		#单列授权

4）在企业里权限设定

#开发跟你要一个数据库用户
0.你需要什么权限？
1.你要操作什么库，有没有指定表？
2.你要什么用户？
3.你从哪台主机连过来？
4.密码你有没有要求？
5.这个用户你要用多久？
6.走流程，发邮件？

#一般情况给开发的权限
grant select,update,insert on dev.* to dev@'172.16.1.50' identified by 'QiuDao@123';

#开发：你把root用户给我呗？

4.权限设置实践

1）准备数据库

#创建wordpress数据库
create database wordpress;
#使用wordpress库
use wordpress;
#创建t1、t2表
create table t1 (id int);
create table t2 (id int);

#创建blog库
create database blog;
#使用blog库
use blog;
#创建t1表
create table tb1 (id int);

2）授权

#授权wordpress@'10.0.0.5%'对于所有库所有表有查看权限，密码是123
1.grant select on *.* to wordpress@'10.0.0.5%' identified by '123';

#授权wordpress@'10.0.0.5%'对于wordpress下所有表有插入，删除，修改权限，密码是123
2.grant insert,delete,update on wordpress.* to wordpress@'10.0.0.5%' identified by '123';

#授权wordpress@'10.0.0.5%'对于wordpress下t1表所有权限，密码123
3.grant all on wordpress.t1 to wordpress@'10.0.0.5%' identified by '123';

3）提问

#有一个人，使用wordpress用户通过10.0.0.51登录数据库，请问
1.对于t1表，有哪些操作权限？
	所有权限
2.对于t2表，有哪些操作权限？
	增、删、改、查
3.对于tb1表，有哪些操作权限？
	查

4）总结

1.如果不在同一级别授权，权限是相加关系
2.但是我们不推荐在多级别定义重复权限。
3.最常用的权限设定方式是单库级别授权
	即：grant select,update,insert on dev.* to dev@'172.16.1.50' identified by 'QiuDao@123';
4.如果涉及到敏感信息，我们使用脱敏，即单列授权
	grant select(user) on mysql.user.host to root@'localhost' identified by '123';
5.查看用户权限
	show grants for 用户名@'主机域';

三、mysql连接管理

1.连接工具

1）mysql自带的连接工具 mysql

#mysql 常用的参数
-u:		#指定用户   		mysql -uroot
-p:		#指定用户的密码	  mysql -uroot -p123
-h:		#指定连接的主机	  mysql -uroot -p123 -h172.16.1.51
-S:		#指定socket文件		mysql -uroot -p123 -S /tmp/mysql.sock
-P:		#指定端口			mysql -uroot -p123 -P3307
-e:		#库外执行sql语句	   mysql -uroot -p123 -e 'show databases'

2）第三方连接工具

1.navicat
2.sqlyog
3.phpmyadmin

2.连接方式

1）socket连接

mysql -uroot -p123
mysql -uroot -p123 -S /tmp/mysql.sock

2）TCP/IP连接

mysql -uroot -p123 -h 172.16.1.51

四、mysql启动关闭流程

1.启动数据库

1.systemctl start mysql
2./etc/init.d/mysqld start
3.mysqld_safe --defaults-file=/etc/my.cnf
4.mysqld --defaults-file=/etc/my.cnf

#最终都是mysqld_safe启动，mysqld守护进程在后台

2.关闭数据库

#正经关闭数据
1.systemctl stop mysql
2./etc/init.d/mysqld stop
3.mysqldadmin -uroot -p123 shutdown

#不正经关闭数据库
1.kill -9 mysqlpid
2.killall mysqld
3.pkill mysqld
#后果：
1.如果业务量很大，数据库不会立刻关闭，只是停止服务，pid文件和socket文件还存在
2.如果业务量很大，会丢失数据

五、mysql配置管理

1.配置文件作用

1）我不知道我的程序在哪？
	--basedir=
2）我也不知道我将来启动后去哪找数据库数据？
	--datadir
3）将来我启动的时候启动信息和错误信息放在哪？
	$datadir/db01.err
4）我启动的时候sock文件pid文件放在哪？
	$datadir/mysql.pid
5）我启动，你们给了我多少内存？

2.预编译阶段

cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.20 \
-DMYSQL_DATADIR=/application/mysql-5.6.20/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.6.20/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITH_ZLIB=bundled \
-DWITH_SSL=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLE_DOWNLOADS=1 \
-DWITH_DEBUG=0 

3.配置文件

vim /etc/my.cnf
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock

1）配置文件位置

/etc/my.cnf
/etc/mysql/my.cnf
$MYSQL_HOME/my.cnf（前提是在环境变量中定义了MYSQL_HOME变量）
defaults-extra-file （类似include）
~/my.cnf

3.命令行

mysqld_safe
--skip-grant-tables 
--skip-networking
--datadir=/application/mysql/data
--basedir=/application/mysql
--defaults-file=/etc/my,cnf
--pid-file=/application/mysql/data/db01.pid
--socket=/application/mysql/data/mysql.sock
--user=mysql
--port=3306
--log-error=/application/mysql/data/db01.err