做软件跟做网站哪个难,网站虚拟主机空间,wordpress theme o'connor 下载,门户网站怎样做MySQL备份与恢复#xff08;重点#xff09;
一、用户管理与权限管理
☆ 用户管理
1、创建MySQL用户 注意#xff1a;MySQL中不能单纯通过用户名来说明用户#xff0c;必须要加上主机。如jack10.1.1.1 基本语法#xff1a;
mysql create user 用户名被允许连接的主…MySQL备份与恢复重点
一、用户管理与权限管理
☆ 用户管理
1、创建MySQL用户 注意MySQL中不能单纯通过用户名来说明用户必须要加上主机。如jack10.1.1.1 基本语法
mysql create user 用户名被允许连接的主机名称或主机的IP地址 identified by 用户密码;
mysql select user,host from mysql.user;
案例创建一个MySQL账号用户名tom用户密码123
mysql create user tomlocalhost identified by 123;
或
mysql create user tom127.0.0.1 identified by 123;
案例创建一个MySQL账号要求开通远程连接主机IP地址10.1.1.23用户名harry用户密码123
mysql create user harry10.1.1.23 identified by 123;
测试在IP地址为10.1.1.23的主机上
# yum install mysql -y
# mysql -h 10.1.1.10 -P 3306 -uharry -p
Enter password:123
选项说明
10.1.1.10 MySQL服务器端的IP地址 yum安装mysql代表安装的是MySQL的客户端 yum安装mysql-server代表安装的是MySQL的服务器端 案例创建一个MySQL账号要求开通远程连接主机IP的网段10.1.1.0用户名jack用户密码123
mysql create user jack10.1.1.% identified by 123;
案例创建一个MySQL账号要求开通远程连接要求面向所有主机开放用户名root用户密码123
mysql create user root% identified by 123;
2、删除MySQL用户
基本语法
mysql drop user 用户名主机名称或主机的IP地址;
特别说明
如果在删除用户时没有指定主机的名称或主机的IP地址则默认删除这个账号的所有信息。
案例删除tom这个账号
mysql drop user tomlocalhost;
案例删除jack这个账号
mysql drop user jack10.1.1.%;
案例创建两个harry账号localhost/10.1.1.23然后删除其中的某个
mysql create user harrylocalhost identified by 123;
mysql create user harry10.1.1.23 identified by 123;mysql drop user harry10.1.1.23;
扩展删除MySQL账号的另外一种方式
mysql delete from mysql.user where userroot and host%;
mysql flush privileges;
3、修改MySQL用户
特别说明MySQL用户重命名通常可以更改两部分一部分是用户的名称一部分是被允许访问的主机名称或主机的IP地址。
基本语法
mysql rename user 旧用户信息 to 新用户信息;
案例把用户root%更改为root10.1.1.%
mysql rename user root% to root10.1.1.%;
案例把tomlocalhost更名为harrylocalhost
mysql create user tomlocalhost identified by 123;
mysql rename user tomlocalhost to harrylocalhost; 扩展使用update语句更新用户信息 mysql update mysql.user set usertom,hostlocalhost where userharry and hostlocalhost;
mysql flush privileges;
☆ 权限管理
1、权限说明
所有权限说明MySQL :: MySQL 5.7 Reference Manual :: 6.2.2 Privileges Provided by MySQL
USAGE 无权限,只有登录数据库,只可以使用test或test_*数据库
ALL 所有权限以下权限为指定权限
select/update/delete/super/replication slave/reload...with grant option 选项表示允许把自己的权限授予其它用户或者从其他用户收回自己的权限 默认情况下分配权限时如果没有指定with grant option代表这个用户不能下发权限给其他用户但是这个权限分配不能超过自身权限。 2、权限保存位置了解
mysql.user 所有mysql用户的账号和密码以及用户对全库全表权限*.*
mysql.db 非mysql库的授权都保存在此(db.*)
mysql.table_priv 某库某表的授权(db.table)
mysql.columns_priv 某库某表某列的授权(db.table.col1)
mysql.procs_priv 某库存储过程的授权
3、给用户授权
基本语法
mysql grant 权限1,权限2 on 库.表 to 用户主机
mysql grant 权限(列1,列2,...) on 库.表 to 用户主机 库.表表示方法*.*代表所有数据库的所有数据表db_itheima.*代表db_itheima数据库中的所有数据表db_itheima.tb_admin代表db_itheima数据库中的tb_admin表 案例给tom账号分配db_itheima的查询权限
mysql grant select on db_itheima.* to tomlocalhost;
mysql flush privileges;
案例给tom账号分配db_itheima.tb_student数据表的权限要求只能更改age字段
mysql grant update(age) on db_itheima.tb_student to tomlocalhost;
mysql flush privileges;
案例添加一个root%账号然后分配所有权限
mysql create user root% identified by 123;
mysql grant all on *.* to root%;
mysql flush privileges;
4、查询用户权限
查询当前用户权限
mysql show grants;
查询其他用户权限
mysql show grants for 用户名称授权的主机名称或IP地址;
5、with grant option选项
with grant option选项作用代表此账号可以为其他用户下发权限但是下发的权限不能超过自身权限。
mysql grant all on *.* to amy10.1.1.% identified by 123 with grant option;
mysql grant all on *.* to harry10.1.1.% identified by 123; 如以上命令所示
amy拥有下发权限的功能而harry不具备下发权限的功能。 如果grant授权时没有with grant option选项则其无法为其他用户授权。 6、使用grant创建用户 说明5.7以后不推荐未来会被弃用 基本语法
mysql grant 权限 on 数据库.数据表 to 新用户名称授权主机名称或IP地址 identified by 用户的密码;
案例创建一个root账号主机为%授予所有权限密码为123
mysql grant all on *.* to root% identified by 123;
7、revoke回收权限
基本语法
revoke 权限 on 库.表 from 用户;撤消指定的权限
mysql revoke update on db01.tt1 from tom10.1.1.1;
撤消所有的权限
mysql revoke select on db01.* from tom10.1.1.1;
案例从tom账号中回收select权限
mysql revoke select on db_itheima.* from tomlocalhost;
mysql flush privileges;
mysql show grants for tomlocalhost;
案例从tom账号中回收update权限
mysql revoke update(age) on db_itheima.tb_student from tomlocalhost;
mysql flush privileges;
mysql show grants for tomlocalhost;
二、MySQL备份概述
1、关于数据保存你要知道
思考备份和冗余有什么区别
备份 能够防止由于机械故障以及人为误操作带来的数据丢失例如将数据库文件保存在了其它地方。
冗余 数据有多份冗余但不等备份只能防止机械故障带来的数据丢失例如主备模式、数据库集群。
2、到底要备份什么
☆ 备份什么
数据库一堆物理文件的集合日志文件(二进制日志)数据文件配置文件
① 数据文件
② 配置文件 my.cnf
③ 日志文件主要是二进制日志文件
☆ MySQL体系结构
扩展MySQL体系结构MySQL DBMS软件到底是由哪些层构成的 ☆ 存储引擎层MyISAM与InnoDB引擎
存储引擎层简单来说就是数据的存储方式。在MySQL中我们可以使用show engines查看当前数据库版本支持哪些引擎常见的数据存储引擎InnoDB、MyISAM、NDB... 常见面试题请简述MySQL的MyISAM引擎与InnoDB引擎的区别 ① MyISAM引擎擅长数据的查询支持全文索引
② InnoDB引擎Supports transactions, row-level locking, and foreign keys支持事务处理、行级锁、支持外键。
☆ 存储层数据文件是如何进行存储的
问题存储引擎到底是如何保存数据文件的
mysql create database db_itheima default charsetutf8; 提出问题我能不能不进入MySQL终端然后在命令行中直接创建一个数据库呢-e 当数据库创建完毕后查看/mysql_3306/data文件夹 db_itheima文件夹中还有一个文件db.opt存放内容为数据库的编码格式。 MyISAM引擎
mysql use db_itheima;
mysql create table tb_user(id int, name char(1)) enginemyisam default charsetutf8;
创建完成后查看db_itheima目录信息发现产生了3个文件 *.frm 框架文件定义数据表结构 *.MYI INDEX索引主要用于存放索引文件 *.MYD数据文件 InnoDB引擎
mysql use db_itheima;
mysql create table tb_user(id int, name char(1)) engineinnodb default charsetutf8;
创建完成后查看db_itheima目录信息发现产生了2个文件 *.frm 框架文件定义数据表结构 *.ibd索引文件数据文件 其实InnoDB引擎不仅仅会产生以上两个文件其在外部data目录中也会产生一个文件确切来说不能叫做产生文件而应该叫做共享文件 所以由此可知InnoDB引擎的数据备份不能简简单单的通过拷贝方式实现必须使用专业的备份工具。 ☆ 日志文件MySQL中我们需要了解哪些日志
日志类型写入日志的信息error错误日志存放数据库的启动、停止或运行时的错误信息找ERRORbinlog二进制日志数据库的所有更改操作DDL/DML/DCL不包含select或者show这类语句。 error错误日志的命令规则与存放的目录/data目录下 主机名称.err 更改错误日志的存放位置
# vim my.cnf
[mysqld]
...
log_errordata数据目录/主机名称.err或mysql.err binlog二进制日志应用场景 用于主从复制中master主服务器将二进制日志中的更改操作发送给slave从服务器从服务器执行这些更改操作是的和主服务器的更改相同。 用于数据的恢复操作 binlog二进制日志如何开启
默认binlog日志是关闭的可以通过修改配置文件完成开启如下
# vim my.cnf
[mysqld]
...
server-id10
log-bindata数据目录/binlog 当我们更改了my.cnf配置文件一定要记得重启MySQL服务器。service命令 3、备份过程须考虑的因素 必须制定详细的备份计划(策略)备份频率、时间点、周期 备份数据应该放在非数据库本地并建议有多份副本 必须做好数据恢复的演练每隔一段时间对备份的数据在测试环境中进行模拟恢复保证当出现数据灾难的时候能够及时恢复数据。 根据数据应用的场合、特点选择正确的备份工具。 数据的一致性 服务的可用性
4、备份的类型
☆ 逻辑备份 备份的是建表、建库、插入等操作所执行SQL语句DDL DML DCL。 适用于中小型数据库效率相对较低。 一般在数据库正常提供服务的前提下进行如mysqldump、mydumper、into outfile表的导出导入等。 备份实质就是把要备份的数据导出成.sql或.txt文件
☆ 物理备份 直接复制数据库文件 适用于大型数据库环境不受存储引擎的限制但不能恢复到不同的MySQL版本。 一般是在数据库彻底关闭或者不能完成正常提供服务的前提下进行的备份如tar、cp、xtrabackup数据库可以正常提供服务、lvm snapshot、rsync等 备份的实质对数据文件 配置文件 日志文件进行拷贝操作
☆ 在线热备数据冗余、AB复制、主从复制 MySQL的replication架构如M-S|M-S-S|M-M-S等 实时在线备份
5、备份工具
㈠ 社区版安装包中的备份工具
① mysqldump(逻辑备份只能全量备份
1企业版和社区版都包含
2本质上使用SQL语句描述数据库及数据并导出
3在MYISAM引擎上锁表Innodb引擎上锁行
4数据量很大时不推荐使用
② mysqlhotcopy(物理备份工具
1企业版和社区版都包含
2perl写的一个脚本本质上是使用锁表语句后再拷贝数据
3只支持MYISAM数据引擎
㈡ 企业版安装包中的备份工具
mysqlbackup
1在线备份
2增量备份
3部分备份
4在某个特定时间的一致性状态的备份
㈢ 第三方备份工具
① XtraBackup和innobackupex(物理备份
1Xtrabackup是一个对InnoDB做数据备份的工具支持在线热备份备份时不影响数据读写是商业备份工具InnoDB Hotbackup的一个很好的替代品。
2Xtrabackup有两个主要的工具xtrabackup、innobackupex
a、xtrabackup只能备份InnoDB和XtraDB两种数据表不能备份myisam类型的表。
b、innobackupex是将Xtrabackup进行封装的perl脚本所以能同时备份处理innodb和myisam的存储引擎但在处理myisam时需要加一个读锁。
② mydumper(逻辑备份备份SQL语句
多线程备份工具
https://launchpad.net/mydumper/mydumper-0.9.1.tar.gz 2015-11-06最后更新时间
6、备份方法 完全备份全备 增量备份增量备份基于全量备份
三、MySQL逻辑备份
##1、mysqldump基本备份
本质导出的是sql语句文件
优点无论是什么存储引擎都可以用mysqldump备成sql语句
缺点速度较慢,导入时可能会出现格式不兼容的突发状况.无法直接做增量备份.
提供三种级别的备份表级库级和全库级
2、mysqldump基本语法
表级别备份
mysqldump [OPTIONS] database [tables]
库级别备份
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
全库级别备份
mysqldump [OPTIONS] --all-databases [OPTIONS]
准备一些要备份的数据
mysql create database db_itheima default charsetutf8;
mysql use db_itheima;
mysql create table tb_student(id int not null auto_increment,name varchar(20),age tinyint unsigned default 0,gender enum(male,female),subject enum(ui,java,yunwei,python),primary key(id)
) engineinnodb default charsetutf8;mysql insert into tb_student values (null,刘备,33,male,java);
mysql insert into tb_student values (null,关羽,32,male,yunwei);
mysql insert into tb_student values (null,张飞,30,male,python);
mysql insert into tb_student values (null,貂蝉,18,female,ui);
mysql insert into tb_student values (null,大乔,18,female,ui);
3、mysqldump表级备份与还原
☆ 备份
案例把db_itheima数据库中的tb_student数据表进行备份
# mkdir /tmp/sqlbak
# mysqldump db_itheima tb_student /tmp/sqlbak/tb_student.sql -p
Enter password:123
☆ 还原
# mysql 数据库名称 .sql文件位置 -p
Enter password:123
或
# mysql -uroot -p
Enter password:123
mysql use db_itheima
mysql source .sql文件的位置
4、mysqldump库级备份与还原
☆ 备份
案例把db_itheima数据库进行备份
# mysqldump --databases db_itheima /tmp/sqlbak/db_itheima.sql -p
Enter password:123
☆ 还原
# mysql .sql文件位置 -p
Enter password:123
或
# mysql -uroot -p
Enter password:123
mysql source .sql文件的位置
5、mysqldump全库级备份 在MySQL中如果想使用mysqldump进行全库级备份必须开启二进制日志 开启二进制日志
# vim my.cnf
[mysqld]
...
server-id10
log-bin/mysql_3306/data/binlog# service mysql_3306 restart
mysqldump高级选项说明
常用选项描述说明--flush-logs, -F开始备份前刷新日志二进制日志binlog.000001 binlog.000002--flush-privileges备份包含mysql数据库时刷新授权表 刷新用户和授权信息--lock-all-tables, -xMyISAM一致性服务可用性针对所有库所有表--lock-tables, -l备份前锁表针对要备份的库--single-transaction适用InnoDB引擎保证一致性服务可用性--master-data2表示将二进制日志位置和文件名写入到备份文件并在dump文件中注释掉这一行--master-data1表示将二进制日志位置和文件名写入到备份文件,在dump文件中不注释这一行
--master-data参数其他说明
1恢复时会执行默认是1 2需要RELOAD privilege并必须打开二进制文件 3这个选项会自动打开--lock-all-tables关闭--lock-tables
☆ 备份
前提一定要开启二进制日志
# mysqldump --all-databases --master-data --single-transaction /tmp/sqlbak/all.sql -p
Enter password:123
☆ 还原
# mysql all.sql -p
Enter password:123
总结 mysqldump工具备份的是SQL语句故备份不需要停服务 使用备份文件恢复时要保证数据库处于运行状态 只能实现全库指定库表级别的某一时刻的备份本身不能增量备份 适用于中小型数据库
四、mysqldump binlog实现增量备份
1、核心思路
搞明白一件事到底什么是增量备份
答① 要有全量备份 ② 继续增删改数据 ③ 再次需要备份时不需要进行全量备份只需要备份binlog日志文件即可因为binlog日志记录了增删改操作的所有SQL语句
2、增量备份实验步骤
第一步先准备数据前提
第二步开启二进制然后做全量备份全库备份
第三步继续对数据库进行增删改操作
第四步突然发生了硬件故障数据库丢失了
第五步恢复全量备份导出的数据不完整可能只有90%
第六步备份二进制日志根据其信息导入剩余的10%的数据
...
完成
3、增量备份的具体实践
第一步准备数据
mysql create database db_itheima default charsetutf8;
mysql use db_itheima;
mysql create table tb_student(id int not null auto_increment,name varchar(20),age tinyint unsigned default 0,gender enum(male,female),subject enum(ui,java,yunwei,python),primary key(id)
) engineinnodb default charsetutf8;mysql insert into tb_student values (null,刘备,33,male,java);
mysql insert into tb_student values (null,关羽,32,male,yunwei);
mysql insert into tb_student values (null,张飞,30,male,python);
mysql insert into tb_student values (null,貂蝉,18,female,ui);
mysql insert into tb_student values (null,大乔,18,female,ui);
第二步开启二进制日志重启服务然后进行全库备份
# vim my.cnf
[mysqld]
...
server-id10
log-bin/mysql_3306/data/binlog# service mysql_3306 restart
# rm -rf /tmp/sqlbak/*
# mysqldump --single-transaction --flush-logs --master-data2 --all-databases /tmp/sqlbak/all.sql -p
第三步继续对数据库进行增删改操作
mysql insert into tb_student values (null,小乔,16,female,ui);
mysql delete from tb_student where id 3;
第四步突然发生了硬件故障数据库丢失了
# mysql -e drop database db_itheima; -p
Enter password:123
...故事开始了删库
第五步动员运维工程师开始进行数据恢复马上把最新的二进制文件进行备份
# cp /mysql_3306/data/binlog.000003 空格 /tmp/sqlbak/
第六步先进行全库恢复
# mysql /tmp/sqlbak/all.sql -p
Enter password:123
第七步通过binlog增量备份还原数据到100%
学会读二进制日志文件必须通过专业的工具
# mysqlbinlog /tmp/sqlbak/binlog.000003 重点找事故的临界点如drop database
确认at位置
# mysqlbinlog --start-position4 --stop-position740 /tmp/sqlbak/binlog.000003 |mysql -p
到此恢复100%数据
