mysql隻讀模式下資料遷移，保證資料一緻性

經過實際測試，對于MySQL單執行個體資料庫和master庫，如果需要設定為隻讀狀态，需要進行如下操作和設定：

一般單執行個體情況下将MySQL設定為隻讀狀态的指令：

<code># mysql -uroot -p</code>

<code>mysql> show </code><code>global</code> <code>variables </code><code>like</code> <code>"%read_only%"</code><code>;</code>

<code>mysql>flush tables </code><code>with</code> <code>read</code> <code>lock;</code>

<code>mysql></code><code>set</code> <code>global</code> <code>read_only=1;</code>

将MySQL從隻讀設定為讀寫狀态的指令：

<code>mysql> unlock tables;</code>

<code>mysql> </code><code>set</code> <code>global</code> <code>read_only=0;</code>

對于需要保證master-slave主從同步的salve庫，如果要設定為隻讀狀态，需要執行的指令為：

mysql> set global read_only=1; 打開隻讀開關

将salve庫從隻讀狀态變為讀寫狀态，需要執行的指令是：

mysql> set global read_only=0; 打開讀寫開關

對于資料庫讀寫狀态，主要靠 “read_only”全局參數來設定；

預設情況下，資料庫是用于讀寫操作的，是以read_only參數也是0或faluse狀态，這時候不論是本地使用者還是遠端通路資料庫的使用者，都可以進行讀寫操作；

如需設定為隻讀狀态，将該read_only參數設定為1或TRUE狀态，但設定 read_only=1 狀态有兩個需要注意的地方：

1.read_only=1隻讀模式，不會影響slave同步複制的功能，是以在MySQL slave庫中設定了read_only=1後，通過 show slave status\G 指令檢視salve狀态，可以看到salve仍然會讀取master上的日志，并且在slave庫中應用日志，保證主從資料庫同步一緻；

2.read_only=1隻讀模式，可以限定普通使用者進行資料修改的操作，但不會限定具有super權限的使用者的資料修改操作；在MySQL中設定read_only=1後，普通的應用使用者進行insert、update、delete等會産生資料變化的DML操作時，都會報出資料庫處于隻讀模式不能發生資料變化的錯誤，但具有super權限的使用者，例如在本地或遠端通過root使用者登入到資料庫，還是可以進行資料變化的DML操作；

為了確定所有使用者，包括具有super權限的使用者也不能進行讀寫操作，就需要執行給所有的表加讀鎖的指令 “flush tables with read lock;”，這樣使用具有super權限的使用者登入資料庫，想要發生資料變化的操作時，也會提示表被鎖定不能修改的報錯。

這樣通過設定“read_only=1”和“flush tables with read lock;”兩條指令，就可以確定資料庫處于隻讀模式，不會發生任何資料改變，在MySQL進行資料庫遷移時，限定master主庫不能有任何資料變化，就可以通過這種方式來設定。

但同時由于加表鎖的指令對資料庫表限定非常嚴格，如果再slave從庫上執行這個指令後，slave庫可以從master讀取binlog日志，但不能夠應用日志，slave庫不能發生資料改變，當然也不能夠實作主從同步了，這時如果使用 “unlock tables;”解除全局的表讀鎖，slave就會應用從master讀取到的binlog日志，繼續保證主從庫資料庫一緻同步。

為了保證主從同步可以一直進行，在slave庫上要保證具有super權限的root等使用者隻能在本地登入，不會發生資料變化，其他遠端連接配接的應用使用者隻按需配置設定為select,insert,update,delete等權限，保證沒有super權限，則隻需要将salve設定“read_only=1”模式，即可保證主從同步，又可以實作從庫隻讀。

相對的，設定“read_only=1”隻讀模式開啟的解鎖指令為設定“read_only=0”;設定全局鎖“flush tables with read lock;”，對應的解鎖模式指令為：“unlock tables;”.

當然設定了read_only=1後，所有的select查詢操作都是可以正常進行的。

實驗一：設定了read_only=1後，遠端業務使用者進行資料庫修改會提示ERROR 1290錯誤：

<code>([email protected]) [data03]> show tables; </code>

<code>| Tables_in_data03 | </code>

<code>([email protected]) [data03]> </code>

<code>([email protected]) [data03]> show </code><code>global</code> <code>variables </code><code>like</code> <code>"%read_only%"</code><code>; </code>

<code>| Variable_name | Value | </code>

<code>| innodb_read_only | </code><code>OFF</code> <code>| </code>

<code>([email protected]) [data03]> </code><code>delete</code> <code>from</code> <code>t01 </code><code>where</code> <code>id1=3; </code>

<code><span style=</code><code>"color:#ff0000;"</code><code>>ERROR 1290 (HY000): The MySQL server </code><code>is</code> <code>running </code><code>with</code> <code>the </code><code>--read-only option so it cannot execute this statement</span> </code>

<code>([email protected]) [data03]> </code><code>update</code> <code>t01 </code><code>set</code> <code>id1=id1+30 </code><code>where</code> <code>id1=3; </code>

<code>ERROR 1290 (HY000): The MySQL server </code><code>is</code> <code>running </code><code>with</code> <code>the </code><code>--read-only option so it cannot execute this statement </code>

<code>([email protected]) [data03]> </code><code>insert</code> <code>into</code> <code>t01(id1,a1,b1) </code><code>values</code><code>(9,9,9); </code>

<code>([email protected]) [data03]> </code><code>select</code> <code>* </code><code>from</code> <code>t01; </code>

<code>([email protected]) [data03]></code>

實驗二：設定了全局讀寫後，具有super權限的使用者進行資料修改後，也會提示錯誤ERROR 1223：

<code>view</code> <code>plain copy</code>

<code>mysql> use data03; </code>

<code>Reading </code><code>table</code> <code>information </code><code>for</code> <code>completion </code><code>of</code> <code>table</code> <code>and</code> <code>column</code> <code>names </code>

<code>You can turn </code><code>off</code> <code>this feature </code><code>to</code> <code>get a quicker startup </code><code>with</code> <code>-A </code>

<code>Database</code> <code>changed </code>

<code>mysql> show tables; </code>

<code>mysql> </code><code>select</code> <code>* </code><code>from</code> <code>t01; </code>

<code>mysql> </code>

<code>mysql> show </code><code>global</code> <code>variables </code><code>like</code> <code>"%read_only%"</code><code>; </code>

<code>mysql> </code><code>insert</code> <code>into</code> <code>t01(id1,a1,b1) </code><code>values</code><code>(8,8,8); </code>

<code>Query OK, 1 row affected (0.00 sec) </code>

<code>mysql> </code><code>update</code> <code>t01 </code><code>set</code> <code>a1=a1+10 </code><code>where</code> <code>id1=2; </code>

<code>Rows</code> <code>matched: 1 Changed: 1 Warnings: 0 </code>

<code>mysql> </code><code>delete</code> <code>from</code> <code>t01 </code><code>where</code> <code>id1=4; </code>

<code>mysql> flush tables </code><code>with</code> <code>read</code> <code>lock; </code>

<code>Query OK, 0 </code><code>rows</code> <code>affected (0.00 sec) </code>

<code>mysql> </code><code>insert</code> <code>into</code> <code>t01(id1,a1,b1) </code><code>values</code><code>(9,9,9); </code>

<code><span style=</code><code>"color:#ff0000;"</code><code>>ERROR 1223 (HY000): Can</code><code>'t execute the query because you have a conflicting read lock</span> </code>

<code>mysql> update t01 set a1=a1+10 where id1=5; </code>

<code>ERROR 1223 (HY000): Can'</code><code>t </code><code>execute</code> <code>the query because you have a conflicting </code><code>read</code> <code>lock </code>

<code>mysql> </code><code>delete</code> <code>from</code> <code>t01 </code><code>where</code> <code>id1=5; </code>

<code>ERROR 1223 (HY000): Can't </code><code>execute</code> <code>the query because you have a conflicting </code><code>read</code> <code>lock </code>

<code>mysql></code>

實驗三：MySQL從庫設定read_only=1後主從同步正常，設定表讀鎖後，不能同步，解除讀鎖後，主從同步恢複。

mysql>

mysql> show databases;

+--------------------+

| Database |

| information_schema |

| bitvc |

| data03 |

| ga |

| jiradb |

| meibi |

| meibi02 |

| mysql |

| performance_schema |

| sbtest |

10 rows in set (0.00 sec)

mysql> show global variables like "%read_only%";

+------------------+-------+

| Variable_name | Value |

| innodb_read_only | OFF |

| read_only | ON |

| tx_read_only | OFF |

3 rows in set (0.00 sec)

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 172.32.1.200

Master_User: repl

Master_Port: 3307

Connect_Retry: 60

Master_Log_File: mysql-bin.000009

Read_Master_Log_Pos: 5853

Relay_Log_File: huobiDBtest-relay-bin.000002

Relay_Log_Pos: 6016

Relay_Master_Log_File: mysql-bin.000009

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 5853

Relay_Log_Space: 6195

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 2003307

Master_UUID: 6f68eea7-76e9-11e4-8f99-00221904cd5d

Master_Info_File: /data/mysqldata/3308/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

Master_Log_File: mysql-bin.000009

Read_Master_Log_Pos: 6531

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 5853

Relay_Log_Space: 6873

Seconds_Behind_Master: 120

Replicate_Ignore_Server_Ids:

Master_Server_Id: 2003307

Master_UUID: 6f68eea7-76e9-11e4-8f99-00221904cd5d

Master_Info_File: /data/mysqldata/3308/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Waiting for global read lock

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Auto_Position: 0

mysql> select * from data03.t01;

+-----+------+------+

| id1 | a1 | b1 |

| 1 | 1 | 1 |

| 2 | 2 | 2 |

| 4 | 4 | 4 |

| 5 | 5 | 5 |

| 6 | 6 | 6 |

5 rows in set (0.00 sec)

mysql> unlock tables;

Master_Log_File: mysql-bin.000009

Read_Master_Log_Pos: 6531

Relay_Log_Pos: 6694

Last_Errno: 0

<code> </code><code>Last_Error: </code>

<code> </code><code>Skip_Counter: 0 </code>

<code> </code><code>Exec_Master_Log_Pos: 6531 </code>

<code> </code><code>Relay_Log_Space: 6873 </code>

<code> </code><code>Until_Condition: None </code>

<code> </code><code>Until_Log_File: </code>

<code> </code><code>Until_Log_Pos: 0 </code>

<code> </code><code>Master_SSL_Allowed: </code><code>No</code>

<code> </code><code>Master_SSL_CA_File: </code>

<code> </code><code>Master_SSL_CA_Path: </code>

<code> </code><code>Master_SSL_Cert: </code>

<code> </code><code>Master_SSL_Cipher: </code>

<code> </code><code>Master_SSL_Key: </code>

<code> </code><code>Seconds_Behind_Master: 0 </code>

<code>Master_SSL_Verify_Server_Cert: </code><code>No</code>

<code> </code><code>Last_IO_Errno: 0 </code>

<code> </code><code>Last_IO_Error: </code>

<code> </code><code>Last_SQL_Errno: 0 </code>

<code> </code><code>Last_SQL_Error: </code>

<code> </code><code>Replicate_Ignore_Server_Ids: </code>

<code> </code><code>Master_Server_Id: 2003307 </code>

<code> </code><code>Master_UUID: 6f68eea7-76e9-11e4-8f99-00221904cd5d </code>

<code> </code><code>Master_Info_File: /data/mysqldata/3308/data/master.info </code>

<code> </code><code>SQL_Delay: 0 </code>

<code> </code><code>SQL_Remaining_Delay: </code><code>NULL</code>

<code> </code><code>Slave_SQL_Running_State: Slave has </code><code>read</code> <code>all</code> <code>relay log; waiting </code><code>for</code> <code>the slave I/O thread </code><code>to</code> <code>update</code> <code>it </code>

<code> </code><code>Master_Retry_Count: 86400 </code>

<code> </code><code>Master_Bind: </code>

<code> </code><code>Last_IO_Error_Timestamp: </code>

<code> </code><code>Last_SQL_Error_Timestamp: </code>

<code> </code><code>Master_SSL_Crl: </code>

<code> </code><code>Master_SSL_Crlpath: </code>

<code> </code><code>Retrieved_Gtid_Set: </code>

<code> </code><code>Executed_Gtid_Set: </code>

<code> </code><code>Auto_Position: 0 </code>

<code>mysql> </code><code>select</code> <code>* </code><code>from</code> <code>data03.t01; </code>

##########################################

.FLUSH TABLES WITH READ LOCK

這個指令是全局讀鎖定，執行了指令之後所有庫所有表都被鎖定隻讀。一般都是用在資料庫聯機備份，這個時候資料庫的寫操作将被阻塞，讀操作順利進行。

解鎖的語句也是unlock tables。

2.LOCK TABLES tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}

這個指令是表級别的鎖定，可以定制鎖定某一個表。例如： lock tables test read; 不影響其他表的寫操作。

解鎖語句也是unlock tables。

重點：

P.S. MYSQL的read lock和wirte lock

read-lock: 允許其他并發的讀請求，但阻塞寫請求，即可以同時讀，但不允許任何寫。也叫共享鎖

write-lock: 不允許其他并發的讀和寫請求，是排他的(exclusive)。也叫獨占鎖

本文轉自crazy_charles 51CTO部落格，原文連結：http://blog.51cto.com/douya/1868243，如需轉載請自行聯系原作者

mysql隻讀模式下資料遷移，保證資料一緻性

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述