MYSQL Log

LOG FILE TYPE

1. Error log: record mysql server startup, shutdown, crash and recovery information

2. Binlog: 

format type: A. row (recommendation) / statement / mix

parameter: sync_binlog = 1 (set to make sure master slave sync)

operate mode: backup and purge

check binlog: mysqlbinlog --database (database level) / --start-datetime / --stop-datetime (time level) / --table (table level)

Recovery with binlog

3. Slow log: reflect the system performance

parameter: long_query_time (0: record all execute SQL script)

define the file location in my.cnf in [mysqld]

long_query_time=0

log-slow-queries=/var/lib/mysql/mysql-slow.log

restart the mysql service

check slow log: 

A. 3rd party mysqlsla 

#perl Makefile.PL

#make & make install

#yum -y install perl-Time-HiRes

#mysqlsla -lt show <slow log file>

B. 3rd party pt-query-digest

#yum install percona-toolkit-2.2.9-1.noarch.rpm

#pt-query-digest <slow log file>

4. General log: database audit / execute track

parameter: general_log / sql_log_off

5. Redo log

6. Undo log

查看日志是否启用：

show variables like 'log_bin'

如果启用，即ON

那日志文件在mysql安装目录data目录下

(rpm安装默认在/var/lib/mysql)

 

查看当前日志：

show master status;

 

查看当前时间：

select now();

 

使用日志文件进行回退：

1.查看回退时间对应的日志文件；

2.输入如下命令：

mysqlbinlog --stop-datetime="回退时间点" 日志文件 | mysql -u用户名 -p密码

 

例如：

mysqlbinlog --stop-datetime="2011-12-31 15:00:00" /var/lib/mysql/mysql-bin.000003 | mysql -u root -p123456

 

查看日志报错：unknow: default-character-set = utf8

解决方案：修改/etc/my.cnf文件：

注释client下default-character-set = utf8

 

数据库错误日志：<hostname>.err