本文详细介绍数据库备份的方法，包括mysqldump、复制目录文件、mysqlhotcopy等，并讲解数据还原流程，探讨数据库日志的作用及二进制日志的管理。
数据库备份恢复和常用语句
-来源于视频教学
目录
常用语句
备份数据库
方法一：mysqldump备份
方法二：复制数据库中的目录文件
方法三：mysqlhotcopy备份
数据还原
数据库日志
常用语句
show databases; 查看数据;表相同(show tables;)
create database DBNAME; 创建数据库;
drop databale DBNAME; 删除数据库;表相同
use DBNAME; 使用数据库
show tables; 查看使用数据库中的表
create table if not exists stu(
年龄 varchar(20) not null,
姓名 varchar(20) not null,
性别 varchar(20) not null,
primary key ( 年龄 )
)engine=InnoDB default charset=utf8;
desc stu; 查看stu这个表的表结构
show create tables stu \G 查看创建时的语句
备注：
create table if not exeist `aaa` ：`为反引号，1键。左边若aaa不存在则创建表 aaa。存在则直接写入内容。
姓名` varchar(20) not null， ：表结构， 分别是 字段名+数据类型(长度为20)+值
primary key ( `姓名` ) ：姓名为主键，其他为外键。----主键只可能有一个，但一个主键可以由多个字段组成。
CHARSET=utf8; ：CHARSET 设置字符编码
insert into stu values ('18','cyx','man'); 在表stu中写入相应的数据
select * from stu; 查看表stu的内容
select count(*) from stu; 查看表stu中内容数量
备份数据库
方法一：mysqldump(支持MyISAM引擎和innodb引擎)
1、备份单个数据库的其中一个表
mysqldump -uroot -p aaa stu > /root/stu.bak.sql （将数据库aaa中的表stu备份到root下命名为stu.bak.sql。可查看版本，注释，备份日期等）
2、备份一个或多个数据库：
mysqldump -u username -p --databases dbname1 dbname2 > /root/stu.bak.sql (username---例如：msyqldump -u root)
3、备份所有数据库
mysqldump -u username -p --all-databases > alldb.bak.sql
注意：如下图，若存在stu，则删除，再重新创建。即覆盖内容的意思
方法二：
复制数据库中的目录文件（省略）
优点：速度快
缺点：必须停止服务器，确保数据一致性
只对MyISAM存储引擎的表使用
对数据库版本有要求
方法三：（mysqlhotcopy只支持MyISAM引擎）
使用工具mysqlhotcopy进行备份(需要下载)
-- 若备份时不能停止mairadb服务器，可使用nysqlhotcopy工具备份。比mysqldump 更快
-- 工作原理：mysqlhotcopy工具是一个Perl脚本，主要在linux环境使用，mysqlhotcopy 工具使用LOCK TABLES(锁表),FLUSH TABLES(刷新表)和cp进行快速备份。
-- 即先读锁操作，再FLUSH TABLES将内存中的数据写到硬盘上的数据库。最后把需要 备份的数据库文件复制到目标目录
-- 命令格式
Mysqlhotcopy [option选项] dbname1 dbname2 dbname… backDir/
BackDir代表备份到哪个目录（
‘--allowold’ :若备份目录下存在相同的备份文件，则将旧文件加上——old的标识
‘--keepold’ :若备份目录下存在相同的备份文件，不删除就的备份文件，而是改名
‘--flushlog’ :本次备份完成将对数据库的更新记录到日志）
mysqlhotcopy对比mysqldump
1.mysqlhotcopy
- 优点
- 速度快 - 缺点
- 只支持MyISAM引擎
- 需要安装perl语言及配置支持环境，因为hotcopy由perl写成
- 选项参数较少，功能不够强大，比如不提供–where选项，无法选取某部分数据备份
- 只能运行在数据库目录所在的机器上
2.mysqldump
- 优点
- 支持MyISAM和innodb引擎
- 只要装了mysql就可以用，无需别的操作，不存在平台兼容问题
- 参数较多，功能强大，可以备份整个数据库，也可以备份单个表，还可以备份单个表的部分数据
- 可生成多种文件，还可以用于从一个MySQL服务器向另一个服务器复制数据 - 缺点
- 速度没有那么快
数据还原
-
使用mysql命令还原
-mysqldump工具将数据库中的数据备份成一个文件，后最通常为.sql
-还原通过mysql进行还原
-备份一般包含create和insert语句mysql -u username -p [dbname] < back.sql
注意：如果使用–all-databases参数备份了所有数据库。还原时不需要指定数据库
- 直接复制数据库目录
-通过直接复制目录方式还原必须保持两个mariadb的数据库主版必须相同
-只对MyISAM类型表生效
-针对Innodb表不能直接复制，mysqlhotcopy工具也是这种方式还原
-chown –R mysql:mysql dataDIR(修改属组)
数据库迁移
-
数据库迁移指的是数据库从一个系统移动到另一个系统上
-
案例：
-linux操做系统下的mysql向Mairadb迁移
-卸载原有的mysql相关组件
-安装mariadb软件包
-执行升级命令mysql_upgrade –p
-升级完成后查看数据库版本mysql -uroot -p -e "show global variables like 'version'"
数据库日志(用途：恢复、复制、审计)
1、 二进制日志（变更日志）
-
主要记录数据库的变化情况，可以查询mariadb的变化情况
-
binlog主要作用
-mysql的主从复制
-需要在master段开启binlog，将二进制日志发送给slave端达到主从数据一致
-数据恢复 -
启动和设置二进制
-通过/etc/my.cnf开启二进制日志
-需要将log-bin选项加入到my.cnf的[mysqld]组中(重启数据库)
-
重启一次多一个mysql-bin文件
-
查看binlog日志是否开启
show variables like 'log%'\G
注意：二进制日志与数据库的数据文件最好不要放在同一个磁盘上防止磁盘被损坏，无法恢复。
-
查看二进制日志
-使用二进制格式可以查看部分信息
-使用mysqlbinlog命令查看
-语法格式：mysqlbinlog filename.number
-mysqlbinlog命令将在当前文件下查找指定日志，否则将无法查找
mysqlbinlog /var/lib/mysql/mysql-bin.000001
-
查看所有日志列表
show master logs; -
查看master状态，最后一个日志
show master status; -
刷新日志，也会产生一个新的二进制日志
flush logs;
-
更方便的查询命令
-pos点show binlog events [ in 'bin_log_name']\G （不指定从首个开始。\G： 格式化数据。例： show binlog events in 'mysql-bin.000001'\G -show binlog events\G）-FROM pos : 指定从pos起始查询（不指定从首个开始）
show binlog events in 'mysql-bin.000002' from 245\G (指定查询 mysql-bin.000002文件，pos从245开始)-LIMIT [ offset, ] : 偏移量（不指定为0）
show binlog events in 'mysql-bin.000002' from 4 limit 1,5\G 指定查询mysql-bin.000002文件，pos从4开始，查询5条，偏移量为1，即隔1个，查询一个
2、 删除二进制日志。
1、删除所有二进制日志
-
使用reset master 语句删除所有二进制日志
reset master
2、根据编号删除二进制日志
-
使用purge master logs to 语句删除指定的二进制日志编号之前的日志
purge master logs to 'mysql-bin.000003'; (删除000003之前的) purge master logs before '2019-12-21 15:39:21'; （删除这个时间之前的 简化日期：date '+%Y-%m-%d %H:%M:%S'） reset master;（重置二进制日志，会重新记录一个新的的二进制日志）
3、二进制还原数据库
-
还原过程
-首先使用最近的备份文件来还原数据库
-备份之后数据库可能进行一些更新，可以使用二进制日志还原
-二进制还原命令mysqlbinlog filename.number | mysql –uroot –p 原理：理由mysqlbinlog语句读出二进制然后用mysql语句进行还原 注意：使用mysqlbinlog命令还原时，必须时编号小的先还原-
案例
-创建cyx库，创建boy表
例：MariaDB [cyx]> create table boy( -> id int(8) unsigned not null auto_increment, -> name varchar(10) not null, -> sex enum('m','w') not null default 'm', -> age int(10) unsigned not null, -> classid char(6) default null, -> primary key(id) -> )default charset=utf8;-写入内容：
insert into member values(null,'a','m','8','c1'),(null,'b','w','9','c2')-创建任务，每天00:00备份
crontab –e （crontab用法在最下面）-写入内容
0 0 * * * /usr/bin/mysqldump -u root -p123456 -B -F -R -x --master0-data=2 cyx| gzip > /root/ops_$(date +%F).sql.gz (注意是小写x，大写恢复时会报错)
-
-查看内容：
crontab -l
-手动备份：
mysqldump -uroot -p123456 -B -F -R -x --master-data=2 cyx | gzip > /root/ops_$(date +%F).sql.gz
注释：
-B 指定数据库
-F 刷新日志
-R 备份存储过程
-x 锁表
--master-Data 在备份语句添加change master 语句以及binlog文件位置 信息
-更新数据
update boy set name='cyx' where id=1;
**-并且删除了数据库cyx;**
drop database cyx;
开始恢复
1、
cp -v mysql-bin.000004 /root/
flush logs;
- 读取log日志分析
show binlog events in 'mysql-bin.000004'\G
- 解压之前手动备份的数据
gzip -d ops_2019-12-21.sql.gz
mysql -uroot -p123456 -v < cyx_2019-12-21.sql
恢复到备份这个数据时
2、
mysqlbinlog --stop-position=964 --database=cyx /var/lib/mysql/mysql-bin.000004 | mysql -uroot -p -v cyx
contab注释及用法
crontab （创建定时任务命令）
-
注释：* * * * * /绝对路径/执行操作
例：1 * * * * /etc/cyx.sh 意思就是每天00:01 执行一次etc下的cyx.sh* * * * * 分钟 小时 日期（1-30） 月份 星期几（0-7，0或7为星期天）
例1：让我的zabbix 214的入口流量增加 延迟是zabbix的反应时间
26 17 * * * /usr/bin/ping -c 20 -s 40000 214agent
扫一扫
1776
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
