mysql日志文件
日志类型
1、错误日志
2、二进制日志
3、查询日志
4、慢查询日志
如果启动相关的日志，需要到/etc/my.cnf文件添加
log-error=/mysql/myerr.log 记录数据库启动关闭信息，以及运行过程中产生的错误信息
log-bin=/mysql/bin 记录除select语句之外的所有sql语句到日志中，可以用来恢复数据文件
log=/mysql.log 记录所有sql语句
log-slow-queries=/mysql/slow.log 记录查询慢的sql语句
lang_query_time=3 记录查询操作3秒的sql语句
[mysqld]
log=/mysql/mysql.log
log-error=/mysql/myerr.log
log-bin=/mysql/bin
max_binlog_size=3M
log-slow-queries=/mysql/slow.log
log=/mysql/mysql.log
log-error=/mysql/myerr.log
log-bin=/mysql/bin
max_binlog_size=3M
log-slow-queries=/mysql/slow.log
lang_query_time=3 #有的服务器输入起动不了
重新启动服务后，查看日志。
[root@zhaoyun mysql]# service mysqld restart
停止 MySQL： [确定]
启动 MySQL： [确定]
[root@zhaoyun mysql]# ls
bin.000001 bin.index myerr.log mysql.log slow.log
停止 MySQL： [确定]
启动 MySQL： [确定]
[root@zhaoyun mysql]# ls
bin.000001 bin.index myerr.log mysql.log slow.log
bin.000001是binlog 记录除select外的所有sql语句
bin.index 是记录生成的binlog文件
myerr.log记录的是错误日志
slow.log记录查询慢的日志
mysql.log记录所有sql执行的日志。
二、刷新log-bin日志，没刷新一次将生成一个新的日志
1、重新启动服务生成
2、使用mysql -e "flush logs"
3、使用mysqladmin flosh-logs
三、查看binlog
使用mysqlbinlog命令查看binlog
[root@zhaoyun mysql]# mysqlbinlog bin.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#111202 7:12:16 server id 1 end_log_pos 98 Start: binlog v 4, server v 5.0.77-log created 111202 7:12:16 at startup
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
ROLLBACK/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;这是一个没有执行过sql语句的binlog
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#111202 7:12:16 server id 1 end_log_pos 98 Start: binlog v 4, server v 5.0.77-log created 111202 7:12:16 at startup
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
ROLLBACK/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;这是一个没有执行过sql语句的binlog
执行一个删除语句，再看下这个文件的变化
[root@zhaoyun mysql]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.77-log Source distribution
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.77-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use ar
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> delete from http;
Query OK, 0 rows affected (0.01 sec)
mysql> delete from http;
Query OK, 0 rows affected (0.01 sec)
mysql> quit
Bye
[root@zhaoyun mysql]# mysqlbinlog bin.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#111202 7:12:16 server id 1 end_log_pos 98 Start: binlog v 4, server v 5.0.77-log created 111202 7:12:16 at startup
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
ROLLBACK/*!*/;
# at 98
#111202 7:16:04 server id 1 end_log_pos 175 Query thread_id=5 exec_time=0 error_code=0
use ar/*!*/;
SET TIMESTAMP=1322781364/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=0/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=33/*!*/;
delete from http
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
Bye
[root@zhaoyun mysql]# mysqlbinlog bin.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#111202 7:12:16 server id 1 end_log_pos 98 Start: binlog v 4, server v 5.0.77-log created 111202 7:12:16 at startup
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
ROLLBACK/*!*/;
# at 98
#111202 7:16:04 server id 1 end_log_pos 175 Query thread_id=5 exec_time=0 error_code=0
use ar/*!*/;
SET TIMESTAMP=1322781364/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=0/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=33/*!*/;
delete from http
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
可以看到刚才执行的操作都被记录了。
可以使用binlog恢复自备份binlog以来的所有操作。
恢复方法有俩种
1、通过数据节点
mysqlbinlog bin.000001 --start-position=4 |mysql -uroot -p123456 从节点4开始一直往下恢复，荡悠删除语句时需注意，不然恢复时，又给删除了。
mysqlbinlog bin.000001 --stop-position=500 |mysql -uroot -p123456 一直恢复到500节点
mysqlbinlog bin.000001 --start-positiion=50 --stop-position=100 |mysql -uroot -p123456 恢复节点从50到100的记录
2、通过起始时间
mysqlbinlog --start-datetime="2011-12-02 20:30:11" |mysql -uroot -p123456
mysqlbinlog --stop-datetime="2011-12-02 20:50:30" |mysql -uroot -p123456
mysqlbinlog --start-datetime="2011-12-02 20:30:11" --stop-datetime="2011-12-02 20:50:30" |mysql -uroot -p123456
本文转自zhaoyun00 51CTO博客，原文链接:http://blog.51cto.com/zhaoyun/732826
