MySQL数据备份与恢复

一.备份数据的方式

MySQL参数说明:

MySQL管理之数据备份及恢复

1.物理备份 

1.1冷备 cp tar等 

1.2热备 mysqlhotcopy    只能备份存储引擎是myisam的表,依赖perl-DBD-MySQL

2.逻辑备份:用mysql自己提供的备份命令或第三放方软件提供的备份命令,备份创建库、表、和产生表记录时的sql语句。

二.数据备份策略:

1.完整备份        备份数据库服务器上的所有库所有表。

2.差异备份        备份自完整备份后所有产生的新数据。

3.增量备份        备份自上一次备份后所有产生的新数据

生产环境一般采用  完整备份+增量备份 或者 完整备份+差异备份

三.逻辑备份与恢复

1.mysqldump命令做数据的完整备份

命令格式:

mysqldump -h数据库服务器ip -u用户名 -p密码  数据库名 > 目录名/备份文件名

数据库名的表示方式:

--all-databases                    备份数据库服务器上的所有库所有表

数据库名                            对某个库做完整备份

数据库名  表                        对指定库里的指定表做完整备份

-B 数据库名1 数据库名2 数据库N      对某几个库做完整备份

#目录名 :若备份时不写目录名备份文件就存放在,执行备份命令时 所在的目录下

#备份文件名 :可以自己定义,要有标识性,约定俗成mysql的备份文件都以.sql 结尾

若文件重名,上一次备份会被当前备份覆盖。

# 备份用来连接数据库服务器的用户,要有被备份的目标库有相应的权限才可以。

2.恢复完整备份数据

命令格式:

mysql  -h数据库服务器ip  -u用户名  -p密码  数据库名  <  目录名/xxx.sql

mysql  -h数据库服务器ip  -u用户名  -p密码    <  目录名/xxx.sql

# 数据库名:可选项,当备份文件中有 create database,use  sql语句时,恢复数据时可以不指定数据库名。

# 用完整备份文件 恢复数据,只能把数据恢复到备份时的状态,完整备份之后产生的信息数据是无法恢复。

四.使用binlog日志做增量备份与数据恢复

1.mysql日志类型

mysql共有4种类型日志:

binlog日志(二进制日志)  记录除查询之外sql语句。

错误日志                  记录mysql服务在启动和运行过程中产生的错误。

查询日志                  记录执行的所有的sql操作。

慢查询日志                只记录超过指定时间,显示查询结果的sql语句。默认超时时间是10秒。

#默认情况下,mysql只开启错误日志。


#默认情况下日志都存放在数据库目录下。每种日志文件都默认命名方式。

2.启用binlog日志

vim /etc/my.cnf

[mysqld]

log_bin=/binlogdir/filename        //设置存放binlog目录和文件名,自定义目录必须让mysql用户有写入权限,目录不设置则默认在数据库目录/var/lib/mysql/下,文件名不设置则默认为:主机名-bin.000001                                   

max-binlog-size=100M                //设置binlog日志文件的大小,不设置则默认为500M自动生成第二个binlog日志文件

service  mysql  restart

# 对所有库操作的使用数据发送改变的sql语句都会写进当前编号最大的binlog日志文件里。

# localhost-bin.index  记录当前已有binlog日志文件

3.分析binlog日志文件内容。

binlog日志记录sql语句的方式:

① 时间方式

② 字符偏移量方式


命令格式:

mysqlbinlog  [选项]  binlog日志文件名

选项:

偏移量

--start-position=100        起始位置

--stop-position=1200        结束位置

时间点:

--start-datetime="YYYY-mm-dd  HH:MM:SS"  起始时间

--stop-datetime="YYYY-mm-dd  HH:MM:SS"    结束时间

# 不加任何选项则默认查看全部日志内容

4.手动生成新的binlog日志文件方式:

① mysql>flush logs;

② #mysql -h数据库服务器IP -u用户名 -p密码 -e "flush logs"

③ #mysqldump -h数据库服务器IP -u用户名 -p密码 --flush-logs 数据库名> /目录名/xxx.sql

④ service  mysql  restart              //一般不用

5.增量备份数据恢复

原理:使用mysqlbinlog提取历史sql操作管道给mysql命令重做

命令格式:

mysqlbinlog  [选项]  binlog日志文件名  |  mysql  -h数据库服务器ip  -u用户名  -p密码  数据库名

批量恢复增量备份:

mysqlbinlog  `cat localhost-bin.index`| mysql -h数据库服务器ip -u用户名 -p密码  [数据库名]

6.清理binlog日志

删除早于指定编号的binlog日志文件:

mysql> PURGE  MASTER LOGS TO  'binlog日志文件名';

删除所有binlog日志文件,重建新日志:

mysql> RESET  MASTER;

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/b4a9963945fbfff2f9f609d8d9b6b071.html