Shell script batch export MYSQL database logs automatically export MYSQL binary logs locally, backed up by day
Mysqlbinlog
A tool that reads statements from binary logs. Logs of executed statements contained in the binary log file can be used to help recover from a crash.
I. MYSQL database logs:
1. error log:-log-error
2. query logs:-log
3. slow query log:-log-slow-queries
4. update log:-log-update
5. binary log:-log-bin
The following describes how to export and import MYSQL binary logs, complete MYSQL binary log backup, and Incremental backup.
By default, all logs are created in the mysqld data directory, or you can manually specify the option settings for the/etc/my. cnf [mysqld] setting segment. In linux:
# In [mysqld? Inbound
Python[mysqld]log_long_formatlog-bin = /data/mysql/3306/binlogbinlog_cache_size = 4Mbinlog_format = MIXEDmax_binlog_cache_size = 16Mmax_binlog_size = 512Mexpire_logs_days = 30
Above, enable MYSQL binary log and specify the path to save the log. Binlog log Opening Method
Add a line (my. ini for Windows) to the my. cnf file ).
[Mysqld]
Log-bin = mysqlbin-log # If you add this line, you can define the name after the = sign.
Then we can perform simple operations on the database and view the binlog file in the directory where the mysql data file is located.
[Root @ jimmyli mysql] # ll
-Rw ---- 1 mysql 813255 Nov 25 mysqlbin-log.000001
Seeing this similar file proves that it is done.
Ii. Use the mysqlbinlog command to view binary log filesWhether logs are enabled
Mysql> show variables like 'Log _ % ';
How to know the current log
Mysql> show master status;
Show 2? Number of logs
Mysql> show master logs;
Use mysqlbinlog to view binary log files
Shell> mysqlbinlog mail-bin.000001
Or shell> mysqlbinlog mail-bin.000001 | tail 9000
View the SQL log records of the last (last) 9000 rows of the binary log file
Iii. Batch export MYSQL database logs using shell scriptsExport binary logs in the form of the last N days
In the settings above, MYSQL binary logs are saved for 30 days, and the size of mail-bin.000001-like files is 512 MB. Based on the operational needs of the Website, you need to completely back up and incrementally back up MYSQL binary logs, export the log files in the form of the last N days, and save them as TXT files.
The shellshell code is as follows :#! /Bin/bashiday = 60 # export the MySQL BINLOG for 60 days cyclically startday =day (date-d "-$ iday day" + "% y-% m-% d ") stopday = $ (date + "% y-% m-% d") # while ["$ startday "! = "$ Stopday"] while [$ iday-ge 1] # while ("$ iday"> = 1 )) doecho $ idaystartday = $ (date-d "-$ iday day" + "% y-% m-% d") echo startday = $ startdayecho stopday = $ stopday. /mysqlbinlog -- start-datetime = "$ startday 00:00:00" -- stop-datetim = "$ startday 23:59:59" binlog. * [0-9]> export startday.txt echo --------------- iday = 'expr $ iday-1 'done execution result: [root @ JimmyLi bin] #. /test. sh60startday = 12-04-17stopday = 12-06-16 --------------- # ignore in the middle #1 startday = 12-06-15stopday = 12-06-16 ---------------
Logs generated from 12-04-17.txtto 12-06-15.txt for a total of 60 days are generated on a daily basis.
4. Automatically export MYSQL binary logs locally and back up logs by dayYou can upload the output of mysqlbinlog to the mysql client to execute the statements contained in the binary log. If you have an old backup, this option is also useful for crash recovery: shell> mysqlbinlog hostname-bin.000001 | mysql
Or:
Shell> mysqlbinlog hostname-bin. [0-9] * | mysql
Shell> mysqlbinlog hostname-bin. * [0-9]> bin.txt
If you need to modify the log containing statements, you can also point the output of mysqlbinlog to a text file again.
(For example, you want to delete a statement that you do not want to execute for some reason ). After editing the file, enter it into the mysql program and execute the statements it contains. Automatically export MYSQL binary logs locally. Run the daily BACKUP command: shell>. /mysqlbinlog -- start-datetime = "12-06-16 00:00:00" -- stop-datetim = "12-06-16 23:59:59" binlog. * [0-9]> 12-06-16.txt
5. Discuss the safe processing method if the MySQL server has multiple binary logs to be executed.Mysqlbinlog has a -- position option, which only prints statements whose offset in the binary log is greater than or equal to a given position (the given position must match the start of an event ).
It also has the option to stop or start after you see an event of the given date and time. In this way, you can use the -- stop-datetime option for point-to-point recovery (for example, you can say "rolling the database to the position at AM Today ").
If the MySQL server has multiple binary logs to be executed, the safe way is to process them in a connection. The following is an example of what is insecure: shell> mysqlbinlog hostname-bin.000001 | mysql-u root
Shell> mysqlbinlog hostname-bin.000002 | mysql-u root uses a different connection to the server to process binary logs, if the 1st log files contain a create temporary table statement, 2nd logs contain a statement that uses the temporary table, which may cause problems. When 1st mysql processes are completed, the server revokes the temporary table. When 2nd mysql processes want to use this table, the server reports "do not know this table ". To avoid such problems, use a connection to execute the content in all binary logs to be processed. The following provides a method: shell> mysqlbinlog hostname-bin.000001 hostname-bin.000002 | mysql another method is: shell> mysqlbinlog hostname-bin.000001>/tmp/statements. SQL
Shell> mysqlbinlog hostname-bin.000002>/tmp/statements. SQL
Shell> mysql-e "source/tmp/statements. SQL" output from mysqlbinlog can be regenerated without the original DATA file. Mysqlbinlog copies DATA to a temporary file and writes a load data local infile statement that references the file. The system determines the default location of directories written to these files. To explicitly specify a directory, use the -- local-load option. Mysqlbinlog can convert the load data infile statement to the load data local infile Statement (that is, it adds LOCAL). The client and server used to process the statement must be configured to allow LOCAL operations. Warning temporary files created for the load data local statement are not automatically deleted, because they are required before they are actually executed. Delete temporary files by yourself after you no longer need statement logs. The file is located in the temporary file directory, and the file name is similar to original_file_name -#-#.
6. Other commands for viewing MYSQL logs1. view the command of your BINLOG name: show binary logs; mysql> show binary logs;
+ --------------- + ----------- +
| Log_name | File_size |
+ --------------- + ----------- +
| Binlog.000044 | 471894871 |
| Binlog.000045 | 267061 |
+ --------------- + ----------- +
2 rows in set (0.00 sec), this File_size will increase every time you perform related operations on the table.
2. After several operations, it will be recorded. Command: show binlog events
3. Use the mysqlbinlog tool to display the binary result of the record and import it to a text file for future recovery. The detailed process is as follows:
C: \ Program Files \ MySQL Server 5.0 \ bin> mysqlbinlog -- start-position = 4 -- sto p-position = 106 mysqlbin-log.000001> c: \ test1.txt or export all: c: \ Program Files \ MySQL Server 5.0 \ bin> mysqlbinlog mysqlbin-log.000001> c: \ test1.txt
4. Import the result to MYSQL for data recovery. C: \ Program Files \ MySQL Server 5.0 \ bin> mysqlbinlog -- start-position = 134 -- stop-position = 330 mysqlbin-log.000001 | mysql-uroot-p or C: \ Program Files \ MySQL Server 5.0 \ bin> mysqlbinlog -- start-position = 134 -- stop-position = 330 mysqlbin-log.000001> test1.txt enter MYSQL import mysql> source c: \ test1.txt another way is to restore C by date: \ Program Files \ MySQL Server 5.0 \ bin> mysqlbinlog -- start-datetime = "0:20:00" -- stop-datetim = "01:25:00"/diskb/bin-logs/xxx_db-bin.000001 | mysql-u root 5. Check whether the Select * from User 6 data is enabled by other MYSQL Log commands.
Mysql> show variables like 'Log _ % ';
How to know the current log
Mysql> show master status;
Show 2? Number of logs
Mysql> show master logs;
Use mysqlbinlog to view binary log files
Shell> mysqlbinlog mail-bin.000001
Or shell> mysqlbinlog mail-bin.000001 | tail 9000
View the SQL log records of the last (last) 9000 rows of the binary log file
Appendix:
Usage of mysqlbinlogThe binary log files generated by the server are written in binary format. To check files in these text formats, use the mysqlbinlog utility.
Call mysqlbinlog as follows:
Shell> mysqlbinlog [options] log-files... for example, to display binary log binlog.000003, run the following command:
Shell> mysqlbinlog binlog.0000003 output includes all statements contained in binlog.000003 and other information, such as the time spent by each statement, the thread ID sent by the customer, and the timestamp when the thread is sent.
In general, you can use mysqlbinlog to directly read binary log files and use them on the local MySQL server. You can also use the -- read-from-remote-server option to read Binary logs from a remote server.
When reading remote binary logs, you can use the connection parameter option to indicate how to connect to the server, but they are often ignored unless you have also specified the -- read-from-remote-server option. These options are -- host, -- password, -- port, -- protocol, -- socket, and -- user.
You can also use mysqlbinlog to read the relay Log Files written from the server during the replication process. The format of the relay log is the same as that of the binary log file. Mysqlbinlog supports the following options:
·
--- Help ,-?
Displays the help message and exits.
·
--- Database = db_name,-d db_name
Only list entries of the database (only local logs are used ).
·
-- Force-read,-f
This option is used. If mysqlbinlog reads binary log events that it cannot recognize, it prints a warning, ignores the event, and continues. This option is not available. If mysqlbinlog reads this type of event, it stops.
·
-- Hexdump,-H
The log hexadecimal dump is displayed in the comment. This output helps debugging During the replication process. This option is added to MySQL 5.1.2.
·
-- Host = host_name,-h host_name
Obtain the binary log of the MySQL server on the specified host.
·
-- Local-load = path,-l pat
Pre-processes local temporary files for the load data infile in the specified directory.
·
-- Offset = N,-o N
Skip the first N entries.
·
-- Password [= password],-p [password]
The password used to connect to the server. If you use the short option format (-p), there must be no space between the option and password. If there is no password value after the -- password or-p option in the command line, a prompt is displayed for entering a password.
·
-- Port = port_num,-P port_num
The TCP/IP Port number used to connect to the remote server.
·
-- Position = N,-j N
-- Start-position should be used if it is not in favor of use.
·
-- Protocol = {TCP | SOCKET | PIPE |-position connection protocol.
·
-- Read-from-remote-server,-R
Read Binary logs from the MySQL server. If this option is not provided, any connection Parameter options will be ignored. These options are -- host, -- password, -- port, -- protocol, -- socket, and -- user.
·
-- Result-file = name,-r name
Point the output to the specified file.
·
-- Short-form,-s
Only the statements contained in the log are displayed, and other information is not displayed.
·
-- Socket = path,-S path
The socket file used for connection.
·
-- Start-datetime = datetime
Read data from 1st events whose date time is equal to or later than the datetime parameter in binary logs. The datetime value is relative to the local time zone on the machine that runs mysqlbinlog. The value format must be DATETIME or TIMESTAMP data type. For example:
Shell> mysqlbinlog -- start-datetime = "2004-12-25 11:25:56" binlog.000003 this option can help point-to-point recovery.
·
-- Stop-datetime = datetime
Stop reading from 1st events whose date time is equal to or later than the datetime parameter in the binary log. For descriptions of datetime values, see the -- start-datetime option. This option can be used for timely recovery.
·
-- Start-position = N
Read from the event where the second position in the binary log is equal to N.
·
-- Stop-position = N
Stop reading from the event where 1st locations are equal to or greater than N in the binary log.
·
-- To-last-logs,-t
The end of the binary log requested by the MySQL server is not stopped, but is printed until the end of the last binary log. If the output is sent to the same MySQL server, an infinite loop occurs. This option requires -- read-from-remote-server.
·
-- Disable-logs-bin,-D
Disable binary logs. If you use the -- to-last-logs option to send the output to the same MySQL server, you can avoid infinite loops. This option is also useful for crash recovery, avoiding copying recorded statements. Note: This option requires the SUPER permission.
·
-- User = user_name,-u user_name
The MySQL user name used to connect to the remote server.
·
-- Version,-V
Display version information and exit.
You can also use the -- var_name = value option to set the following variables:
·
Open_files_limit
Specifies the number of open file descriptors to be retained.
·
The -- hexdump option can generate a hexadecimal dump of log Content in the comment:
Shell> mysqlbinlog -- hexdump master-bin.000001 the output of the above command should be similar to a hexadecimal dump:
Http://jimmyli.blog.51cto.com/Jimmy Li Blog
