MySQL5.6和5.5的一些差別地方

5.6的改進地方：

1、在5.5和之前的版本mysql中，主從配置的話，要在從節點配置change master to 指明binlog和POS。而在5.6及以後

，這2個參數就可以省略掉。MySQL可以通過内部的GTID機制自動找點同步。我們隻要指明master的IP、使用者名和密碼、端口即可。

2、5.6支援多線程複制

在5.5裡，同步複制是單線程、隊列的，隻能一個的執行。而在5.6裡，多個庫可以同時進行複制(注意：同一個庫内仍是不能多線程了)。

5.6裡會涉及到UUID這個參數

MySQL [(none)]>show variables like '%uuid%';

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

| Variable_name | Value                                |

| server_uuid   | ca910cf0-3aec-11e6-9319-b888e3dcfeb8 |

1 row in set (0.00 sec)

注意：在mysql初次啟動時候會自動生成這個UIID，寫入到auto.cnf中，官方不建議修改這個值。并且server_uuid和GTID有密切關系。

GTID：全局事務辨別符

     使用這個功能時，每次事務送出都會在binlog裡生成唯一的辨別符，它由UUID和事務ID組成。首次送出的事務ID為1，以後依次遞增。

     開啟GTID時，slave做同步複制時，無需找到binlog日志和POS點。直接

GTID寫法：

change master to 

     master_HOST=192.168.2.100,

     master_PORT=2206,

     master_USER=repluser,

     master_PASSWORD='123456',

     master_AUTO_POSITION=1;

另外傳統的寫法：

CHANGE MASTER TO

  MASTER_HOST='master2.mycompany.com',

  MASTER_USER='replication',

  MASTER_PASSWORD='bigs3cret',

  MASTER_PORT=3306,

  MASTER_LOG_FILE='master2-bin.001',

  MASTER_LOG_POS=4,

  MASTER_CONNECT_RETRY=10;

如果之前啟用過了GTID，那麼就不能不能再使用傳統的change master to的方式了，會報錯，如下：

 ERROR 1776 (HY000): Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active.

<a href="http://s1.51cto.com/wyfs02/M02/83/85/wKiom1d03SWgvd1KAAA7n1_WA44465.png" target="_blank"></a>

GTID的工作流程：

1、在master上送出一個事務，并寫入到binlog

2、binlog發送到slave上，slave接收并寫入relay log，slave讀取到這個GTID，并設定gtid_next的值。例如：

     set @@SESSION.GTID_NEXT='B0869D03-D332223-35454:3';

     然後告訴slave接下來的事務必須使用GTID，并寫入到自己的binlog中。

3、slave檢查并确認這個gtid沒有被使用，如果沒有被使用，那麼開始執行這個事務并寫入到它自己的binlog裡。

4、由于gtid_next的值不是空的，slave不會嘗試去生成一個新的gtid，而是通過主從同步來獲的GTID。

     另外，要使用GTID方式進行主從同步的話，還要在my.cnf裡面加上如下的配置：

     [mysqld]

     log-bin=mysql-bin

     binlog_format = mixed

     log_slave_updates = ON

     gtid-mode = ON

     enforce_gtid_consistency = ON

     然後在master上導出mysqldump -uroot -proot -q --single-transaction -R -E --triggers -B hellodb &gt; /root/hello.sql

     在slave上導入mysql -uroot -proot &lt; /root/hello.sql

     在slave上配置change master to指向（如下6行代碼）：

     master_PORT=3306,

GTID的局限性：

1、GTID的複制是基于事務的，不支援MyISAM，這可能導緻多個GTID配置設定各同一個事務。

2、對create table ... select語句不支援。因為該語句會被拆分成create table和insert 兩個事務，并且如果這兩個事務被配置設定了同一個GTID，将會導緻insert被備庫忽略掉。

3、不支援建立、删除臨時表

多線程複制示範：

在slave上執行下面幾條指令：

&gt; stop slave;

&gt; set global  slave_parallel_workers = 4;

&gt; start slave;

&gt; show full processlist;可以看到有4個線程 Waiting for an event from Coordinator

如果此時在主上有大量的insert操作，可以在slave上執行&gt; select * from mysql.slave_worker_info\G 應該可以檢視到worker_id在不斷變化，說明是多線程複制在起作用了。

說明：slave_parallel_workers 即可實作在slave上多線程并發複制。不過，它隻能支援一個執行個體下多個 database 間的并發複制，并不能真正做到多表并發複制。是以在較大并發負載時，slave還是沒有辦法及時追上master，需要想辦法進行優化（比如：盡量将一個庫中的表按照業務邏輯拆分成多個庫來儲存，這樣在寫操作時候，slave就能開啟多線程複制，減少了同步的時延。）

此外，建議修改my.cnf，增加2行(預設這個info_file是檔案的，不寫入資料庫的)

relay_log_info_repository = table

master_info_repository = table

單單這樣還不夠，預設這2張表是MyISAM的，不安全還要轉換下

&gt; alter table slave_master_info engine innodb;

&gt; alter table slave_relay_log_info engine innodb;

&gt; alter table slave_worker_info engine innodb;

這樣的話，可防止表損壞，在損壞後可以自行修複。

GTID模式下的主從複制，同步時候報錯不能跳過的解決方法：

假如在slave上看到同步報錯“從節點的XXX鍵不存在”

我們可以嘗試使用5.5上的老方法

&gt; set global sql_slave_skip_counter=1

執行的時候會發現報錯了，提示如下：

<a href="http://s5.51cto.com/wyfs02/M00/83/83/wKioL1d03TiDgnIaAAAhwTF6_W4390.png" target="_blank"></a>

可以看出運作在GTID模式下，不支援sql_slave_skip_counter這種方式跳過的。

那麼可以如下方法來跳過：

&gt; show  slave status\G檢視如下2行的資訊：

  Retrieved_Gtid_Set: ca910cf0-3aec-11e6-9319-b888e3dcfeb8:1-2

  Executed_Gtid_Set: ca910cf0-3aec-11e6-9319-b888e3dcfeb8:1

第一行表示收到的事務，第二行表示已經執行完的事務。也就是說執行到Retrieved_Gtid_Set時候發生錯誤了。

是以，我們直接單單跳過這個事務即可。

&gt; set GTID_NEXT='ca910cf0-3aec-11e6-9319-b888e3dcfeb8:2';     就是這種寫法,不要加什麼1-2這些玩意

&gt; begin;

&gt; commit;

&gt; set GTID_NEXT="AUTOMATIC";      #把gtid_next設定回來

&gt; show slave status\G   驗證下是否IO/SQL都是YES狀态。

GTID模式轉換為傳統模式的方法及注意點：

要轉換成傳統模式，需要在my.cnf裡面注釋掉下面2行：

# gtid-mode=ON

# enforce_gtid_consistency = ON

然後重新開機MySQL。

登進mysql，執行類似如下指令：

&gt; CHANGE MASTER TO

     MASTER_HOST='master2.mycompany.com',

      MASTER_USER='replication',

     MASTER_PASSWORD='bigs3cret',

     MASTER_PORT=3306,

     MASTER_LOG_FILE='master2-bin.001',

     MASTER_LOG_POS=4,

     MASTER_CONNECT_RETRY=10;

     結果報錯了，如下圖：

<a href="http://s1.51cto.com/wyfs02/M02/83/83/wKioL1d03UvA1q_VAAAwoKwkQzY134.png" target="_blank"></a>

解決方法：

&gt; change master to MASTER_AUTO_POSITION=0;       # 關閉這個參數，這個參數是GTID複制才用到的。

&gt; CHANGE MASTER TO

        MASTER_HOST = '192.168.2.11',

        MASTER_USER='repluser',

        MASTER_PASSWORD='123456',

        MASTER_PORT=3306,

        MASTER_LOG_FILE='mysql-bin.000012',

        MASTER_LOG_POS=500,

        MASTER_CONNECT_RETRY=10;

&gt; show slave status\G 驗證下是否IO/SQL都是YES狀态。

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