聚會時間公告: 因應COSCUP 2011, Kalug 8月份休會一次

六月 20, 2015

小惡魔AppleBOY
AppleBOY
小惡魔 – 電腦技術 – 工作筆記 – AppleBOY is about »

tag cloud

» 優化 Percona XtraDB Cluster for write hotspots

percona

Percona Blog 上看到這篇 Optimizing Percona XtraDB Cluster for write hotspots 優化多重寫入 MySQL 的狀況,舉例來說,要計算 global counter 的時候,就會遇到很頻繁的寫入 (write hotspot),目前是不能同時寫入資料到同一個 record,會造成 performance 降低,所以大家開始導入 Percona XtraDB Cluster 來解決同時間寫入到同一個 record,大家都認為,搞了三台 Percona Server,可以將寫入的動作分散到其他兩台,就不會遇到 Lock 問題,但是實際上根本就不是這樣。

在獨立 Server 同時執行 SQL 語法

簡單舉例,底下有三個 SQL 語法

# T1
UPDATE t SET ... WHERE id = 100
 
# T2
UPDATE t SET ... WHERE id = 100
 
# T3
UPDATE t SET ... WHERE id = 101

我們執行上述語法,會發現產生 row lock 在 T1 或 T2,而 T3 則會同時間執行完畢,因為 T3 不是存取同一筆資料,讓我們假設 InnoDB 先執行 T1,這時候 T2 就要等到 T1 執行完畢,才可以接著執行 T2。

同時執行寫入到 multiple nodes (PXC)

Screen Shot 2015-06-20 at 2.48.56 PM

看到上述圖,我們先假設 T1 先在 Node 1 執行,T1 會同步執行到 Node 2 和 Node 3,在這同時,T2 在 Node 2 執行就會碰到 certification test fail,這時後,T2 執行失敗,就會執行 roll back,這時候解法就是變成 T2 要 retry。結論就是 PXC 架構多台寫入解決方案不適合的。

結論

如果需要大量寫入,正確的解決方式就是將寫入寫到同一台 server,這樣全部的 Lock 都會發生在同一台,但是 PXC 架構跟獨立一台 server 效能上來比,後者還是會比較好。另外的解法就是減少寫入,將計算 counter 這狀況先寫到 redis server,等到一段時間再同步到 MySQL Server。

四月 12, 2014

小惡魔AppleBOY
AppleBOY
小惡魔 – 電腦技術 – 工作筆記 – AppleBOY is about »

tag cloud

» OSDC 2014 Talk: Introduction to Percona XtraDB Cluster and HAProxy

2014.05.07 OSDC 影片檔出爐

percona

很高興可以參加今年 2014 OSDC,投了一篇 Introduction to Percona XtraDB Cluster and HAProxy,主要介紹 Percona XtraDB Cluster 這套 open source 軟體搭配前端 HAProxy,底下是 Slides

OSDC 影片出爐,底下是錄影檔,大家可以參考

一月 19, 2014

小惡魔AppleBOY
AppleBOY
小惡魔 – 電腦技術 – 工作筆記 – AppleBOY is about »

tag cloud

» Percona XtraDB Cluster 搭配 HAProxy

percona

本篇文章紀錄安裝 Percona XtraDB Cluster (簡稱 PXC) 及搭配 HAProxy 做分散流量系統,其實在業界已經很常看到 HAProxy + MySQL Cluster Database 解決方案,HAProxy 幫您解決負載平衡,並且偵測系統是否存活,管理者也就不用擔心 MySQL 服務是否會掛掉。本篇會著重於 HAProxy 設定部份,並且紀錄每一步安裝步驟。之前本作者寫過一篇 Galera Cluster for MySQL Multi-master Replication,也可以參考。今天測試系統都會以 CentOS 為主,各位讀者可以直接開 Amazone EC2 來測試,測試完成再關閉即可。

安裝 Percona XtraDB Cluster

我們會使用官方 PerconaEPEL repositories 進行軟體安裝,底下是 Yum 安裝步驟

$ yum -y install Percona-XtraDB-Cluster-server Percona-XtraDB-Cluster-client Percona-Server-shared-compat percona-xtrabackup

如果系統已經有安裝過 MariaDB + Galera,請務必先移除套件

完成安裝 PXC 套件後,接著設定 my.cnf 設定檔

[mysqld]
server_id=1
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address="gcomm://"
wsrep_sst_auth=username:password
wsrep_provider_options="gcache.size=4G"
wsrep_cluster_name=Percona
wsrep_sst_method=xtrabackup
wsrep_node_name=db_01
wsrep_slave_threads=4
log_slave_updates
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2

再開啟第 2 台或第 3 台 PXC 服務的時候,務必確認第 1 台已經正確開啟成功。上面設定檔是針對第 1 台做設定,之後新增 Node,請務必修改 wsrep_cluster_address 填上你想要 Join 的 Cluster Server IP Address,另外每台的 server_idwsrep_node_name 也會不同,請務必注意

第 2 台設定值

server_id=2
wsrep_cluster_address=gcomm://192.168.1.100 # replace this with the IP of your first node
wsrep_node_name=db_02

第 3 台設定值

server_id=3
wsrep_cluster_address=gcomm://192.168.1.100 # replace this with the IP of your first node
wsrep_node_name=db_03

根據 State Snapshot Transfer (簡稱 SST),我們使用 Xtrabackup,當新的 Node 連上時,就會開始複製資料到新的 Node 上,成功複製完成,可以看到底下 Log 訊息

140117 11:56:05 [Note] WSREP: Flow-control interval: [28, 28]
140117 11:56:05 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 678691)
140117 11:56:05 [Note] WSREP: State transfer required:
        Group state: 28e87291-da41-11e2-0800-34a03cad54a7:678691
        Local state: 28e87291-da41-11e2-0800-34a03cad54a7:678684
140117 11:56:05 [Note] WSREP: New cluster view: global state: 28e87291-da41-11e2-0800-34a03cad54a7:678691, view# 33: Primary, number of nodes: 3, my index: 1, protocol version 2
140117 11:56:05 [Warning] WSREP: Gap in state sequence. Need state transfer.
140117 11:56:07 [Note] WSREP: Running: 'wsrep_sst_xtrabackup --role 'joiner' --address '122.146.119.102' --auth 'username:password' --datadir '/var/lib/mysql/' --defaults-file '/etc/my.cnf' --parent '965''
WSREP_SST: [INFO] Streaming with tar (20140117 11:56:07.517)
WSREP_SST: [INFO] Using socat as streamer (20140117 11:56:07.519)
WSREP_SST: [INFO] Evaluating socat -u TCP-LISTEN:4444,reuseaddr stdio | tar xfi - --recursive-unlink -h; RC=( ${PIPESTATUS[@]} ) (20140117 11:56:07.531)
140117 11:56:07 [Note] WSREP: Prepared SST request: xtrabackup|122.146.119.102:4444/xtrabackup_sst
140117 11:56:07 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
140117 11:56:07 [Note] WSREP: Assign initial position for certification: 678691, protocol version: 2
140117 11:56:07 [Note] WSREP: Prepared IST receiver, listening at: tcp://122.146.119.102:4568
140117 11:56:07 [Note] WSREP: Node 1 (db_01) requested state transfer from '*any*'. Selected 0 (db_02)(SYNCED) as donor.
140117 11:56:07 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 678692)
140117 11:57:36 [Note] WSREP: Synchronized with group, ready for connections
140117 11:57:36 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
140117 11:57:36 [Note] WSREP: 1 (db_02): State transfer from 0 (db_01) complete.
140117 11:57:36 [Note] WSREP: Member 1 (db_02) synced with group.

最後我們可以透過 MySQL Status 來看看是否有建立成功

mysql> show global status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.00 sec)

看到 wsrep_cluster_size 出現正確的 Server 數量,就代表設定成功。

設定 HAProxy 負載平衡

上述完成了 3 台 Cluster 設定,接著所有的 Application 服務都需要直接跟此 Cluster 溝通,為了完成此需求,我們必須將 HAProxy 安裝在其中一台伺服器來做負載平衡,今天會介紹兩種設定方式,第一種是採用 round robin 方式,意思就是說所有的 Application 都可以連上並且寫入資料到三台機器,這狀況其實沒有錯誤,但是如果同時寫入三台機器,難免會出現 optimistic locking 而產生 rollback,如果可以確定不會產生 conflict,其實這方案是不錯的。第2種設定方式就是只寫入單一 Node,但是可以讀取三台機器,也就是 insert, update 都是在同一台 Node 完成,所以 Application 不用擔心會產生 rollback 情形。第1種設定在大部份的狀況底下都是可以運作很好的,所以其實也不用擔心。

底下是 /etc/haproxy/haproxy.cfg 設定

frontend stats-front
bind *:8080
mode http
default_backend stats-back

frontend pxc-front
bind *:3307
mode tcp
default_backend pxc-back

frontend pxc-onenode-front
bind *:3308
mode tcp
default_backend pxc-onenode-back

backend stats-back
mode http
balance roundrobin
stats uri /haproxy/stats
stats auth username:password

backend pxc-back
mode tcp
balance leastconn
option httpchk
server c1 192.168.1.100:3306 check port 9200 inter 12000 rise 3 fall 3
server c2 192.168.1.101:3306 check port 9200 inter 12000 rise 3 fall 3
server c3 192.168.1.102:3306 check port 9200 inter 12000 rise 3 fall 3

backend pxc-onenode-back
mode tcp
balance leastconn
option httpchk
server c1 192.168.1.100:3306 check port 9200 inter 12000 rise 3 fall 3
server c2 192.168.1.101:3306 check port 9200 inter 12000 rise 3 fall 3 backup
server c3 192.168.1.102:3306 check port 9200 inter 12000 rise 3 fall 3 backup

從上述設定,可以看到我們定義了 3 個 frontend-backend,其中 stats-front 是 HAProxy Status Page,另外兩組則是針對 PXC 設定。看到此設定,可以知道系統會 Listen 3307 及 3308 兩個 port,其中 3308 會讓 App 使用一台 PXC Node 而已,此設定可以避免因為 optimistic locking 而產生 rollbacks,如果 Node 掛點,則會啟動其他 Node。然而如果是連接 3307 port,系統會直接對3台 node 寫入或讀取,我們使用 leastconn 取代 round robin,這代表著 HAProxy 會偵測所有機器,並且取得現在連線數目最少的 Node 來給下一個連線。最後 stats-front 是顯示 HAProxy 偵測及連線狀態,請務必設定帳號密碼。

完成設定,如何偵測 MySQL Server 是否存活,靠著就是 9200 port,透過 Http check 方式,讓 HAProxy 知道 PXC 狀態,安裝完 PXC 後,可以發現多了 clustercheck 指令,我們必須先給 clustercheckuser 使用者帳號密碼

# Grant privileges required:
$ GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercheckpassword!';

clustercheck 指令會在 Local 執行 SHOW STATUS LIKE 'wsrep_local_state' MySQL 指令,回傳值為 200503,指令確定成功執行,最後步驟就是透過 xinetd 產生 9200 port 的服務。底下先安裝 xinetd 服務

$ yum -y install xinetd

產生 mysqlchk 設定

# default: on
# description: mysqlchk
service mysqlchk
{
# this is a config for xinetd, place it in /etc/xinetd.d/
    disable = no
    flags = REUSE
    socket_type = stream
    port = 9200
    wait = no
    user = nobody
    server = /usr/bin/clustercheck
    log_on_failure += USERID
    only_from = 0.0.0.0/0
    # recommended to put the IPs that need
    # to connect exclusively (security purposes)
    per_source = UNLIMITED
}

上面步驟全部成功,請打開 URL 輸入 HAProxy Status 頁面,看到底下狀態,就是代表設定成功

Statistics Report for HAProxy

一月 16, 2014

小惡魔AppleBOY
AppleBOY
小惡魔 – 電腦技術 – 工作筆記 – AppleBOY is about »

tag cloud

» Percona XtraDB Server 出現 read only issue

mysql_logo

最近在整理 Percona XtraDB Server 將 Read Write 全部拆開,寫入部份只開一台 Node,讀取機器 Replica 則是越多越好,當然如果預計 write 都不會有任何 conflict 的話,那就不用拆開,read write 都可以在同一台即可,拆開的目的要解決 Innodb transaction locking 問題。今天遇到的是將機器讀寫都放在同一台,然後同時多台 Master 架構,結果跑在 CodeIgniter 上面出現底下錯誤訊息

The MySQL server is running with the –read-only option so it cannot execute this statement

出現這問題的時候,第一時間去看一下 Percona cluster mysql 版本,發現不是踩到 read-only blocks SELECT statements in PXC 雷,這雷在 Percona XtraDB Cluster 5.5.33-23.7.6 被解掉了

Server version: 5.5.34-55-log Percona XtraDB Cluster (GPL), wsrep_25.9.r3928

後來查到原因是 MySQL User 權限不足,請將 SUPER Privilege 權限開啟。這樣 MySQL 使用者就可以忽略 read_only = on 參數,執行 SQL Command。

GRANT SUPER ON *.* TO 'ustv'@'%'

一月 15, 2014

小惡魔AppleBOY
AppleBOY
小惡魔 – 電腦技術 – 工作筆記 – AppleBOY is about »

tag cloud

» Sysbench 測試 Percona XtraDB Server 效能

mysql_logo

今天拿 MySQL benchmark tool – Sysbench 來測試看看 Percona XtraDB Server + Haproxy 效能如何,實驗主機是執行在 CentOS 6.4 版本,記憶體 128 G,在 CentOS 本身用 Yum 安裝 Sysbench 時,內建的版本為 0.4.12,單機測試 MySQL 效能不會出現任何錯誤,但是只要是透過 Haproxy,並且有兩台以上的 Server,就會噴出底下錯誤訊息:

ALERT: failed to execute mysql_stmt_execute(): Err1317 Query execution was interrupted

碰到這問題,在網路上找到一篇 sysbench duplicate entries,裡面提到請使用 Sysbench 0.5.0 版本就不會噴出此錯誤,直接看 sysbench 0.5 for CentOS 6 這篇,裡面有 CentOS 64 打包好的 rpm package,下載後透過 rpm 指令安裝即可

$ rpm -ivh sysbench-0.5-3.el6_.x86_64.rpm

安裝完成,實際底下測試看看

建立測試用資料庫

請先建立 sbtest 資料庫

mysql> create database sbtest;

產生測試用資料

$ sysbench --test=oltp --db-driver=mysql --mysql-engine-trx=yes --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-user=xxxx --mysql-password=xxxxx --oltp-auto-inc=off --test=/usr/share/doc/sysbench/tests/db/oltp.lua --oltp-table-size=1000000 prepare

請注意 mysql-port 請務必填寫 Haproxy port,--oltp-table-size 用來產生多少筆資料,先產生 100 萬筆資料,大小大概是 270 MB

開始測試

$ sysbench --test=oltp --db-driver=mysql --mysql-engine-trx=yes --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-user=xxxx --mysql-password=xxxxx --oltp-auto-inc=off --test=/usr/share/doc/sysbench/tests/db/oltp.lua --oltp-table-size=100000 --num-threads=100 run

差別在於最後執行的步驟 run,上一個步驟則是 prepare

看結果

會跑出底下的數據給您參考

OLTP test statistics:
    queries performed:
        read:                            172102
        write:                           48943
        other:                           22293
        total:                           243338
    transactions:                        10000  (46.37 per sec.)
    deadlocks:                           2293   (10.63 per sec.)
    read/write requests:                 221045 (1025.00 per sec.)
    other operations:                    22293  (103.37 per sec.)

General statistics:
    total time:                          215.6540s
    total number of events:              10000
    total time taken by event execution: 21427.5778s
    response time:
         min:                                 53.45ms
         avg:                               2142.76ms
         max:                              22097.26ms
         approx.  95 percentile:            5689.02ms

Threads fairness:
    events (avg/stddev):           100.0000/43.42
    execution time (avg/stddev):   214.2758/0.75

清除測試資料

$ sysbench --test=oltp --db-driver=mysql --mysql-engine-trx=yes --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-user=xxx --mysql-password=xxx --oltp-auto-inc=off --test=/usr/share/doc/sysbench/tests/db/oltp.lua --oltp-table-size=1000000 cleanup

請直接下 cleanup 即可

參考資料: MySQL benchmark tool – sysbench

四月 5, 2013

小惡魔AppleBOY
AppleBOY
小惡魔 – 電腦技術 – 工作筆記 – AppleBOY is about »

tag cloud

» MySQL 用 MySQLDump 備份 InnoDB 注意事項

mysql_logomysql_logo

大家在備份 MySQL 資料庫時一定是使 mysqldump 指令,不管是 MyISAM 或 InnoDB 都一樣, 在處理 InnoDB 格式備份時使用 mysqldump -single-transaction,但是你會發現在大多的備份狀況都是 OK 的,只是有時候會發現有的資料表只有備份到 structure 而無備份到 Data?

MySQL Performance Blog 看到這篇講解 Best kept MySQLDump Secret,此問題出在 how MySQL’s Transactions work with DDL,ALTER TABLE 會建立一個 temporary table,並且將該資料表資料複製過去,接著刪除原有資料表,最後將 temporary table 命名為原來資料表。

底下是原作者提到的原因

How does data visibility works in this case ? DDLs are not transactional and as such the running transaction will not see the contents of old table once it is dropped, transaction also will see the new table which was created after transaction was started, including table created by ALTER TABLE statement. Transactions however apply to DATA which is stored in this table and so data which was inserted after start of transaction (by ALTER TABLE statement) will not be visible. In the end we will get new structure in the dump but no data.

最好的解法就是改用 mysqldump -lock-all-tables,但是備份的時候,資料庫是無法寫入,僅能讀取,也或者可以透過 Percona Xtrabackup 工具來備份 InnoDB。

三月 31, 2013

小惡魔AppleBOY
AppleBOY
小惡魔 – 電腦技術 – 工作筆記 – AppleBOY is about »

tag cloud

» Galera Cluster for MySQL Multi-master Replication

galera_mysql_replicationgalera_mysql_replication

最近公司買了幾台機架伺服器來處理 HTTP 跟 DB Load balancer,要做到 DB 的分散式架構,首先需要同步多台機器資料,也就是寫入或更動任意一台單筆資料,另外平行的機器也會同時進行更新。同步的好處可以用來做備援及分散處理連線,而要做到此功能,可子參考網路上評價不錯的 Galera Cluster for MySQL 方案。本篇會介紹在 UbuntuCentOS 6.x final 版本如何安裝 Galera 伺服器套件及設定。要架設 Galera Cluster Server,有兩種套件選擇,一個是 Percona XtraDB Cluster 另一個是 MariaDB Galera Cluster,這次作者會介紹後者的安裝。

Galera Cluster 介紹

為什麼要選擇 Galera Cluster Server,它有什麼優點及功能呢?MySQL/Galera 是一套可以同步多台 MySQL/InnoDB 機器的叢集系統,底下可以列出功能。

  • 同步複製資料
  • 可讀取和寫入叢集系統內任一節點
  • 自動偵測節點錯誤,如果有節點當機,則叢集系統自動移除該節點
  • 可任意擴充節點
  • 採用 row level 方式來平行複製資料

從上面功能看來,我們可以平行任意擴充節點,動態增加伺服器到叢集系統,要做到上面功能,就是利用 Galera library 來做到同步資料處理,同步的詳細細節,可以參考 Galera library 連結。這邊就不再多描述了。

安裝 Galera Cluster Server

本篇介紹的 MySQL Server 是使用 MariaDB 套件,而不是安裝原始的 MySQL。CentOS 和 Ubuntu 安裝方式雷同,前者是用 yum 後者則是 aptitude,在安裝前請先下載對應的 repository 設定檔

CentOS

$ yum -y update && yum -y upgrade
$ yum -y install MariaDB-Galera-server MariaDB-client galera

Ubuntu

$ aptitude -y update
$ aptitude -y install mariadb-galera-server-5.5 galera

啟動 MySQL

$ /etc/init.d/mysql start

設定 Galera Cluster Server

先講一下環境,目前總共兩台 Galera Server,IP 分別是:

Node_1: 192.168.1.100
Node_2: 192.168.1.101

建立 Node_1, Node_2 MySQL User,用來認證使用,先進入 MySQL Console.

$ mysql -u root -p
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 154
Server version: 10.0.1-MariaDB-mariadb1~precise-log mariadb.org binary distribution

Copyright (c) 2000, 2012, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

建立 cluster 使用者,密碼為 1234,針對 % 跟 localhost 同時建立。

MariaDB [(none)]> GRANT USAGE ON *.* to cluster@'%' IDENTIFIED BY '1234';
MariaDB [(none)]> GRANT ALL PRIVILEGES on *.* to cluster@'%';
MariaDB [(none)]> GRANT USAGE ON *.* to cluster@'localhost' IDENTIFIED BY '1234';
MariaDB [(none)]> GRANT ALL PRIVILEGES on *.* to cluster@'localhost';

在 192.168.1.100 建立 Galera 設定檔 /etc/mysq/conf.d/wsrep.cnf

[MYSQLD]                                                                                                              
wsrep_provider=/usr/lib64/galera/libgalera_smm.so                                                                    
wsrep_cluster_address="gcomm://"                                              
wsrep_sst_auth=cluster:1234

重新啟動 mysqld 會看到多 listen 4567 port

tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      1118/mysqld
tcp        0      0 0.0.0.0:4567            0.0.0.0:*               LISTEN      1118/mysqld

在 192.168.1.101 建立 Galera 設定檔 /etc/mysq/conf.d/wsrep.cnf 並且將 cluster address 設定為 192.168.1.100

[MYSQLD]                                                                                                              
wsrep_provider=/usr/lib64/galera/libgalera_smm.so                                                                    
wsrep_cluster_address="gcomm://192.168.1.100:4567"                                              
wsrep_sst_auth=cluster:1234

設定完成後,重新啟動 mysql

$ /etc/init.d/mysql restart

要怎麼驗證設定成功呢?可以透過 mysql console 來確認

$ mysql -e "SHOW STATUS LIKE 'wsrep%';"

可以看到結果如下

+----------------------------+--------------------------------------------------------------+
| wsrep_local_state_uuid     | b4e876e0-8c1e-11e2-0800-8da732edfe2a                         |
| wsrep_protocol_version     | 4                                                            |
| wsrep_last_committed       | 516                                                          |
| wsrep_replicated           | 0                                                            |
| wsrep_replicated_bytes     | 0                                                            |
| wsrep_received             | 3                                                            |
| wsrep_received_bytes       | 282                                                          |
| wsrep_local_commits        | 0                                                            |
| wsrep_local_cert_failures  | 0                                                            |
| wsrep_local_bf_aborts      | 0                                                            |
| wsrep_local_replays        | 0                                                            |
| wsrep_local_send_queue     | 0                                                            |
| wsrep_local_send_queue_avg | 0.000000                                                     |
| wsrep_local_recv_queue     | 0                                                            |
| wsrep_local_recv_queue_avg | 0.000000                                                     |
| wsrep_flow_control_paused  | 0.000000                                                     |
| wsrep_flow_control_sent    | 0                                                            |
| wsrep_flow_control_recv    | 0                                                            |
| wsrep_cert_deps_distance   | 0.000000                                                     |
| wsrep_apply_oooe           | 0.000000                                                     |
| wsrep_apply_oool           | 0.000000                                                     |
| wsrep_apply_window         | 0.000000                                                     |
| wsrep_commit_oooe          | 0.000000                                                     |
| wsrep_commit_oool          | 0.000000                                                     |
| wsrep_commit_window        | 0.000000                                                     |
| wsrep_local_state          | 4                                                            |
| wsrep_local_state_comment  | Synced                                                       |
| wsrep_cert_index_size      | 0                                                            |
| wsrep_causal_reads         | 0                                                            |
| wsrep_incoming_addresses   | xxx.xxx.xxx.xxx:3306,xxx.xxx.xx.xxx:3306,xxx.xxx.xxx.xx:3306 |
| wsrep_cluster_conf_id      | 45                                                           |
| wsrep_cluster_size         | 3                                                            |
| wsrep_cluster_state_uuid   | b4e876e0-8c1e-11e2-0800-8da732edfe2a                         |
| wsrep_cluster_status       | Primary                                                      |
| wsrep_connected            | ON                                                           |
| wsrep_local_index          | 0                                                            |
| wsrep_provider_name        | Galera                                                       |
| wsrep_provider_vendor      | Codership Oy <info@codership.com>                            |
| wsrep_provider_version     | 23.2.4(r147)                                                 |
| wsrep_ready                | ON                                                           |
+----------------------------+--------------------------------------------------------------+

看到上述結果,有一個非常重要的數值,那就是 wsrep_ready,正確值是 ON,另外看看 wsrep_cluster_size 是否跟您設置 Node 的數量相同,這兩個如果都正確,那就表示設定成功了,由於上面 192.168.1.100 是主 Cluster Server,現在我們必須互相設定雙方 Address 也就是設定如下

node 01: gcomm://192.168.1.101
node 02: gcomm://192.168.1.100

設定如上述的好處就是,當 Node 01 關機時,資料還是在 Node 2 繼續運作,等到 Node 01 恢復上線後,資料又會從 Node 02 同步複製過來。

增加新 Node

我們可以任意新增多台 Node 到 Cluster 叢集系統裡,設置過程非常簡易

1. 安裝 MariaDB Server
2. 安裝 Galera Library
3. 設定 wsrep_cluster_address="gcomm://192.168.1.100:4567"

設定完成,就可以看到資料庫已經同步複製資料到新 Node 上面。如果遇到任何一台 Node 突然關機,不用緊張,系統會保持目前的資料,等到機器上線時,又會從 gcomm://another_node_ipaddress 同步後續的資料。

動態設定 gcomm://

如果新增一台新的 Node,我們就必須改動其它 Node 的 gcomm:// 設定,並且重新啟動 mysqld 服務,這樣會有些微時間影響,我們可以透過直接修改 GLOBAL wsrep_cluster_address 的值

$ mysql -e "SHOW VARIABLES LIKE 'wsrep_cluster_address';"                                                            
+-----------------------+-------------------------------------------------+                                          
| Variable_name         | Value                                           |                                          
+-----------------------+-------------------------------------------------+                                          
| wsrep_cluster_address | gcomm://xxx.xxx.xxx.xx:4567,xxx.xxx.xx.xxx:4567 |                                          
+-----------------------+-------------------------------------------------+

mysql> SET GLOBAL wsrep_cluster_address='gcomm://192.168.1.100:4567';
Query OK, 0 ROWS affected (3.51 sec)

mysql> SHOW VARIABLES LIKE 'wsrep_cluster_address';
+-----------------------+---------------------------+
| Variable_name         | VALUE                     |
+-----------------------+---------------------------+
| wsrep_cluster_address | gcomm://192.168.1.100:4567|
+-----------------------+---------------------------+

最後需要注意的地方是,由於我們每一台機器都互相設定,如果要關閉全部 Node 機器,請務必將第一台重新設定 gcomm:// 為空值,讓後續重新啟動的機器可以先連上此機器進行同步,

這次把 MySQL Replication 安裝設定完成,也就是完成架構圖的最下面部份

Server-load-balancersServer-load-balancers

之後會介紹如何透過 HAproxy 來處理 MySQL Load Balancer。

一月 1, 2013

小惡魔AppleBOY
AppleBOY
小惡魔 – 電腦技術 – 工作筆記 – AppleBOY is about »

tag cloud

» Percona XtraBackup InnoDB 備份工具

Percona XtraBackup 是一套 compiled C 程式,用於備份 MySQL InnoDB 資料庫,過去備份 MyISAM 或 InnoDB 都是透過 mysqldump 指令,或者是直接 copy /var/lib/mysql 目錄當作備份(這方法盡量少做,請確定 MySQL 版本一致),XtraBackup 用於備份 InnoDB 資料部份,請注意這邊,真的只有”備份資料”,而不是全部(結構跟資料),底下仔細介紹如何安裝:

安裝方式

大家可以選擇透過 yumapt Repository 方式安裝,下面介紹 apt 方式即可。

$ gpg --keyserver  hkp://keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
$ gpg -a --export CD2EFD2A | sudo apt-key add -

將 apt 伺服器寫入 /etc/apt/sources.list

deb http://repo.percona.com/apt VERSION main
deb-src http://repo.percona.com/apt VERSION main

VERSION 請至換 Ubuntu Server 版號,如果您想測試實驗性版本請加入底下連結

deb http://repo.percona.com/apt VERSION main experimental
deb-src http://repo.percona.com/apt VERSION main experimental

注意事項

根據不同的 MySQL 版本來選擇 XtraBackup 指令,可以參考 Choosing the Right Binary,所以大家不要用錯指令了。

透過寫入 my.cnf 可以設定備份目錄,此步驟可以省略

[xtrabackup]
target_dir = /home/backups/mysql/

完整備份

可以備份 InnoDB data and log files 從 /var/lib/mysql/ 到 /home/backups/mysql/

$ xtrabackup --defaults-file=/etc/mysql/my.cnf --backup --target-dir=/home/backup/mysql --datadir=/var/lib/mysql

–defaults-file 吃 MySQL 設定檔,我們可以另外指定 –target-dir 備份目錄,如果之前你有寫入 my.cnf,指令就可以少寫 –target-dir,備份完成以後,我們需要 Prepare 兩次 MySQL Data

$ xtrabackup --defaults-file=/etc/mysql/my.cnf --prepare --target-dir=/home/backup/mysql

看到底下訊息就代表成功了

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
130101 11:55:26  InnoDB: Starting shutdown...
130101 11:55:30  InnoDB: Shutdown completed; log sequence number 450927116

恢復備份資料

XtraBackup 程式並非用於備份 MyISAM 資料及 .frm 檔案,所以必須分開備份,底下是用於恢復 InnoDB 資料

$ cd /home/backup/mysql/
$ rsync -rvt --exclude 'xtrabackup_checkpoints' --exclude 'xtrabackup_logfile' ./ /var/lib/mysql
$ chown -R mysql:mysql /var/lib/mysql/
$ service mysql restart

另外請記的先備份 .frm 檔案,沒 .frm 檔案,備份資料就沒有用了。

Related View

十二月 15, 2012

小惡魔AppleBOY
AppleBOY
小惡魔 – 電腦技術 – 工作筆記 – AppleBOY is about »

tag cloud

» MySQL InnoDB Engine 轉換成 InnoDB

mysql_logo

如果對於 InnoDB 不了解的讀者們,可以參考作者之前寫的 MySQL 預設儲存引擎: InnoDB 介紹,最近開始把原本 MySQL 5.1 預設 MyISAM Table 全部轉換成 InnoDB,MySQL 5.5 版本開始預設的儲存引擎就是 InnoDB,InnoDB 現在也非常完整,也支援 Full Text (5.6.4 開始支援)。作者在轉換過程其實蠻順利的,步驟也不是很複雜,只要按底下步驟,就可以順利轉換。

備份原本資料庫

為了避免資料庫被玩壞,轉換之前一定要做好備份,也或者先在別台機器實驗,備份 MySQL InnoDB 非常簡單,如果你是安裝 Windows 版的 MySQL,透過像是 Appservxampp 懶人包,其實可以找到 MySQL 底下有個 data 目錄,將這目錄直接備份即可。如果是 Linux 也是一樣,備份 /var/lib/mysql,最後提供 MySQL 指令備份,透過 mysqldump 就可以了

$ mysqldump -u root -p database_name > db_name.sql

轉換 InnoDB tabe to InnoDB

直接用 vim 或編輯器打開上面指令所備份的 .sql 檔案,將

ENGINE=InnoDB

改成

ENGINE=InnoDB ROW_FORMAT=COMPRESSED

存檔後,再透過底下指令將資料存回到指定資料庫

$ mysql -u root -p database_name < db_name.sql

完成後可以透過 phpMyAdmin 檢查看看是不是全部的都已經轉換成 InnoDB。

新增 FOREIGN KEY

最後設定 InooDB 好用的 FOREIGN KEY,FOREIGN KEY 可以綁定 parent table 跟 child table 多個 key 值,可以指定,當刪除 parent table 資料時,連帶 child table 也一起刪除或者是改成 Default value,轉換之前有一點非常要注意的是,FOREIGN KEY 的欄位格式需要一致,也就是如果 parent 欄位是 int(11) 那 child 的欄位就必須一樣,否則會無法設定 FOREIGN KEY,另外如果原本的資料庫非常大,也許會存在有些 child key 沒對應到 parent key,原因就是刪除了 parent row,但是忘記刪除相關 table 資料,所以務必寫程式將那些冗員刪除。

利用 ALTER 指令來增加 FOREIGN KEY

ALTER TABLE tbl_name ADD FOREIGN KEY (index_name) REFERENCES tbl_name (index_col_name) ON DELETE reference_option ON UPDATE reference_option;

reference_option 可以是 CASCADE, SET NULL, RESTRICT, NO ACTION 或 SET DEFAULT。舉個例子,建立 users 跟 uses_groups 資料表,users 內有 id auto increament key,uses_groups 則是有 user_id 欄位來對應,所以透過底下可以設定該 FOREIGN KEY

ALTER TABLE uses_groups ADD FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE;

經過上述步驟,相信大家都可以輕鬆轉換,如果遇到什麼問題,可以在此篇留言。或者可以參考 gslin 大神寫的 把大量的 InnoDB table 換成 InnoDB

Related View

十一月 10, 2012

小惡魔AppleBOY
AppleBOY
小惡魔 – 電腦技術 – 工作筆記 – AppleBOY is about »

tag cloud

» MySQL 預設儲存引擎: InnoDB 介紹

mysql_logo

MySQL 是一套眾所皆知的 Database System,今天來簡介 InnoDB 儲存引擎,在 MySQL 5.5.5 之前預設的儲存引擎是 InnoDB,但是為什麼在 5.5 之後官方要將預設儲存引擎換成 InnoDB 呢?大家都知道 InnoDB 用來管理交易非常方便,因為 InnoDB 透過 row lock,相對於 InnoDB 透過 table lock 來的有效率,也避免大量寫入的時候,造成無法讀取資料,這就是 row lock 的優勢,當然用 InnoDB 最主要的原因還有 Full-text search indexes 功能,但是別擔心 MySQL 5.6.4 之後(含此版本) InnoDB 開始支援 Full-text search 功能,另外在使用 InnoDB 時候,如果資料突然出問題,還必須使用 MySQL Binary Log 來恢復資料,如果用 InnoDB 這就沒關係了。大家還在用 InnoDB 嘛?開始升級伺服器,一起體驗 InnoDB 的功能,如果已經上線很久的網站,作者不建議轉換,因為可能會遇到很多雷。

目前不用太擔心硬體的架構這方面了,隨便都是 64G 記憶體,四核心主機,大家所在意的還是 MySQL 是否 reliability 跟錯誤恢復,所以 MySQL 在 5.5 以後的版本大膽將 InnoDB 儲存引擎,建立資料表不用再加上 ENGINE=InnoDB,但是大家可以發現 MySQL 安裝好後,內建 mysqlinformation_schema 資料庫還是用 InnoDB 儲存引擎,請大家不要亂動這兩個資料庫。底下來看看 InnoDB 的優勢。

InnoDB 優勢

已經使用過 InnoDB 的朋友們,作者相信你可以來嘗試看看 InnoDB,使用的同時會發現很多 InnoDB 優點。

1. 如果伺服器因為硬體或軟體疏失,無論發生任何問題,請重新啟動伺服器,啟動之後並不需要做任何事情,InnoDB 會自動修復 crash 部份,將已經 commit 的資料全部寫回資料表。假如您處理任何資料,但是尚未 commit,系統會自動恢復,所以只要將伺服器重新啟動,就可以恢復到 crash 之前的狀態。

2. InnoDB 將 Table 及 index 資料 cache 在 buffer pool,所以可以快速存取任何資料,因為這些資料都是直接從 Memory 讀取,快取可以存放任和型態的資料,提升處理效能,假如您有實體主機,請設定 60% ~ 80% 實體記憶體給 InnoDB buffer pool。

3. 設計資料庫時,請務必在每個資料表設定適當的 Primary key,當您在執行任何 SQL 語法時,只要牽扯到 Primary key,InnoDB 會自動優化效能,如果將 Primary key 用在 WHERE,ORDER BY,GROUP 等條件子句或 join 操作,讀取速度會是非常快。

4. InnoDB 可以讓您同時讀取或寫入同一個資料表,它將需要改變的資料存在 streamline disk I/O。所以大家不用擔心 Lock Table 的問題了。

5. InnoDB 提供錯誤偵測 (checksum mechanism),假如有資料已經損壞在 Disk 或 Memory,使用此資料之前,系統將會提醒你。

6. InnoDB 具備處理極大量資料的效能優勢,假如在同一資料表存取同樣的資料,內部透過 Adaptive Hash Index 機制提升讀取速度。

以上是作者覺得 InnoDB 改善的地方,大家可以透過 SHOW ENGINES 指令知道伺服器是否支援 InnoDB

Related View

support:

biggo.com.tw

A Django site.