binlog to view the specified database method
MySQL's binlog (binary log) mainly records all SQL statements that change data executed on the database, including data insertion, update and delete operations. But looking directly at binlog is not intuitive because it is stored in binary format. To view the content in binlog, we usually usemysqlbinlogtool.
If we want to view the content of binlog in the specified database, we need to first determine the location and name of the binlog file, and then usemysqlbinlogView the tool. Here are the detailed steps and examples:
1.1 Determine the location and name of the binlog file
First, we need to know where the MySQL binlog file is saved. This can usually be found in MySQL configuration files (e.g.or) or we can get it through SQL query:
SHOW VARIABLES LIKE 'log_bin%';
This displays variables related to binlog, includinglog_bin(Indicates whether binlog is enabled) andlog_bin_basename(Basic name of binlog file).
1.2 Use mysqlbinlog tool to view binlog
Suppose we have found the location and name of the binlog file (e.g./var/lib/mysql/binlog.000001), we can usemysqlbinlogTools to view it. However, looking directly at the entire binlog file may contain a lot of content, and we may be interested only in the operations of a specific database.
In order to filter operations for a specific database, we can use--databaseOptions. Here is a sample command:
mysqlbinlog --database=your_database_name /var/lib/mysql/binlog.000001
hereyour_database_nameIt is the name of the database we want to view.
1.3 View multiple binlog files
If our operations span multiple binlog files, we can use wildcards or list all related files and usemysqlbinlogView them one by one, or usemysqlbinlogof--start-datetimeand--stop-datetimeOption to specify the time range.
1.4 Export binlog content as SQL file
If we want to export the contents of binlog as SQL files for later viewing or analysis, we can use the redirect operator>：
mysqlbinlog --database=your_database_name /var/lib/mysql/binlog.000001 >
This will export the content of the binlog to the namein the file.
1.5 Things to note
- Make sure we have sufficient permissions to read the binlog file.
- If the binlog files are large, it may take some time to view or export them.
- In a production environment, viewing or operating binlog directly can have a performance impact, so it is best to do these operations during low peak periods.
What is it
mysqlbinlogis a practical tool provided by MySQL for processing binary log files. Binary logs are log files used by MySQL database management systems to record database changes, including SQL statements that change data (such as INSERT, UPDATE, DELETE, etc.) and possible data table structure changes (such as ALTER TABLE).
mysqlbinlogThe main uses of the tool include:
（1）View binary log content: We can usemysqlbinlogTo view SQL statements recorded in binary log files for troubleshooting, auditing, or data recovery.
（2）Convert binary logs to SQL statements:passmysqlbinlog, we can convert events in binary log files into SQL statements and save them to a file. This is especially useful for data recovery or data migration.
（3）Time or location based filtering：mysqlbinlogAllows us to filter events based on timestamps or log locations, displaying only the parts we are interested in.
（4）Replay the binary log: In some cases, we may need to replay events in the binary log onto another MySQL server or another instance of the same server. This is usually used for data replication or recovery.
（5）Analyze binary logs: By analyzing binary logs, we can understand the activity of the database, such as which tables or data rows have been changed, and how often they have changed.
usemysqlbinlogThe basic syntax of is as follows:
mysqlbinlog [options] log_file ...
inlog_fileis the name of the binary log file we want to process, andoptionsis an optional parameter that specifies how to process log files.
For example, to view the namebinlog.000001The binary log file is aboutmydatabaseFor all events in the database, we can use the following command:
mysqlbinlog --database=mydatabase binlog.000001
This will display all targetsmydatabaseSQL statements for the database.
3. How to use mysqlbinlog
To usemysqlbinlogThe tool to view the binary log (binlog) of MySQL, we need to follow the following steps:
3.1 Determine the location and file name of the binary log
First, we need to know where the binary log files are stored and their file names. We can view this information through the MySQL command line client:
SHOW BINARY LOGS;
This command lists all binary log files.
3.2 Using mysqlbinlog tool
Once we know the location and file name of the binary log, we can usemysqlbinlogTools to view them. On the command line, we can call it like thismysqlbinlog：
mysqlbinlog /path/to/binlog-file-name
in/path/to/binlog-file-nameis the complete path and file name of our binary log file.
3.3 Filtering specific databases or time ranges
If we are only interested in the operations of a specific database, we can use--databaseOptions to filter results:
mysqlbinlog --database=your_database_name /path/to/binlog-file-name
If we want to filter results based on time range, we can use--start-datetimeand--stop-datetimeOptions:
mysqlbinlog --start-datetime="2023-10-23 10:00:00" --stop-datetime="2023-10-23 14:00:00" /path/to/binlog-file-name
3.4 Save the output to a file
If we want tomysqlbinlogThe output of is saved to a file, and the redirection operator can be used.>：
mysqlbinlog /path/to/binlog-file-name >
Or, if we are using the filtering option, we can do this:
mysqlbinlog --database=your_database_name /path/to/binlog-file-name >
3.5 Other options
mysqlbinlogMany other options and features are provided that we can usemysqlbinlog --helpCommand to view the complete list of options and descriptions.
3.6 Things to note
- Make sure we have sufficient permissions to read the binary log file.
- It may take some time to complete when working with large files.
- If we use binary logs for replication in production environments, please be careful to avoid affecting the replication process.
- In use
--start-datetimeand--stop-datetimeWhen Options, make sure the time format is correct (YYYY-MM-DD HH:MM:SS).
Hope this information helps us! If we have any other questions, feel free to ask.
4. How to create binary log files in MySQL
In MySQL, binary logs (also known as binlogs) are mainly used for replication and data recovery. By default, it may not be enabled, but we can enable it by configuring the MySQL server.
Here are the steps to enable and configure MySQL binary logging:
（1）Edit MySQL configuration file：
MySQL configuration files are usually located in/etc/mysql/(For Debian/Ubuntu systems) or/etc/(For other systems). On some systems, it may be located/etc/mysql//in a subdirectory below.
Open the configuration file and find[mysqld]part.
（2）Enable binary logging：
exist[mysqld]Under the section, add or modify the following lines to enable binary logging:
log_bin = /var/log/mysql/
The above path is just an example, we can store the logs wherever we want. Ensure that the MySQL user has permission to write to the directory.
（3）(Optional) Set binary log format：
We can also set the format of binary logs. MySQL supports three formats: STATEMENT, ROW, and MIXED.
-
STATEMENT: Logging based on SQL statements. -
ROW: Logging of row-based changes. -
MIXED: Mixed STATEMENT and ROW formats.
We can use the following configuration to set the format:
binlog_format = ROW
（1）(Optional) Set the expiration time of the binary log：
We can set the length of time that the binary log file will remain before it is automatically deleted. For example, if we want the file to be deleted after 7 days, we can add:
expire_logs_days = 7
（2）Save and close the configuration file。
（3）Restart MySQL server：
For the change to take effect, we need to restart the MySQL server. This can be done with one of the following commands (depending on our system and how we install):
sudo service mysql restart # orsudo systemctl restart mysql
（4）Verify that the binary log is enabled：
Log in to MySQL and execute the following query:
SHOW VARIABLES LIKE 'log_bin%';
We should be able to seelog_binThe value of is the path we set before, andlog_bin_basenameandlog_bin_indexThe relevant value of .
Now, MySQL has started recording binary logs. These logs are very useful for replication, data recovery and auditing. However, note that enabling binary logging may add some disk I/O and CPU overhead, especially on high-load servers.
This is the article about mysql binlog viewing the specified database. For more related mysql binlog viewing the specified database, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!