数据库应用_授权与备份(物理与逻辑)
1.用户授权; 2.完全备份; 3.增量备份.
一, 用户授权
1.用户授权
1.1.1 用户授权的由来:在数据库服务器上添加新的连接用户,但不能登录;因为默认只允许管理员root登录. 如果有非root用户需要登录,则需要先在数据库给新用户授权.
1.1.2 语法格式:
select 权限 on 库名.表名 to 用户名@”客户端地址” identified by ‘密码’ with grant options ;(斜字体为可选项,示意用户有授权权限)
1.1.3 权限列表选项
(1)赋予所有权限 all; (2)不给权限 usage; (3)个别权限 select,update,insert; (4)指定字段 select,update(字段1,字段2...字段n)
1.1.4 库名选项
(1)所有库,所有表: *.* ; (2)一个库: 库名.* ; (3)一张表: 库名.表名
1.1.5 用户名:授权时自定义,但要有标识性,存储在mysql.user中.
1.1.6 客户端地址选项
(1)所有主机: % ; (2)网段内的所有主机: 192.168.4.% ; (3)一台主机: 192.168.4.50 ; (4)本机: localhost
1.2 mysql库也叫授权库,授权用户存储在mysql.user表中.
1.3 在客户端使用授权连接到数据库服务器的语法格式: mysql -h数据库ip地址 -u授权用户 -p密码;
1.4 登录用户的应用命令
1.4.1 显示当前访问的目标数据库名称: select @@hostname; 简言之,即我访问的谁.
1.4.2 显示登录名及客户端地址: select user(); 简言之, 是谁登录的,从哪里登录的:
1.4.3 用户显示自身的访问权限有哪些: show grants;
1.4.4 数据库管理员查看其它用户的授权状态: show grants for 用户名@”客户地址”;
1.4.5 授权用户修改自身的连接密码: set password=password(“密码”);
1.4.6 管理员重置授权用户的连接密码: set password for用户名@”客户地址” =password(“新密码”);
1.4.7 管理员删除授权用户: drop user 用户名@”客户地址”;
例:
a.添加用户admin,并允许其从192.168.4网段访问db3.user,且有查询权限.
mysql> grant select on db3.user to admin@"192.168.4.%" identified by "123ppp...A";
b.添加用户admin2,并允许从本机连接db3库,且有查询,更新,插入,删除权限.
mysql> grant select,update,insert,delete on db3.* to admin2@"localhost" identified by '123qqq...A';
1.5 mysql数据库记录授权信息的容器有:
1.5.1 user表 记录有哪些用户被授权了 例: mysql> select host,user from mysql.user;
1.5.2 db表 记录授权用户对哪些数据库有访问权限 例: select host,db from mysql.user;
1.5.3 tables_priv表 记录授权用户对数据库中哪些表格有访问权限 例: select * from mysql.tables-priv where name=”user” and db=”db3” \G;
1.5.4 columns_pirv表 记录授权用户对表格中的哪些字段有访问权限 例: select count(*) from mysql.columns_priv;
1.6 删除/撤销权限
语法格式: revoke 权限 on 库名.表名 from 用户名@”客户端地址”;
例:
mysql> revoke select on *.* from admin@"192.168.4.%";
mysql> revoke all on *.* from admin@"192.168.4.%";
2. 破解并重载root密码
步骤如下:
2.1停止mysql服务器程序:
systemctl stop mysqld
2.2 跳过授权表启动mysql服务程序
#vim /etc/my.cnf
[mysqld]
skip-grant-tables //跳过授权表
:wq //保存退出
2.3 #mysql //登录数据库
2.4修改root密码
mysql>update mysql.user set authentication_string=passsword(“123qqq...A”) where user=”root” and host=”localhost”;
2.5 刷新并重载记录:flush privileges;
3. 在shell命令行修改登录密码
mysqladmin -uroot -p password “123456”
enter password:123qqq...A #输入旧密码
二,完全备份
数据备份的方式: 物理备份; 逻辑备份
备份的策略:(1)完全备份: 备份所有数据; (2)增量备份: 基于上次备份,只备份新数据; (3)差异备份: 基于完全备份,备份新数据
1.物理备份
冷备: cp, tar ...
例: 主机192.168.4.50备份数据 主机192.168.4.51 恢复数据
备份数据,在主机192.168.4.50上操作如下:
# cp -r /var/lib/mysql /opt/mysql.bak //拷贝配置文件,以作备份.
# scp -r /opt/mysql.bak root@192.168.4.51:/opt/
恢复数据,在主机192.168.4.51上操作如下:
systemctl stop mysqld
rm -rf /var/lib/mysql //清空主机4.51原来的配置文件
cp -r /opt/mysql.bak /var/lib/mysql //拷贝主机4.50上的备份数据到主机4.51
chown -R mysql:mysql /var/lib/mysql //修改配置文件的归属权限为mysql
2. 逻辑备份
2.1 语法格式: mysqldump -uroot -p密码 库名 > 目录/xxx.sql
库名的表示方式有:
2.1.1 --all-databases或-A //示意备份所有库 例:mysqldump -uroot -p123456 --all-databases或A > /opt/db3_bak.sql
2.1.2 数据库名 //备份单个库 例:mysqldump -uroot -p123456 db3 > /opt/db3_bak.sql
2.1.3 数据名 表名 //备份单个表 例:mysqldump -uroot -p123456 db3.user > /opt/db3_bak.sql
2.1.4 -B 数据库1 数据库2... //备份多个库 例:mysqldump -uroot -p123456 -B db3 db4 db5 > /opt/db3_bak.sql
2.2 Mysql //恢复命令
语法格式: #mysql -uroot -p密码 库名 < 目录/xxx.sql
例: mysql -uroot -pliusingbon db3 < allbak.sql //执行恢复之前,先建个空库db3
2.3 注意事项
无论是备份还是恢复,都必须验证用户权限.
三, 增量备份
增量备份是利用binlog日志来实现的.
1.binlog日志
1.1 概念
Binlog日志,也被称为二进制日志,mysql服务日志的一种,记录除查询之外的所有sql命令,可用于数据备份和恢复,是mysql主从同步的必要条件.
1.2 日志的启用
#vim /etc/my.cnf
[mysqld]
log-bin //启用binlog日志
server_id=50m //日志最大50m
:wq //保存退出
#systemclt restart mysqld //重启服务
1.4 手动生成日志
#systemctl restart mysqld
mysql>flush logs 或mysql -uroot -p密码 -e ‘flush log’
例: mysqldump -uroot -p123456 --flush-logs db3 > /db3.sql
1.5 清理日志
1.5.1 删除指定编号之前的binlog日志
语法格式: mysql>purge master logs to “binlog文件名”
1.5.2 删除所有binlog日志,重建新日志
语法格式: mysql>reset master;
1.6 启用日志时,自定义存储目录/文件名
例:
vim /etc/my.cnf
log-bin=/mylog/db50 //指定目录/文件名
:wq 保存退出
#mkdir -p /mylog/db50
#chown -R mysql:mysql /mylog/db50 //修改目录的归属权限
#systemctl restart mysqld
2. 使用binlog恢复数据
在研究恢复数据之前,我们需要先了解日志的分析.
2.1 分析日志
2.1.1 查看日志当前记录格式
binlog_format=”名称”,其中名称有3种记录方式:statment报表模式; row行模式;mixed混合模式.
mysql> show variables like "binlog_format";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.01 sec)
2.1.2 修改日志记录格式
Vim /etc/my.cnf
binlog_format="mixed" //修改binlog记录模式为混合模式mixed
:wq 保存退出
#systemctl restart mysqld
#mysql -uroot -p123456
mysql> show variables like “binlog_format”; 查看修改后的记录
mysql> show master status;
#mysqlbinlog /mylog/db50.000002 |grep -i insert 查看日志中使用过的命令
2.1.3 查看日志内容
语法格式: mysqlbinlog 选项 binlog日志文件名
mysqlbinlog --start-position=1087 --stop-position=2094 /tmp/db50.0000002
常规的选项见下表:
2.2 恢复数据
思路:使用mysqlbinlog提取历史SQL操作命令, 而后管道给mysql命令执行.
语法格式: mysqlbinlog 日志文件|mysql -uroot -p密码
例: 在主机192.168.4.50上备份并生成binlog日志; 在主机192.168.4.51上恢复数据.
主机192.168.4.50上操作:
#mysqldump -uroot -p123456 db3 user > /root/db3_user.sql 备份
mysql>show master status; 查看binlog信息
mysql>insert into db3.user(name) values(“xixi”); 插入新数据
mysql>insert into db3.user(name) values(“haha”); 插入新数据
mysql>insert into db3.user(name) values(“hehe”); 插入新数据
mysql>show master status; 再次查看binlog信息
#scp /mylog/db50.000001 root@192.168.4.51:/tmp/ 上传数据到192.168.4.51上,以便于恢复数据的验证
主机192.168.4.51上操作:
#mysqlbinlog db50.000001|mysql -uroot -p123456 //使用编号为000001的binlog日志文件,恢复数据
mysql>select count(*) from db3.user;
mysql>select name from db3.user;
结束.
