mysql数据库入门 4 +每日一问

day4

一.用户授权 :在服务器上添加新的链接用户,默认允许本机root登录

二.格式:
grant 权限(select,all) on 库名 to 用户名@“客户端地址”
identitied by “密码” //授权用户密码
with grant option ; //用授权权限,可选项

grant all on db4.* to yaya@"%"
identified by “123qqq…A”;

grant 权限列表

all 所有权限
usage 无权限
select, update,insert //个别权限
select ,update(字段值,…,字段值n) //指定字段授权

库名
. //所有库所有表
库名.* //一个库的所有表
库名.表名 //库的指定表

用户名
授权时自定义,要有识别性
存储在mysql库的user表里

客户端地址
% //所有主机
192.168.4.% //4网段内的所有主机
192.168.4.1 //1台主机
localhost //数据库服务器本机

相关命令
登录用户使用
select @@hostname; //查当前用户的主机名
select user; //显示当前用户
show grants; //显示当前用户的所有权限
show grants for 用户名@“客户端地址”; //管理员查看已有授权用户权限
set password= password(“密码”); //管理员充值授权用户链接密码
drop user 用户名@“客户端地址”; //删除授权用户(必须有管理员权限)

例子
mysql> grant all on . to mydba@"%" identified by “123qqq…A” with grant option;
mysql> show grants for mydba@"%" ;
mysql> set password for mydba@"%"=password(“123456”);
mysql> drop user mydba@"%";

mysql> grant select on db3.user to admin@“192.168.4.%” identified by “123qqq…A” ;

mysql> grant select ,update,insert,delete on db3.* to admin2@“localhost”
-> identified by “123qqq…A”;

授权库
mysql库
user表 记录已有的授权客户以及权限
db表 记录已有授权用户对数据库的访问权限
tables_pri表 记录已有授权用户对表的访问权限
columns_priv表 记录已有授权用户对字段的访问权限

例子1
mysql> desc mysql.user; //查看mysql库的user表结构

mysql> select host,user from mysql.user ; //查看哪些用户有登录权限

mysql> show grants for mydba@"%"; //查看某个客户的具体权限

例子2
mysql> desc mysql.db; //查看mysql库的db表结构

mysql> select host,user,db from mysql.db; //查看某个字段的信息

mysql> select * from mysql.db
-> where db=“db3” and user=“admin2” and host=“localhost”\G; //精准查看某个用户的某个表的权限

mysql> show grants for admin2@“localhost”; //查看某个用户对整个数据库的权限

例子3
mysql> update mysql.db set Drop_priv=“Y”
-> where db=“db3” and user=“admin2” and host=“localhost”; //为指定用户,对指定库增加权限

mysql> flush privileges; //重新加载权限表

mysql> show grants for admin2@“localhost”; //查看修改后的用户对表的权限

例子4

mysql> desc mysql.tables_priv; //查看记录用户权限的表结构

mysql> select host,user,db ,Table_name from mysql.tables_priv; //查看具体字段的数据

mysql> select * from mysql.tables_priv
-> where Table_name=“user” and db=“db3”\G; //具体查询某用户对某个表的权限 ,\G用竖型显示

mysql> show grants for admin@“192.168.4.%”; //查看来自4网段的admin用户在本数据库有什么权限

mysql> desc mysql.columns_priv; //查看字段表结构

mysql> select * from mysql.columns_priv; //查看字段表内容

mysql> grant select,update(name,password) on db3.user to admin@“localhost” identified by “123qqq…A”; //授权查询和更新db3.user表的权限给admin

mysql> select * from mysql.columns_priv; //显示admin对db3.user 里面的name 和password 字段有update权限

检验权限
[root@fay50 ~]# mysql -uadmin -p123qqq…A

mysql> update db3.user set age=18; //command denied

mysql> update db3.user set password=“F”; //更高password的值

mysql> select password from db3.user; //查看更改后的password字段

撤销权限

命令格式
mysql>revoke 权限列表 on 库名.表 from 用户名@“客户端地址” ;

例子1
mysql> select user,host from mysql.user; //查看当前有哪些地址来的用户对本数据库有权限

mysql> show grants for mydba@"%"; //查看mydba的所有权限

revoke grant option on . from mydba@"%"; //取消mydba的授权权限

mysql> show grants for mydba@"%"; //查看mydba的剩余权限

mysql> revoke grant option on . form mydba@"%"; //撤销mydba的所有权限

mysql> show grants for mydba@"%"; //查看mydba当前的权限 GRANT USAGE 没有权限

mysql> drop user mydba@"%"; //删除用户

例子2
mysql> select user,host from mysql.user; 查有哪些用户可以登录

mysql> show grants for admin@“192.168.4.%”; 查来自这个网段的admin有什么权限

mysql> revoke all on . from admin@“192.168.4.%”; 撤销这个用户的所有限制

mysql> drop user admin@“192.168.4.%”; 撤销这个用户

root密码管理
systemctl stop mysqld

vim //etc/my.cnf
skip-grant-tables
#validate_password_policy=0
#validate_password_length=6

systemctl start mysqld

数据备份和恢复
[root@fay52 ~]# mysql //免密登录

mysql> update mysql.user set authentication_string=password(“tarena”)
-> where
-> user=“root” and host=“localhost”; //重设root密码

mysql> flush privileges; //重新加载权限表

[root@fay52 ~]# mysql //改密码后无法无密码登录

[root@fay52 ~]# mysql -uroot -ptarena 成功

修改root密码
[root@fay52 ~]# mysqladmin -uroot -p password “123qqq…A” //修改root的新密码
Enter password: //输入旧密码才能修改成功

[root@fay52 ~]# mysql -uroot -p123qqq…A //登录成功

数据备份方式

物理备份和恢复
cp -r /var/lib/mysql 备份文件/mysql.bak
tar -zcvf /root/mysql/tar.gz /var/lib/mysql/*

冷备份:cp tar

逻辑备份
mysqldump 备份命令
mysql 恢复

把fay50的数据库备份并替换到Fay51

在fay50上操作

[root@fay50 ~]# systemctl stop mysqld //暂停fay50的数据库

[root@fay50 ~]# ls /var/lib/mysql //粗略查看数据库内容

[root@fay50 ~]# cp -r /var/lib/mysql /var/lib/mysql.bak //拷贝数据库文件

[root@fay50 ~]# scp -r /var/lib/mysql.bak root@192.168.4.51:/tmp //把备份数据库发送到fay51
输入fay51的root密码

在fay51上操作
[root@fay51 ~]# systemctl stop mysqld //暂停mysql数据库

[root@fay51 ~]# ls /var/lib/mysql //查看原数据库内容

[root@fay51 ~]# rm -rf /var/lib/mysql //删除所有现有数据库文件

[root@fay51 ~]# ls /var/lib/mysql //查看发现为空

[root@fay51 ~]# cp -r /tmp/mysql.bak/ /var/lib/mysql //把fay50拷贝发过来的数据复制到正式路径

[root@fay51 ~]# ls /var/lib/mysql //查看来自fay50的数据库内容

[root@fay51 ~]# ls -l /var/lib/mysql //查看拷贝过来的数据库文件权限

[root@fay51 ~]# chown -R mysql:mysql /var/lib/mysql //把数据库文件的所有者和所属组改成mysql

[root@fay51 ~]# ls -l /var/lib/mysql //在查看所有者是所属组,均是mysql

[root@fay51 ~]# systemctl start mysqld //开启数据库

[root@fay51 ~]# mysql -uroot -ptarena //用fay50一样的数据库管理员root密码登录本地数据库

mysql> show databases; //可以进行各种操作

mysql> select * from db3.user;//可以进行各种操作

mysql> use db1;//可以进行各种操作

mysql> show tables;//可以进行各种操作

数据备份策略
完全备份: 备份所有数据

增量备份:备份上次备份后,所有新产生的数据

差异备份:备份完全后,所有新产生的数据

完全备份
[root@fay50 ~]# mysqldump -uroot -ptarena -A > all.sql
完全恢复
[root@fay ~]# mysql -uroot -ptarena < /root/all.sql

例子
在fay50上
[root@fay50 ~]# mysqldump -uroot -ptarena -A > all.sql //对当前数据库的所有数据备份

[root@fay50 ~]# mysqldump -uroot -ptarena db3 > db3.sql //对当前数据库的db3库的所有数据备份

[root@fay50 ~]# scp db3.sql root@192.168.4.52:/tmp //拷贝到fay52
输入fay52的密码

在fay52上
[root@fay52 ~]# mysql -uroot -p123qqq…A db3 < /tmp/db3.sql //失败,当地数据库没有数据库db3

[root@fay52 ~]# mysql -uroot -p123qqq…A //进入当地数据库

mysql> show databases; //查看当前所有数据库

mysql> create database db3; //创建db3数据库

mysql> show databases; //查看当前所有数据库

[root@fay52 ~]# mysql -uroot -p123qqq…A db3 < /tmp/db3.sql //导入数据库内容

[root@fay52 ~]# mysql -uroot -p123qqq…A //进入数据库
%
mysql> show databses; //显示当前数据库

mysql> use db3; //进入db3数据库

mysql> show tables; //查看所有表

mysql> select * from user; //查看表的内容

完成

[root@fay50 ~]# cal
六月 2019
日 一 二 三 四 五 六
1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30

完全备份的风险(缺点): 若有删除数据,完全备份的所有版本都没有被删除的数据

增量备份
1.binlog 日志
binlog 是二进制日志,不是文本文件
是mysql服务日志文件的一种
记录查询之外(show select desc)的所有sql命令
可用于数据备份和恢复
配置mysql主从同步的必要条件

启用日志
[root@fay50 ~]# vim /etc/my.cnf //修改配置文件
[mysqld]
log-bin
server_id=50

[root@fay50 ~]# systemctl start mysqld //重启服务

[root@fay50 ~]# ls /var/lib/mysql/.-bin. //查看日志文件,暂时无

手动生成新的日志文件
方法1. systemctl restart mysqld (服务会短时间暂停,不推荐,以免数据丢失)

方法2. mysql > flush logs //在数据库里重新加载
或 [ ]# mysql -uroot -p -e ‘flush logs’ //交互式输入管理员密码
交互式输入管理员root密码:

[root@fay50 ~]# mysql -uroot -p -e ‘flush logs’
Enter password:

方法3:mysqldump --flush-logs
[root@fay50 ~]# mysqldump -uroot -ptarena --flush-logs db3 > /root/db3.sql

删除日志
mysql> purge master logs to “fay50-bin.000003”; //删除003之前的日志,不包括003

mysql> reset master; //删除0001后面的 所有日志

2.使用binlog日志恢复数据

在fay50上操作

[root@fay50 ~]# vim /etc/my.cnf

[mysqld]
#log-bin
log-bin=/mylog/db50
server_id=50

secure_file_priv="/mybak"

[root@fay50 ~]# mkdir /mylog
[root@fay50 ~]# chown mysql /mylog
[root@fay50 ~]# systemctl mysql /mylog

[root@fay50 ~]# systemctl restart mysqld

show master status; //当前新产生的日志状态

[root@fay50 ~]# mysql -uroot -p
Enter password:

mysql> show master status;

[root@fay50 ~]# scp /mylog/db50.000001 root@192.168.4.51:/tmp

在fay51上操作

[root@fay51 ~]# mysqlbinlog /tmp/db50.000001

日志恢复数据的命令
[ ]# mysqlbinlog 日志文件(如/tmp/fay50-bin.000001) | mysql -uroot -p密码

[root@fay51 ~]# mysqlbinlog /tmp/db50.000001 | mysql -uroot -p
Enter password:

[root@fay51 ~]# mysql -uroot -p
Enter password:

mysql> select count(*) from db3.user;

mysql> select name from db3.user;

分析日志
查看当前记录格式

mysql> show variables like “binlog_format”;

三种记录格式:
1.statement 报表模式
2.row 行模式
3.mixed 混合模式

[root@fay50 ~]# vim /etc/my.cnf

[mysqld]
#log-bin
binlog_format=“mixed” //更换成混合模式
log-bin=/mylog/db50
server_id=50

secure_file_priv="/mybak"

validate_password_policy=0
validate_password_length=6

[root@fay50 ~]# systemctl restart mysqld

[root@fay50 ~]# mysql -uroot -p
Enter password:

mysql> show variables like “binlog_format”;

mysql> show variables like “binlog_format”; //已经切换到0002文件,可以在新的日志下进行操作了

[root@fay50 ~]# mysqlbinlog --start-position=1463 --stop-position=2765 /mylog/db50.000002 | mysql -uroot -p123qqq…A //把user2表删除之前的数据恢复到数据库

mysql> select * from db3.user2; //可以查到恢复后的数据

每日有问(2019-06-13):
1.在mysql界面中,用户显示自身访问权限的命令是?
2.在mysql界面中,显示数据库所在的主机命令是?
3.在mysql库中,用于"记录已有授权用户对表的访问权限"是哪一个表?
4.root用户密码忘记后,有几种方法重置密码,具体操作?
5.数据备份策略主要有哪几种?





每日有问(2019-06-13):
1.在mysql界面中,用户显示自身访问权限的命令是?
show grants ;

2.在mysql界面中,显示数据库所在的主机命令是?
Select @@hostname;

3.在mysql库中,用于"记录已有授权用户对表的访问权限"是哪一个表?
mysql.tables_priv表

4.root用户密码忘记后,有几种方法重置密码,具体操作?
方法1：重置密码
systemctl stop mysqld //关闭服务
vim /etc/my.cnf //修改配置文件
[mysqld]
skip-grant-tables

systemctl restart mysqld //重启服务

[ ]#mysql //无密码登录
mysql>update mysql.user
>set authentication_string=password(“123qqq…A”) //以加密方法重置密码
>where user=“root” and
>host=“localhosts”\G;
mysql>flush privileges; //重新加载表记录

方法二：改密码
[ ]#mysqladmin -uroot -p password"新密码“
enter password: 旧密码


5.数据备份策略主要有哪几种?
物理备份 冷备
cp tar

逻辑备份
mysqldump > 文本 备份操作
mysql < 文本 恢复操作


逻辑备份数据备份策略
完全备份 ：备份所有数据
增量备份：备份上次备份后，所有新产生的数据
差异备份：完全备份后，所有新产生的数据