下面讲讲关于MySQL如何实现数据备份与恢复,文字的奥妙在于贴近主题相关。所以,闲话就不谈了,我们直接看下文吧,相信看完MySQL如何实现数据备份与恢复这篇文章你一定会有所受益。
创新互联建站成立与2013年,先为怀宁等服务建站,怀宁等地企业,进行企业商务咨询服务。为怀宁企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。
常见的MySQL管工具
mysql 命令行 跨平台 MySQL官方bundle包自带
MySQL-Workbench 图形 跨平台 MySQL官方提供
MySQL-Front 图形 Windows 开源,轻量级客户端软件
phpMyAdmin 浏览器 跨平台 开源,需LAMP平台
Navicat 图形 Windows 专业、功能强大、商业版
PhpMyAdmin部署思路:
1.安装httpd、mysql、php-mysql及相关包
2.启动httpd服务程序
3.解压phpMyAdmin包,部署到网站目录
4.配置config.inc.php,指定MySQL主机地址
5.创建授权用户
6.浏览器访问、登录使用
yum -y install httpd php php-mysql
tar -zxf phpMyAdmin-2.11.11-all-languages.tar.gz -C /var/www/html/
cd /var/www/html/
mv phpMyAdmin-2.11.11-all-languages/ phpmyadmin
chown -R apache:apache phpmyadmin/
cp phpmyadmin/config.sample.inc.php phpmysdmin/config.inc.php
vim /var/www/html/phpmyadmin/config.inc.php
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['blowfish_secret'] = '123456';
mysql>create database bbsdb;
mysql>grant all on bbsdb.* to admin@'localhost' identified by '654321';
systemclt start httpd
firefox http://localhost/phpmyadmin
MySQL数据备份与恢复
数据备份方式:
1.物理备份,冷备份:cp 、 tar 、 ....
例:物理备份与恢复
备份操作格式:
cp -rp /var/lib/mysql/数据库 备份目录/文件名
tar -zcvf xxx.tar.gz /var/lib/mysql/数据库/*
恢复操作格式:
cp -rp 备份目录/文件名 /var/lib/mysql/(默认存储路径)
tar -zxvf xxx.tar.gz -C /var/lib/mysql/数据库名/
2.逻辑备份,mysqldump 、mysql
原理:执行备份时,根据已有的库和表生成对应SQL命令,把生成的SQL命令存储到指定的备份文件里。
备份策略:
完全备份:备份所有数据(一台云服务器,一个库,一张表)
增量备份:备份自上一次备份(包含完全备份、差异备份、增量备份)之后有变化的数据。
差异备份:备份自上一次完全备份之后有变化的数据。
例:逻辑备份及恢复
完全备份操作格式:
mysqldump -u用户名 -p密码 源库名 > 路径/xxx.sql
完全备份的恢复操作格式:
mysql -u用户名 -p密码 目标库名 < 路径/xxx.sql
库名的表示方式:
--all-databases 所有库
库名 指定的单个库
库名.表名 指定库的指定表
-B 库名1 库名2 .. 备份多个库
注意事项:
无论备份还是恢复,都要验证用户及权限。
例:
将所有的库备份为mysql-all.sql文件
mysqldump -u root -p 123456 --all-databases > alldb.sql
将userdb库备份为userdb.sql文件
mysqldump -u root -p 123456 userdb > userdb.sql
将备份文件userdb.sql恢复到userdb3库
mysql>create databases userdb3;
mysql -u roo -p 123456 userdb3 < userdb.sql
实时增量备份
binlog日志
类型:二进制日志,用途:记录所有更改数据的操作,
配置:
log_bin[=dir/name]
server_id=数字
max_binlog_size=数字m
启用binlog日志
采用binlog日志的好处
1.记录除查询之外的所有SQL命令。
2.用于数据恢复。
3.配置mysql主从同步的必要条件。
例:
vim /etc/my.cnf
[mysqld]
....
log_bin //启用binlog日志
server_id=100 //指定id值,id号不能重复(1-255)范围。
systemctl restart mysqld
binlog相关文件
默认日志文件名:
主机名-bin.index //记录已有的binlog日志文件名
主机名-bin.000001 //第1个二进制日志(达到500M存去下一条纪录)
主机名-bin.000002 //第2份二进制日志
....
手动生成新的日志文件
1.重启mysql服务
2.执行SQL操作 mysql > flush logs;(flush logs,切换到下一个binlog日志文件)
3.mysqldump --flush-logs
4.mysql -uroot -p密码 -e 'flush logs'
清理binlog日志
删除早于指定版本的binlog日志:
purge master logs to 'binlog文件名';
删除所有binlog日志,重建新日志:
reset master;
例:
mysql>purge master logs to 'mysql-bin.000003';
mysql>reset master;
分析binlog日志
查看日志当前记录格式:
mysql>show variables like 'binlog_format';
修改日志记录格式:
vim /etc/my.cnf
[mysqld]
....
binlog_format="mixed" 设置日志文件的记录格式
systemctl restart mysqld
三种记录格式:
1.statement: 每一条修改数据的sql命令都会记录在binlog日志中。
2.row: 不记录sql语句上下文相关信息,仅保存哪条记录被修改。
3.mixed: 是以上两种格式的混合使用。
binlog日志文件记录sql命令的方式:
1.时间点
2.pos点(偏移量)
查看有哪些bilog日志:
mysql>show master logs;
使用mysqlbinlog工具
格式:mysqlbinlog [选项] binlog日志文件名
常用选项:
1.时间点:
--start-datetime="yyyy-mm-dd hh:mm:ss"
--stop-datetime="yyyy-mm-dd hh:mm:ss"
2.pos点:
--start-position=数字
--stop-position=数字
例: 查看从2017年1月2日15:30开始的更改操作 mysqlbinlob --start-datetime="2017-01-01 15:30" /var/lib/mysql-bin.000001 .... at 318
解释:
server id 1 : 数据库主机的服务号;
end_log_pos 796 : sql结束时的pos节点
thread_id=11 : 线程号
binlog恢复数据
方式一:
基本思路:
1.使用mysqlbinlog提取历史SQL操作
2.通过管道交给mysql命令执行
例: 恢复第1份binlog日志的部分信息 mysqlbinlog --base64-output=decode-rows -v /var/lib/mysql/mysql-bin.000001 mysqlbinlog --start-position=296 --stop-position=1073 /var/lib/mysql-bin.000001 |mysql -uroot -p123456
方式二:
命令格式:
mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
参数解释:
IN 'log_name' :指定要查询的binlog文件名(不指定就是第一个binlog文件)
FROM pos :指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
LIMIT [offset,] :偏移量(不指定就是0)
row_count :查询总条数(不指定就是所有行)
例:
mysql>show binlog events in 'mysql-bin.000002'\G;
MySQL备份工具
物理备份缺点:
1.跨平台性差
2.备份时间长、冗余备份、浪费存储空间
mysqldump备份缺点:
1.效率较低,备份和还原速度慢。
2.备份过程中,数据插入和更新操作会被挂起。
XtraBackup备份工具:
1.备份过程中不锁库表,适合生产环境。
2.由专业组织Percona提供(改进MySQL分支)。
主要含两个组件:
1.xtrabackup:C程序,支持InnoDB/XtraDB
2.innobackupex:以Perl脚本封装xtrabackup,还支持MyISAM
支持事务和事务回滚,要求存储引擎为innodb
事务日志文件:
ibdata
LSN 日志序列号
ib_logfile0 //SQL命令
ib_loggile1
....
安装XtraBackup
yum -y install perl-Digest-MD5.x86_64 rsync perl-DBD-MySQL
rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm
rpm -ivh percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
rpm -ql percona-xtrabackup-24
/usr/bin/innobackupex //备份innodb、xtrdb、myisam引擎的表
/usr/bin/xbcloud
....
/usr/bin/xtrabackup //备份innodb、xtrdb引擎的表
....
innobackupex基本选项 --host //主机名 --user //用户名 --port //端口号 --password //密码 --databases //数据库名(单个库:databases="库名",多个库:databases="库1 库2",单个表:databases="库.表") --no-timestamp //不用日期命名备份文件存储的子目录名 --redo-only //日志回滚合并(最后一次增量备份日志回滚不需要此选项) --apply-log //准备还原(回滚日志) --copy-back //恢复数据 --incremental 目录名 //增量备份 --incremental-basedir=目录名 //增量备份时,指定上一次备份数据存储的目录名 --incremental-dir=目录名 //准备恢复数据时,指定增量备份数据存储的目录名 --export //导出表信息 import //导出表空间
XtraBackup完全备份
格式:innobackupex -user 用户名 --password 密码 --databases="系统库列表和存储数据库" 备份目录名 --no-timestamp
例:将所有库完全备份到 /backup innobackupex --user root --password 123456 /backup --no-timestamp
XtraBackup完全恢复
完全恢复时要求空的库目录
rm -rf /var/lib/mysql
mkdir /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
格式:innobackupex -user 用户名 --password 密码 --databases="系统库列表和存储数据库" --copy-back 备份目录名
例:恢复所有数据 innobackupex --apply-log /backup innobackupex --copy-back /backup
XtraBackup增量备份
必须先有一次完全备份
格式:innobackupex --user 用户名 --password 密码 databases="系统库列表和存储数据库" --incremental 目录名 --incremental-basedir="完全备份目录名" -no-timestamp
例:完全备份到/allbak、第一次增量备份到/new1、第二次增量备份到/new2 cp -rp /var/lib/mysql/mysql /root/mysql.bak //备份授权库 innobackupex --user root --password 123456 --databases="gamedb" /fullbak --no-timestamp //完全备份 innobackupex --user root --password 123456 --databases="gamedb" --incremental /new1 --incremental-basedir="/fullbak" --no-timestamp //第一次增量备份 innobackupex --user root --password 123456 --databases="gamedb" --incremental /new2 --incremental-basedir="/new1" --no-timestamp //第二次增量备份
XtraBackup增量恢复
rm -rf /var/lib/mysql
mkdir /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
格式:
1.innobackupex --user 用户名 --password 密码 --databases="系统库列表和存储数据库" --apply-log --redo-only 完全备份目录名
2.innobackupex --user 用户名 --password 密码 --databases="系统库列表和存储数据库" --apply-log --redo-only 完全备份目录名 --incremental-dir=增量备份目录名
3.innobackupex --user 用户名 --psssword 密码 --databases="系统库列表和存储数据库" --copy-back 完全备份目录名
例:恢复第一次增量备份到/new1、第二次增量备份到/new2的数据 rm -rf /var/lib/mysql mkdir /var/lib/mysql innobackupex --user root --password 123456 --databases="gamedb" --apply-log --redo-only /fullbak //恢复完全备份 innobackupex --user root --password 123456 --databases="gamedb" --apply-log --redo-only /fullbak --incremental-dir="/new1" //恢复增量 innobackupex --user root --password 123456 --databases="gamedb" --apply-log /fullbak --incremental-dir="new2" //恢复增量 innobackupex --user root --password 123456 --databases="gamedb" --copy-back /fullbak //拷贝文件 cp -r /root/mysql.bak /var/lib/mysql/mysql chown -R mysql:mysql /var/lib/mysql
恢复完全备份文件中的单个表
格式:innobackupex innobackupex --user 用户名 --password 密码 --databases="系统库列表和存储数据库" --apply-log --export 完全备份目录名
例:完全备份数据库到/allbak目录 innobackupex --user root --password 123456 --databases="gamedb" /allbak --no-timestamp //完全备份 mysql>drop table gamedb.a; innobackupex --user root --password 123456 --databases="gamedb" --apply-log --export /allbak //导出表信息 mysql>create table gamedb.a(id int); //创建表 mysql>alter table gamedb.a discard tablespace; //删除表空间 cp /allbak/gamedb/a.{ibd,cfg,exp} /var/lib/mysql/gamedb //拷贝表信息文件 chown mysql:mysql /var/lib/mysql/gamedb/a.* //修改所有者mysql>alter table gamedb.a import tablespace; //导入表空间
对于以上MySQL如何实现数据备份与恢复相关内容,大家还有什么不明白的地方吗?或者想要了解更多相关,可以继续关注我们的行业资讯板块。
当前文章:MySQL如何实现数据备份与恢复
网页地址:https://www.cdcxhl.com/article26/jgpdcg.html
成都网站建设公司_创新互联,为您提供企业建站、外贸建站、企业网站制作、网站排名、网站制作、定制网站
声明:本网站发布的内容(图片、视频和文字)以用户投稿、用户转载内容为主,如果涉及侵权请尽快告知,我们将会在第一时间删除。文章观点不代表本网站立场,如需处理请联系客服。电话:028-86922220;邮箱:631063699@qq.com。内容未经允许不得转载,或转载时需注明来源: 创新互联