{"id":59,"date":"2021-03-13T12:19:18","date_gmt":"2021-03-13T04:19:18","guid":{"rendered":"http:\/\/daishen.ltd\/?p=59"},"modified":"2021-03-13T12:19:20","modified_gmt":"2021-03-13T04:19:20","slug":"%e6%97%a5%e5%bf%97%e7%ae%a1%e7%90%86","status":"publish","type":"post","link":"https:\/\/daishen.ltd\/?p=59","title":{"rendered":"\u65e5\u5fd7\u7ba1\u7406"},"content":{"rendered":"<p><meta charset=\"UTF-8\"><meta name=\"viewport\" content=\"width=device-width initial-scale=1\"><br \/>\n<title>\u65e5\u5fd7\u7ba1\u7406<\/title><\/p>\n<h1>1.\u9519\u8bef\u65e5\u5fd7(log_error)<\/h1>\n<h2>1.1 \u4f5c\u7528<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u8bb0\u5f55\u542f\u52a8\\\u5173\u95ed\\\u65e5\u5e38\u8fd0\u884c\u8fc7\u7a0b\u4e2d,\u72b6\u6001\u4fe1\u606f,\u8b66\u544a,\u9519\u8bef\n\u6392\u67e5mysql\u8fd0\u884c\u4e2d\u7684\u9519\u8bef\n<\/code><\/pre>\n<h2>1.2 \u9519\u8bef\u65e5\u5fd7\u914d\u7f6e<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-dart\" lang=\"dart\">\u9ed8\u8ba4\u5c31\u662f\u5f00\u542f\u7684:  \/\u6570\u636e\u8def\u5f84\u4e0b\/hostname.err\n\u624b\u5de5\u8bbe\u5b9a:\nmysql&gt;select @@log_error;\nvim \/etc\/my.cnf\nlog_error=\/var\/log\/mysql.log\nlog_timestamps=system\n\u91cd\u542f\u751f\u6548\nshow variables like 'log_error';\n<\/code><\/pre>\n<h2>1.3 \u65e5\u5fd7\u5185\u5bb9\u67e5\u770b<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-css\" lang=\"css\">\u4e3b\u8981\u5173\u6ce8[ERROR],\u770b\u4e0a\u4e0b\u6587\n<\/code><\/pre>\n<h1>2. binlog(binary logs):\u4e8c\u8fdb\u5236\u65e5\u5fd7 <strong>*<\/strong><\/h1>\n<h2>2.1 \u4f5c\u7528<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">(1)\u5907\u4efd\u6062\u590d\u5fc5\u987b\u4f9d\u8d56\u4e8c\u8fdb\u5236\u65e5\u5fd7\n(2)\u4e3b\u4ece\u73af\u5883\u5fc5\u987b\u4f9d\u8d56\u4e8c\u8fdb\u5236\u65e5\u5fd7\n<\/code><\/pre>\n<h2>2.2 binlog\u914d\u7f6e (5.7\u5fc5\u987b\u52a0server_id)<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-css\" lang=\"css\">\u6ce8\u610f\uff1aMySQL\u9ed8\u8ba4\u662f\u6ca1\u6709\u5f00\u542f\u4e8c\u8fdb\u5236\u65e5\u5fd7\u7684\u3002\n\u57fa\u7840\u53c2\u6570\u67e5\u770b:\n\u5f00\u5173:\nmysql&gt;select @@log_bin;\n\u65e5\u5fd7\u8def\u5f84\u53ca\u540d\u5b57\nmysql&gt;select @@log_bin_basename;\n\u670d\u52a1ID\u53f7:\nmysql&gt;select @@server_id;\n\u4e8c\u8fdb\u5236\u65e5\u5fd7\u683c\u5f0f:\nmysql&gt;select @@binlog_format;\n\u53cc\u4e00\u6807\u51c6\u4e4b\u4e8c:\nmysql&gt;select @@sync_binlog;\n\u67e5\u770b\u4e8c\u8fdb\u5236\u8bb0\u5f55\u7684\u6587\u4ef6\u4fe1\u606f\nshow binary logs;\n<\/code><\/pre>\n<h3>2.2.1 \u521b\u5efa\u65e5\u5fd7\u76ee\u5f55<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-kotlin\" lang=\"kotlin\">mkdir \/data\/binlog\nchown -R mysql.mysql \/data\/binlog\n<\/code><\/pre>\n<h3>2.2.2 \u4fee\u6539\u914d\u7f6e\u6587\u4ef6<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-jsx\" lang=\"jsx\">vim \/etc\/my.cnf\nserver_id=6                                    -----&gt;5.6\u4e2d\uff0c\u5355\u673a\u53ef\u4ee5\u4e0d\u9700\u8981\u6b64\u53c2\u6570              \nlog_bin=\/data\/binlog\/mysql-bin\t\t\t\t\tmysql-bin\u524d\u7f00\u540d\nbinlog_format=row\t\t\t\t\t\t\t\t5.7\u7248\u672c\u9ed8\u8ba4\u662frow\n<\/code><\/pre>\n<h3>2.2.3 \u91cd\u542f\u6570\u636e\u5e93\u751f\u6548<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">[root@db01 mysql]# \/etc\/init.d\/mysqld restart\n<\/code><\/pre>\n<h3>2.2.4 \u53c2\u6570\u8bf4\u660e<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-jsx\" lang=\"jsx\">server_id=3306 \n\u4e3b\u8981\u662f\u5728\u4e3b\u4ece\u590d\u5236\u8fc7\u7a0b\u4e2d\u5fc5\u987b\u8981\u52a0\u7684,\u4f46\u662f\u57285.7\u7248\u672c\u4e2d,\u8981\u7528\u4ee5\u4e0b\u53c2\u6570(log_bin),\u5f00\u542fbinlog\u65e5\u5fd7,\u5373\u4f7f\u662f\u5355\u673a\u4e5f\u662f\u5fc5\u52a0\u7684\nlog_bin=\/data\/binlog\/mysql-bin\n(1)\u5f00\u542f\u4e8c\u8fdb\u5236\u65e5\u5fd7\u529f\u80fd\n(2)\u8bbe\u7f6e\u4e8c\u8fdb\u5236\u65e5\u5fd7\u76ee\u5f55\u53ca\u540d\u79f0\u524d\u7f00\nbinlog_format=row\nbinlog\u7684\u8bb0\u5f55\u683c\u5f0f??\n<\/code><\/pre>\n<h2>2.3 binlog\u8bb0\u5f55\u4e86\u4ec0\u4e48?<\/h2>\n<h3>2.3.0 \u5f15\u5165<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">binlog\u662fSQL\u5c42\u7684\u529f\u80fd\u3002\u8bb0\u5f55\u7684\u662f\u53d8\u66f4SQL\u8bed\u53e5\uff0c\u4e0d\u8bb0\u5f55\u67e5\u8be2\u8bed\u53e5\u3002\n<\/code><\/pre>\n<h3>2.3.1 \u8bb0\u5f55SQL\u8bed\u53e5\u79cd\u7c7b<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">DDL \uff1a\u539f\u5c01\u4e0d\u52a8\u7684\u8bb0\u5f55\u5f53\u524dDDL(statement\u8bed\u53e5\u65b9\u5f0f)\u3002\nDCL \uff1a\u539f\u5c01\u4e0d\u52a8\u7684\u8bb0\u5f55\u5f53\u524dDCL(statement\u8bed\u53e5\u65b9\u5f0f)\u3002\nDML \uff1a\u53ea\u8bb0\u5f55\u5df2\u7ecf\u63d0\u4ea4\u7684\u4e8b\u52a1DML\n<\/code><\/pre>\n<h3>2.3.2 DML\u4e09\u79cd\u8bb0\u5f55\u65b9\u5f0f<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-css\" lang=\"css\">binlog_format\uff08binlog\u7684\u8bb0\u5f55\u683c\u5f0f\uff09\u53c2\u6570\u5f71\u54cd\n\uff081\uff09statement\uff085.6\u9ed8\u8ba4\uff09SBR(statement based replication) \uff1a\u8bed\u53e5\u6a21\u5f0f\u539f\u5c01\u4e0d\u52a8\u7684\u8bb0\u5f55\u5f53\u524dDML\u3002\n\uff082\uff09ROW(5.7 \u9ed8\u8ba4\u503c) RBR(ROW based replication) \uff1a\u8bb0\u5f55\u6570\u636e\u884c\u7684\u53d8\u5316(\u7528\u6237\u770b\u4e0d\u61c2\uff0c\u9700\u8981\u5de5\u5177\u5206\u6790)\n\uff083\uff09mixed\uff08\u6df7\u5408\uff09MBR(mixed based replication)\u6a21\u5f0f  \uff1a\u4ee5\u4e0a\u4e24\u79cd\u6a21\u5f0f\u7684\u6df7\u5408\n<\/code><\/pre>\n<h3>2.3.3 \u9762\u8bd5\u9898<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">SBR\u4e0eRBR\u6a21\u5f0f\u7684\u5bf9\u6bd4\nSTATEMENT\uff1a\u53ef\u8bfb\u6027\u8f83\u9ad8\uff0c\u65e5\u5fd7\u91cf\u5c11\uff0c\u4f46\u662f\u4e0d\u591f\u4e25\u8c28\nROW      \uff1a\u53ef\u8bfb\u6027\u5f88\u4f4e\uff0c\u65e5\u5fd7\u91cf\u5927\uff0c\u8db3\u591f\u4e25\u8c28\nupdate t1 set xxx=xxx where id&gt;1000   ? --&gt;\u4e00\u5171500w\u884c\uff0crow\u6a21\u5f0f\u600e\u4e48\u8bb0\u5f55\u7684\u65e5\u5fd7\n\u4e3a\u4ec0\u4e48row\u6a21\u5f0f\u4e25\u8c28\uff1f\nid  name    intime\ninsert into t1 values(1,'zs',now())\n\u6211\u4eec\u5efa\u8bae\u4f7f\u7528\uff1arow\u8bb0\u5f55\u6a21\u5f0f\n<\/code><\/pre>\n<h2>2.4 event\uff08\u4e8b\u4ef6\uff09\u662f\u4ec0\u4e48?<\/h2>\n<h3>2.4.1 \u4e8b\u4ef6\u7684\u7b80\u4ecb<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">\u4e8c\u8fdb\u5236\u65e5\u5fd7\u7684\u6700\u5c0f\u8bb0\u5f55\u5355\u5143\n\u5bf9\u4e8eDDL,DCL,\u4e00\u4e2a\u8bed\u53e5\u5c31\u662f\u4e00\u4e2aevent\n\u5bf9\u4e8eDML\u8bed\u53e5\u6765\u8bb2:\u53ea\u8bb0\u5f55\u5df2\u63d0\u4ea4\u7684\u4e8b\u52a1\u3002\n\u4f8b\u5982\u4ee5\u4e0b\u5217\u5b50,\u5c31\u88ab\u5206\u4e3a\u4e864\u4e2aevent\nbegin;      120  - 340\t\u4e8b\u4ef61\nDML1        340  - 460\t\u4e8b\u4ef62\nDML2        460  - 550\t\u4e8b\u4ef63\ncommit;     550  - 760\t\u4e8b\u4ef64\n<\/code><\/pre>\n<h3>2.4.2 event\u7684\u7ec4\u6210<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u4e09\u90e8\u5206\u6784\u6210:\n(1) \u4e8b\u4ef6\u7684\u5f00\u59cb\u6807\u8bc6\n(2) \u4e8b\u4ef6\u5185\u5bb9\n(3) \u4e8b\u4ef6\u7684\u7ed3\u675f\u6807\u8bc6\nPosition:\n\u5f00\u59cb\u6807\u8bc6: at 194\n\u7ed3\u675f\u6807\u8bc6: end_log_pos 254\n194? 254?\n\u67d0\u4e2a\u4e8b\u4ef6\u5728binlog\u4e2d\u7684\u76f8\u5bf9\u4f4d\u7f6e\u53f7\n\u4f4d\u7f6e\u53f7\u7684\u4f5c\u7528\u662f\u4ec0\u4e48\uff1f\n\u4e3a\u4e86\u65b9\u4fbf\u6211\u4eec\u622a\u53d6\u4e8b\u4ef6\n<\/code><\/pre>\n<h2>2.5 \u65e5\u5fd7\u6587\u4ef6\u67e5\u770b<\/h2>\n<h3>2.5.1 \u67e5\u770b\u65e5\u5fd7\u7684\u5f00\u542f\u60c5\u51b5<\/h3>\n<p>log_bin\u53c2\u6570\u8bbe\u7f6e\u7684\u8def\u5f84,\u53ef\u4ee5\u627e\u5230\u4e8c\u8fdb\u5236\u65e5\u5fd7<\/p>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">mysql&gt;show variables like '%log_bin%';\n+---------------------------------+------------------------------+\n| Variable_name                   | Value                        |\n+---------------------------------+------------------------------+\n| log_bin                         | ON                           |\n| log_bin_basename                | \/data\/binlog\/mysql-bin       |\n| log_bin_index                   | \/data\/binlog\/mysql-bin.index |\n| log_bin_trust_function_creators | OFF                          |\n| log_bin_use_v1_row_events       | OFF                          |\n| sql_log_bin                     | ON                           |\n+---------------------------------+------------------------------+\n6 rows in set (0.01 sec)\n<\/code><\/pre>\n<h3>2.5.2 \u67e5\u770b\u4e00\u5171\u591a\u5c11\u4e2abinlog<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">mysql&gt;show binary logs;\n+------------------+-----------+\n| Log_name         | File_size |\n+------------------+-----------+\n| mysql-bin.000001 |       154 |\n+------------------+-----------+\n1 row in set (0.01 sec)\n\nmysql&gt;flush logs;  #\u6eda\u52a8\u4e00\u4e2a\u65b0\u65e5\u5fd7\nQuery OK, 0 rows affected (0.03 sec)\n\nmysql&gt;flush logs;\nQuery OK, 0 rows affected (0.01 sec)\n\nmysql&gt;show binary logs;\n+------------------+-----------+\n| Log_name         | File_size |\n+------------------+-----------+\n| mysql-bin.000001 |       201 |\n| mysql-bin.000002 |       201 |\n| mysql-bin.000003 |       154 |\n+------------------+-----------+\n3 rows in set (0.00 sec)\n\nmysql&gt;\n<\/code><\/pre>\n<h3>2.5.3 \u67e5\u770bmysql\u6b63\u5728\u4f7f\u7528\u7684\u65e5\u5fd7\u6587\u4ef6<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">mysql&gt;show master status;\n+------------------+----------+--------------+------------------+-------------------+\n| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |\n+------------------+----------+--------------+------------------+-------------------+\n| mysql-bin.000003 |      154 |              |                  |                   |\n+------------------+----------+--------------+------------------+-------------------+\nmysql&gt;\n<\/code><\/pre>\n<p>file\uff1a\u5f53\u524dMySQL\u6b63\u5728\u4f7f\u7528\u7684\u6587\u4ef6\u540d<br \/>\nPosition\uff1a\u6700\u540e\u4e00\u4e2a\u4e8b\u4ef6\u7684\u7ed3\u675f\u4f4d\u7f6e\u53f7<\/p>\n<h2>2.6 \u65e5\u5fd7\u5185\u5bb9\u67e5\u770b<\/h2>\n<h3>2.6.1 event\u67e5\u770b<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">Master [binlog]&gt;show binlog events in 'mysql-bin.000001';\n+------------------+-----+----------------+-----------+-------------+----------------------------------------+\n| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                   |\n+------------------+-----+----------------+-----------+-------------+----------------------------------------+\n| mysql-bin.000001 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.26-log, Binlog ver: 4  |\n| mysql-bin.000001 | 123 | Previous_gtids |         6 |         154 |                                        |\n| mysql-bin.000001 | 154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |\n| mysql-bin.000001 | 219 | Query          |         6 |         335 | create database binlog charset utf8mb4 |\n| mysql-bin.000001 | 335 | Anonymous_Gtid |         6 |         400 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |\n| mysql-bin.000001 | 400 | Query          |         6 |         501 | use `binlog`; create table t1(id int)  |\n+------------------+-----+----------------+-----------+-------------+----------------------------------------+\n6 rows in set (0.00 sec)\n\n\nLog_name\uff1abinlog\u6587\u4ef6\u540d\nPos\uff1a\u5f00\u59cb\u7684position    *****\nEvent_type\uff1a\u4e8b\u4ef6\u7c7b\u578b\nFormat_desc\uff1a\u683c\u5f0f\u63cf\u8ff0\uff0c\u6bcf\u4e00\u4e2a\u65e5\u5fd7\u6587\u4ef6\u7684\u7b2c\u4e00\u4e2a\u4e8b\u4ef6\uff0c\u591a\u7528\u6237\u6ca1\u6709\u610f\u4e49\uff0cMySQL\u8bc6\u522bbinlog\u5fc5\u8981\u4fe1\u606f\nServer_id\uff1amysql\u670d\u52a1\u53f7\u6807\u8bc6\nEnd_log_pos\uff1a\u4e8b\u4ef6\u7684\u7ed3\u675f\u4f4d\u7f6e\u53f7 *****\nInfo\uff1a\u4e8b\u4ef6\u5185\u5bb9*****\n\u8865\u5145:\nSHOW BINLOG EVENTS\n   [IN 'log_name']\n   [FROM pos]\n   [LIMIT [offset,] row_count]\n[root@db01 binlog]# mysql -e \"show binlog events in 'mysql-bin.000004'\" |grep drop\n<\/code><\/pre>\n<h3>2.6.2 binlog\u6587\u4ef6\u5185\u5bb9\u8be6\u7ec6\u67e5\u770b<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-kotlin\" lang=\"kotlin\">mysqlbinlog \/data\/mysql\/mysql-bin.000006\nmysqlbinlog --base64-output=decode-rows -vvv \/data\/binlog\/mysql-bin.000003\nmysqlbinlog  -d binlog \/data\/binlog\/mysql-bin.000003\n\n[root@db01 binlog]# mysqlbinlog --start-datetime='2019-05-06 17:00:00' --stop-datetime='2019-05-06 17:01:00'  \/data\/binlog\/mysql-bin.000004 \n\nmysqlbinlog -d cuoni mysql-bin.000001\t\t\u53ea\u67e5\u770b\u67d0\u4e2a\u5e93\u4e0b\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\n<\/code><\/pre>\n<h2>2.7 \u57fa\u4e8ePosition\u53f7\u8fdb\u884c\u65e5\u5fd7\u622a\u53d6<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">\u6838\u5fc3\u5c31\u662f\u627e\u622a\u53d6\u7684\u8d77\u70b9\u548c\u7ec8\u70b9\n--start-position=321\n--stop-position=513\n    \n mysqlbinlog --start-position=219 --stop-position=1347 \/data\/binlog\/mysql-bin.000003 &gt;\/tmp\/bin.sql\n\n\u6848\u4f8b: \u4f7f\u7528binlog\u65e5\u5fd7\u8fdb\u884c\u6570\u636e\u6062\u590d\n\u6a21\u62df:\n1. \nmysql&gt;create database binlog charset utf8;\n2. \nmysql&gt;use binlog;\n[binlog]&gt;create table t1(id int);\n3. \n[binlog]&gt;insert into t1 values(1);\n[binlog]&gt;commit;\n[binlog]&gt;insert into t1 values(2);\n[binlog]&gt;commit;\n[binlog]&gt;insert into t1 values(3);\n[binlog]&gt;commit;\n4. \n[binlog]&gt;drop database binlog;\n\n\u6062\u590d:\nmysql&gt;show master status ;\t\t\u786e\u8ba4\u4f7f\u7528\u7684\u90a3\u4e2a\u65e5\u5fd7\nmysql&gt;show binlog events in 'mysql-bin.000004';\n[root@db01 binlog]# mysqlbinlog --start-position=1227 --stop-position=2342 \/data\/binlog\/mysql-bin.000004 &gt;\/tmp\/bin.sql\nmysql&gt;set sql_Log_bin=0;\t\t\u4e34\u65f6\u5173\u95ed\u6062\u590d\u4ea7\u751f\u7684\u65b0\u65e5\u5fd7\nmysql&gt;source \/tmp\/bin.sql\nmysql&gt;set sql_Log_bin=1;\t\t\u6539\u56de\u6765\n\n\u9762\u8bd5\u6848\u4f8b:\n1. \u5907\u4efd\u7b56\u7565\u6bcf\u5929\u5168\u5907,\u6709\u5168\u91cf\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\n2.\u4e1a\u52a1\u4e2d\u4e00\u517110\u4e2a\u5e93,\u5176\u4e2d\u4e00\u4e2a\u88ab\u8befdrop\u4e86\n3. \u9700\u8981\u5728\u5176\u4ed69\u4e2a\u5e93\u6b63\u5e38\u5de5\u4f5c\u8fc7\u7a0b\u4e2d\u8fdb\u884c\u6570\u636e\u6062\u590d\n<\/code><\/pre>\n<h2>2.8 binlog\u65e5\u5fd7\u7684GTID\u65b0\u7279\u6027<\/h2>\n<h3>2.8.1 GTID \u4ecb\u7ecd<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-css\" lang=\"css\">5.6 \u7248\u672c\u65b0\u52a0\u7684\u7279\u6027,5.7\u4e2d\u505a\u4e86\u52a0\u5f3a\n5.6 \u4e2d\u4e0d\u5f00\u542f,\u6ca1\u6709\u8fd9\u4e2a\u529f\u80fd.\n5.7 \u4e2d\u7684GTID,\u5373\u4f7f\u4e0d\u5f00\u4e5f\u4f1a\u6709\u81ea\u52a8\u751f\u6210\nSET @@SESSION.GTID_NEXT= 'ANONYMOUS'\n    \n\u5bf9\u4e8ebinlog\u4e2d\u7684\u6bcf\u4e00\u4e00\u4e2a\u4e8b\u52a1,\u90fd\u4f1a\u751f\u6210\u4e00\u4e2aGTID\u53f7\u7801.\nDDL , DCL\u4e8e\u4e2aevent\u5c31\u662f \u4e00\u4e2a\u4e8b\u52a1 ,\u5c31\u4f1a\u6709\u4e00\u4e2aGTID\u53f7.\nDML\u8bed\u53e5\u6765\u8bb2, begin\u5230commit,\u662f\u4e00\u4e2a\u4e8b\u52a1,\u5c31\u662f\u4e00\u4e2aGTID\u53f7\n\nshow variables like '%gtid%' ;   \u67e5\u770bgtid\u72b6\u6001\n\n<\/code><\/pre>\n<h3>2.8.2. GTID(Global Transaction ID)<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u662f\u5bf9\u4e8e\u4e00\u4e2a\u5df2\u63d0\u4ea4\u4e8b\u52a1\u7684\u7f16\u53f7\uff0c\u5e76\u4e14\u662f\u4e00\u4e2a\u5168\u5c40\u552f\u4e00\u7684\u7f16\u53f7\u3002\n\u5b83\u7684\u5b98\u65b9\u5b9a\u4e49\u5982\u4e0b\uff1a\n\nGTID = server_uuid \uff1aTID\n7E11FA47-31CA-19E1-9E56-C43AA21293967:29\nTID\u662f\u4e00\u4e2a\u81ea\u589e\u957f\u7684\u6570\u636e\uff0c\u4ece1\u5f00\u59cb\n<\/code><\/pre>\n<p>\u91cd\u8981\u53c2\u6570\u4ecb\u7ecd\uff1a<\/p>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">vim \/etc\/my.cnf\ngtid-mode=on\nenforce-gtid-consistency=true\nsystemctl restart mysqld\n\n--skip -gtids\t\t\u5728\u5bfc\u51fa\u65f6,\u5ffd\u7565\u539f\u6709\u7684gtid\u4fe1\u606f\uff0c\u6062\u590d\u65f6\u751f\u6210\u6700\u65b0\u7684gtid\u4fe1\u606f\n--include -gtids \t\u60f3\u622a\u53d6\u7684gtid\n--exclude-gtids= 'd60b549f-9e10-11e9-ab04-000c294a1b3b:6','d60b549f-9e10-11e9-ab04-000c294a1b3b:8'\t\u6392\u9664\u7684\n\n<\/code><\/pre>\n<h3>2.8.3. \u57fa\u4e8eGTID\u8fdb\u884c\u67e5\u770bbinlog<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-php\" lang=\"php\">\u5177\u5907GTID\u540e,\u622a\u53d6\u67e5\u770b\u67d0\u4e9b\u4e8b\u52a1\u65e5\u5fd7:\n--include-gtids\n--exclude-gtids\nmysqlbinlog --include-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:1-6' --exclude-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:4'  \/data\/binlog\/mysql-bin.000004\n<\/code><\/pre>\n<h3>2.8.4  GTID\u7684\u5e42\u7b49\u6027<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-kotlin\" lang=\"kotlin\">\u5982\u679c\u62ff\u6709GTID\u7684\u65e5\u5fd7\u53bb\u6062\u590d\u65f6,\u68c0\u67e5\u5f53\u524d\u7cfb\u7edf\u4e2d\u662f\u5426\u6709\u76f8\u540cGTID\u53f7,\u6709\u76f8\u540c\u7684\u5c31\u81ea\u52a8\u8df3\u8fc7\n\u4f1a\u5f71\u54cd\u5230binlog\u6062\u590d\u548c\u4e3b\u4ece\u590d\u5236.\n\n\u5c31\u60f3\u6062\u590d?\u600e\u4e48\u529e?\n--skip-gtids\nmysqlbinlog --include-gtids='3ca79ab5-3e4d-11e9-a709-000c293b577e:4' \/data\/binlog\/mysql-bin.000004 \/data\/binlog\/mysql-bin.000004\nset sql_log_bin=0;\nsource \/tmp\/binlog.sql\nset sql_log_bin=1;\n<\/code><\/pre>\n<h2>2.9 \u4f7f\u7528\u4e8c\u8fdb\u5236\u65e5\u5fd7\u6062\u590d\u6570\u636e\u6848\u4f8b<\/h2>\n<h3>2.9.1 \u6545\u969c\u73af\u5883\u4ecb\u7ecd<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">\u521b\u5efa\u4e86\u4e00\u4e2a\u5e93  db, \u5bfc\u5165\u4e86\u8868t1 ,t1\u8868\u4e2d\u5f55\u5165\u4e86\u5f88\u591a\u6570\u636e\n\u4e00\u4e2a\u5f00\u53d1\u4eba\u5458,drop database db;\n\u6ca1\u6709\u5907\u4efd,\u65e5\u5fd7\u90fd\u5728.\u600e\u4e48\u6062\u590d?\n\u601d\u8def:\u627e\u5230\u5efa\u5e93\u8bed\u53e5\u5230\u5220\u5e93\u4e4b\u524d\u6240\u6709\u7684\u65e5\u5fd7,\u8fdb\u884c\u6062\u590d.(\u5f00\u542f\u4e86GTID\u6a21\u5f0f)\n\u6545\u969c\u6848\u4f8b\u6a21\u62df:\n(0) drop database if exists db ;\n(1) create database db charset utf8;     \n(2) use db;\n(3) create table t1 (id int);\n(4) insert into t1 values(1),(2),(3);\n(5) insert into t1 values(4),(5),(6);\n(6) commit\n(7) update t1 set id=30 where id=3;\n(8) commit;\n(9) delete from t1 where id=4;\n(10)commit;\n(11)insert into t1 values(7),(8),(9);\n(12)commit;\n(13)drop database db;\n========================\ndrop database if exists db ;\ncreate database db charset utf8; \nuse db;\ncreate table t1 (id int);\ninsert into t1 values(1),(2),(3);\ninsert into t1 values(4),(5),(6);\ncommit;\nupdate t1 set id=30 where id=3;\ncommit;\ndelete from t1 where id=4;\ncommit;\ninsert into t1 values(7),(8),(9);\ncommit;\ndrop database db;\n=======\n\u8fd0\u884c\u4ee5\u4e0a\u8bed\u53e5\uff0c\u6a21\u62df\u6545\u969c\u573a\u666f\n\u9700\u6c42\uff1a\u5c06\u6570\u636e\u5e93\u6062\u590d\u5230\u4ee5\u4e0b\u72b6\u6001\uff08\u63d0\u793a\u7b2c9\u6b65\u548c\u7b2c13\u6b65\u662f\u8bef\u64cd\u4f5c\uff0c\u5176\u4ed6\u90fd\u662f\u6b63\u5e38\u64cd\u4f5c\uff09\n<\/code><\/pre>\n<h3>2.9.2 \u6062\u590d\u8fc7\u7a0b(\u65e0GTID\u65f6\u7684\u6062\u590d)<\/h3>\n<ol start=\"\">\n<li>\u67e5\u770b\u5f53\u524d\u4f7f\u7528\u7684 binlog\u6587\u4ef6<\/li>\n<\/ol>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">mysql&gt;show master status ;\n+------------------+----------+--------------+------------------+-------------------+\n\n| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |\n\n+------------------+----------+--------------+------------------+-------------------+\n\n| mysql-bin.000006 |    1873 |              |                  |                  |\n\n+------------------+----------+--------------+------------------+-------------------+\n\n2.\u67e5\u770b\u4e8b\u4ef6\uff1a\n\n\u7b2c\u4e00\u6bb5\uff1a\n| mysql-bin.000006 |  813 | Query      |        1 |        907 | use `db`; create table t1 (id int)                  |\n\n| mysql-bin.000006 |  907 | Query      |        1 |        977 | BEGIN                                              |\n\n| mysql-bin.000006 |  977 | Table_map  |        1 |        1020 | table_id: 77 (db.t1)                                |\n\n| mysql-bin.000006 | 1020 | Write_rows  |        1 |        1070 | table_id: 77 flags: STMT_END_F                      |\n\n| mysql-bin.000006 | 1070 | Table_map  |        1 |        1113 | table_id: 77 (db.t1)                                |\n\n| mysql-bin.000006 | 1113 | Write_rows  |        1 |        1163 | table_id: 77 flags: STMT_END_F                      |\n\n| mysql-bin.000006 | 1163 | Xid        |        1 |        1194 | COMMIT \/* xid=74 *\/                                |\n\n| mysql-bin.000006 | 1194 | Query      |        1 |        1264 | BEGIN                                              |\n\n| mysql-bin.000006 | 1264 | Table_map  |        1 |        1307 | table_id: 77 (db.t1)                                |\n\n| mysql-bin.000006 | 1307 | Update_rows |        1 |        1353 | table_id: 77 flags: STMT_END_F                      |\n\n| mysql-bin.000006 | 1353 | Xid        |        1 |        1384 | COMMIT \/* xid=77 *\/   \n\nmysqlbinlog --start-position=813 --stop-position=1384 \/data\/mysql\/mysql-bin.000006 &gt;\/tmp\/bin1.sql \n<\/code><\/pre>\n<p>\u7b2c\u4e8c\u6bb5\uff1a<\/p>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">| mysql-bin.000006 | 1568 | Query      |        1 |        1638 | BEGIN                                              |\n\n| mysql-bin.000006 | 1638 | Table_map  |        1 |        1681 | table_id: 77 (db.t1)                                |\n\n| mysql-bin.000006 | 1681 | Write_rows  |        1 |        1731 | table_id: 77 flags: STMT_END_F                      |\n\n| mysql-bin.000006 | 1731 | Xid        |        1 |        1762 | COMMIT \/* xid=81 *\/ \n\nmysqlbinlog --start-position=1568 --stop-position=1762 \/data\/mysql\/mysql-bin.000006 &gt;\/tmp\/bin2.sql\n<\/code><\/pre>\n<p>3.\u6062\u590d<\/p>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">set sql_log_bin=0;\nsource \/tmp\/bin1.sql\nsource \/tmp\/bin2.sql\nset sql_log_bin=1;\n\nmysql&gt;select * from t1;\n<\/code><\/pre>\n<h3>2.9.3 \u6709GTID\u7684\u6062\u590d:<\/h3>\n<p>(1)\u622a\u53d6<\/p>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">mysqlbinlog --skip-gtids --include-gtids='3ca79ab5-3e4d-11e9-a709-000c293b577e:7-12' mysql-bin.000004&gt; \/tmp\/bin.sql\n\n--skip-gtids\t\u4f5c\u7528:\u5728\u5bfc\u51fa\u65f6,\u5ffd\u7565\u539f\u6709\u7684gtid\u4fe1\u606f\uff0c\u6062\u590d\u65f6\u751f\u6210\u6700\u65b0\u7684gtid\u4fe1\u606f\n<\/code><\/pre>\n<p>(2)\u6062\u590d<\/p>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-shell\" lang=\"shell\">set sql_log_bin=0;\nsource \/tmp\/bin.sql\n<\/code><\/pre>\n<h2>2.10\u4e8c\u8fdb\u5236\u65e5\u5fd7\u5176\u4ed6\u64cd\u4f5c<\/h2>\n<h3>2.10.1 \u81ea\u52a8\u6e05\u7406\u65e5\u5fd7<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">show variables like '%expire%';\nexpire_logs_days  0   \n\u81ea\u52a8\u6e05\u7406\u65f6\u95f4,\u662f\u8981\u6309\u7167\u5168\u5907\u5468\u671f+1\nset global expire_logs_days=8;\n\u6c38\u4e45\u751f\u6548:\nmy.cnf\nexpire_logs_days=15;\n\u4f01\u4e1a\u5efa\u8bae,\u81f3\u5c11\u4fdd\u7559\u4e24\u4e2a\u5168\u5907\u5468\u671f+1\u7684binlog\n<\/code><\/pre>\n<h3>2.10.2 \u624b\u5de5\u6e05\u7406<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;\nPURGE BINARY LOGS TO 'mysql-bin.000010';\n\u6ce8\u610f:\u4e0d\u8981\u624b\u5de5 rm binlog\u6587\u4ef6\n1. my.cnf binlog\u5173\u95ed\u6389,\u542f\u52a8\u6570\u636e\u5e93\n2.\u628a\u6570\u636e\u5e93\u5173\u95ed,\u5f00\u542fbinlog,\u542f\u52a8\u6570\u636e\u5e93\nreset master \u5220\u9664\u6240\u6709binlog,\u5e76\u4ece000001\u5f00\u59cb\u91cd\u65b0\u8bb0\u5f55\u65e5\u5fd7\n<\/code><\/pre>\n<p><strong>*reset master;     \u4e3b\u4ece\u5173\u7cfb\u4e2d\uff0c\u4e3b\u5e93\u6267\u884c\u6b64\u64cd\u4f5c\uff0c\u4e3b\u4ece\u73af\u5883\u5fc5\u5d29<\/strong><\/p>\n<h3>2.10.3 \u65e5\u5fd7\u662f\u600e\u4e48\u6eda\u52a8<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">flush logs; \n\u91cd\u542fmysql\u4e5f\u4f1a\u81ea\u52a8\u6eda\u52a8\u4e00\u4e2a\u65b0\u7684\n\u65e5\u5fd7\u6587\u4ef6\u8fbe\u52301G\u5927\u5c0f(max_binlog_size)\n| max_binlog_size                          | 1073741824     \n\u5907\u4efd\u65f6,\u52a0\u5165\u53c2\u6570\u4e5f\u53ef\u4ee5\u81ea\u52a8\u6eda\u52a8\n<\/code><\/pre>\n<h1>3.slow_log \u6162\u65e5\u5fd7<\/h1>\n<h2>3.1 \u4f5c\u7528:<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u8bb0\u5f55\u6162SQL\u8bed\u53e5\u7684\u65e5\u5fd7,\u5b9a\u4f4d\u4f4e\u6548SQL\u8bed\u53e5\u7684\u5de5\u5177\u65e5\u5fd7\n\u8bb0\u5f55\u8fd0\u884c\u8f83\u6162\u7684\u8bed\u53e5\uff0c\u4f18\u5316\u8fc7\u7a0b\u4e2d\u5e38\u7528\u7684\u5de5\u5177\u65e5\u5fd7.\n<\/code><\/pre>\n<h2>3.2 \u5f00\u542f\u6162\u65e5\u5fd7(\u9ed8\u8ba4\u6ca1\u5f00\u542f)<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-cpp\" lang=\"cpp\">\u67e5\u770b\u6709\u5173\u4e8e\u6162\u65e5\u5fd7\u7684\u4fe1\u606f\nshow variables like '%slow%';\n\n\n#\u6162\u65e5\u5fd7\u5f00\u5173:\nslow_query_log=1 \n#\u6587\u4ef6\u4f4d\u7f6e\u53ca\u540d\u5b57 \nslow_query_log_file=\/data\/mysql\/slow.log\n#\u8bbe\u5b9a\u6162\u67e5\u8be2\u65f6\u95f4:\nlong_query_time=0.1\n#\u6ca1\u8d70\u7d22\u5f15\u7684\u8bed\u53e5\u4e5f\u8bb0\u5f55:\nlog_queries_not_using_indexes\n    \nvim \/etc\/my.cnf\nslow_query_log=1 \nslow_query_log_file=\/data\/mysql\/slow.log\nlong_query_time=0.1\nlog_queries_not_using_indexes\n    \nsystemctl restart mysqld\n<\/code><\/pre>\n<h2>3.3 mysqldumpslow \u5206\u6790\u6162\u65e5\u5fd7<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-cpp\" lang=\"cpp\">mysqldumpslow -s c -t 10 \/data\/mysql\/slow.log\n\n# \u7b2c\u4e09\u65b9\u5de5\u5177(\u81ea\u5df1\u6269\u5c55)\nhttps:\/\/www.percona.com\/downloads\/percona-toolkit\/LATEST\/\nyum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-Digest-MD5\ntoolkit\u5de5\u5177\u5305\u4e2d\u7684\u547d\u4ee4:\n.\/pt-query-diagest  \/data\/mysql\/slow.log\nAnemometer\u57fa\u4e8ept-query-digest\u5c06MySQL\u6162\u67e5\u8be2\u53ef\u89c6\u5316\n<\/code><\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u65e5\u5fd7\u7ba1\u7406 1.\u9519\u8bef\u65e5\u5fd7(log_error) 1.1 \u4f5c\u7528 &nbsp; \u8bb0\u5f55\u542f\u52a8\\\u5173\u95ed\\\u65e5\u5e38\u8fd0\u884c\u8fc7\u7a0b\u4e2d,\u72b6\u6001 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[],"class_list":["post-59","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/daishen.ltd\/index.php?rest_route=\/wp\/v2\/posts\/59","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/daishen.ltd\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/daishen.ltd\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/daishen.ltd\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/daishen.ltd\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=59"}],"version-history":[{"count":1,"href":"https:\/\/daishen.ltd\/index.php?rest_route=\/wp\/v2\/posts\/59\/revisions"}],"predecessor-version":[{"id":60,"href":"https:\/\/daishen.ltd\/index.php?rest_route=\/wp\/v2\/posts\/59\/revisions\/60"}],"wp:attachment":[{"href":"https:\/\/daishen.ltd\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=59"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/daishen.ltd\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=59"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/daishen.ltd\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=59"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}