mysql备份恢复日志_MySQL-日志 备份 恢复

1、 MySQL日志记录

日志文件是MySQL数据库的重要组成部分。MySQL有几种不同的日志文件，通常包括错误日志文件，二进制日志，通用日志，慢查询日志，等等。这些日志可以帮助我们定位mysqld内部发生的事件，数据库性能故障，记录数据的变更历史，用户恢复数据库等等

默认情况下，所有日志创建于mysql数据目录(data目录)

2、 MySQL日志文件系统的组成

a、错误日志：记录启动、运行或停止mysqld时出现的问题。

b、通用日志：记录建立的客户端连接和执行的语句。

c、二进制日志：记录所有更改数据的语句。还用于复制。

d、慢查询日志：记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。

备注： 重点关注二进制日志、慢查询日志，其他了解即可。

3、错误日志

1. 查看错误日志的位置

mysql> show variables like "%log_error%";

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

| Variable_name | Value |

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

| log_error | /usr/local/mysql/data/localhost.localdomain.err |

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

1 row in set (0.27 sec)

4、 通用日志

1. 查看通用日志是否开启以及位置

mysql> show variables like "%general%";

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

| Variable_name | Value |

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

| general_log | OFF |

| general_log_file | /usr/local/mysql/data/localhost.log |

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

2. 开启通用日志

mysql> set @@global.general_log=1;

5、 二进制日志(重点)

MySQL的二进制日志(binary log)是一个二进制文件，主要用于记录写类型的SQL语句(增删改),不记录读类型SQL语句(select)

二进制日志作用

二进制日志主要用于数据库数据恢复和主从复制

6、 二进制日志相关操作

1. 查看二进制日志是否开启

mysql> show variables like "log_bin";

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

| Variable_name | Value |

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

| log_bin | ON |

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

2. 查看每个二进制文件的大小

mysql> show master logs;

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

| Log_name | File_size |

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

| mysql-bin.000004 | 478421400 |

| mysql-bin.000005 | 9653 |

| mysql-bin.000006 | 340631484 |

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

3 rows in set (0.00 sec)

3. 查看当前二进制日志文件状态

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000006 | 107 | | |

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

4. 开启二进制日志

[root@localhost /]# vim /etc/my.cnf

log-bin=mysql-bin #二进制日志

5. 查看二进制日志的内容

[root@localhost /]# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-000001.bin

备注： /usr/local/mysql/bin/mysqlbinlog 是MySQL提供的命令,只不过这里敲绝对路径

/usr/local/mysql/data/mysql-000001.bin 是需要查看的二进制文件内容

6. 清空所有二进制日志文件

mysql> reset master;

7. 新增一个日志文件,新写入的SQL语句，在最新的二进制日志中

mysql> flush logs;

8、二进制日志文件恢复数据的实例

1. 做了一个完整备份

mysqldump –uroot –p test –l –F > '/tmp/test201507/sql'

-l 备份时加锁，在整个备份期间，只允许读操作

-F 即flush logs 可以重新生成新的bin-log日志文件

2. 接下来，模拟进行新操作：

mysql> insert into users values(3,'jack');

mysql> update users set name='mary' where id=2;

然后模拟一次误操作，所有数据库被清空：

mysql> delete from users;

3. 准备恢复丢失的数据，开始一个新的bin-log日志(目的是保留需要原来的bin-log日志)：

mysql>flush logs;

4. 先恢复到之前的完整备份文件

mysql –uroot –p test –f < '/tmp/test201507.sql';

5. 接着恢复后面的操作(8:05分)，需要从bin-log日志中恢复：

mysqlbinlog mysql-bin.0000003 --start-position='106' –-stop-position='306' | mysql –uroot -p

备注：

mysqlbinlog 使用mysql提供的mysqlbinlog命令

--start-position 代表开始的位置

--stop-position 结束的位置,只会将开始位置和结束位置之间的SQL语句拿到

9、主从复制(重点)

所谓的主从复制，就是让写操作的SQL语句(insert/delete/update)在一台服务器执行，读操作的SQL语句(select)在另外一台服务器执行。

我们把负责写的服务器叫做主数据库，简称主库。负责读的服务器，叫做从数据库，简称从库。

10、主从复制的原理

1、主库把数据更改记录到二进制日志(Binary Log)中

2、从库将主库上的二进制日志复制到自己的中继日志(Relay Log)中

3、从库读取中继日志中的事件(SQL语句)，将其重放到从库数据库上执行，从而实现从库数据与主库数据的同步。

11、 主从复制优缺点

优点：

1. 主从复制后，读操作与写操作分别在不同的服务器，减轻了单台服务器的压力

2. 主从一般配置为一主多从，多个从库，增加查询速度，并且避免了数据库的单点故障

缺点：

1. 当有大量数据写入时，从库与主库之间的数据存在一定的时间差。

2. 主从延迟没有不可避免。