MySQL 备份与恢复

阅读量：4229 次

发布时间：2019-05-26

本文共 6634 字，大约阅读时间需要 22 分钟。

一、数据备份

备份方式

物理备份（冷备）：tar/cp 数据库目录

逻辑备份：mysqldump ， mysql

备份策略

1）完全备份

---备份所有数据

2）增量备份

---备份上次备份后，所有新产生的数据

3）差异备份

---备份完全备份后，所有新产生的数据

1.物理备份

要(cp拷贝)备份整个数据库目录/var/lib/mysql

使用系统命令备份

缺点：兼容性不好，数据量大，适合数据小的备份

备份：1.cp -rp /var/lib/mysql/数据库备份目录/文件名

2.tar -zcvf xxx.tar.gz /var/lib/mysql/数据库/*

恢复：1.cp -rp 备份目录/文件名 /var/lib/mysql/

chown -R mysql:mysql /var/lib/mysql

2.tar -xf xxx.tar.gz -C /var/lib/mysql/数据库名/

innodb

myisam

2.逻辑备份

在执行备份命令时，把数据保存到指定的备份文件里，可以使用备份文件恢复数据

备份：mysqldump -uroot -p密码库名 > /路径/xxx.sql

恢复：mysql -uroot -p密码库名 < /路径/xxx.sql

库名表示方式：

--all-databases 或-A #所有库

数据库名 #单个库

数据库名表名 #单张表

-B 数据库1 数据库2 #多个库

注：无论备份还是恢复都要验证用户权限

]# mysqldump -uroot -pabc123 -A > /dbbak/all.sql

]# mysql -uroot -pabc123 db3 < /root/db3.sql #恢复单个库时需先建库

2.1使用数据库服务服务提供备分命令备份 mysqldump

恢复多个库就不需跟库名

一个一个的备份就要跟库名(需先进数据库建好库,备份文件内没有建库命令)

缺点：恢复时会加写锁

二、binlog日志

什么是binlog日志？

二进制日志，记录除查询外的SQL命令

配置：

log_bin[=dir/name]

server_id=1-255 (范围)

max_binlog_size=数字m

一个日志文件最多能记录1G日志

binlog是配置mysql主从同步的必要条件

1.启用binlog日志

mysql> show master status;查看binlog是否启用

]#vim /etc/my.cnf
[mysqld]
...
log_bin #不写则使用默认路径,默认在/var/lib/mysql/下
server=50 #指定id值通常用主机名的数字方便与其他不冲突

]#systemctl restart mysqld

]#mysql -uroot -p123456 -e "show master status"

2.手动生成新的日志文件

mysql>system ls /var/lib/mysql #system声明是系统命令

2.1）重启mysql服务

2.2）执行SQL操作 mysql>flush logs;

2.3) mysqldump -uroot -p123 --flush-logs 库名 > "目录/文件.sql"

2.4) mysql -uroot -p密码 -e 'flush logs'

]#mysqldump -uroot -p123 --flush-logs db3 > /priv/db3.sql

]#mysql -uroot -p123 -e select user,host from mysql.user

3.删除

1）mysql>purge master logs to "binlog日志文件"

2）mysql>reset master 删除所有binlog日志，重建新日志

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

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

| hwy.000002 | 154 | | | |

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

Position 154 偏移量

日志记录格式

mysql>show cariables like "binlog_format";

statement:每条修改数据的SQL命令都记录

row：不记录SQL语句上下文相关信息，仅保存哪条记录被修改【默认】

mixed：以上2种格式的混合使用

修改日志记录格式

]#vim /etc/my.cnf

binlog_format=mixed

]#重启

格式：mysqlbinlog [选项] binlog日志文件名

常用选项：

--start-datetime="yyyy-mm-dd hh:mm:ss"

--stop-datetime="yyyy-mm-dd hh:mm:ss"

--start-position=数字

--stop-position=数字

查看从2019.5.15开始的更改操作

例：]# mysqlbinlog --start-datetime="2019-05-15 20:30" /var/lib/mysql/mysql-bin.000001

4.使用binlog日志恢复数据

格式：mysqlbinlog(专门读日志命令) binlog日志文件名 | mysql -uroot -p12346

]#mysqlbinlog 日志文件 #查看日志内容

[root@host50 ~]# vim /etc/my.cnf

[mysqld]

log-bin=/mylog/hwy(文件名)

server_id=50

]# 重启

建对设置默认日志路径

目录授权mysql

mysql>show master status;

mysql>create database db6;

mysql>create table db6.t1(id int);

mysql>insert into db6.t1 values(100);

/|\ 上面操作执行N次

...

--------------------------------------------------------------------------------------------------------------

物理缺点：兼容性差，数据越多备份越久

mysqldump缺点：备份还原慢（受硬盘转速影响），备份（插入,更新）过程中会被锁表

binlog：实时备份，但数据多时备份部分数据文件查找难

XtraBakup

热备份,备份过程中不锁库表，由Percona提供

组件

xtrabackup：

innobackupex

innnobackupex增量备份需是InnoDB引擎

----------------------------------------------------------------

yum装libev percona-xtrabackup-24软件包

innobackupex基本选项
--host 主机名
--user 用户名
--port 端口号
--password 密码
--databases 数据库名 ="库.表"
--no-timestamp 不用日期命名备份文件存储的子目录名

--redo-only 日志合并
--apply-log 准备还原（回滚日志）
--copy-back 恢复数据

--incremental 目录名 #增量备份

--incremental-dir=目录名 #准备恢复数据时指定增量备份数据存储的目录名
--incremental-basedir=目录名 #增量备份时，指定上一次备份数据存储的目录名

--export 导出表信息

import 导入表空间

完全备份

]# innobackupex --user 用户 -password 密码备份目录名A --no-timestamp # 完全备份

]# innobackupex --user 用户 -password 密码 --apply-log 备份目录名A #准备恢复数据

]# rm -rf /var/lib/mysql/* #恢复时要求空的库目录

]# innobackupex --user 用户 -password 密码 --copy-back 备份目录名A #恢复数据

]# chown -R mysql:mysql /var/lib/mysql

]# 重启mysqld

]# mysql -user 用户 -p密码 #进去查看数据

增量备份

格式：innobackupex --user 用户 -password 密码 --incremental 目录名(增量备份新目录) --incremental-basedir=目录名(上次完全备份的目录)

首次备份(完全备份)

]# innobackupex --user 用户 -password 密码 ~~[--databases=库名列表]~~ --incremental 目录B --incremental-basedir=备份目录名A(完全备份文件) --no-timestamp(文件名默认以时间命名这里表示no)

]# nnobackupex --user 用户 -password 密码 --incremental 目录C --incremental-basedir=备份目录名A --no-timestamp

----------------------------------------------

增量恢复

格式：

--innobackupex --apply-log --redo-only 完全备份目录

--innobackupex --apply-log --redo-only 完全备份目录 --incremental-dir=目录名(合并日志)

--innobackupex copy-back 完全备份目录

清空mysql目录

准备恢复数据

不能cp过去，数据要与日志一致

mysql>alter table 库.表 import tablespace;

应用案例

完全备份：

innobackupex --user root --password 123456 /backup --no-timestamp 完全备份（不以时间命名文件）

innobackupex --user root --password 123456 --apply-log /backup　　准备回恢复数据

rm -rf /var/lib/mysql/*　　　＃恢复时要求清空数据库目录

innobackupex --user root --password 123456 --copy-back /backup　　回复数据

chown -R mysql:mysql /var/lib/mysql　　　　　　　　　　　　　　　＃mysql要有权限

systemctl restart mysqld

mysql -uroot -p123456 # 查看数据

增量备份与恢复：

innobackupex --user root --password 123456 --databases="库名列表"　/fullbak --no-timestamp #增量前必须要有一次完全备份

innodbackupex --user root --password 123456 --databases="库名列表" --incremental /new1 --incremental-basedir="/fullbak" --no-timestamp #第一次增量备份

innobackupex --user root --password 123456 --databases="库名列表" --incremental /new2 --incremental-basedir="/new1" --no-timestamp #第二次增量备份

增量恢复：

rm -rf /var/lib/mysql/*

innobackupex --user root --passwd 123456 --databases="库名列表" --apply-log --redo-only /fullbak #完整恢复

innobackupex --user root --password 123456 --databases="库名列表" --apply-log --redo-only /fullbak --incremental-dir="/new1"

＃增量恢复

innobackupex --user root --password 123456 --databases="库名列表" --apply-log --redo-only /fullbak --incremental-dir="/new2"

＃恢复增量

innobackupex --user root --password 123456 --databases="库名列表" --copy-back /fullbak #拷贝文件

chown -R mysql:mysql /var/lib/mysql

systemctl stop mysqld ; systemctl start mysqld #先停再启加载数据文件

在完全备份文件中恢复单个表

innobackupex --user root --password 123456 --databases="gamedb" /allbak --no-timestamp

innobackupex --user root --password 123456 --databases="gamedb" --apply-log --export /allbak #导出表信息

mysql> create table gamedb.a(id int);　创建表

mysql> alter gamedb.a discard tablespace; 　删除表空间

mysql> system cp /allbak/gamedb/a.{ibd,cfg,exp} /var/lib/mysql/gamedb #拷贝表信息文件

mysql> system chown -R mysql:mysql /var/lib/mysql

mysql> alter table gamedb.a import tablespace; #导入表空间

mysql> select * from gamedb.a;