MySQL線上更改binlog格式

標籤：ast   flush   errno   SQL_error   span   一個   for   because   safe   

　　今天變更jboss報錯如下：

SQLWarning ignored: SQL state ‘HY000‘, error code ‘1592‘, message [Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.]

提示：警告，從另一個表中選擇一個具有自動增量列的表的語句是不安全的，因為檢索行的順序決定了將寫入哪些行（如果有的話）。這個命令是無法預測的，會使主從的資料不一致。

於是修改主庫和從庫的binglog格式由statement改為ROW格式。

方法：

1、先修改從庫

mysql> show global variables like "binlog%";+-----------------------------------------+--------------+| Variable_name                           | Value        |+-----------------------------------------+--------------+| binlog_cache_size                       | 32768        || binlog_checksum                         | CRC32        || binlog_direct_non_transactional_updates | OFF          || binlog_error_action                     | IGNORE_ERROR || binlog_format                           | MIXED        || binlog_gtid_simple_recovery             | OFF          || binlog_max_flush_queue_time             | 0            || binlog_order_commits                    | ON           || binlog_row_image                        | FULL         || binlog_rows_query_log_events            | OFF          || binlog_stmt_cache_size                  | 32768        || binlogging_impossible_mode              | IGNORE_ERROR |+-----------------------------------------+--------------+12 rows in set (0.00 sec)

mysql> set global binlog_format=ROW;Query OK, 0 rows affected (0.00 sec)mysql> show global variables like "binlog_format";+---------------+-------+| Variable_name | Value |+---------------+-------+| binlog_format | ROW   |+---------------+-------+1 row in set (0.00 sec)

2、在修改主庫的

mysql> set global binlog_format=ROW;Query OK, 0 rows affected (0.00 sec)mysql> show global variables like "binlog_format";+---------------+-------+| Variable_name | Value |+---------------+-------+| binlog_format | ROW   |+---------------+-------+1 row in set (0.00 sec)

3、修改設定檔my.cnf

binlog_format=ROW

 

如果先修改主庫，可能會有以下報錯。

slave報錯資訊如下：Last_SQL_Errno: 1666               Last_SQL_Error: Error executing row event: ‘Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.‘

 

歡迎轉載，請註明出處！

 

MySQL線上更改binlog格式