Mysql Replication 之 GTID 實戰

準備工作

Mysql 單機多執行個體詳解

What is the GTID of the replication

實驗環境

Os: CentOS 6.X

Mysql: 5.6 單機多執行個體 [3306,3307]

Hostname: lab.suzf.net

場景一：新機器無資料

對于GTID的配置，主要修改配置檔案中與GTID特性相關的幾個重要參數

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /usr/local/mysql/data/log/mysqld_multi.log
user = root ## Used for stopping the server via mysqladmin

# master
[mysqld3306]
socket = /usr/local/mysql/data/run/mysqld_3306.sock
port = 3306
pid-file = /usr/local/mysql/data/run/mysqld_3306.pid
datadir = /usr/local/mysql/data/mysql_3306

# set for GTID Replication
server_id = 3232237894
gtid_mode = on
enforce_gtid_consistency = on  #強制gtid一緻性，開啟後對于特定create table不被支援

# binlog
log-bin = /usr/local/mysql/data/binlogs/master-binlog
log-slave-updates = 1
binlog_format = row            #強烈建議，其他格式可能造成資料不一緻

# rely log
skip_slave_start=1


# slave
[mysqld3307]
socket = /usr/local/mysql/data/run/mysqld_3307.sock
port = 3307
pid-file = /usr/local/mysql/data/run/mysqld_3307.pid
datadir = /usr/local/mysql/data/mysql_3307

# set for GTID Replication
server_id = 3232237866
gtid_mode = on
enforce_gtid_consistency = on

# bin log
log-bin = /usr/local/mysql/data/binlogs/slave-binlog
log-slave-updates = 1
binlog_format = row

# rely log
skip_slave_start=1


[client]
default-character-set = utf8

重新開機mysql

/etc/init.d/mysqld_multi.server restart

Mysql 多執行個體啟動腳本

cat /etc/init.d/mysqld_multi.server
#!/bin/sh
#
# A simple startup script for mysqld_multi by Tim Smith and Jani Tolonen.
# This script assumes that my.cnf file exists either in /etc/my.cnf or
# /root/.my.cnf and has groups [mysqld_multi] and [mysqldN]. See the
# mysqld_multi documentation for detailed instructions.
#
# This script can be used as /etc/init.d/mysql.server
#
# Comments to support chkconfig on RedHat Linux
# chkconfig: 2345 64 36
# description: A very fast and reliable SQL database engine.
#
# Version 1.0
#

basedir=/usr/local/mysql
bindir=/usr/local/mysql/bin

conf=$basedir/data/etc/my.cnf
export PATH=$PATH:$bindir


if test -x $bindir/mysqld_multi
then
  mysqld_multi="$bindir/mysqld_multi";
else
  echo "Can't execute $bindir/mysqld_multi from dir $basedir";
  exit;
fi

case "$1" in
    'start' )
        "$mysqld_multi" --defaults-extra-file=$conf start $2
        ;;
    'stop' )
        "$mysqld_multi" --defaults-extra-file=$conf stop $2
        ;;
    'report' )
        "$mysqld_multi" --defaults-extra-file=$conf report $2
        ;;
    'restart' )
        "$mysqld_multi" --defaults-extra-file=$conf stop $2
        "$mysqld_multi" --defaults-extra-file=$conf start $2
        ;;
    *)
        echo "Usage: $0 {start|stop|report|restart}" >&2
        ;;
esac

Master OPS

mysql -uroot -S /usr/local/mysql/data/run/mysqld_3306.sock
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'localhost' IDENTIFIED BY 'suzf.net666';
Query OK, 0 rows affected (0.00 sec)

Slave OPS

mysql -uroot -S /usr/local/mysql/data/run/mysqld_3307.sock
mysql> CHANGE MASTER TO  
    -> MASTER_HOST='localhost',    
    -> MASTER_USER='repluser',    
    -> MASTER_PASSWORD='suzf.net666',    
    -> MASTER_PORT=3306,    
    -> MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> start slave;
Query OK, 0 rows affected (0.19 sec)

驗證

Mysql Replication 之 GTID 實戰

場景二：移除 Replication

首先關閉 SLAVE

mysql> STOP SLAVE;
Query OK, 0 rows affected (0.00 sec)

使用ALL 參數重置 SLAVE (MySQL >= 5.6.7):

mysql> RESET SLAVE ALL;
Query OK, 0 rows affected (0.02 sec)

此時 mysql replication 已經消失了

mysql> SHOW SLAVE STATUS\G
Empty set (0.00 sec)

如果你希望這個SLAVE節點繼續做一個新的MASTER的slave 節點，那麼請設定 gtid_purged,你應該發出一下指令（即使 gtid_puerged 看起來是空的，你也應該重置一下MASTER.）

mysql> reset master;
Query OK, 0 rows affected (0.11 sec)
 
mysql> show variables like '%gtid%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| enforce_gtid_consistency | ON        |
| gtid_executed            |           |
| gtid_mode                | ON        |
| gtid_next                | AUTOMATIC |
| gtid_owned               |           |
| gtid_purged              |           |
+--------------------------+-----------+
6 rows in set (0.00 sec)

備注

我很确定我已經删除了 rely-binary-logs. (relay_log_basename variable)

場景三：拷貝資料建立 GTID slave

Using mysqldump

在 slave 幾點初始化資料庫

$ ./scripts/mysql_install_db --datadir=/usr/local/mysql/data/mysql_3307

為了更簡單使用 mysqldump, 是将下列參數加入配置檔案

[mysqldump]
quick
max_allowed_packet = 16M
port = 3306
socket = /usr/local/mysql/data/run/mysqld_3306.sock

之後使用下面指令導出 Master 節點的 Cherry 資料庫

$ mysqldump --defaults-file=/usr/local/mysql/data/etc/my.cnf --user=root --password=`cat ~mysql/.root_password` --single-transaction --databases Cherry > backup.sql
Warning: Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

備注

–defaults-file 參數必須放在第一個位置, 否則你将會入到一個我認為已經修複的 bug.

GTID 的訣竅在這裡，當然它是通過 mysqldump 來實作的

$grep -i gtid 、/tmp/backup.sql
-- GTID state at the beginning of the backup
SET @@GLOBAL.GTID_PURGED='770d3753-c6e4-11e2-8e78-080027d93e15:1-8';

之後将dump 檔案傳送到 slave 節點，然後導入它；

mysql> source /tmp/backup.sql;

我最初得到了下面的錯誤：

ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

但是我的 gtid_executed 參數是空的:

mysql> show variables like '%gtid%';
+--------------------------+------------------------------------------+
| Variable_name            | Value                                    |
+--------------------------+------------------------------------------+
| enforce_gtid_consistency | ON                                       |
| gtid_executed            |                                          |
| gtid_mode                | ON                                       |
| gtid_next                | AUTOMATIC                                |
| gtid_owned               |                                          |
| gtid_purged              | 770d3753-c6e4-11e2-8e78-080027d93e15:1-6 |
+--------------------------+------------------------------------------+
6 rows in set (0.00 sec)

錯誤消息顯示沖突一個bug已經打開。實際上，gtid_purged也必須是空的，以便能夠設定它。官方文檔中引用的唯一解決方法是使用下面的指令：

mysql> DROP DATABASE replicationdb;
Query OK, 1 row affected (0.07 sec)
 
mysql> RESET MASTER;
Query OK, 0 rows affected (0.06 sec)
 
mysql> SHOW VARIABLES LIKE '%gtid%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| enforce_gtid_consistency | ON        |
| gtid_executed            |           |
| gtid_mode                | ON        |
| gtid_next                | AUTOMATIC |
| gtid_owned               |           |
| gtid_purged              |           |
+--------------------------+-----------+
6 rows in set (0.01 sec)

一旦完成你可以從新導入備份檔案，如果最後沒有錯誤出現，那麼現在可以設定 replication 了。

mysql> change master to master_host='lab.suzf.net', master_port=3306, master_user='repluser', master_password='suzf.net666', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.16 sec)

mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
 
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************

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

如果使用的是mysql 5.5 可能會用到

change master to master_host='lab.suzf.net', master_port=3306, master_user='repluser', master_password='suzf.net666',  master_log_file='mysql-bin.000006', master_log_pos=1026;

從哪裡可以獲得 master_log_file 和 log_position（在你導入資料時請不要将 master 設定為 read-only ）

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

Using XtraBackup

準備工作

$mkdir  /opt/data/backup/mysql -p
$ cat 3306.cnf
[mysqld]
socket = /usr/local/mysql/data/run/mysqld_3306.sock
port = 3306
pid-file = /usr/local/mysql/data/run/mysqld_3306.pid
datadir = /usr/local/mysql/data/mysql_3306

# set for GTID Replication
server_id = 3232237894
gtid_mode = on
enforce_gtid_consistency = on

# binlog
log-bin = /usr/local/mysql/data/binlogs/master-binlog
log-slave-updates = 1
binlog_format = row

# rely log
skip_slave_start=1

[client]
default-character-set = utf8

$ cat 3307.cnf
[mysqld]
socket = /usr/local/mysql/data/run/mysqld_3307.sock
port = 3307
pid-file = /usr/local/mysql/data/run/mysqld_3307.pid
datadir = /usr/local/mysql/data/mysql_3307

# set for GTID Replication
server_id = 3232237866
gtid_mode = on
enforce_gtid_consistency = on

# bin log
log-bin = /usr/local/mysql/data/binlogs/slave-binlog
log-slave-updates = 1
binlog_format = row

# rely log
skip_slave_start=1

[client]
default-character-set = utf8

我們将使用下面指令完整資料庫備份：

$innobackupex --defaults-file=/opt/data/backup/mysql/3306.cnf --user=root  --socket=/usr/local/mysql/data/run/mysqld_3306.sock  /tmp

Mysql Replication 之 GTID 實戰

這裡要注意的重要的檔案是在xtrabackup_binlog_info

$ cat /tmp/2016-04-05_15-26-40/xtrabackup_binlog_info
master-binlog.000006  191 81ee72fe-c957-11e5-ae70-0800272aa66e:1-12

在 Slave 節點恢複資料

$ innobackupex --defaults-file=/opt/data/backup/mysql/3307.cnf --user=root  --copy-back /tmp/2016-04-05_15-26-40

備注

Original data directory must be empty!

即 Slave 節點資料目錄必須為空

權限修複啟動執行個體

$ chown -R mysql:mysql  /usr/local/mysql/data/mysql_3307
$ /etc/init.d/mysqld_multi.server start 3307
$ /etc/init.d/mysqld_multi.server report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running

你需要先設定 gtid_purged 然後再建立 replication，在 Percona 文檔中有說明

mysql>  SET GLOBAL gtid_purged="81ee72fe-c957-11e5-ae70-0800272aa66e:1-12";
ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

# 出現上面個錯誤 執行下面操作
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)

mysql>  SET GLOBAL gtid_purged="81ee72fe-c957-11e5-ae70-0800272aa66e:1-12";
Query OK, 0 rows affected (0.05 sec)

mysql> CHANGE MASTER TO  
    -> MASTER_HOST='localhost',    
    -> MASTER_USER='repluser',    
    -> MASTER_PASSWORD='suzf.net666',    
    -> MASTER_PORT=3306,    
    -> MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> SHOW WARNINGS;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                              |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure.                                                                                                                                                                                                               |
| Note  | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

在經典的複制中（MySQL的5.5及以下）指令應該是這樣的：

change master to master_host='lab.suzf.net', master_port=3306, master_user='repluser', master_password='suzf.net666',  master_log_file='mysql-bin.000006', master_log_pos=1026;

啟動 slave 檢視狀态

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Retrieved_Gtid_Set: 81ee72fe-c957-11e5-ae70-0800272aa66e:13
            Executed_Gtid_Set: 81ee72fe-c957-11e5-ae70-0800272aa66e:1-13
                Auto_Position: 1

場景四：經典複制 –> GTID Replication

a. 按場景一中描述配置參數檔案

b. 所有伺服器設定global.read_only參數，等待主從伺服器同步完畢；

mysql> SET @@global.read_only = ON;

c. 依次重新開機主從伺服器；

d. 使用change master 更新主從配置；

mysql> CHANGE MASTER TO

> MASTER_HOST = host,

> MASTER_PORT = port,

> MASTER_USER = user,

> MASTER_PASSWORD = password,

> MASTER_AUTO_POSITION = 1;

e. 從庫開啟複制

mysql> START SLAVE;

f. 驗證主從複制

FAQ

Skip counter with GTID

mysql> show slave status\G
*************************** 1. row ***************************
           Retrieved_Gtid_Set: 770d3753-c6e4-11e2-8e78-080027d93e15:1-9
            Executed_Gtid_Set: 770d3753-c6e4-11e2-8e78-080027d93e15:1-7,
97e23f6a-cc52-11e2-b1e1-08002776e125:1-2
                Auto_Position: 1
1 row in set (0.00 sec)

從上面我們可以猜測到事務執行到事務ID 8 可能出現錯誤，接下來我們需要注入相同的空事務ID。

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%gtid%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| enforce_gtid_consistency | ON        |
| gtid_executed            |           |
| gtid_mode                | ON        |
| gtid_next                | AUTOMATIC |
| gtid_owned               |           |
| gtid_purged              |           |
+--------------------------+-----------+
6 rows in set (0.00 sec)

mysql> set gtid_next='770d3753-c6e4-11e2-8e78-080027d93e15:8';
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> set gtid_next='AUTOMATIC';
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

Reference : Mysql manual

至此完

Mysql Replication 之 GTID 實戰

License：Attribution-NonCommercial-NoDerivatives 4.0 International

本文出自 Suzf Blog。如未注明，均為 SUZF.NET 原創。

轉載請注明出處：http://suzf.net/thread-0401-696.html

轉載于:https://blog.51cto.com/oceanszf/1760409

Mysql Replication 之 GTID 實戰

準備工作

實驗環境

場景一：新機器無資料

場景二：移除 Replication

場景三：拷貝資料建立 GTID slave

Using mysqldump

Using XtraBackup

場景四：經典複制 –> GTID Replication

FAQ

Skip counter with GTID

繼續閱讀

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

艱難安裝LDAP,SSL認證

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

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

ACS基本配置-權限等級管理

XX系統實施過程問題總結

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

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

傳說FreeBSD等比Linux更穩定，更“健壯”

無人機--飛控科普

27 Best Free Eclipse Plug-ins for Java Developer to be ProductiveCode Quality PluginsText Editor PluginsDependency ManagementVersion Control Integration PluginsFramework Development Continuous Integration Related PluginsOther Utility Plugins

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

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

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

Mysql Replication 之 GTID 實戰

準備工作

實驗環境

場景一：新機器 無資料

場景二：移除 Replication

場景三：拷貝資料 建立 GTID slave

Using mysqldump

Using XtraBackup

場景四：經典複制 –> GTID Replication

FAQ

Skip counter with GTID

繼續閱讀

場景一：新機器無資料

場景三：拷貝資料建立 GTID slave