mysql用户授权及数据备份恢复

2022-03-09 01:14:28

用户授权与权限撤销

修改数据库管理员从本机登陆的密码
测试：
mysqladmin -hlocalhost -uroot -p password "新密码"
Enter password:（输入旧密码）
[root@mysql ~]# mysqladmin -hlocalhost -uroot -p password "123456789"
Enter password:

重置数据库管理员从本机登陆的密码（不记得旧密码的时候）
测试：
[root@mysql ~]# service mysql stop（先停服务）
[root@mysql ~]# service mysql start --skip-grant-table（再次起服务的时候跳过授权表）
[root@mysql ~]# mysql（直接登陆）
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |（虚拟库，不占用物理存储空间，存储已有库和表的信息）
| hydra01 |
| mysql |（授权库，存储授权信息，使用不同的表来存储）
| performance_schema |（数据库服务运行时的参数信息）
| test |（公共库，只要用户能连接数据库，默认对此库有完全权限）
+--------------------+
mysql> use mysql;
mysql> show tables;
+---------------------------+
| user |（user，授权用户的权限信息）
+---------------------------+
mysql> desc mysql.user;
db ：存储授权用户对库的权限信息
tables_priv：存储授权用户对表的权限信息
columns_priv：存储授权用户对表中字段的权限信息

授权
权限列表表示方式：
all：完全权限
usage：无权限
select,关键字(字段),关键字：授予指定的权限
关键字列表：（详情找grant权限说明）

数据库名的表示方式：
*.*：所有库所有表
库名.*：对某个库下的所有表
库名.表名：指定库下的指定表

用户名的表示方式：
授权时自定义的用户，要有标识性

客户端地址的表示方式：（不设置时，匹配网络中的所有地址）
192.168.1.1：固定地址
192.168.1.%：指定网段
%：所有地址

identified by "密码"：设置授权用户的登陆密码

with grant option：让授权用户有授权权限（要对mysql数据库有写入权限，才可以授权，授权时，授权的权限只能小于等于次用户）

查看哪些用户可以链接数据库服务器
格式：select user，host from mysql.user;
mysql> select user,name from mysql.user;

查看授权用户的权限信息
格式：
show grants for 用户名@"客户端地址";
mysql> show grants for hydra@"192.168.4.%";

命令格式：
grant 权限列表 on 数据库名 to 用户名;
grant 权限列表 on 数据库名 to 用户名;（指定的地址才可以链接）
grant 权限列表 on 数据库名 to 用户名@客户端地址 identified by "密码";（指定的地址，且输入密码才可以链接）
grant 权限列表 on 数据库名 to 用户名@客户端地址 identified by "密码" with grant option;（授权的用户，可以给其他用户授权）

示例：
grant all on *.* to hydra@192.168.1.1;
grant select on userinfo.* to hydra@192.168.1.45 identified by "123456" with grant option;
grant select,insert,update(name) on studb.t1 to hydra;

撤销用户权限
格式：
revoke 权限列表 on 数据库名 from 用户@"客户端地址";
实例：mysql> revoke insert on userinfo.t1 from hydra@"192.168.4.254";

撤销用户授权权限：(只有对库做过明确授权才可以撤销对其的权限)
格式：
revoke grant option on 数据库名 from 用户名@"客户端地址";
实例：mysql> revoke grant option on userinfo.* form hydra@"192.168.4.254";

撤销指定的权限：
格式：revoke 权限列表 on 数据库名 from 用户名@"客户端地址";
实例：mysql> revoke delete on userinfo.* form hydra@"192.168.4.254";（撤销删除权限）

————————————————————————————————————————————————————————————————————

mysql图形管理工具（phpadmin）需要lamp环境
phpMyAdmin-2.11.11-all-languages.tar.gz（把软件解压到var/www/html目录下）
[root@mysql ~]# ls /var/www/html/
hydra.php phpMyAdmin-2.11.11-all-languages
[root@mysql html]# mv phpMyAdmin-2.11.11-all-languages phpmyadmin（为了访问方便，改名）
[root@mysql html]# chown -R apache:apache phpmyadmin （为了安全，改一下所有者和所属主）

修改phpadmin配置文件
[root@mysql phpmyadmin]# cp config.sample.inc.php config.inc.php（拷贝模板文件）
[root@mysql phpmyadmin]# vim config.inc.php
$cfg['blowfish_secret'] = 'hydra'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */（随便写一个，不空就可以）
$cfg['Servers'][$i]['host'] = 'localhost';（数据库服务器地址）

客户端访问测试：
mysql> grant all on hydra01.* to hydra@"%" identified by "123";（指定一个用户访问测试）
[root@mysql ~]# firefox http://127.0.0.1/phpmyadmin（用户名hydra 密码123）

——————————————————————————————————————————————————————————————————

数据备份与恢复
数据丢失或误删时，使用备份数据恢复数据

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

经常使用的备份方式：
完整备份+差异备份
完整备份+增量备份

备份方式：
物理备份，拷贝库，表对应的源文件
格式：
cp -r /var/lib/mysql/mysql /opt/mysql.bak（拷贝文件）
tar -zcvf /opt/mysql.tar.gz /var/lib/mysql/mysql/*（压缩备份）
mysqlhotcopy -u root -p Anonymous mysql /opt/（myql自带的备份命令，但是只能备份myisam引擎的表）
物理备份，文件还原数据：
格式：
cp 备份文件数据库目录
chwon -R mysql:mysql 还原文件目录/还原文件
/etc/init.d/mysql restart（重启服务）

逻辑备份：
备份时，根据已有的库，表记录，生成对应的sql命令，把生成的sql命令保存到指定的备份文件里
格式：
mysqldump -uroot -pAnonymous 数据库名 > 目录/备份名.sql
数据库名的表示方式：
--all-databases：服务器上的所有数据
库名：服务器上的某库的所有数据
库名表名：一个库里面某张表的数据
-B 库名库名：备份指定的几个库

示例：
[root@mysql ~]# mysqldump -uroot -pAnonymous --all-databases > /opt/all.sql （备份所有）
[root@mysql ~]# mysqldump -uroot -pAnonymous hydra01 > /opt/hydra.sql（备份某库）
[root@mysql ~]# mysqldump -uroot -pAnonymous hydra01 userinfo > /opt/hydratable.sql（某库里面的某表）
[root@mysql ~]# mysqldump -uroot -pAnonymous -B hydra01 mysql > /opt/two.sql（备份多个指定库）

使用mysql命令做完整恢复
格式：
mysql -uroot -pAnonymous 库名 < 备份目录/备份文件
mysql> create database hydra01;（备份文件没有创建库的命令，先创建库名）
[root@mysql ~]# mysql -uroot -pAnonymous hydra01 < /opt/hydra.sql
[root@mysql ~]# mysql -uroot -pAnonymous < /opt/hydratable.sql （恢复多个库的时候不写库名）

自动备份数据：
计划任务+备份脚本
实例：每周一23:30对数据库上的hydra01库做完整备份，
备份文件存放在/mydb目录下，
使用库名+日期的方式给备份文件命名
测试：
[root@mysql ~]# mkdir shell（创建文件夹）
[root@mysql shell]# vim bakhydra.sh（完整备份脚本）
#!/bin/bash
day=`date +%F`
if [ ! -e /mydb ];then
mkdir /mydb
fi
mysqldump -uroot -pAnonymous hydra01 > /mydb/hydra01_$day.sql
[root@mysql shell]# chmod +x bakhydra.sh（授执行权限）
[root@mysql shell]# crontab -e（添加到计划任务）
30 23 * * 1 /shell/bakhydra.sh &> /dev/null
[root@mysql ~]# /etc/init.d/crond restart（重启计划任务）
[root@mysql ~]# chkconfig crond on（开机自启）

使用完整备份数据时，只能把数据恢复到备份时的状态
完整备份后新产生的数据无法恢复
完整备份，在备份数据的时候都会锁表（写锁）

增量备份
binlog对数据做增量备份：
binlog日志是mysql数据库服务日志文件的一种，
记录客户端连接数据库服务器后，执行的除查询外的sql命令
binlog日志又被称作二进制日志

启用binlog日志：
[root@mysql ~]# vim /etc/my.cnf
[mysqld]
log-bin
[root@mysql ~]# /etc/init.d/mysql restart
[root@mysql ~]# ls /var/lib/mysql/
mysql-bin.000001 （主机头名字命名的，当文件大于500m会生新的）
[root@mysql mysql]# mysqlbinlog mysql-bin.000001 （查看文件）

自定义binlog日志文件名和存储位置：
[root@mysql ~]# mkdir /mylog（自定义文件夹）
[root@mysql ~]# chown mysql /mylog/
[root@mysql ~]# vim /etc/my.cnf
[mysqld]
log-bin=/mylog/hydra（指定存储路径）
[root@mysql ~]# /etc/init.d/mysql restart
[root@mysql ]# ls /mylog/
hydra.000001 hydra.index
[root@mysql mylog]# mysqlbinlog hydra.000001（测试查看内容）

手动生成新的binlog日志
mysqldump -uroot -pAnonymous --flush-logs hydra > /opt/hydra.sql

删除binlog日志文件
mysql> purge master log to "日志文件名";
mysql> purge master log to "hydra.000003";

重新初始化第一个binlog日志文件
mysql> reset master;

使用binglog日志恢复数据
binlog日志记录sql命令的方法：
偏移量
时间点

恢复数据：
mysqlbinlog [选项] binlog日志文件名 | mysql -uroot -pAnonymous 数据库名
选项：
偏移量
--start-position=数字
--stop-position=数字
时间点
--start-datetime="yyyy-mm-dd hh:mm:ss"
--stop-datetime="yyyy-mm-dd hh:mm:ss"
测试：
mysql> mysqlbinlog --start-position=201 --stop-posotion=1190 hydra.000001 | mysql -uroot -pAnonymous hydra01;

安装第三方软件做增量备份
xtrabackup工具：
是一款强大的在线热备份工具
备份过程中不锁表
适合生产环境
由专业组织percona提供（改进mysql分支）
只能备份innodb和xtradb存储引擎的表
主要包含两个组件：
xtrabckup：c程序，支持innodb/xtradb
innobackupex：以perl脚本封装xtrabackup，还支持myisam

[root@mysql ~]# rpm -qa | grep -i mysql-server
MySQL-server-5.6.15-1.el6.x86_64（备份要根据mysql的版本来使用）
[root@mysql ~]# xtrabackup
xtrabackup xtrabackup_55 xtrabackup_56（56）
要求：必须先有一次完整备份，这样第二次备份时
才知道哪些数据是新生成的，备份时只备份表记录
不备份表结构

格式：
xtrabackup_56 <选项>
选项：
--backup 备份数据
--datadir=/var/lib/mysql 指定数据库目录的位置
--target-dir=目录名指定备份文件存储的目录
--prepare 准备恢复数据
--incremental-basedir=目录名增量备份数据时，指定上一次文件存储的目录
--incremental-dir=目录名增量恢复数据时，使用哪个备份目录下数据做恢复
示例：
mysql> insert into db1.t1 values(100);（写入数据）
[root@mysql ~]# xtrabackup_56 --backup --datadir=/var/lib/mysql/ --target-dir=/allbak（完整备份）
mysql> insert into db1.t1 values(200);（写入新数据）
[root@mysql ~]# xtrabackup_56 --backup --datadir=/var/lib/mysql/ --target-dir=/newdir1 --incremental-basedir=/allbak（第一次增量备份）
mysql> insert into db1.t1 values(300);（再次写入新数据）
[root@mysql ~]# xtrabackup_56 --backup --datadir=/var/lib/mysql/ --target-dir=/newdir2 --incremental-basedir=/newdir1（第二次增量备份）
mysql> insert into db1.t1 values(400);（继续写入）
[root@mysql ~]# xtrabackup_56 --backup --datadir=/var/lib/mysql/ --target-dir=/newdir3 --incremental-basedir=/newdir2（第三次备份）

事务日志文件/var/lib/mysql
ib_logfile0
ib_logfile1
xtrabackup_checkpoints （本次备份的类型和lsn的范围）
xtrabackup_logfile（lsn序列号范围对应得sql命令）
ibdata1（sql命令产生的数据信息）
日至对应的数据
备份目录/数据库名/表名同名的文件

xtrabackup恢复数据步骤
示例
准备恢复数据：
[root@mysql ~]# xtrabackup_56 --prepare --datadir=/var/lib/mysql --target-dir=/allbak
xtrabackup_56 --prepare --datadir=/var/lib/mysql --target-dir=/allbak/ --incremental-dir=/newdir1
xtrabackup_56 --prepare --datadir=/var/lib/mysql --target-dir=/allbak/ --incremental-dir=/newdir2
xtrabackup_56 --prepare --datadir=/var/lib/mysql --target-dir=/allbak/ --incremental-dir=/newdir3
把备份目录下的备份文件拷贝回对应的数据库目录下：
[root@mysql ~]# cp /allbak/db1/t1.ibd /var/lib/mysql/db1/
重启数据库服务：
[root@mysql ~]# /etc/init.d/mysql restart
验证是否恢复成功：
mysql> select * from db1.t1;

——————————————————————————————————————————————————————————————————

码农公寓

相关文章