无锡 公共建设中心网站,使用wordpress编辑器,青岛网站推广途径,关于建设网站的培训知识文章目录开启二进制日志功能关闭/打开二进制日志记录刷新二进制日志文件查看二进制日志文件的存储位置利用二进制日志文件恢复数据的本质二进制日志提取/导出到脚本文件中查看当前二进制日志的最后一个位置查看二进制日志文件的内容执行 SQL 脚本文件以恢复数据二进制日志分析指…
文章目录开启二进制日志功能关闭/打开二进制日志记录刷新二进制日志文件查看二进制日志文件的存储位置利用二进制日志文件恢复数据的本质二进制日志提取/导出到脚本文件中查看当前二进制日志的最后一个位置查看二进制日志文件的内容执行 SQL 脚本文件以恢复数据二进制日志分析指定恢复时间指定恢复位置数据恢复演示案例备份数据库操作数据恢复数据开启二进制日志功能
在配置文件 /etc/my.cnf 中设置二进制日志文件的保存目录及文件名前缀
log-bin/var/lib/mysql/mybinlog/mysql-bin
server-id1 启用独占表空间:
innodb_file_per_table1说明
1.如上的配置数据库服务重启后就自动生成一个二进制日志文件保存在 /var/lib/mysql/mybinlog 目录下而且二进制日志文件的名称会以 mysql-bin 为前缀在后面从序号 000001 开始命名。
第一次生成日志文件的名称为mysql-bin.000001如果你刷新了日志则会生成一个新的日志文件名称为mysql-bin.000002继续书刷新日志则会生成名为mysql-bin.000003 的日志文件以此类推。
2.在配置变量 log-bin 的同时必须要配置变量 server-id否则数据库服务根本无法重启。
3.启用独占表空间则每个表都有自己独立的表空间文件默认是共享表空间即所有数据库使用一个表空间。至于这项功能是否必须开启暂时不清楚
关闭/打开二进制日志记录
mysql set global sql_log_bin0;
mysql set global sql_log_bin1;刷新二进制日志文件
mysql flush logs;
Query OK, 0 rows affected (0.00 sec)注 1.会生成新的二进制日志文件 2.旧日志文件中的数据库操作日志并不会复制到新日志文件中
查看二进制日志文件的存储位置
mysql show variables like %log_bin%;
--------------------------------------------------------------------------
| Variable_name | Value |
--------------------------------------------------------------------------
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mybinlog/mysql-bin |
| log_bin_index | /var/lib/mysql/mybinlog/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
--------------------------------------------------------------------------
6 rows in set (0.01 sec)看变量 log_bin_basename 的值得知二进制日志文件存放在目录 /var/lib/mysql/mybinlog/ 下
利用二进制日志文件恢复数据的本质
二进制日志机制其实就是把一些 DDL、DML 操作语句记录到二进制日志文件中而你想利用该文件恢复数据就必须找到相关数据的命令语句然后去执行这些命令语句。所以你需要看得懂日志文件的内容能够把相关命令语句提取出来。关于提取你想要的命令语句的命令请看下面。
二进制日志提取/导出到脚本文件中
--start-position指定从哪个位置开始导出日志 --stop-position指定导出日志的结束位置 --start-datetime指定从哪个时间开始导出日志时间格式为2005-12-25 11:25:56 --stop-datetime指定导出日志的结束时间
[roothtlwk0001host ~]# mysqlbinlog --no-defaults --start-position1125 --stop-position1344 /var/lib/mysql/mybinlog/mysql-bin.000004 /root/test/001.sql查看当前二进制日志的最后一个位置
mysql show master status; 查看日志文件的最后一个位置其实没有什么意义。
查看二进制日志文件的内容
[roothtlwk0001host ~]# mysqlbinlog --no-defaults /var/lib/mysql/mybinlog/mysql-bin.000004执行 SQL 脚本文件以恢复数据
[roothtlwk0001host ~]# mysql -uroot -p123456 -D test /root/test/004.sql;二进制日志分析 指定恢复时间
对于 MySQL 4.1.4可以在 mysqlbinlog 语句中通过 --start-date 和 --stop-date 选项指定恢复数据的起止时间。
举例说明假设在今天上午10:00今天是2005年4月20日不小心删除一个表。要想恢复表和数据你可以先恢复前晚上的备份假设你有定时每天凌晨对数据库进行备份备份后自动生成一个新的二进制日志文件 mysql-bin.00004然后执行下面的语句
[roothtlwk0001host ~]# mysqlbinlog --stop-date2005-04-20 9:59:59 /var/log/mysql/mysql-bin.000004 | mysql -uroot -p123456这条命令可能有问题因为 2005-04-20 9:59:59 之前有很多数据库操作语句这些操作语句不仅仅是前晚备份时间开始到 2005-04-20 9:59:59 之间的操作语句前晚备份时间之前也有无数的操作语句这些操作语句也包括在内吧难道都执行吗都执行肯定会报错所以感觉有问题。
接着你可能要恢复 2005-04-20 10:01:00 之后产生的数据你可以用起使日期和时间再次运行 mysqlbinlog
[roothtlwk0001host ~]# mysqlbinlog --start-date2005-04-20 10:01:00 /var/log/mysql/bin.123456 | mysql -uroot -p123456指定恢复位置
也可以不指定日期和时间而使用 mysqlbinlog 的选项 --start-position 和 --stop-position 来指定日志位置。它们的作用与起止日期选项相同不同的是给出日志内容中的位置号。使用日志位置是更准确的恢复方法特别是执行破坏性SQL语句的同时发生许多事务的时候。要想确定位置号你必须查看日志文件的内容以寻找执行了不期望的事务的时间范围建议你将这个时间范围的日志数据导出到文本文件中以便进行检查你可以执行下面的语句
[roothtlwk0001host ~]# mysqlbinlog --start-date2005-04-20 9:55:00 --stop-date2005-04-20 10:05:00 /var/log/mysql/mysql-bin.000004 /tmp/mysql_restore.sql执行到停止位置为止的所有事务即将数据恢复到指定的停止位置
[roothtlwk0001host ~]# mysqlbinlog --stop-position368312 /var/log/mysql/mysql-bin.000004 | mysql -u root -pmypwd 将恢复从给定的起始位置直到二进制日志结束的所有事务
[roothtlwk0001host ~]# mysqlbinlog --start-position368315 /var/log/mysql/mysql-bin.000004 | mysql -u root -pmypwd数据恢复演示案例
备份数据库
先对某个数据库进行完整的备份
[roothtlwk0001host ~]# mysqldump -h10.6.208.183 -uroot -p123456 -P3306 --single-transaction --master-data2 test test.sql执行上述的命令语句后会在当前工作目录下产生一个备份脚本文件 test.sql。
在 test.sql 文件中我们会看到
-- CHANGE MASTER TO MASTER_LOG_FILEbin-log.000002, MASTER_LOG_POS107;指备份后所有的更改将会保存到 bin-log.000002 二进制文件中备份数据在日志文件中对应的位置是 107。
操作数据
接着往 test 数据库下的 student 表中插入两条记录然后执行 flush logs 命令产生一个新的二进制日志文件 bin-log.000003。在 flush logs 之前数据库的更改操作日志是保存在文件 bin-log.000002 中既往表 student 增加记录的操作日志是保存在文件 bin-log.00002 中。
接着再往 test 数据库下 teacher 表中增加两条记录然后删除 student 表和 teacher 表。执行命令 flush logs 之后增加记录和删除表的操作日志都记录在新的二进制日志文件 bin-log.000003 中。
恢复数据
接着我们来恢复数据首先导入全备数据
[roothtlwk0001host ~]# mysql -h10.6.208.183 -uroot -p123456 -P3306 test.sql恢复 bin-log.000002
[roothtlwk0001host ~]# mysqlbinlog /var/lib/mysql/binlog/bin-log.000002 | mysql -h10.6.208.183 -uroot -p123456 -P3306恢复部分 bin-log.000003
需要查看文件 bin-log.000003 中的内容找到开始删除数据的位置然后将这个位置之前的日志数据导出到 SQL 脚本文件中然后执行该脚本文件以恢复被删除的数据当然你找到恢复数据的位置点之后也可以直接指定这个位置点通过下面的语句来恢复数据
[roothtlwk0001host ~]# mysqlbinlog /var/lib/mysql/binlog/mysql-bin.000003 --stop-position208 | mysql -h10.6.208.183 -uroot -p123456 -P3306
