Mysql数据库学习（四）用户授权、数据备份、binlog日志

最新推荐文章于 2021-07-19 20:06:05 发布

呆笨企鹅

最新推荐文章于 2021-07-19 20:06:05 发布

阅读量286

点赞数 1

分类专栏：数据库系统学习文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_45231541/article/details/112850487

版权

数据库系统学习专栏收录该内容

15 篇文章 0 订阅

订阅专栏

数据库系统学习
用户授权、完全备份、增量备份

主机192.168.4.50 主机192.168.4.51

1 用户授权：在数据库服务器里添加新用户给客户端连接时使用。
默认只有数据库管理root 用户在本机自己访问自己机器的数据库服务。

  1.1 用户授权 
	命令格式 grant

grant 权限列表 on 库名 to 用户名@“客户端地址”
identified by “密码” //授权用户密码
with grant option ; //有授权权限 ,可选项

  例子：50添加用户

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

mysql> grant all on . to mydba@"%" identified by “123qqq…A” with grant option;

mysql> select user,host from mysql.user;

  测试51主机测试添加的用户

host51]# mysql -h192.168.4.50 -uyaya -p123qqq…A
mysql> select user();
mysql> select @@hostname;
mysql> show grants;
mysql> create database db4;

host51]# mysql -h192.168.4.50 -umydba -p123qqq…A
mysql> grant select on db1.* to admin2@“localhost” identified by “123qqq…A”;
mysql> exit

50]# mysql -uadmin2 -p123qqq…A

用户授权补充

host50]# 创建用户plj：
mysql> grant all on db3.* to plj@“192.168.4.%” identified by “123qqq…A” with grant option;
给已有用户添加insert权限：
mysql> grant insert on mysql.* to plj@“192.168.4.%”;
给用户mydba删除授权权限：
mysql> revoke grant option on . from mydba@"%";
创建用户root主机为192.168.4.51
mysql> grant all on . to root@“192.168.4.51” identified by “666888” with grant option;
删除mysql库里user表下用户名是root主机是localhost的用户并刷新：
mysql> delete from mysql.user where user=“root” and host=“localhost”;
mysql> flush privileges;
上面创建的用户plj对db3.有grant option授权权限，在51主机用plj用户再创建用户给与db3下的权限：
host51]# mysql -h192.168.4.50 -uplj -p123qqq…A
mysql>grant all on db3. to plj88@"%" identified by “123qqq…A”;
上面创建的用户root对*.有grant option授权权限，在51主机用root用户再创建用户给与.*下的权限
host51]#mysql -h192.168.4.50 -uroot -p666888
mysql> grant all on . to root@“localhost” identified by “123456” WITH GRANT OPTION;
(如果用户只对某个库有权限，那么他为别的用户授权的权限也是仅授权于这个库，并且这个用户还要有对mysql.user有insert权限
，如果用户对所有库所有表有权限那么他授权的用户也可以对所有库所有表有权限）

     与授权用户相关命令

相关命令：
授权用户登录后修改自己的登录密码
mysql> set password=password(“abc123”);

数据库管理员重置授权用户的密码
	mysql> set password for mydba@"%"=password("123456");

   查看已有用户的访问权限
    mysql> show grants for plj@"192.168.4.%";

1.2 撤销权限 (删除已有用户的权限)
revoke 权限列表 on 数据库名 from 用户名@“客户端地址”;

50]#mysql -uroot -p密码
查看已有的授权用户 select user,host from mysql.user;
查看已有用户的访问权限 show grants for 用户名@“客户端地址”;

#删除用户的权限
mysql> revoke delete on db4.* from yaya@"%";
mysql> revoke update , drop on db4.* from yaya@"%";
mysql> revoke all on db4.* from yaya@"%";

drop user yaya@"%"; #删除用户

1.3 授权库
授权库 MySQL 存储授权信息,查看表记录可以获取用户权限；也可以通过更新记录，修改用户权限
要了解表中的表头有哪些分别存储的是什么授权信息

user表记录已有的授权用户及权限（all .）

mysql> grant all on . to admin4@"%" identified by “123456”;
mysql> update mysql.user set grant_priv=“Y” where host="%" and user=“admin4”;
mysql> flush privileges;
mysql> select * from mysql.user \G
mysql> show grants for admin4@"%";

db表记录已有授权用户对数据库的访问权限 (库名.) db3. mysql.*

desc mysql.db;

mysql> select host,user,db from mysql.db;

mysql> select * from mysql.db where db=“db3” and user=“plj88” and host="%" \G

mysql> update mysql.db set update_priv=“N” , delete_priv=“N” where db=“db3” and user=“plj88” and host="%";

mysql> flush privileges;

mysql> show grants for plj88@"%";

tables_priv表记录已有授权用户对表的访问权限（库名.表名）

desc mysql.tables_priv;

select * from mysql.tables_priv \G

mysql> update mysql.tables_priv set Table_priv=“select,delete” where Table_name=“user” and user=“admin3”;

mysql> flush privileges;

mysql> select * from mysql.tables_priv \G

mysql> show grants for admin3@"%";

mysql> select user , table_name from mysql.tables_priv where table_name=“user”;

columns_priv表记录已有授权用户对字段的访问权限（ update(name,age) ）

mysql> grant select,update(name) on db3.user to admin3@"%" identified by “123456”;
mysql> select * from columns_priv;

1.4 破解数据库管理员本机登录密码
修改主配置文件
]# vim /etc/my.cnf
[mysqld]
skip-grant-tables

#validate_password_policy=0
#validate_password_length=6
:wq

	重启mysqld服务

】#systemctl restart mysqld

            无密码登录

[root@localhost ~]# mysql
MySQL>

            修改密码 断开连接

mysql> desc mysql.user;

mysql> select host,user,authentication_string from mysql.user;

mysql> update mysql.user set authentication_string=password(“123qqq…A”)
-> where user=“root” and host=“localhost”;

mysql> flush privileges;

mysql> exit

            还原配置文件， 重启mysqld服务

[mysqld]
#skip-grant-tables
validate_password_policy=0
validate_password_length=6
:wq

]# systemctl restart mysqld

            使用修改后的密码连接服务

]# mysql -uroot -p123qqq…A

2 数据备份与恢复之mysqldump命令使用
2.1 相关概念
1 什么是数据备份？为什么要备份？怎么备份？
2 数据备份方式？物理备份逻辑备份
3 数据备份策略：
完全备份：备份所有数据
备份新数据：

                 增量备份 备份上次备份后 所有新产生的数据  
                 差异备份 备份完全备份后 所有新产生的数据

物理备份与恢复

物理备份：直接拷贝数据目录下的文件

host50]# cp -r /var/lib/mysql /root/mysql.bak
host50]# tar -zcvf /root/mysql.tar.gz /var/lib/mysql/*
host50]# scp -r /root/mysql.bak root@192.168.4.51:/opt/

  恢复：把备份的文件 再次拷贝的数据库目录下,具体步骤如下：
    停止mysqld服务
    清空数据库目录
    把备份文件拷贝的数据库目录下
    修改所有者者组用户为mysql
    启动服务
    管理员登录查看数据

在 host51 恢复数据
15 systemctl stop mysqld
16 ls /var/lib/mysql
17 rm -rf /var/lib/mysql/*
18 ls /var/lib/mysql/
19 ls /opt/mysql.bak/
20 cp -r /opt/mysql.bak/* /var/lib/mysql/

23 chown -R mysql:mysql /var/lib/mysql

25 systemctl start mysqld
24 ss -utnlp | grep 3306

27 mysql -uroot -p123456

使用MySQL服务自带的mysqldump命令对数据做完全备份和完全恢复

[root@host50 ~]# man mysqldump

完全备份数据 ]# mysqldump -uroot -p密码数据库名 > /目录名/文件名.sql
数据库名的表示方式：
完全恢复数据 ]# mysql -uroot -p密码数据库名 < /目录名/文件名.sql

host50]# 完全备份数据例子
]# mysqldump -uroot -p123456 --all-databases > /opt/allbak.sql 或者 mysqldump -uroot -p123456 -A > /opt/allbak.sql
]# mysqldump -uroot -p123456 db1 > /opt/db1.sql
]# mysqldump -uroot -p123456 -B db3 db1 > /opt/twodb.sql
]# mysqldump -uroot -p123456 db1 t19 > /opt/db1_t19.sql
]# ls /opt/*.sql

host51 完全恢复数据
注意：覆盖恢复数据。不需要停止或重启mysql服务
[root@host51 ~]# scp root@192.168.4.50:/opt/.sql /opt/
[root@host51 ~]# ls /opt/.sql
/opt/allbak.sql /opt/db1.sql /opt/db1_t19.sql /opt/twodb.sql
[root@host51 ~]#

mysql> delete from db1.t19;
[root@host51 ~]# mysql -uroot -p123456 db1 < /opt/db1_t19.sql

mysql> drop database db1;
mysql> drop database db3;
[root@host51 ~]# mysql -uroot -p123456 < /opt/twodb.sql

mysql> drop database db1;
mysql> create database db1;
[root@host51 ~]# mysql -uroot -p123456 db1 < /opt/db1.sql

mysql> drop database db1; drop database mysql;
[root@host51 ~]# mysql -uroot -p123456 < /opt/allbak.sql

（恢复数据时两个及两个以上的库在<前啥都不用写，一个库或者一个库下的表需要在<前写库名）

可以写计划任务比如每周一晚上十一点（这里要会周期性计划任务）：00 23 * * 1 /root/bak.sh 计划任务+备份脚本

工作中使用的备份策略是

完全+差异
完全+增量

3 增量备份之binlog日志的使用
3.1 binlog日志的使用
binlog日志介绍：
1.binlog也叫二进制日志
2.mysql服务日志文件中的一种
3.记录查询之外的所有sql命令
4.可用于数据备份和恢复
5.配置mysql主从同步的必要条件

	 启用日志文件

]# vim /etc/my.cnf
[mysqld]
server_id=50
log_bin
:wq
]# systemctl restart mysqld
]# mysql -uroot -p密码

mysql> show master status ; 查看当前数据库服务正在使用的日志信息
]# ls /var/lib/mysql/

	 修改日志文件的存放目录和文件名

]# vim /etc/my.cnf
[mysqld]
server_id=50
#log_bin
log_bin=/mylog/plj
:wq

]# mkdir /mylog
]# chown mysql /mylog
]# setenforce 0

]# systemctl restart mysqld

]# mysql -uroot -p密码

mysql> show master status ; 查看当前数据库服务正在使用的日志信息

]# ls /mylog/plj*

	 手动创建新的日志文件

		mysql> flush logs;

[root@host50 ~]# mysql -uroot -p123456 -e ‘flush logs’
[root@host50 ~]# mysql -uroot -p123456 -e ‘show mater status’

[root@host50 ~]# systemctl restart mysqld

[root@host50 ~]# mysqldump -uroot -p123456 --flush-logs db1 > /opt/db1.sql

	 删除已有的日志文件

		使用SQL命令删除

mysql> purge master logs to “plj.000005”; #把编号之前的日志文件删除

mysql> reset master ; #删除所有日志文件，从新创建第1个日志文件

		使用系统命令rm删除  rm -rf /mylog/*

             验证日志的功能
                执行除查询之外的命令看偏移量是否改变
                 select * from db3.user;
                 update
                 delete
                 show  databases;

	 查看日志文件的内容

3.2 使用binlog日志恢复数据
3.2.1 使用日志恢复所有数据（执行日志文件里记录的所有命令恢复数据）
mysqlbinlog /目录名/日志文件名 | mysql -uroot -p密码

例子：
host50：
230 mysql -uroot -p123456 -e ‘show master status’
229 mysql -uroot -p123456 -e ‘select count() from db3.user’
228 mysqldump -uroot -p123456 --flush-logs db3 user > /root/user.sql
230 mysql -uroot -p123456 -e ‘show master status’
231 mysql -uroot -p123456 -e ‘insert into db3.user(name,uid)values(“yy”,33)’
232 mysql -uroot -p123456 -e ‘insert into db3.user(name,uid)values(“zz”,33)’
233 mysql -uroot -p123456 -e ‘insert into db3.user(name,uid)values(“kk”,33)’
234 mysql -uroot -p123456 -e ‘show master status’
235 mysql -uroot -p123456 -e 'select count() from db3.user’
236 scp /root/user.sql root@192.168.4.51:/root/
237 scp /mylog/plj.000002 root@192.168.4.51:/root/

host51 使用日志恢复数据（使用mysqlbinlog提取历史SQL操作通过管道交给 mysql 命令执行）

54 mysql -uroot -p123456 -e ‘drop table db3.user’

55 mysql -uroot -p123456 db3 < /root/user.sql
56 mysql -uroot -p123456 -e ‘select count(*) from db3.user’

57 ls /root/plj.000002
58 mysqlbinlog /root/plj.000002 | mysql -uroot -p123456

59 mysql -uroot -p123456 -e ‘select count(*) from db3.user’

mysql>
2 修改binlog日志记录格式（支持的格式报表行混合）

mysql> show master status;

]# vim /etc/my.cnf
[mysqld]
binlog_format=“mixed”
:wq

]# systemctl restart mysqld

mysql> show master status;
mysql> show variables like “binlog_format”;

insert into db3.user(name,uid)values(“a9”,22)
insert into db3.user(name,uid)values(“a8”,22)
insert into db3.user(name,uid)values(“a18”,22)
insert into db3.user(name,uid)values(“a88”,22)
delete from db3.user

[root@host50 ~]# mysqlbinlog /mylog/plj.000004 | grep -i insert
[root@host50 ~]# mysqlbinlog /mylog/plj.000004 | grep -i delete

	3 使用日志文件恢复指定的数据

]# mysqlbinlog 选项 /目录名/文件名 | mysql -uroot -p密码
时间范围选项
起始时间结束时间
–start-datetime=“yyyy/mm/dd hh:mm:ss” --stop-datetime=“yyyy/mm/dd hh:mm:ss”

   偏移量范围选项
--start-position=偏移量的值  --stop-position=偏移量的值

insert上面第一个#加时间和#加数字就是起始的时间和偏移量的，insert下面的commit下面第一个#加时间和#加数字就是结束的时间和偏移值

[root@host50 ~]# mysql -uroot -p123456 db3 < /root/user.sql

[root@host50 ~]# mysql -uroot -p123456 -e ‘select count(*) from db3.user’

[root@host50 ~]# mysqlbinlog /mylog/plj.000004 #自己查看文件看执行命令的范围
[root@host50 ~]# mysqlbinlog --start-datetime=“2021/01/21 5:30:47” --stop-datetime=“2021/01/21 5:30:50” /mylog/plj.000004 | mysql -uroot -p123456

[root@host50 ~]# mysqlbinlog --start-position=1274 --stop-position=1420 /mylog/plj.000004 | mysql -uroot -p123456

[root@host50 ~]# mysql -uroot -p123456 -e ‘select count(*) from db3.user’

呆笨企鹅

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql数据库学习（四）用户授权、数据备份、binlog日志

数据库系统学习主机192.168.4.50 主机192.168.4.511 用户授权：在数据库服务器里添加新用户给客户端连接时使用。默认只有数据库管理root 用户在本机自己访问自己机器的数据库服务。 1.1 用户授权命令格式 grant grant 权限列表 on 库名 to 用户名@“客户端地址”identified by “密码” //授权用户密码with grant option ; //有授权权限 ,可选项例子50添加用户
复制链接

扫一扫

专栏目录