{"id":13479,"date":"2023-12-25T21:24:49","date_gmt":"2023-12-26T04:24:49","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=13479"},"modified":"2023-12-25T21:50:55","modified_gmt":"2023-12-26T04:50:55","slug":"mysql-binary-logs","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-binary-logs\/","title":{"rendered":"MySQL Binary Logs"},"content":{"rendered":"\n<p><strong>Summary<\/strong>: in this tutorial, you will learn about MySQL binary logs including essential concepts, configurations, and related statements.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Introduction to the MySQL binary logs<\/h2>\n\n\n\n<p>Binary logs are files that store the changes to the MySQL database. These logs contain a series of events that represent the modifications to data and database objects such as tables, views, databases, and so on. <\/p>\n\n\n\n<p>For example, when you create a new table, MySQL records the corresponding <code><a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-create-table\/\">CREATE TABLE<\/a><\/code> statement in the binary log. Also, when you delete a row from a table, MySQL records the <code><a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-delete\/\">DELETE<\/a><\/code> statement in the binary log.<\/p>\n\n\n\n<p>However, if you execute a <code><a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-select\/\">SELECT<\/a><\/code> statement, MySQL will not record it in the binary log because the <code>SELECT<\/code> statement does not change the database.<\/p>\n\n\n\n<p>MySQL uses the binary logs for the following purposes:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Replication<\/strong>: Binary logs provide a reliable and efficient way to replicate data between MySQL servers.<\/li>\n\n\n\n<li><strong>Recovery<\/strong>: Binary logs also play a crucial role in <a href=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-point-in-time-recovery\/\">point-in-time recovery<\/a>.<\/li>\n<\/ul>\n\n\n\n<p>MySQL supports several types of binary logs, including:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Statement-based (<code>STATEMENT<\/code>) &#8211; enables logging to use row-based.<\/li>\n\n\n\n<li>Row-based  (<code>ROW<\/code>) &#8211; enables logging to use the row-based.<\/li>\n\n\n\n<li>Mixed format (<code>MIXED<\/code>) &#8211;&nbsp;enables logging to use mixed format.<\/li>\n<\/ul>\n\n\n\n<p>Each format has its advantages and use cases.<\/p>\n\n\n\n<p>Binary logging adds some overhead that may impact performance. Therefore, you need to closely monitor it. Also, you may want to <a href=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-disable-binary-logs\/\">disable it in the development and test servers<\/a>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Binary log configuration<\/h2>\n\n\n\n<p>The following are typical binary log configurations:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">1) Enabling binary logging<\/h3>\n\n\n\n<p>MySQL enables the binary logs by default. To check if the binary log is enabled, you follow these steps:<\/p>\n\n\n\n<p>First, open the Command Prompt on Windows or Terminal on Unix-like systems and <a href=\"https:\/\/www.mysqltutorial.org\/getting-started-with-mysql\/connect-to-mysql-server\/\">connect to the MySQL server<\/a>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">mysql -u root -p<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Second, retrieve the value of the <code>log_bin<\/code> variable:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">show<\/span> <span class=\"hljs-keyword\">global<\/span> <span class=\"hljs-keyword\">variables<\/span> <span class=\"hljs-keyword\">like<\/span> <span class=\"hljs-string\">'log_bin'<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">\n+<span class=\"hljs-comment\">---------------+-------+<\/span>\n| Variable_name | Value |\n+<span class=\"hljs-comment\">---------------+-------+<\/span>\n| log_bin       | ON    |\n+<span class=\"hljs-comment\">---------------+-------+<\/span>\n1 row in <span class=\"hljs-keyword\">set<\/span> (<span class=\"hljs-number\">0.00<\/span> sec)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>If the <code>log_bin<\/code> is <code>ON<\/code>, meaning that the binary log is enabled. If it is <code>OFF<\/code>, the binary log is disabled.<\/p>\n\n\n\n<p>To enable binary logging, you add the following line to the <a href=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-configuration-file\/\">MySQL configuration<\/a> file (<code>my.cnf<\/code> or <code>my.ini<\/code>):<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">log-bin=mysql-bin<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">2) Binary log format<\/h3>\n\n\n\n<p>The <code>binlog_format<\/code> variable stores the binary log format. For example:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">show<\/span> <span class=\"hljs-keyword\">global<\/span> <span class=\"hljs-keyword\">variables<\/span> <span class=\"hljs-keyword\">like<\/span> <span class=\"hljs-string\">'binlog_format'<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">\n+<span class=\"hljs-comment\">---------------+-------+<\/span>\n| Variable_name | Value |\n+<span class=\"hljs-comment\">---------------+-------+<\/span>\n| binlog_format | ROW   |\n+<span class=\"hljs-comment\">---------------+-------+<\/span>\n1 row in <span class=\"hljs-keyword\">set<\/span> (<span class=\"hljs-number\">0.00<\/span> sec)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The output indicates that MySQL uses the <code>ROW<\/code> binary log format. To specify a different binary log format, you add the following line to the MySQL configuration file:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">binlog_format=STATEMENT<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The valid values are <code>ROW<\/code>, <code>STATEMENT<\/code>, and <code>MIXED<\/code>.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">3) Binary Log Location<\/h3>\n\n\n\n<p>By default, MySQL stores the binary logs in the <a href=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-data-directory\/\">data directory<\/a>. If you want to store the binary logs in a different location, you can specify an absolute path to the desired directory in the <a href=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-configuration-file\/\">MySQL configuration file<\/a>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-8\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">log-bin=\/absolute\/path\/to\/binary\/logs\/<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-8\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">4) Binary Log Retention<\/h3>\n\n\n\n<p>Binary logs can consume disk space over time. To prevent storage issues, you can control the lifespan of binary log files via a <em>binary log retention policy<\/em>.<\/p>\n\n\n\n<p>To set the binary log retention policy, you can set the number of days, the maximum size of the binary log file, and the number of log files in the configuration file.<\/p>\n\n\n\n<p>For example:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-9\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">expire_logs_days=7 <span class=\"hljs-comment\"># 7 days<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-9\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>This example sets the retention period to 7 days. If the logs are older than 7 days, MySQL will remove them during the regular rotation process<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-10\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">max_binlog_size=100M<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-10\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this example, we set the maximum size for a binary log file to 100 megabytes.  Note that you can specify the size in bytes, kilobytes (K), megabytes (M), or gigabytes (G).<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-11\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">max_binlog_files=10<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-11\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this example, we set the maximum number of binary log files to 10. Once the number of log files is 10, MySQL deletes (or purges) the older log files to make room for new ones.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">5) Encryption of binary logs<\/h3>\n\n\n\n<p>To enhance security, you can encrypt the binary logs using the <code>encrypt-binlog<\/code> option:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-12\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">encrypt-binlog=1<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-12\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\">Binary log statements<\/h2>\n\n\n\n<p>We&#8217;ll introduce the most commonly used statement for dealing with binary logs.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">1) SHOW BINARY LOGS<\/h3>\n\n\n\n<p>To show a list of available binary logs, you use the <code>SHOW<\/code> <code>BINARY<\/code> <code>LOGS<\/code> statement:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-13\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SHOW<\/span> <span class=\"hljs-built_in\">BINARY<\/span> <span class=\"hljs-keyword\">LOGS<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-13\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Sample output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-14\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">\n+<span class=\"hljs-comment\">---------------+-----------+-----------+<\/span>\n| Log_name      | File_size | Encrypted |\n+<span class=\"hljs-comment\">---------------+-----------+-----------+<\/span>\n| binlog.000001 |     11322 | No        |\n| binlog.000002 |       201 | No        |\n| binlog.000003 |       483 | No        |\n+<span class=\"hljs-comment\">---------------+-----------+-----------+<\/span>\n3 rows in <span class=\"hljs-keyword\">set<\/span> (<span class=\"hljs-number\">0.00<\/span> sec)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-14\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">2) SHOW MASTER STATUS<\/h3>\n\n\n\n<p>To display information about the binary log of the source server and the replication position, you use the <code>SHOW<\/code> <code>MASTER<\/code> <code>STATUS<\/code> statement:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-15\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SHOW<\/span> <span class=\"hljs-keyword\">MASTER<\/span> <span class=\"hljs-keyword\">STATUS<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-15\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Sample output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-16\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">\n+<span class=\"hljs-comment\">---------------+----------+--------------+------------------+-------------------+<\/span>\n| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |\n+<span class=\"hljs-comment\">---------------+----------+--------------+------------------+-------------------+<\/span>\n| binlog.000003 |      483 |              |                  |                   |\n+<span class=\"hljs-comment\">---------------+----------+--------------+------------------+-------------------+<\/span>\n1 row in <span class=\"hljs-keyword\">set<\/span> (<span class=\"hljs-number\">0.00<\/span> sec)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-16\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">3) PURGE BINARY LOGS<\/h3>\n\n\n\n<p>To remove old binary logs based on a given file, you use the <code>PURGE<\/code> <code>BINARY<\/code> <code>LOGS<\/code> statement. For example, the following removes the binary files 000001 and 000002:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-17\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">PURGE<\/span> <span class=\"hljs-built_in\">BINARY<\/span> <span class=\"hljs-keyword\">LOGS<\/span> <span class=\"hljs-keyword\">TO<\/span> <span class=\"hljs-string\">'binlog.000003'<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-17\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>If you display a list of binary logs, you&#8217;ll see only the log file <code>binlog.000003<\/code> left:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-18\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SHOW<\/span> <span class=\"hljs-built_in\">BINARY<\/span> <span class=\"hljs-keyword\">LOGS<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-18\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-19\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">+<span class=\"hljs-comment\">---------------+-----------+-----------+<\/span>\n| Log_name      | File_size | Encrypted |\n+<span class=\"hljs-comment\">---------------+-----------+-----------+<\/span>\n| binlog.000003 |       483 | No        |\n+<span class=\"hljs-comment\">---------------+-----------+-----------+<\/span>\n1 row in <span class=\"hljs-keyword\">set<\/span> (<span class=\"hljs-number\">0.00<\/span> sec)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-19\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">4) FLUSH BINARY LOGS<\/h3>\n\n\n\n<p>To force the MySQL server to close the current log file and open a new one, you use the <code>FLUSH<\/code> <code>BINARY<\/code> <code>LOGS<\/code> statement:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-20\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">FLUSH<\/span> <span class=\"hljs-built_in\">BINARY<\/span> <span class=\"hljs-keyword\">LOGS<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-20\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>If you issue a statement that changes the database, you&#8217;ll see a new log file. For example, let&#8217;s create a new database called <code>sampledb<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-21\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">DATABASE<\/span> sampledb;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-21\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>MySQL will record the new event in a new binary log file. <\/p>\n\n\n\n<p>The following statement displays the log file list:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-22\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SHOW<\/span> <span class=\"hljs-built_in\">BINARY<\/span> <span class=\"hljs-keyword\">LOGS<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-22\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-23\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">\n+<span class=\"hljs-comment\">---------------+-----------+-----------+<\/span>\n| Log_name      | File_size | Encrypted |\n+<span class=\"hljs-comment\">---------------+-----------+-----------+<\/span>\n| binlog.000003 |       527 | No        |\n| binlog.000004 |       157 | No        |\n+<span class=\"hljs-comment\">---------------+-----------+-----------+<\/span>\n2 rows in <span class=\"hljs-keyword\">set<\/span> (<span class=\"hljs-number\">0.00<\/span> sec)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-23\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\">The mysqlbinlog utility: examining binary log files<\/h2>\n\n\n\n<p>To view the SQL statements recorded in the binary logs, you use the <code>mysqlbinlog<\/code> utility. <\/p>\n\n\n\n<p>First, open the Command Prompt on Windows or Terminal on Unix-like systems.<\/p>\n\n\n\n<p>Second, view the contents of the binary log file <code>binlog.000003<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-24\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">mysqlbinlog \/var\/lib\/mysql\/binlog.000003<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-24\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>It&#8217;ll show the contents of the log file <code>binlog.000003<\/code>.<\/p>\n\n\n\n<p>If you want to save the contents into a file, you can use the redirection <code>><\/code> on Unix-like systems. For example, the following saves the contents of the binary log file into the <code>binlog.txt<\/code> file.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-25\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">mysqlbinlog \/var\/lib\/mysql\/binlog.000003 &gt; ~\/binlog.txt<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-25\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\">Summary<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>MySQL uses binary logs to record the change events to the data and database objects.<\/li>\n\n\n\n<li>Use the binary logs for replication and point-in-time recovery purposes.<\/li>\n\n\n\n<li>Use the <code>mysqlbinlog<\/code> utility to view the contents of binary log files.<\/li>\n<\/ul>\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Was this tutorial helpful? <\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"13479\"\n\t\t\t\tdata-post-url=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-binary-logs\/\"\n\t\t\t\tdata-post-title=\"MySQL Binary Logs\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"13479\"\n\t\t\t\tdata-post-url=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-binary-logs\/\"\n\t\t\t\tdata-post-title=\"MySQL Binary Logs\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Summary: in this tutorial, you will learn about MySQL binary logs including essential concepts, configurations, and related statements. Introduction to the MySQL binary logs Binary logs are files that store the changes to the MySQL database. These logs contain a series of events that represent the modifications to data and database objects such as tables, [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"parent":441,"menu_order":27,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-13479","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>MySQL Binary Logs<\/title>\n<meta name=\"description\" content=\"In this tutorial, you will learn about MySQL binary logs including essential concepts, configurations, and related statements.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-binary-logs\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MySQL Binary Logs\" \/>\n<meta property=\"og:description\" content=\"In this tutorial, you will learn about MySQL binary logs including essential concepts, configurations, and related statements.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-binary-logs\/\" \/>\n<meta property=\"og:site_name\" content=\"MySQL Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2023-12-26T04:50:55+00:00\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-administration\\\/mysql-binary-logs\\\/\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-administration\\\/mysql-binary-logs\\\/\",\"name\":\"MySQL Binary Logs\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/#website\"},\"datePublished\":\"2023-12-26T04:24:49+00:00\",\"dateModified\":\"2023-12-26T04:50:55+00:00\",\"description\":\"In this tutorial, you will learn about MySQL binary logs including essential concepts, configurations, and related statements.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-administration\\\/mysql-binary-logs\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-administration\\\/mysql-binary-logs\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-administration\\\/mysql-binary-logs\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.mysqltutorial.org\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MySQL Administration\",\"item\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-administration\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"MySQL Binary Logs\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/#website\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/\",\"name\":\"MySQL Tutorial\",\"description\":\"A comprehensive MySQL Tutorial\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.mysqltutorial.org\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"MySQL Binary Logs","description":"In this tutorial, you will learn about MySQL binary logs including essential concepts, configurations, and related statements.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-binary-logs\/","og_locale":"en_US","og_type":"article","og_title":"MySQL Binary Logs","og_description":"In this tutorial, you will learn about MySQL binary logs including essential concepts, configurations, and related statements.","og_url":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-binary-logs\/","og_site_name":"MySQL Tutorial","article_modified_time":"2023-12-26T04:50:55+00:00","twitter_misc":{"Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-binary-logs\/","url":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-binary-logs\/","name":"MySQL Binary Logs","isPartOf":{"@id":"https:\/\/www.mysqltutorial.org\/#website"},"datePublished":"2023-12-26T04:24:49+00:00","dateModified":"2023-12-26T04:50:55+00:00","description":"In this tutorial, you will learn about MySQL binary logs including essential concepts, configurations, and related statements.","breadcrumb":{"@id":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-binary-logs\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-binary-logs\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-binary-logs\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.mysqltutorial.org\/"},{"@type":"ListItem","position":2,"name":"MySQL Administration","item":"https:\/\/www.mysqltutorial.org\/mysql-administration\/"},{"@type":"ListItem","position":3,"name":"MySQL Binary Logs"}]},{"@type":"WebSite","@id":"https:\/\/www.mysqltutorial.org\/#website","url":"https:\/\/www.mysqltutorial.org\/","name":"MySQL Tutorial","description":"A comprehensive MySQL Tutorial","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.mysqltutorial.org\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/13479","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/comments?post=13479"}],"version-history":[{"count":6,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/13479\/revisions"}],"predecessor-version":[{"id":13490,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/13479\/revisions\/13490"}],"up":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/441"}],"wp:attachment":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/media?parent=13479"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}