数据库学习二天

权限管理

权限级别

1.权限级别

Global level：系统级，所有库，所有表的权限（全局）mysql.user

HOST :授权主机 localhost 只允许本地访问 %所有人都可访问（远程访问）指定IP或者网段

user :用户

authentication_string：密码

priv:权限

max_question

Database level：某个数据库中的所有表的权限

select * from mysql.db\G

Table level：库中的某个表的权限

select * from mysql.tables_priv\G

Column level：表中的某个字段的权限(保证库和表的权限 )

procs level：某个存储过程的权限

proxies level：代理服务器的权限

用户管理

登录和退出

-h 指定主机名 【默认为localhost】 ​ -P MySQL服务器端口 【默认3306】 ​ -u 指定用户名 【默认root】 ​ -p 指定登录密码 【默认为空密码】 ​ 此处mysql为指定登录的数据库  ​ -e 接SQL语句 (在脚本中使用)

创建用户

法一

create user xingdian@'%' identified by 'QianFeng@123';

create user 用户名@‘HOST主机’ identified by '密码'；

法二

grant all on *.* to 'diange'@'localhost' identified by 'QianFeng@123';//授权创建在版本8.0不能使用

grant  权限 on  库名.表名 ‘用户名’@‘主机名’ identified by '密码'；

删除用户

Drop user xingdian@'%';

drop user 用户名@‘主机名’；在数据库内

delete from mysql.user where user='diandian' AND Host='% 在配置文件内添加删除

修改密码

mysqladmin -uroot -p'123' password 'new_password'

mysqladmin -u用户名 -p'旧密码'  password '新的密码'

update mysql.user set authentication_string=password(123456) where user='diange' And Host='%';//在他的配置文件内修改他的密码

自己设置自己的密码

set password='123';

root 用户修改其他用户密码

SET PASSWORD FOR user3@'localhost'='new_password';

UPDATE mysql.user SET authentication_string=password('new_password') WHERE user='user3' AND host='localhost';///跟修改root的密码很像

查看密码策略 必须打开密码

修改配置文件vim /etc/my.cnf，添加以下参数： validate_password=on

SHOW VARIABLES LIKE 'validate_password%';显示密码策略

mysql> set global validate_password_length=4;g更改里面的参数

参数解释

validate_password_dictionary_file 指定密码验证的文件路径

validate_password_length 密码最小长度

validate_password_mixed_case_count 密码至少要包含的小写字母个数和大写字母个数

validate_password_number_count 密码至少要包含的数字个数

validate_password_policy 密码强度检查等级，对应等级为：0/LOW、1/MEDIUM、2/STRONG,默认为1

0/LOW：只检查长度

1/MEDIUM：检查长度、数字、大小写、特殊字符

2/STRONG：检查长度、数字、大小写、特殊字符字典文件

validate_password_special_char_count密码至少要包含的特殊字符数

授权

mysql> grant all on *.* to '用户名'@'主机名' identified by '密码';

all是所有的权限 （select drop create update delete...）也可以多权限用逗号隔开

第一个* 是所有的库，可以指定库

第二个*是所有的表 ，也可以制定表

刷新授权表 flush privileges

数据备份

备份数据应该放在非数据库本地，建议多个副本

备份防止机械故障和人为误操作造成的数据丢失

冗余 数据有冗余，只能防止机械故障造成的数据丢失

数据的一致性

服务的可用性

分类

逻辑备份

备份是建表，建库，插入等操作 ，适合小中型数据库，效率相对比较低 。

物理备份

直接复制数据库文件，适合大型数据库环境，不受存储引擎的限制，但不能恢复不同的数据库版本

备份方式的分类

完全备份 ：备份全部的数据

增量备份 ： 每次备份上一次备份到现在产生的新数据

差异备份 ：只备份跟完整备份不一样的

tar备份 ：在备份期间，服务不可用

备份过程：完全物理备份

得先停止数据库，备份完之后在启动数据库

[root@xingdian ~]# mkdir /backup
[root@xingdian ~]# cd /var/lib/mysql
[root@xingdian ~]# tar -zcvf /backup/`date +%F`-mysql-all.tar.gz ./*

清理环境

rm -rf /var/lib /mysql/*

导入备份数据

[root@xingdian ~]# tar -xvf /backup/2019-08-20-mysql-all.tar.gz -C /usr/lib/mysql

修改数据库配置文件的所有者和所属组

[root@xingdian ~]# chown mysql.mysql /var/lib/mysql/* -R

如果之间修改过配置文件得先将之前的修改先注释掉，等重启数据库之后在修改配置

vim /etc/my.cnf

在删除数据库的日志

rm -rf /var/log/mysql.log

在启动数据库

systemctl restart mysqld

grep 'password' /var/log/mysqld.log///获取新数据库的密码

mysqladmin -u root -p'k12zPB1r;2Ta' password 'QianFeng@123'

利用软件percona- xtrabackup 实现数据的备份

软件是开源的，支持数据的热备份，能对innoDB 和XtraDB存储引擎非阻塞的备份 ，可以对innoDB 进行热备份 ，为mysql 做增量备份

安装

[root@xingdian ~]# yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y [root@xingdian ~]# yum install percona-xtrabackup-24 -y

全备

首先创建备份目录

mkdir -p /xtrabackup/full/（目录随便）

在备份数据到备份数据的目录

innobackupex --user=root --password='QianFeng@123' /xtrabackup/full/

innobackupex --user =用户名  --password ='密码'   全备的目录

在查看备份数据

ls /xtrabackup/full/

数据丢失

[root@xingdian ~]# systemctl stop mysqld ///先停止数据库服务

[root@xingdian ~]# rm -rf /var/lib/mysql/*  ///删除mysql 的数据目录，重启数据库服务会再次获取

[root@xingdian ~]# rm -rf /var/log/mysqld.log///删除mysql 的日志

[root@xingdian ~]# rm -rf /var/log/mysql-slow/slow.log (有则删除，无则不需要操作)

恢复前的验证 看全备目录

innobackupex --apply-log /xtrabackup/full/2022-09-25_19-40-47/

innobackupex --apply-log 全备目录

恢复之前需要确认配置文件内有数据库目录指定，不然xtrabackup不知道恢复到哪里

[root@xingdian ~]# cat /etc/my.cnf

datadir=/var/lib/mysql

恢复数据

innobackupex --copy-back /xtrabackup/full/2022-09-25_19-40-47/

innobackupex --copy -back 全备目录

修改权限

chown mysql.mysql /var/lib/mysql -R

因为拷贝的数据库文件的所有者和所属组都是root ,需要我们去修改他的属主和属组，让数据库可以去使用

启动服务

systemctl start mysqld

验证:登录然后看之前删除的表或者库是否存在

mysql -u root -pQianFeng@123进入数据库

show databases;

增量备份

每次备份都是上一次备份到现在的产生的数据，增量备份对备份来说就是，可以节省存储空间，加快存储速度

对恢复数据来说就比较麻烦

在增量备份前要先进行完整备份

[root@xingdian ~]# innobackupex --user=root --password='QianFeng@123' /xtrabackup/full

创建增量备份存放数的目录

mkdir /xtrabackup/zeng -p

第一次增量备份：（周二）

[root@xingdian ~]# innobackupex --user=root --password='QianFeng@123' --incremental /xtrabackup/zeng/ --incremental-basedir=/xtrabackup/full/2022-09-25_19-40-47/（第一次备份在全备的基础上）

innobackupex --user =用户名 --password='密码' --incremental  增量备份目录 --incremental-basedir=全备目录 

第一次增量备份的数据： [root@xingdian ~]# ls /xtrabackup/zeng/ 2022-09-25_19-56-00

第二次增量备份：（周三）

[root@xingdian ~]# innobackupex --user=root --password='QianFeng@123' --incremental /xtrabackup/zeng/ --incremental-basedir=/xtrabackup/zeng/2022-09-25_19-56-00/ （第二次备份在第一次备份的基础上）

innobackupex  --user=用户名 --password=‘密码’ ---incremental 增量目录  ---incremental-basedir=第一次的备份目录  

第二次增量备份的数据： [root@xingdian ~]# ls /xtrabackup/zeng/ 2022-09-25_19-56-00 2022-09-25_19-58-12

增量备份数据恢复流程

先停止数据库

systemctl stop mysqld

删除数据

[root@xingdian ~]# rm -rf /var/lib/mysql/*

[root@xingdian ~]# rm -rf /var/log/mysqld.log

其他数据根据实际情况删除

依次重演回滚

全备回滚： [root@xingdian ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2022-09-25_19-40-47/

innobackupex --apply-log  --apply-log --redo-only 全备目录

第一次增量回滚： [root@xingdian ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2022-09-25_19-40-47/ --incremental-dir=/xtrabackup/zeng/2022-09-25_19-56-00/

innobackupex --apply-log  --apply-log --redo-only 全备目录  --incremental-dir=增量目录

第二次增量回滚： [root@xingdian ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2022-09-25_19-40-47/ --incremental-dir=/xtrabackup/zeng/2022-09-25_19-58-12/

innobackupex --apply-log  --apply-log --redo-only 全备目录  --incremental-dir=增量第二目录

根据实际增量备份的次数回滚，可以想恢复到那个时间节点就回滚到那个时间节点，所有的回滚都给全备

注意的是得在全备的基础上，将需要的时间之前的增量备份都回滚

恢复数据

[root@xingdian ~]# innobackupex --copy-back /xtrabackup/full/2022-09-25_19-40-47/

修改权限

[root@xingdian ~]# chown mysql.mysql /var/lib/mysql -R

启动数据库

[root@xingdian ~]# systemctl start mysqld

差异备份

只备份跟完整备份不一样的

在增量备份之前先进行完整备份，增量备份对备份来说就是比较占内存，存储速度慢，不过在恢复数据的时候比较方便，需要那个时间就恢复到那个时间就好

先完整备份

[root@xingdian ~]# mkdir -p /xtrabackup/full [root@xingdian ~]# innobackupex --user=root --password=QianFeng@123 /xtrabackup/full

差异备份

第一次差异备份：（周二）

[root@xingdian ~]# mkdir -p /xtrabackup/jian [root@xingdian ~]# innobackupex --user=root --password=QianFeng@123 --incremental /xtrabackup/jian --incremental-basedir=/xtrabackup/full/2022-09-25_20-10-52/

查看第一次差异备份的数据： [root@xingdian ~]# ls /xtrabackup/jian/ 2022-09-25_20-12-55

第二次差异备份：（周三）

[root@xingdian ~]# innobackupex --user=root --password=QianFeng@123 --incremental /xtrabackup/jian --incremental-basedir=/xtrabackup/full/2022-09-25_20-10-52/

查看第二次差异备份的数据： [root@xingdian ~]# ls /xtrabackup/jian/ 2022-09-25_20-12-55 2022-09-25_20-14-32

注意：后面的差异备份跟之前一样，根据需求可以继续差异备份

差异备份恢复流程：（模拟数据丢失）

停止数据库：

[root@xingdian ~]# systemctl stop mysqld

删除数据：

[root@xingdian ~]# rm -rf /var/lib/mysql/* [root@xingdian ~]# rm -rf /var/log/mysqld.log

数据回滚：

完整备份回滚： [root@xingdian ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2022-09-25_20-10-52/

差异备份回滚（根据差异备份的原理，如果恢复所有数据只需要将最后依次差异回滚） [root@xingdian ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2022-09-25_20-10-52/ --incremental-dir=/xtrabackup/jian/2022-09-25_20-14-32/

恢复数据：

[root@xingdian ~]# innobackupex --copy-back /xtrabackup/full/2022-09-25_20-10-52/

修改权限：

[root@xingdian ~]# chown mysql.mysql /var/lib/mysql -R

启动数据库：

[root@xingdian ~]# systemctl start mysqld

binlog 日志备份

日志方法备份恢复数据

日志默认存储位置 /var/lib/mysql

编译：安装目录的var下

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

log-bin=mylog

server-id=1 //做主从复制使用

mysqlbinlog /var/lib/mysql/mylog.000001 -v --base64-output=decode-rows

show binlog events in "mylog.000001";

show binlog events;