mysql恢复授权表登录_mysql用户授权及数据备份恢复

用户授权与权限撤销

修改数据库管理员从本机登陆的密码

测试：

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：存储授权用户对表中字段的权限信息

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

+------+-----------+-------------------------------------------+

| user | host | password |

+------+-----------+-------------------------------------------+

| root | localhost | *E23149D4931DA6B3ED39B9F8228919EA20C55B18 |

| root | mysql | *79753594446313D2DDBBEB704EA410F9355C7547 |

| root | 127.0.0.1 | *79753594446313D2DDBBEB704EA410F9355C7547 |

| root | ::1 | *79753594446313D2DDBBEB704EA410F9355C7547 |

+------+-----------+-------------------------------------------+

mysql> update mysql.user set password=password("Anonymous") where user="root" and host="localhost";(设置新密码)

mysql> flush privileges;(刷新)

[root@mysql ~]# service mysql restart

[root@mysql ~]# mysql -uroot -pAnonymous(测试登陆)

授权

权限列表表示方式：

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;

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