mysqlbinlog Command in Linux
mysqlbinlog is a utility provided by MySQL that allows you to process and analyze binary log files. Binary logs are essential for replication and data recovery in MySQL. They record all changes made to the database, such as INSERT, UPDATE, DELETE operations, and can be used to replicate these changes to other servers or restore data after a crash.
Table of Contents
Here is a comprehensive guide to the options available with the mysqlbinlog command −
Understanding mysqlbinlog Command
Binary logs are files that record all changes made to the database. They are essential for replication and data recovery. The binary log consists of a set of binary log files and an index file that lists all the binary log files.
Before using mysqlbinlog, you need to have MySQL installed on your Linux system. mysqlbinlog is included with the MySQL server package. If it's not already installed, you can install it using your package manager.
For Debian-based systems (e.g., Ubuntu)
sudo apt update sudo apt install mysql-client
For Red Hat-based systems (e.g., CentOS)
sudo yum install mysql-server
Verify installation −
which mysqlbinlog
How to Use mysqlbinlog Command in Linux?
Enabling Binary Logging
To use binary logs, you need to enable binary logging in your MySQL server configuration. This is done by adding the following lines to your MySQL configuration file (my.cnf or my.ini) −
[mysqld] log-bin=mysql-bin server-id=1
After making these changes, restart the MySQL server −
sudo systemctl restart mysql
Viewing Binary Logs
You can view the binary logs using the SHOW BINARY LOGS command in the MySQL client.
SHOW BINARY LOGS;
This command will display a list of all binary log files and their sizes.
Using mysqlbinlog to Read Binary Logs
The mysqlbinlog utility is used to read and process binary log files. The basic syntax for using mysqlbinlog is as follows −
mysqlbinlog [options] log_file
Reading a Binary Log File
To read a binary log file, use the mysqlbinlog command followed by the log file name.
mysqlbinlog mysql-bin.000001
This command will display the contents of the binary log file mysql-bin.000001.
Filtering Events by Date and Time
You can filter events in the binary log by date and time using the --start-datetime and --stop-datetime options.
mysqlbinlog --start-datetime="2024-01-01 00:00:00" --stop-datetime="2024-01-02 00:00:00" mysql-bin.000001
This command will display events in the binary log file mysql-bin.000001 that occurred between January 1, 2024, and January 2, 2024.
Filtering Events by Position
You can also filter events by position using the --start-position and --stop-position options.
mysqlbinlog --start-position=120 --stop-position=240 mysql-bin.000001
This command will display events in the binary log file mysql-bin.000001 that occurred between positions 120 and 240.
Applying Binary Log Events to a Database
You can apply the events in a binary log file to a database using the mysql command.
mysqlbinlog mysql-bin.000001 | mysql -u root -p
This command will read the events in the binary log file mysql-bin.000001 and apply them to the database.
Backing Up Binary Logs
You can back up binary logs by copying them to a secure location. This is useful for data recovery and replication.
cp /var/lib/mysql/mysql-bin.000001 /backup/mysql-bin.000001
This command will copy the binary log file mysql-bin.000001 to the /backup directory.
Rotating Binary Logs
You can rotate binary logs using the FLUSH LOGS command in the MySQL client. This creates a new binary log file and closes the current one.
FLUSH LOGS;
This command will create a new binary log file and close the current one.
Purging Binary Logs
To delete old binary log files, use the PURGE BINARY LOGS command in the MySQL client.
PURGE BINARY LOGS TO 'mysql-bin.000010';
This command will delete all binary log files up to mysql-bin.000010.
Checking Binary Log Format
You can check the format of the binary log using the SHOW VARIABLES LIKE 'binlog_format'; command in the MySQL client.
SHOW VARIABLES LIKE 'binlog_format';
This command will display the format of the binary log, which can be STATEMENT, ROW, or MIXED.
Changing Binary Log Format
To change the format of the binary log, use the SET GLOBAL binlog_format command in the MySQL client.
SET GLOBAL binlog_format = 'ROW';
This command will change the format of the binary log to ROW.
Using mysqlbinlog with Replication
mysqlbinlog is essential for MySQL replication. You can use it to set up and manage replication between a master and slave server.
mysqlbinlog --read-from-remote-server --host=master_host --user=replication_user --password=replication_password mysql-bin.000001 | mysql -u root -p
This command will read the binary log file mysql-bin.000001 from the master server and apply it to the slave server.
Analyzing Binary Logs for Troubleshooting
You can use mysqlbinlog to analyze binary logs for troubleshooting purposes. This can help you identify issues with data changes and replication.
mysqlbinlog --verbose mysql-bin.000001
This command will display detailed information about the events in the binary log file mysql-bin.000001.
Using mysqlbinlog with GTIDs
Global Transaction Identifiers (GTIDs) are unique identifiers for transactions in MySQL. You can use mysqlbinlog to work with GTIDs.
mysqlbinlog --include-gtids='UUID:1-10' mysql-bin.000001
This command will display events in the binary log file mysql-bin.000001 that have GTIDs in the range UUID:1-10.
Using mysqlbinlog with Compression
You can use mysqlbinlog with compressed binary log files. This can save storage space and improve performance.
mysqlbinlog --read-from-remote-server --host=master_host --user=replication_user --password=replication_password --compress mysql-bin.000001 | mysql -u root -p
This command will read the compressed binary log file mysql-bin.000001 from the master server and apply it to the slave server.
Using mysqlbinlog with SSL
You can use mysqlbinlog with SSL to secure the connection between the client and the server.
mysqlbinlog --read-from-remote-server --host=master_host --user=replication_user --password=replication_password --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem mysql-bin.000001 | mysql -u root -p
This command will read the binary log file mysql-bin.000001 from the master server using an SSL connection and apply it to the slave server.
Conclusion
mysqlbinlog is a powerful and versatile tool for managing and analyzing binary log files in MySQL. It provides a wide range of commands for reading, filtering, and applying binary log events, making it an essential tool for database administrators.
By understanding and using the various options and commands available in mysqlbinlog, you can effectively manage your MySQL server's binary logs and ensure the smooth operation of your database.