MySQL enable log, view Log

MySQL enable log, view Log

MYSQLEnable logging
[Email protected]]# whereis My.ini
[[Email protected]]# vi/etc/my.cnf
[MySQLD
datadir=/var/lib/MySQL
socket=/var/lib/MySQL/MySQL. Sock
User=MySQL
# Default to using old password format for compatibility withMySQL3.x
# clients (those using theMySQLCLIENT10 compatibility package).
Old_passwords=1
[MySQLD_safe]
log-error=/var/log/MySQLD.log
Long_query_time =1
Log-slow-queries=slowqueris.log
Log-queries-not-using-indexes = Nouseindex.log
Log=mylog.log
pid-file=/var/run/MySQLd/MySQLD.pid

--------------------------------------------------------

View Logs

Whether the log is enabled
MySQL>show variables like ' log_bin ';

How to know the current log
MySQL> show master Status;

See binary log files with mysqlbinlog
shell>mysqlbinlog mail-bin.000001
or shell>mysqlbinlog mail-bin.000001 | Tail

A similar command is used under Windows.

MySQLThere are several types of logs:
Error log:-log-err
Query log:-log
Slow query log:-log-slow-queries
Update log:-log-update
Binary log:-log-bin

InMySQLIn the installation directory, open the My.ini, add the above parameters in the back, save and restartMySQLService on the line.
Last_updated2
-------------------------------------------------------------

IfMySQLThe server has binary logging enabled and you can use theMySQLBinlog tool to recover data from a specified point in time (for example, from your last backup) until now or another specified point in time. For information on enabling binary logging, see section 5.11, 3, "binary logs". ForMySQLFor more information on Binlog, seeMySQLManual Section 8.6, "MySQLBinlog: Utility for working with binary log files.

To recover data from a binary log, you need to know the path and file name of the current binary log file. You can typically find the path from the options file (that is, my.cnf or My.ini, depending on your system). If it is not included in the options file, it can be given as an option on the command line when the server is started. The option to enable binary logging is--log-bin. To determine the file name of the current binary log file, enter the following MySQL statement:

SHOW BINLOG EVENTS G

You can also enter the following from the command line:

MySQL --user=root-pmy_pwd-e ' SHOW BINLOG EVENTS G '

Replace the password my_pwd with the root password of the server.

1. Specify the recovery time

For MySQL 4.1.4, you can specify the start and end time of the DateTime format in the MySQLbinlog statement through the--start-date and--stop-date options. For example, suppose that today 10:00 (today is April 20, 2005), execute the SQL statement to delete a large table. To restore the table and data, you can restore the backup of the previous night and enter:

MySQLbinlog--stop-date= "2005-04-20 9:59:59"/var/log/mysql/bin.123456
| MySQL -u root-pmypwd

This command restores all data as of the date and time given in datetime format in the--stop-date option. If you do not detect a few hours after entering the wrong SQL statement, you may want to restore the activity that occurs later. Based on these, you can use the date and time to run MySQLbinlogagain:

MySQLbinlog--start-date= "2005-04-20 10:01:00"/var/log/mysql/bin.123456
| MySQL -u root-pmypwd

In that row, the SQL statement that is logged in from 10:01 runs. Combined execution of the previous night's dump file and two lines of MySQLbinlog can restore all data to a second before 10:00. You should check the logs to make sure the time is correct. The next section describes how to implement it.

2. Specify the recovery location

You can also specify a log location without specifying a date and time, and using mysqlbinlog options--start-position and--stop-position. They function the same as the start and End Date option, and the difference is that the location number from the log is given. Using the log location is a more accurate method of recovery, especially when many transactions occur simultaneously due to destructive SQL statements. To determine the location number, you can run mysqlbinlog to find the time range for the transaction that you did not expect, but you should re-point the result to a text file for review. How to do this:

mysql binlog  --start-date= "2005-04-20 9:55:00"--stop-date= " 2005-04-20 10:05:00 "
/var/log/mysql /bin.123456 >/tmp/mysql _restore.sql

This command will create a small text file in the/tmp directory, The SQL statement that was executed with the wrong SQL statement is displayed. You can open the file with a text editor and look for statements that you don't want to repeat. If the location number in the binary log is used to stop and resume the recovery operation, comments should be made. Use Log_pos plus a number to mark the position. After recovering the previous backup file using the location number, you should enter the following from the command line:

mysql binlog--stop-position= "368312"/var/log/mysql /bin.123456
| mysql  -u root-pmypwd
mysql binlog--start-position=" 368315 "/var/log/ mysql /bin.123456
| < Span style= "Background-color:inherit" >mysql  -u root-pmypwd

The 1th row above reverts to all transactions until the stop location. The next line restores all transactions from the given starting position until the end of the binary log. Because the output of MySQLbinlog includes the set TIMESTAMP statement before each SQL statement is recorded, the recovered data and the associated MySQL log will react to the original time of the transaction execution.

MySQL enable log, view Log