MySQL复制应用中继日志解析

> select * from table_pk; +-----+-----------+-----+-----+ | id | name | age | sex | +-----+-----------+-----+-----+ | 111 | zhangsan | 20 | 0 | | 222 | lisi | 22 | 1 | | 333 | wangwu | 22 | 1 | | 444 | lilei | 32 | 0 | | 555 | hanmeimei | 30 | 1 | +-----+-----------+-----+-----+ 5 rows in set (0.00 sec) (dg6)root@localhost [mytest]> show global variables like ‘%binlog_format%‘; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 1 row in set (0.00 sec)

那么我们去从库看看

(dg7)root@localhost [mytest]> <span style="color: #0000ff;">select</span> *<span style="color: #000000;"> from table_pk;
</span>+-----+-----------+-----+-----+
| <span style="color: #0000ff;">id</span>  | name      | age | sex |
+-----+-----------+-----+-----+
| <span style="color: #800080;">111</span> | zhangsan  |  <span style="color: #800080;">20</span> |   <span style="color: #800080;">0</span> |
| <span style="color: #800080;">222</span> | lisi      |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">333</span> | wangwu    |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">444</span> | lilei     |  <span style="color: #800080;">32</span> |   <span style="color: #800080;">0</span> |
| <span style="color: #800080;">555</span> | hanmeimei |  <span style="color: #800080;">30</span> |   <span style="color: #800080;">1</span> |
+-----+-----------+-----+-----+
<span style="color: #800080;">5</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
(dg7)root@localhost [mytest]</span>> show global variables like <span style="color: #800000;">‘</span><span style="color: #800000;">%binlog_format%</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
</span>+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| binlog_format            | ROW               |
+--------------------------+-------------------+
<span style="color: #800080;">8</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
(dg7)root@localhost [mytest]</span>><span style="color: #000000;"> show slave status\G
</span>*************************** <span style="color: #800080;">1</span>. row ***************************<span style="color: #000000;">
               Slave_IO_State: Waiting </span><span style="color: #0000ff;">for</span><span style="color: #000000;"> master to send event
                  Master_Host: </span><span style="color: #800080;">192.168</span>.<span style="color: #800080;">80.106</span><span style="color: #000000;">
                  Master_User: repl
                  Master_Port: </span><span style="color: #800080;">3306</span><span style="color: #000000;">
                Connect_Retry: </span><span style="color: #800080;">60</span><span style="color: #000000;">
              Master_Log_File: dg6</span>-logbin.<span style="color: #800080;">000001</span><span style="color: #000000;">
          Read_Master_Log_Pos: </span><span style="color: #800080;">4469</span><span style="color: #000000;">
               Relay_Log_File: dg7</span>-relay-bin.<span style="color: #800080;">000002</span><span style="color: #000000;">
                Relay_Log_Pos: </span><span style="color: #800080;">4681</span><span style="color: #000000;">
        Relay_Master_Log_File: dg6</span>-logbin.<span style="color: #800080;">000001</span><span style="color: #000000;">
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: </span><span style="color: #800080;">0</span><span style="color: #000000;">
                   Last_Error: 
                 Skip_Counter: </span><span style="color: #800080;">0</span><span style="color: #000000;">
          Exec_Master_Log_Pos: </span><span style="color: #800080;">4469</span><span style="color: #000000;">
              Relay_Log_Space: </span><span style="color: #800080;">4883</span><span style="color: #000000;">
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: </span><span style="color: #800080;">0</span><span style="color: #000000;">
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: </span><span style="color: #800080;">0</span><span style="color: #000000;">
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: </span><span style="color: #800080;">0</span><span style="color: #000000;">
                Last_IO_Error: 
               Last_SQL_Errno: </span><span style="color: #800080;">0</span><span style="color: #000000;">
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: </span><span style="color: #800080;">1</span><span style="color: #000000;">
                  Master_UUID: b888e1ea</span>-<span style="color: #800080;">9739</span>-11e4-a24e-<span style="color: #000000;">000c29b24887
             Master_Info_File: </span>/data/mydata/master.<span style="color: #0000ff;">info</span><span style="color: #000000;">
                    SQL_Delay: </span><span style="color: #800080;">0</span><span style="color: #000000;">
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting </span><span style="color: #0000ff;">for</span> the slave I/<span style="color: #000000;">O thread to update it
           Master_Retry_Count: </span><span style="color: #800080;">86400</span><span style="color: #000000;">
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: b888e1ea</span>-<span style="color: #800080;">9739</span>-11e4-a24e-000c29b24887:<span style="color: #800080;">1</span>-<span style="color: #800080;">17</span><span style="color: #000000;">
            Executed_Gtid_Set: a9926b45</span>-975d-11e4-a339-000c29b24888:<span style="color: #800080;">1</span>-<span style="color: #800080;">9</span><span style="color: #000000;">,
b888e1ea</span>-<span style="color: #800080;">9739</span>-11e4-a24e-000c29b24887:<span style="color: #800080;">1</span>-<span style="color: #800080;">17</span><span style="color: #000000;">
                Auto_Position: </span><span style="color: #800080;">1</span>
<span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span> sec)

数据是复制过来的，MySQL主从复制是正常的，那么我们为了验证MySQL复制SQL线程是居于刚才那张图的流程，有主键，就按主键更新匹配更新记录。

那么我们在从库修改一行数据，故意制造不一致。
  

(dg7)root@localhost [mytest]> UPDATE `table_pk` SET `name`=<span style="color: #800000;">‘</span><span style="color: #800000;">laowang</span><span style="color: #800000;">‘</span> WHERE `<span style="color: #0000ff;">id</span>`=<span style="color: #800000;">333；</span><span style="color: #000000;">
Query OK, </span><span style="color: #800080;">0</span> rows affected (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
Rows matched: </span><span style="color: #800080;">1</span>  Changed: <span style="color: #800080;">0</span>  Warnings: <span style="color: #800080;">0</span><span style="color: #000000;">
(dg7)root@localhost [mytest]</span>> <span style="color: #0000ff;">select</span> *<span style="color: #000000;"> from table_pk;
</span>+-----+-----------+-----+-----+
| <span style="color: #0000ff;">id</span>  | name      | age | sex |
+-----+-----------+-----+-----+
| <span style="color: #800080;">111</span> | zhangsan  |  <span style="color: #800080;">20</span> |   <span style="color: #800080;">0</span> |
| <span style="color: #800080;">222</span> | lisi      |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">333</span> | laowang   |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">444</span> | lilei     |  <span style="color: #800080;">32</span> |   <span style="color: #800080;">0</span> |
| <span style="color: #800080;">555</span> | hanmeimei |  <span style="color: #800080;">30</span> |   <span style="color: #800080;">1</span> |
+-----+-----------+-----+-----+
<span style="color: #800080;">5</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span> sec)

这时候主从数据不一致了

<span style="color: #000000;">主库
(dg6)root@localhost [mytest]</span>> <span style="color: #0000ff;">select</span> * from table_pk where <span style="color: #0000ff;">id</span>=<span style="color: #800080;">333</span><span style="color: #000000;">;
</span>+-----+--------+-----+-----+
| <span style="color: #0000ff;">id</span>  | name   | age | sex |
+-----+--------+-----+-----+
| <span style="color: #800080;">333</span> | wangwu |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
+-----+--------+-----+-----+
<span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
从库
(dg7)root@localhost [mytest]</span>> <span style="color: #0000ff;">select</span> * from table_pk where <span style="color: #0000ff;">id</span>=<span style="color: #800080;">333</span><span style="color: #000000;">;
</span>+-----+---------+-----+-----+
| <span style="color: #0000ff;">id</span>  | name    | age | sex |
+-----+---------+-----+-----+
| <span style="color: #800080;">333</span> | laowang |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
+-----+---------+-----+-----+
<span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
(dg7)root@localhost [mytest]</span>> 

那么，我们在主库更新一行数据。

(dg6)root@localhost [mytest]> UPDATE `table_pk` SET `name`=<span style="color: #800000;">‘</span><span style="color: #800000;">wangzi</span><span style="color: #800000;">‘</span> WHERE `<span style="color: #0000ff;">id</span>`=<span style="color: #800080;">333</span><span style="color: #000000;">;
Query OK, </span><span style="color: #800080;">1</span> row affected (<span style="color: #800080;">0.01</span><span style="color: #000000;"> sec)
Rows matched: </span><span style="color: #800080;">1</span>  Changed: <span style="color: #800080;">1</span>  Warnings: <span style="color: #800080;">0</span><span style="color: #000000;">
(dg6)root@localhost [mytest]</span>> <span style="color: #0000ff;">select</span> * from table_pk where <span style="color: #0000ff;">id</span>=<span style="color: #800080;">333</span><span style="color: #000000;">;
</span>+-----+--------+-----+-----+
| <span style="color: #0000ff;">id</span>  | name   | age | sex |
+-----+--------+-----+-----+
| <span style="color: #800080;">333</span> | wangzi |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
+-----+--------+-----+-----+
<span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span> sec)

我们来看一下从库状态，是不是主库的更新给复制过来了，见证奇迹的时候到了

###############################################<br>(dg7)root@localhost [mytest]> <span style="color: #0000ff;">select</span> * from table_pk where <span style="color: #0000ff;">id</span>=<span style="color: #800080;">333</span><span style="color: #000000;">;
</span>+-----+---------+-----+-----+
| <span style="color: #0000ff;">id</span>  | name    | age | sex |
+-----+---------+-----+-----+
| <span style="color: #800080;">333</span> | laowang |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
+-----+---------+-----+-----+
<span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)<br>#########################  神奇的是主库的更新过来了#############################################
(dg7)root@localhost [mytest]</span>> <span style="color: #0000ff;">select</span> * from table_pk where <span style="color: #0000ff;">id</span>=<span style="color: #800080;">333</span><span style="color: #000000;">;
</span>+-----+--------+-----+-----+
| <span style="color: #0000ff;">id</span>  | name   | age | sex |
+-----+--------+-----+-----+
| <span style="color: #800080;">333</span> | wangzi |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
+-----+--------+-----+-----+
<span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
<br>#########################那么看一下MySQL主从复制状态看看，也是正常的######################
(dg7)root@localhost [mytest]</span>><span style="color: #000000;"> show slave status\G
</span>*************************** <span style="color: #800080;">1</span>. row ***************************<span style="color: #000000;">
               Slave_IO_State: Waiting </span><span style="color: #0000ff;">for</span><span style="color: #000000;"> master to send event
                  Master_Host: </span><span style="color: #800080;">192.168</span>.<span style="color: #800080;">80.106</span><span style="color: #000000;">
                  Master_User: repl
                  Master_Port: </span><span style="color: #800080;">3306</span><span style="color: #000000;">
                Connect_Retry: </span><span style="color: #800080;">60</span><span style="color: #000000;">
              Master_Log_File: dg6</span>-logbin.<span style="color: #800080;">000001</span><span style="color: #000000;">
          Read_Master_Log_Pos: </span><span style="color: #800080;">5249</span><span style="color: #000000;">
               Relay_Log_File: dg7</span>-relay-bin.<span style="color: #800080;">000002</span><span style="color: #000000;">
                Relay_Log_Pos: </span><span style="color: #800080;">5461</span><span style="color: #000000;">
        Relay_Master_Log_File: dg6</span>-logbin.<span style="color: #800080;">000001</span><span style="color: #000000;">
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: </span><span style="color: #800080;">0</span><span style="color: #000000;">
                   Last_Error: 
                 Skip_Counter: </span><span style="color: #800080;">0</span><span style="color: #000000;">
          Exec_Master_Log_Pos: </span><span style="color: #800080;">5249</span><span style="color: #000000;">
              Relay_Log_Space: </span><span style="color: #800080;">5663</span><span style="color: #000000;">
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: </span><span style="color: #800080;">0</span><span style="color: #000000;">
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: </span><span style="color: #800080;">0</span><span style="color: #000000;">
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: </span><span style="color: #800080;">0</span><span style="color: #000000;">
                Last_IO_Error: 
               Last_SQL_Errno: </span><span style="color: #800080;">0</span><span style="color: #000000;">
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: </span><span style="color: #800080;">1</span><span style="color: #000000;">
                  Master_UUID: b888e1ea</span>-<span style="color: #800080;">9739</span>-11e4-a24e-<span style="color: #000000;">000c29b24887
             Master_Info_File: </span>/data/mydata/master.<span style="color: #0000ff;">info</span><span style="color: #000000;">
                    SQL_Delay: </span><span style="color: #800080;">0</span><span style="color: #000000;">
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting </span><span style="color: #0000ff;">for</span> the slave I/<span style="color: #000000;">O thread to update it
           Master_Retry_Count: </span><span style="color: #800080;">86400</span><span style="color: #000000;">
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: b888e1ea</span>-<span style="color: #800080;">9739</span>-11e4-a24e-000c29b24887:<span style="color: #800080;">1</span>-<span style="color: #800080;">20</span><span style="color: #000000;">
            Executed_Gtid_Set: a9926b45</span>-975d-11e4-a339-000c29b24888:<span style="color: #800080;">1</span>-<span style="color: #800080;">11</span><span style="color: #000000;">,
b888e1ea</span>-<span style="color: #800080;">9739</span>-11e4-a24e-000c29b24887:<span style="color: #800080;">1</span>-<span style="color: #800080;">20</span><span style="color: #000000;">
                Auto_Position: </span><span style="color: #800080;">1</span>
<span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
(dg7)root@localhost [mytest]</span>> 

3.验证没有索引的情况

 主库创建表和插入记录

<span style="color: #000000;"> CREATE TABLE `table_index` (
  `</span><span style="color: #0000ff;">id</span>` <span style="color: #0000ff;">int</span>(<span style="color: #800080;">11</span><span style="color: #000000;">) NOT NULL,
  `name` varchar(</span><span style="color: #800080;">20</span><span style="color: #000000;">) NOT NULL,
  `age` tinyint(</span><span style="color: #800080;">4</span><span style="color: #000000;">) NOT NULL,
  `sex` tinyint(</span><span style="color: #800080;">4</span>) NOT NULL COMMENT <span style="color: #800000;">‘</span><span style="color: #800000;">0,man,1,woman</span><span style="color: #800000;">‘</span><span style="color: #000000;">,
  ) ENGINE</span>=<span style="color: #000000;">InnoDB 
(dg6)root@localhost [mytest]</span>> <span style="color: #0000ff;">select</span> *<span style="color: #000000;"> from table_index;
</span>+-----+-----------+-----+-----+
| <span style="color: #0000ff;">id</span>  | name      | age | sex |
+-----+-----------+-----+-----+
| <span style="color: #800080;">111</span> | zhangsan  |  <span style="color: #800080;">20</span> |   <span style="color: #800080;">0</span> |
| <span style="color: #800080;">222</span> | lisi      |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">333</span> | wangwu    |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">444</span> | lilei     |  <span style="color: #800080;">32</span> |   <span style="color: #800080;">0</span> |
| <span style="color: #800080;">555</span> | hanmeimei |  <span style="color: #800080;">30</span> |   <span style="color: #800080;">1</span> |
+-----+-----------+-----+-----+
<span style="color: #800080;">5</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
(dg6)root@localhost [mytest]</span>> 

从库看看

(dg7)root@localhost [mytest]> <span style="color: #0000ff;">select</span> *<span style="color: #000000;"> from table_index;
</span>+-----+-----------+-----+-----+
| <span style="color: #0000ff;">id</span>  | name      | age | sex |
+-----+-----------+-----+-----+
| <span style="color: #800080;">111</span> | zhangsan  |  <span style="color: #800080;">20</span> |   <span style="color: #800080;">0</span> |
| <span style="color: #800080;">222</span> | lisi      |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">333</span> | wangwu    |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">444</span> | lilei     |  <span style="color: #800080;">32</span> |   <span style="color: #800080;">0</span> |
| <span style="color: #800080;">555</span> | hanmeimei |  <span style="color: #800080;">30</span> |   <span style="color: #800080;">1</span> |
+-----+-----------+-----+-----+
<span style="color: #800080;">5</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span> sec)

我们在从库继续搞破坏，把age为22的记录修改为laowang，这时候主从已经不一致了。

(dg7)root@localhost [mytest]> <span style="color: #0000ff;">select</span> * from table_index where age=<span style="color: #800000;">‘</span><span style="color: #800000;">22</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
</span>+-----+--------+-----+-----+
| <span style="color: #0000ff;">id</span>  | name   | age | sex |
+-----+--------+-----+-----+
| <span style="color: #800080;">333</span> | wangwu |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
+-----+--------+-----+-----+
<span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
(dg7)root@localhost [mytest]</span>> update table_index set name=<span style="color: #800000;">‘</span><span style="color: #800000;">laowang</span><span style="color: #800000;">‘</span> where age=<span style="color: #800080;">22</span><span style="color: #000000;">;
Query OK, </span><span style="color: #800080;">1</span> row affected (<span style="color: #800080;">0.01</span><span style="color: #000000;"> sec)
Rows matched: </span><span style="color: #800080;">1</span>  Changed: <span style="color: #800080;">1</span>  Warnings: <span style="color: #800080;">0</span><span style="color: #000000;">
(dg7)root@localhost [mytest]</span>> <span style="color: #0000ff;">select</span> * from table_index where age=<span style="color: #800080;">22</span><span style="color: #000000;">;
</span>+-----+---------+-----+-----+
| <span style="color: #0000ff;">id</span>  | name    | age | sex |
+-----+---------+-----+-----+
| <span style="color: #800080;">333</span> | laowang |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
+-----+---------+-----+-----+
<span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
(dg7)root@localhost [mytest]</span>> 

 

那么我们还是在主库更新一下记录。

(dg6)root@localhost [mytest]> <span style="color: #0000ff;">select</span> * from table_index where name=<span style="color: #800000;">‘</span><span style="color: #800000;">lisi</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
</span>+-----+------+-----+-----+
| <span style="color: #0000ff;">id</span>  | name | age | sex |
+-----+------+-----+-----+
| <span style="color: #800080;">222</span> | lisi |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
+-----+------+-----+-----+
<span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
(dg6)root@localhost [mytest]</span>>  update table_index set age=<span style="color: #800080;">30</span> where name=<span style="color: #800000;">‘</span><span style="color: #800000;">lisi</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
Query OK, </span><span style="color: #800080;">1</span> row affected (<span style="color: #800080;">0.01</span><span style="color: #000000;"> sec)
Rows matched: </span><span style="color: #800080;">1</span>  Changed: <span style="color: #800080;">1</span>  Warnings: <span style="color: #800080;">0</span><span style="color: #000000;">
(dg6)root@localhost [mytest]</span>> <span style="color: #0000ff;">select</span> * from table_index where name=<span style="color: #800000;">‘</span><span style="color: #800000;">lisi</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
</span>+-----+------+-----+-----+
| <span style="color: #0000ff;">id</span>  | name | age | sex |
+-----+------+-----+-----+
| <span style="color: #800080;">222</span> | lisi |  <span style="color: #800080;">30</span> |   <span style="color: #800080;">1</span> |
+-----+------+-----+-----+
<span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
(dg6)root@localhost [mytest]</span>> 

 

回到从库看看，数据没有更新过来，主从复制也是异常的

(dg7)root@localhost [mytest]> <span style="color: #0000ff;">select</span> * from table_index where name=<span style="color: #800000;">‘</span><span style="color: #800000;">lisi</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
</span>+-----+------+-----+-----+
| <span style="color: #0000ff;">id</span>  | name | age | sex |
+-----+------+-----+-----+
| <span style="color: #800080;">222</span> | lisi |  <span style="color: #800080;">33</span> |   <span style="color: #800080;">1</span> |
+-----+------+-----+-----+
<span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
(dg7)root@localhost [mytest]</span>><span style="color: #000000;"> show slave status\G
</span>*************************** <span style="color: #800080;">1</span>. row ***************************<span style="color: #000000;">
               Slave_IO_State: Waiting </span><span style="color: #0000ff;">for</span><span style="color: #000000;"> master to send event
                  Master_Host: </span><span style="color: #800080;">192.168</span>.<span style="color: #800080;">80.106</span><span style="color: #000000;">
                  Master_User: repl
                  Master_Port: </span><span style="color: #800080;">3306</span><span style="color: #000000;">
                Connect_Retry: </span><span style="color: #800080;">60</span><span style="color: #000000;">
              Master_Log_File: dg6</span>-logbin.<span style="color: #800080;">000001</span><span style="color: #000000;">
          Read_Master_Log_Pos: </span><span style="color: #800080;">7376</span><span style="color: #000000;">
               Relay_Log_File: dg7</span>-relay-bin.<span style="color: #800080;">000003</span><span style="color: #000000;">
                Relay_Log_Pos: </span><span style="color: #800080;">724</span><span style="color: #000000;">
        Relay_Master_Log_File: dg6</span>-logbin.<span style="color: #800080;">000001</span><span style="color: #000000;">
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: </span><span style="color: #800080;">1032</span><span style="color: #000000;">
                   Last_Error: Could not execute Update_rows event on table mytest.table_index; Can</span><span style="color: #800000;">‘</span><span style="color: #800000;">t find record in </span><span style="color: #800000;">‘</span>table_index<span style="color: #800000;">‘</span><span style="color: #800000;">, Error_code: 1032; Corrupted replication event was detected, Error_code: 1610; handler error HA_ERR_END_OF_FILE; the event</span><span style="color: #800000;">‘</span>s master log dg6-logbin.<span style="color: #800080;">000001</span>, end_log_pos <span style="color: #800080;">7345</span><span style="color: #000000;">
                 Skip_Counter: </span><span style="color: #800080;">0</span><span style="color: #000000;">
          Exec_Master_Log_Pos: </span><span style="color: #800080;">7112</span><span style="color: #000000;">
              Relay_Log_Space: </span><span style="color: #800080;">8090</span><span style="color: #000000;">
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: </span><span style="color: #800080;">0</span><span style="color: #000000;">
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: </span><span style="color: #800080;">0</span><span style="color: #000000;">
                Last_IO_Error: 
               Last_SQL_Errno: </span><span style="color: #800080;">1032</span><span style="color: #000000;">
               Last_SQL_Error: Could not execute Update_rows event on table mytest.table_index; Can</span><span style="color: #800000;">‘</span><span style="color: #800000;">t find record in </span><span style="color: #800000;">‘</span>table_index<span style="color: #800000;">‘</span><span style="color: #800000;">, Error_code: 1032; Corrupted replication event was detected, Error_code: 1610; handler error HA_ERR_END_OF_FILE; the event</span><span style="color: #800000;">‘</span>s master log dg6-logbin.<span style="color: #800080;">000001</span>, end_log_pos <span style="color: #800080;">7345</span><span style="color: #000000;">
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: </span><span style="color: #800080;">1</span><span style="color: #000000;">
                  Master_UUID: b888e1ea</span>-<span style="color: #800080;">9739</span>-11e4-a24e-<span style="color: #000000;">000c29b24887
             Master_Info_File: </span>/data/mydata/master.<span style="color: #0000ff;">info</span><span style="color: #000000;">
                    SQL_Delay: </span><span style="color: #800080;">0</span><span style="color: #000000;">
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: </span><span style="color: #800080;">86400</span><span style="color: #000000;">
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: </span><span style="color: #800080;">150425</span> <span style="color: #800080;">08</span>:<span style="color: #800080;">30</span>:<span style="color: #800080;">49</span><span style="color: #000000;">
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: b888e1ea</span>-<span style="color: #800080;">9739</span>-11e4-a24e-000c29b24887:<span style="color: #800080;">1</span>-<span style="color: #800080;">28</span><span style="color: #000000;">
            Executed_Gtid_Set: a9926b45</span>-975d-11e4-a339-000c29b24888:<span style="color: #800080;">1</span>-<span style="color: #800080;">14</span><span style="color: #000000;">,
b888e1ea</span>-<span style="color: #800080;">9739</span>-11e4-a24e-000c29b24887:<span style="color: #800080;">1</span>-<span style="color: #800080;">27</span><span style="color: #000000;">
                Auto_Position: </span><span style="color: #800080;">1</span>
<span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
(dg7)root@localhost [mytest]</span>> 

 

4.验证有唯一索引情况

 

测试方法都一样，下面步骤我都就贴结果了。

(dg6)root@localhost [mytest]> <span style="color: #0000ff;">select</span> *<span style="color: #000000;"> from table_index;
</span>+-----+-----+-----------+-----+-----+
| <span style="color: #0000ff;">id</span>  | sid | name      | age | sex |
+-----+-----+-----------+-----+-----+
| <span style="color: #800080;">111</span> |   <span style="color: #800080;">1</span> | zhangsan  |  <span style="color: #800080;">20</span> |   <span style="color: #800080;">0</span> |
| <span style="color: #800080;">222</span> |   <span style="color: #800080;">2</span> | lisi      |  <span style="color: #800080;">30</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">333</span> |   <span style="color: #800080;">3</span> | wangzi    |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">444</span> |   <span style="color: #800080;">4</span> | lilei     |  <span style="color: #800080;">32</span> |   <span style="color: #800080;">0</span> |
| <span style="color: #800080;">555</span> |   <span style="color: #800080;">5</span> | hanmeimei |  <span style="color: #800080;">30</span> |   <span style="color: #800080;">1</span> |
+-----+-----+-----------+-----+-----+
<span style="color: #800080;">5</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
(dg6)root@localhost [mytest]</span>> <span style="color: #0000ff;">select</span> * from table_index where sid=<span style="color: #800080;">3</span><span style="color: #000000;">;
</span>+-----+-----+--------+-----+-----+
| <span style="color: #0000ff;">id</span>  | sid | name   | age | sex |
+-----+-----+--------+-----+-----+
| <span style="color: #800080;">333</span> |   <span style="color: #800080;">3</span> | wangzi |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
+-----+-----+--------+-----+-----+
<span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
(dg6)root@localhost [mytest]</span>> update table_index set name=<span style="color: #800000;">‘</span><span style="color: #800000;">wangwu</span><span style="color: #800000;">‘</span> where sid=<span style="color: #800080;">3</span><span style="color: #000000;">;
Query OK, </span><span style="color: #800080;">1</span> row affected (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
Rows matched: </span><span style="color: #800080;">1</span>  Changed: <span style="color: #800080;">1</span>  Warnings: <span style="color: #800080;">0</span><span style="color: #000000;">
(dg6)root@localhost [mytest]</span>> <span style="color: #0000ff;">select</span> * from table_index where sid=<span style="color: #800080;">3</span><span style="color: #000000;">;
</span>+-----+-----+--------+-----+-----+
| <span style="color: #0000ff;">id</span>  | sid | name   | age | sex |
+-----+-----+--------+-----+-----+
| <span style="color: #800080;">333</span> |   <span style="color: #800080;">3</span> | wangwu |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
+-----+-----+--------+-----+-----+
<span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
(dg6)root@localhost [mytest]</span>> 

从库看看，能更新过来，而且主从复制状态是正常的

(dg7)root@localhost [mytest]> <span style="color: #0000ff;">select</span> *<span style="color: #000000;"> from table_index;
</span>+-----+-----+-----------+-----+-----+
| <span style="color: #0000ff;">id</span>  | sid | name      | age | sex |
+-----+-----+-----------+-----+-----+
| <span style="color: #800080;">111</span> |   <span style="color: #800080;">1</span> | zhangsan  |  <span style="color: #800080;">20</span> |   <span style="color: #800080;">0</span> |
| <span style="color: #800080;">222</span> |   <span style="color: #800080;">2</span> | lisi      |  <span style="color: #800080;">30</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">333</span> |   <span style="color: #800080;">3</span> | wangzi    |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">444</span> |   <span style="color: #800080;">4</span> | lilei     |  <span style="color: #800080;">32</span> |   <span style="color: #800080;">0</span> |
| <span style="color: #800080;">555</span> |   <span style="color: #800080;">5</span> | hanmeimei |  <span style="color: #800080;">30</span> |   <span style="color: #800080;">1</span> |
+-----+-----+-----------+-----+-----+
<span style="color: #800080;">5</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
(dg7)root@localhost [mytest]</span>> update table_index set name=<span style="color: #800000;">‘</span><span style="color: #800000;">laowang</span><span style="color: #800000;">‘</span> where sid=<span style="color: #800080;">3</span><span style="color: #000000;">;
Query OK, </span><span style="color: #800080;">1</span> row affected (<span style="color: #800080;">0.01</span><span style="color: #000000;"> sec)
Rows matched: </span><span style="color: #800080;">1</span>  Changed: <span style="color: #800080;">1</span>  Warnings: <span style="color: #800080;">0</span><span style="color: #000000;">
(dg7)root@localhost [mytest]</span>> <span style="color: #0000ff;">select</span> * from table_index where sid=<span style="color: #800080;">3</span><span style="color: #000000;">;
</span>+-----+-----+---------+-----+-----+
| <span style="color: #0000ff;">id</span>  | sid | name    | age | sex |
+-----+-----+---------+-----+-----+
| <span style="color: #800080;">333</span> |   <span style="color: #800080;">3</span> | laowang |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
+-----+-----+---------+-----+-----+
<span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
(dg7)root@localhost [mytest]</span>><span style="color: #000000;"> show slave status\G
</span>*************************** <span style="color: #800080;">1</span>. row ***************************<span style="color: #000000;">
               Slave_IO_State: Waiting </span><span style="color: #0000ff;">for</span><span style="color: #000000;"> master to send event
                  Master_Host: </span><span style="color: #800080;">192.168</span>.<span style="color: #800080;">80.106</span><span style="color: #000000;">
                  Master_User: repl
                  Master_Port: </span><span style="color: #800080;">3306</span><span style="color: #000000;">
                Connect_Retry: </span><span style="color: #800080;">60</span><span style="color: #000000;">
              Master_Log_File: dg6</span>-logbin.<span style="color: #800080;">000001</span><span style="color: #000000;">
          Read_Master_Log_Pos: </span><span style="color: #800080;">13038</span><span style="color: #000000;">
               Relay_Log_File: dg7</span>-relay-bin.<span style="color: #800080;">000005</span><span style="color: #000000;">
                Relay_Log_Pos: </span><span style="color: #800080;">5841</span><span style="color: #000000;">
        Relay_Master_Log_File: dg6</span>-logbin.<span style="color: #800080;">000001</span><span style="color: #000000;">
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: </span><span style="color: #800080;">0</span><span style="color: #000000;">
                   Last_Error: 
                 Skip_Counter: </span><span style="color: #800080;">0</span><span style="color: #000000;">
          Exec_Master_Log_Pos: </span><span style="color: #800080;">13038</span><span style="color: #000000;">
              Relay_Log_Space: </span><span style="color: #800080;">6615</span><span style="color: #000000;">
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: </span><span style="color: #800080;">0</span><span style="color: #000000;">
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: </span><span style="color: #800080;">0</span><span style="color: #000000;">
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: </span><span style="color: #800080;">0</span><span style="color: #000000;">
                Last_IO_Error: 
               Last_SQL_Errno: </span><span style="color: #800080;">0</span><span style="color: #000000;">
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: </span><span style="color: #800080;">1</span><span style="color: #000000;">
                  Master_UUID: b888e1ea</span>-<span style="color: #800080;">9739</span>-11e4-a24e-<span style="color: #000000;">000c29b24887
             Master_Info_File: </span>/data/mydata/master.<span style="color: #0000ff;">info</span><span style="color: #000000;">
                    SQL_Delay: </span><span style="color: #800080;">0</span><span style="color: #000000;">
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting </span><span style="color: #0000ff;">for</span> the slave I/<span style="color: #000000;">O thread to update it
           Master_Retry_Count: </span><span style="color: #800080;">86400</span><span style="color: #000000;">
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: b888e1ea</span>-<span style="color: #800080;">9739</span>-11e4-a24e-000c29b24887:<span style="color: #800080;">1</span>-<span style="color: #800080;">52</span><span style="color: #000000;">
            Executed_Gtid_Set: a9926b45</span>-975d-11e4-a339-000c29b24888:<span style="color: #800080;">1</span>-<span style="color: #800080;">26</span><span style="color: #000000;">,
b888e1ea</span>-<span style="color: #800080;">9739</span>-11e4-a24e-000c29b24887:<span style="color: #800080;">1</span>-<span style="color: #800080;">52</span><span style="color: #000000;">
                Auto_Position: </span><span style="color: #800080;">1</span>
<span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
(dg7)root@localhost [mytest]</span>> <span style="color: #0000ff;">select</span> * from table_index where sid=<span style="color: #800080;">3</span><span style="color: #000000;">;
</span>+-----+-----+--------+-----+-----+
| <span style="color: #0000ff;">id</span>  | sid | name   | age | sex |
+-----+-----+--------+-----+-----+
| <span style="color: #800080;">333</span> |   <span style="color: #800080;">3</span> | wangwu |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
+-----+-----+--------+-----+-----+
<span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
(dg7)root@localhost [mytest]</span>><span style="color: #000000;"> show slave status\G
</span>*************************** <span style="color: #800080;">1</span>. row ***************************<span style="color: #000000;">
               Slave_IO_State: Waiting </span><span style="color: #0000ff;">for</span><span style="color: #000000;"> master to send event
                  Master_Host: </span><span style="color: #800080;">192.168</span>.<span style="color: #800080;">80.106</span><span style="color: #000000;">
                  Master_User: repl
                  Master_Port: </span><span style="color: #800080;">3306</span><span style="color: #000000;">
                Connect_Retry: </span><span style="color: #800080;">60</span><span style="color: #000000;">
              Master_Log_File: dg6</span>-logbin.<span style="color: #800080;">000001</span><span style="color: #000000;">
          Read_Master_Log_Pos: </span><span style="color: #800080;">13302</span><span style="color: #000000;">
               Relay_Log_File: dg7</span>-relay-bin.<span style="color: #800080;">000005</span><span style="color: #000000;">
                Relay_Log_Pos: </span><span style="color: #800080;">6105</span><span style="color: #000000;">
        Relay_Master_Log_File: dg6</span>-logbin.<span style="color: #800080;">000001</span><span style="color: #000000;">
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: </span><span style="color: #800080;">0</span><span style="color: #000000;">
                   Last_Error: 
                 Skip_Counter: </span><span style="color: #800080;">0</span><span style="color: #000000;">
          Exec_Master_Log_Pos: </span><span style="color: #800080;">13302</span><span style="color: #000000;">
              Relay_Log_Space: </span><span style="color: #800080;">6879</span><span style="color: #000000;">
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: </span><span style="color: #800080;">0</span><span style="color: #000000;">
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: </span><span style="color: #800080;">0</span><span style="color: #000000;">
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: </span><span style="color: #800080;">0</span><span style="color: #000000;">
                Last_IO_Error: 
               Last_SQL_Errno: </span><span style="color: #800080;">0</span><span style="color: #000000;">
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: </span><span style="color: #800080;">1</span><span style="color: #000000;">
                  Master_UUID: b888e1ea</span>-<span style="color: #800080;">9739</span>-11e4-a24e-<span style="color: #000000;">000c29b24887
             Master_Info_File: </span>/data/mydata/master.<span style="color: #0000ff;">info</span><span style="color: #000000;">
                    SQL_Delay: </span><span style="color: #800080;">0</span><span style="color: #000000;">
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting </span><span style="color: #0000ff;">for</span> the slave I/<span style="color: #000000;">O thread to update it
           Master_Retry_Count: </span><span style="color: #800080;">86400</span><span style="color: #000000;">
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: b888e1ea</span>-<span style="color: #800080;">9739</span>-11e4-a24e-000c29b24887:<span style="color: #800080;">1</span>-<span style="color: #800080;">53</span><span style="color: #000000;">
            Executed_Gtid_Set: a9926b45</span>-975d-11e4-a339-000c29b24888:<span style="color: #800080;">1</span>-<span style="color: #800080;">26</span><span style="color: #000000;">,
b888e1ea</span>-<span style="color: #800080;">9739</span>-11e4-a24e-000c29b24887:<span style="color: #800080;">1</span>-<span style="color: #800080;">53</span><span style="color: #000000;">
                Auto_Position: </span><span style="color: #800080;">1</span>
<span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
(dg7)root@localhost [mytest]</span>> 

5.验证有主键和有普通索引情况

(dg6)root@localhost [mytest]> <span style="color: #0000ff;">select</span> *<span style="color: #000000;"> from table_key;
</span>+-----+-----------+-----+-----+
| <span style="color: #0000ff;">id</span>  | name      | age | sex |
+-----+-----------+-----+-----+
| <span style="color: #800080;">111</span> | zhangsan  |  <span style="color: #800080;">20</span> |   <span style="color: #800080;">0</span> |
| <span style="color: #800080;">222</span> | lisi      |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">333</span> | wangwu    |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">444</span> | lilei     |  <span style="color: #800080;">32</span> |   <span style="color: #800080;">0</span> |
| <span style="color: #800080;">555</span> | hanmeimei |  <span style="color: #800080;">30</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">666</span> | lucy      |  <span style="color: #800080;">30</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">777</span> | lili      |  <span style="color: #800080;">30</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">888</span> | lintao    |  <span style="color: #800080;">32</span> |   <span style="color: #800080;">0</span> |
+-----+-----------+-----+-----+
<span style="color: #800080;">8</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
(dg6)root@localhost [mytest]</span>> update table_key set name=<span style="color: #800000;">‘</span><span style="color: #800000;">zhangsir</span><span style="color: #800000;">‘</span> where age=<span style="color: #800080;">20</span><span style="color: #000000;">;
Query OK, </span><span style="color: #800080;">1</span> row affected (<span style="color: #800080;">0.01</span><span style="color: #000000;"> sec)
Rows matched: </span><span style="color: #800080;">1</span>  Changed: <span style="color: #800080;">1</span>  Warnings: <span style="color: #800080;">0</span><span style="color: #000000;">
(dg6)root@localhost [mytest]</span>> <span style="color: #0000ff;">select</span> *<span style="color: #000000;"> from table_key;
</span>+-----+-----------+-----+-----+
| <span style="color: #0000ff;">id</span>  | name      | age | sex |
+-----+-----------+-----+-----+
| <span style="color: #800080;">111</span> | zhangsir  |  <span style="color: #800080;">20</span> |   <span style="color: #800080;">0</span> |
| <span style="color: #800080;">222</span> | lisi      |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">333</span> | wangwu    |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">444</span> | lilei     |  <span style="color: #800080;">32</span> |   <span style="color: #800080;">0</span> |
| <span style="color: #800080;">555</span> | hanmeimei |  <span style="color: #800080;">30</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">666</span> | lucy      |  <span style="color: #800080;">30</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">777</span> | lili      |  <span style="color: #800080;">30</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">888</span> | lintao    |  <span style="color: #800080;">32</span> |   <span style="color: #800080;">0</span> |
+-----+-----------+-----+-----+
<span style="color: #800080;">8</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
(dg6)root@localhost [mytest]</span>> 

从库看看

(dg7)root@localhost [mytest]> <span style="color: #0000ff;">select</span> *<span style="color: #000000;"> from table_key;
</span>+-----+-----------+-----+-----+
| <span style="color: #0000ff;">id</span>  | name      | age | sex |
+-----+-----------+-----+-----+
| <span style="color: #800080;">111</span> | zhangsan  |  <span style="color: #800080;">20</span> |   <span style="color: #800080;">0</span> |
| <span style="color: #800080;">222</span> | lisi      |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">333</span> | wangwu    |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">444</span> | lilei     |  <span style="color: #800080;">32</span> |   <span style="color: #800080;">0</span> |
| <span style="color: #800080;">555</span> | hanmeimei |  <span style="color: #800080;">30</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">666</span> | lucy      |  <span style="color: #800080;">30</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">777</span> | lili      |  <span style="color: #800080;">30</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">888</span> | lintao    |  <span style="color: #800080;">32</span> |   <span style="color: #800080;">0</span> |
+-----+-----------+-----+-----+
<span style="color: #800080;">8</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
(dg7)root@localhost [mytest]</span>> desc update table_key set name=<span style="color: #800000;">‘</span><span style="color: #800000;">xiaozhang</span><span style="color: #800000;">‘</span> where age=<span style="color: #800080;">20</span><span style="color: #000000;">;
</span>+----+-------------+-----------+-------+---------------+-----------+---------+-------+------+-------------+
| <span style="color: #0000ff;">id</span> | select_type | table     | type  | possible_keys | key       | key_len | ref   | rows | Extra       |
+----+-------------+-----------+-------+---------------+-----------+---------+-------+------+-------------+
|  <span style="color: #800080;">1</span> | SIMPLE      | table_key | range | age_index     | age_index | <span style="color: #800080;">1</span>       | const |    <span style="color: #800080;">1</span> | Using where |
+----+-------------+-----------+-------+---------------+-----------+---------+-------+------+-------------+
<span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
(dg7)root@localhost [mytest]</span>> update table_key set name=<span style="color: #800000;">‘</span><span style="color: #800000;">xiaozhang</span><span style="color: #800000;">‘</span> where age=<span style="color: #800080;">20</span><span style="color: #000000;">;
Query OK, </span><span style="color: #800080;">1</span> row affected (<span style="color: #800080;">0.03</span><span style="color: #000000;"> sec)
Rows matched: </span><span style="color: #800080;">1</span>  Changed: <span style="color: #800080;">1</span>  Warnings: <span style="color: #800080;">0</span><span style="color: #000000;">
(dg7)root@localhost [mytest]</span>> <span style="color: #0000ff;">select</span> *<span style="color: #000000;"> from table_key;
</span>+-----+-----------+-----+-----+
| <span style="color: #0000ff;">id</span>  | name      | age | sex |
+-----+-----------+-----+-----+
| <span style="color: #800080;">111</span> | xiaozhang |  <span style="color: #800080;">20</span> |   <span style="color: #800080;">0</span> |
| <span style="color: #800080;">222</span> | lisi      |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">333</span> | wangwu    |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">444</span> | lilei     |  <span style="color: #800080;">32</span> |   <span style="color: #800080;">0</span> |
| <span style="color: #800080;">555</span> | hanmeimei |  <span style="color: #800080;">30</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">666</span> | lucy      |  <span style="color: #800080;">30</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">777</span> | lili      |  <span style="color: #800080;">30</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">888</span> | lintao    |  <span style="color: #800080;">32</span> |   <span style="color: #800080;">0</span> |
+-----+-----------+-----+-----+
<span style="color: #800080;">8</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
(dg7)root@localhost [mytest]</span>> <span style="color: #0000ff;">select</span> *<span style="color: #000000;"> from table_key;
</span>+-----+-----------+-----+-----+
| <span style="color: #0000ff;">id</span>  | name      | age | sex |
+-----+-----------+-----+-----+
| <span style="color: #800080;">111</span> | zhangsir  |  <span style="color: #800080;">20</span> |   <span style="color: #800080;">0</span> |
| <span style="color: #800080;">222</span> | lisi      |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">333</span> | wangwu    |  <span style="color: #800080;">22</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">444</span> | lilei     |  <span style="color: #800080;">32</span> |   <span style="color: #800080;">0</span> |
| <span style="color: #800080;">555</span> | hanmeimei |  <span style="color: #800080;">30</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">666</span> | lucy      |  <span style="color: #800080;">30</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">777</span> | lili      |  <span style="color: #800080;">30</span> |   <span style="color: #800080;">1</span> |
| <span style="color: #800080;">888</span> | lintao    |  <span style="color: #800080;">32</span> |   <span style="color: #800080;">0</span> |
+-----+-----------+-----+-----+
<span style="color: #800080;">8</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
(dg7)root@localhost [mytest]</span>><span style="color: #000000;"> show slave status\G
</span>*************************** <span style="color: #800080;">1</span>. row ***************************<span style="color: #000000;">
               Slave_IO_State: Waiting </span><span style="color: #0000ff;">for</span><span style="color: #000000;"> master to send event
                  Master_Host: </span><span style="color: #800080;">192.168</span>.<span style="color: #800080;">80.106</span><span style="color: #000000;">
                  Master_User: repl
                  Master_Port: </span><span style="color: #800080;">3306</span><span style="color: #000000;">
                Connect_Retry: </span><span style="color: #800080;">60</span><span style="color: #000000;">
              Master_Log_File: dg6</span>-logbin.<span style="color: #800080;">000001</span><span style="color: #000000;">
          Read_Master_Log_Pos: </span><span style="color: #800080;">16026</span><span style="color: #000000;">
               Relay_Log_File: dg7</span>-relay-bin.<span style="color: #800080;">000005</span><span style="color: #000000;">
                Relay_Log_Pos: </span><span style="color: #800080;">8829</span><span style="color: #000000;">
        Relay_Master_Log_File: dg6</span>-logbin.<span style="color: #800080;">000001</span><span style="color: #000000;">
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: </span><span style="color: #800080;">0</span><span style="color: #000000;">
                   Last_Error: 
                 Skip_Counter: </span><span style="color: #800080;">0</span><span style="color: #000000;">
          Exec_Master_Log_Pos: </span><span style="color: #800080;">16026</span><span style="color: #000000;">
              Relay_Log_Space: </span><span style="color: #800080;">9603</span><span style="color: #000000;">
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: </span><span style="color: #800080;">0</span><span style="color: #000000;">
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: </span><span style="color: #800080;">0</span><span style="color: #000000;">
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: </span><span style="color: #800080;">0</span><span style="color: #000000;">
                Last_IO_Error: 
               Last_SQL_Errno: </span><span style="color: #800080;">0</span><span style="color: #000000;">
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: </span><span style="color: #800080;">1</span><span style="color: #000000;">
                  Master_UUID: b888e1ea</span>-<span style="color: #800080;">9739</span>-11e4-a24e-<span style="color: #000000;">000c29b24887
             Master_Info_File: </span>/data/mydata/master.<span style="color: #0000ff;">info</span><span style="color: #000000;">
                    SQL_Delay: </span><span style="color: #800080;">0</span><span style="color: #000000;">
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting </span><span style="color: #0000ff;">for</span> the slave I/<span style="color: #000000;">O thread to update it
           Master_Retry_Count: </span><span style="color: #800080;">86400</span><span style="color: #000000;">
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: b888e1ea</span>-<span style="color: #800080;">9739</span>-11e4-a24e-000c29b24887:<span sty="" <="" div="">
                  
 
                    <div class="">
                        <ul class="m-news-opt fix">
                            <li class="opt-item">
                                <a href="/sql_question-461275.html" target="_blank"><p>< 上一篇</p><p class="ellipsis">MySQL查看和修改最大连接数</p></a>
                            </li>
                            <li class="opt-item ta-r">
                                 <a href="/sql_question-461277.html" target="_blank"><p>下一篇 ></p><p class="ellipsis">要养成良好的书写SQL的习惯</p></a>
                            </li>
                        </ul>
                    </div>
                    
                    
                    
                    
                </span>