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. 主从延迟没有不可避免。