MySQL叢集安裝、負載平衡及備份恢複

標籤：mysql   ndbcluster   nginx   haproxy   binlog   

MYSQL叢集安裝學習筆記

【一】伺服器準備（作業系統linux皆可，我用的是RHEL6.4）：
一個管理節點 10.101.4.32
兩個資料節點 10.101.4.33 10.101.4.34
兩個查詢節點 10.101.4.36 10.101.4.37
 
下載最新版本的mysql（我用的是 mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64.tar）

【二】所有節點配置：
1、hosts配置，增加所有節點IP與主機名稱對應

2、增加mysql使用者和組, 並將mysql使用者添加到sudo組
groupadd mysql
useradd mysql -g mysql

3、關閉防火牆
chkconfig iptables off
service iptables stop

4、關閉selinux
vi /etc/sysconfig/selinux
設定 SELINUX=disabled

5、安裝mysql
上傳tar包到 /tmp 目錄下
cd /tmp
tar -xvf mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64.tar
mv mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64 /usr/local/mysql
rm -rf mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64.tar
chown -R mysql:mysql /usr/local/mysql
su - mysql
cd /usr/local/mysql
scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql

6、設定環境變數
vi /etc/profile
export MYSQL_HOME=/usr/local/mysql
export PATH=$MYSQL_HOME/bin:$PATH
source /etc/profile


【三】管理節點配置：
1、建立設定檔
mkdir /var/lib/mysql-cluster
vi /var/lib/mysql-cluster/config.ini
粘貼下面內容，伺服器IP資訊換成你實際的地址，這裡只是簡單配置，詳細的config參數配置自行百度

[TCP DEFAULT]
SendBufferMemory=256k
ReceiveBufferMemory=64k
#PortNumber=2202

[NDBD DEFAULT]
NoOfReplicas=2
DataMemory=2500M
IndexMemory=500M

#MaxNoOfConcurrentTransactions=
MaxNoOfConcurrentOperations=327680
#MaxNoOfLocalOperations=3276800
MaxNoOfAttributes=5120

TimeBetweenLocalCheckpoints=6

FileSystemPath=/usr/local/mysql/
BackupDataDir=/usr/local/mysql/data/
BackupDataBufferSize=2M
BackupLogBufferSize=2M
BackupMemory=4M
BackupWriteSize=32k

[NDB_MGMD]
NodeId=1
HostName=10.101.4.32
LogDestination=FILE:filename=/var/lib/mysql-cluster/mgmd.log
DataDir=/var/lib/mysql-cluster

[NDBD]
NodeId=11
HostName=10.101.4.33
DataDir=/usr/local/mysql/data
[NDBD]
NodeId=12
HostName=10.101.4.34
DataDir=/usr/local/mysql/data

[MYSQLD DEFAULT]
ArbitrationRank=0
ArbitrationDelay=0

[MYSQLD]
NodeId=21
HostName=10.101.4.36
[MYSQLD]
NodeId=22
HostName=10.101.4.37
# null mysqld node for backup-restore
[MYSQLD]


2、啟動管理節點
chown -R mysql:mysql /var/lib/mysql-cluster
su - mysql
/usr/local/mysql/bin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini  # 如果設定檔發生變化，啟動時需要加  --reload

3、驗證節點是否啟動成功
ps -ef|grep mgmd
netstat -lnput | grep 1186


【四】資料節點配置：
1、增加設定檔
vi /etc/my.cnf
粘貼下面內容

[mysqld]
ndbcluster
ndb-connectstring=10.101.4.32
default_storage_engine=ndb
default_tmp_storage_engine=ndb

[mysql_cluster]
ndb-connectstring=10.101.4.32  

2、啟動資料節點
su - mysql
/usr/local/mysql/bin/ndbd --initial   # 僅第一次啟動需要加 --initial 參數

3、驗證節點是否啟動成功
ps -ef|grep ndbd
netstat -lnput | grep 2202

【五】查詢節點配置：
1、增加設定檔
vi /etc/my.cnf
粘貼下面內容

[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/var/lib/mysql/mysql.sock
ndbcluster
ndb-connectstring=10.101.4.32
default_storage_engine=ndb
default_tmp_storage_engine=ndb

wait_timeout=86400
interactive_timeout=86400
max_allowed_packet=400M

max_open_files=8192
table_cache=2000

skip-name-resolve
lower_case_table_names=1
init_connect=‘SET AUTOCOMMIT=0‘

tmp_table_size=1024M
max_heap_table_size=1024M

[mysql_cluster]
ndb-connectstring=10.101.4.32


2、啟動mysql
第一次啟動最好用
/usr/local/mysql/bin/mysqld_safe &

添加到開機自啟動
cp -rf /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chown mysql:mysql /etc/init.d/mysqld
service mysqld restart

3、驗證節點是否啟動成功
ps -ef|grep mysqld
netstat -lnput | grep 3306

4、在tmp下增加mysql.sock軟鏈
cd /tmp
ln -s /var/lib/mysql/mysql.sock mysql.sock

5、修改mysql root使用者密碼
su - mysql
mysqladmin -u root password ‘mysql‘

【六】管理節點操作
1、查看叢集是否運行正常
su - mysql
用ndb_mgm進入控制台
show
如果出現下面資訊，那麼恭喜你了

Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=11   @10.101.4.33  (mysql-5.6.21 ndb-7.3.7, Nodegroup: 0, *)
id=12   @10.101.4.34  (mysql-5.6.21 ndb-7.3.7, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @10.101.4.32  (mysql-5.6.21 ndb-7.3.7)

[mysqld(API)]   2 node(s)
id=21   @10.101.4.36  (mysql-5.6.21 ndb-7.3.7)
id=22   @10.101.4.37  (mysql-5.6.21 ndb-7.3.7)

2、停止叢集管理節點和資料節點
shutdown

3、查看資料節點使用狀況
all report memory

4、查看錶資料存放區的分區情況
ndb_desc -c 10.101.4.32 -d erp2014 xt_product -p


【七】查詢節點操作
1、建立資料庫
create database erp2014;
2、增加資料庫遠端存取
因user表非ndbcluster引擎，所以每個節點上都要執行一下
GRANT ALL PRIVILEGES ON erp2014.* TO ‘admin‘@‘localhost‘ IDENTIFIED BY ‘admin‘ WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON erp2014.* TO ‘admin‘@‘10.101.50.30‘ IDENTIFIED BY ‘admin‘ WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON erp2014.* TO ‘admin‘@‘10.101.4.32‘ IDENTIFIED BY ‘admin‘ WITH GRANT OPTION;
FLUSH PRIVILEGES;

更換*.*可單獨對資料庫以及表進行授權訪問，更換%為固定主機，則只允許該主機串連，這些跟mysql非叢集版管理一致

2、建表時必須指定資料庫表的引擎為NDB或者NDBCLUSTER，否則同步失敗。

3、哪位大神能告訴我一台查詢節點上建立的預存程序和函數如何能自動同步到其他查詢節點呢？？？

【八.一】負載平衡設定——Nginx
1、下載最新版的nginx
cd /usr/local
wget http://nginx.org/download/nginx-1.7.7.tar.gz

2、由於nginx屬於七層架構，支援的是http協議，本身對tcp協議沒有支援，所以不能代理mysql等實現負載平衡(lvs和haproxy可以)，因此需要下載一個支援tcp的module
wget https://github.com/yaoweibin/nginx_tcp_proxy_module/archive/master.zip

3、解壓、打補丁
tar -xvf nginx-1.7.7.tar.gz
unzip master.zip
mv nginx-1.7.7 nginx
cd nginx
patch -p1 < /usr/local/nginx_tcp_proxy_module-master/tcp.patch

4、編譯、安裝
./configure --add-module=/usr/local/nginx_tcp_proxy_module-master/ --prefix=/usr/local/nginx --conf-path=/usr/local/nginx/nginx.conf
過程中可能會提示下面的錯誤
./configure: error: the HTTP rewrite module requires the PCRE library.
./configure: error: SSL modules require the OpenSSL library.
那麼安裝一下吧
sudo apt-get install pcre-devel openssl-devel
make
make install

5、修改設定檔，由於開始指定了conf-path=/usr/local/nginx/, 所以也要修改該目錄下的設定檔，而非/usr/local/nginx/conf/目錄中的那個，另外server中的那個監聽連接埠如果也配置為3306的話，會導致nginx啟動不了連接埠，換個不一樣的就好了
vi nginx.conf
添加下面內容
tcp {
    timeout 1d;
    proxy_read_timeout 10d;
    proxy_send_timeout 10d;
    proxy_connect_timeout 30;

    upstream mysql {
        server 10.101.4.36:3306 weight=1;
        server 10.101.4.37:3306 weight=1;
        check interval=60000 rise=2 fall=5 timeout=10000 type=tcp;
    }

    server {
        server_name 10.101.4.32;
        listen 10.101.4.32:13306;
        proxy_pass mysql;
        so_keepalive on;
        tcp_nodelay on;
    }
}

6、啟動nginx
cd /usr/local/nginx/xbin
./nginx
lsof | grep 13306
如果發現13306連接埠已經處於監聽狀態，那麼配置成功了。

【八.二】負載平衡設定——HAProxy
1、安裝haproxy
上傳haproxy-1.5.3.tar.gz到/usr/local/下，我是放在mgmt(10.101.4.32)節點上，正式部署需單獨伺服器
cd /usr/local
tar -xvf haproxy-1.5.3.tar.gz
mv haproxy-1.5.3 haproxy
cd /usr/local/haproxy
make TARGET=linux26 PREFIX=/usr/local/haproxy
make install PREFIX=/usr/local/haproxy

2、建立設定檔
mkdir haproxy/etc
vi haproxy.cfg
粘貼下面內容：

# this config needs haproxy-1.1.28 or haproxy-1.2.1

global
  log 127.0.0.1   local0 info
  log 127.0.0.1   local1 notice
  maxconn         40960
  chroot          /usr/local/haproxy
  uid             root
  gid             root
  daemon
  pidfile         /usr/local/haproxy/haproxy.pid

defaults
  log             global
  mode            http
  option          dontlognull
  option          redispatch  
  retries         3
  balance         roundrobin
  #balance        source
  #balance        leastconn

  timeout connect 5000
  timeout client  50000
  timeout server  50000
  timeout check   2000

listen mysql
  mode            tcp
  bind            0.0.0.0:23306
  balance         roundrobin
  #option mysql-check
  server mysql36 10.101.4.36:3306 weight 1 check inter 1s rise 3 fall 3
  server mysql37 10.101.4.37:3306 weight 1 check inter 1s rise 3 fall 3

listen stats
  bind            0.0.0.0:8888
  mode            http
  stats           enable
  stats uri       /admin
  stats refresh   5s
  stats realm     Haproxy\ statistics
  stats auth      admin:admin


3、啟動haproxy
/usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/etc/haproxy.cfg
訪問 http://10.101.4.32:8888/admin，可看到每個查詢節點是否都可以正常訪問
應用程式需要串連的IP為 10.101.4.32， 連接埠為 23306（可在上面配置中變更）

【九】線上添加資料節點
1、修改管理節點的config.ini檔案，增加兩個資料節點
[NDBD]
NodeId=13
HostName=10.101.4.36
DataDir=/usr/local/mysql/data
[NDBD]
NodeId=14
HostName=10.101.4.37
DataDir=/usr/local/mysql/data

2、準備兩台新機器（本例採用兩台查詢節點兼職），執行步驟【二】
複製已有資料節點上my.cnf檔案
順序重啟原cluster中所有節點, 管理節點——>資料節點——>查詢節點
啟動新的資料節點 ndbd --initial

3、增加結點組
進入管理節點控制台
create nodegroup 13,14

4、資料重新分區
ALTER ONLINE TABLE erp2014.cwpos_detail REORGANIZE PARTITION;

【十】備份恢複
1、在管理節點上執行備份
su - mysql
ndb_mgm
start backup
執行完後會在/usr/local/mysql/data/目錄下產生一個BACKUP檔案夾，裡面還有一個BACKUP-1檔案夾，其中1為備份組編號

2、進行一些刪資料或者刪表的操作後，關閉叢集
shutdown

3、按順序重新啟動叢集
管理節點:  
su - mysql
/usr/local/mysql/bin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini
資料節點:
su - mysql
ndbd (注意如果有需要恢複表、視圖、預存程序等中繼資料資訊，則需要採用 ndbd --initial 啟動)

4、恢複資料
因為資料是分區儲存在每個資料節點上的，因此在每個資料節點上都要執行一次恢複指令（參數說明: -c指定管理節點ip，-n指定資料節點的id，-b指定備份組編號，-m指定需要恢複中繼資料，-r指定需要恢複資料記錄，--backup_path指定備份組所在的路徑）
資料節點11:
su - mysql
ndb_restore -c10.101.50.44 -n11 -b1 -m -r --backup_path=/usr/local/mysql/data/BACKUP/BACKUP-1/

資料節點12:
su - mysql
ndb_restore -c10.101.50.44 -n12 -b1 -r --backup_path=/usr/local/mysql/data/BACKUP/BACKUP-1/

資料節點13:
su - mysql
ndb_restore -c10.101.50.44 -n13 -b1 -r --backup_path=/usr/local/mysql/data/BACKUP/BACKUP-1/

資料節點14:
su - mysql
ndb_restore -c10.101.50.44 -n14 -b1 -r --backup_path=/usr/local/mysql/data/BACKUP/BACKUP-1/

5、查看資料
登入到查詢節點，發現備份的資料都回來了，但是這隻能將資料恢複到備份時的那個時間點，假如在備份組產生後，我又誤刪除了一些資料，就不知道怎麼恢複了,這個留待後面研究

【十一】利用binlog實現閃回功能
1、接第十章的疑問，找了下資料，發現可以利用mysql的binlog功能來實現誤操作資料恢複

2、首先在查詢節點上my.cnf檔案中添加參數啟動binlog
log_bin=/usr/local/mysql/data/binlog/mysql-bin.log

3、重啟資料庫後，就會發現目錄下存在 mysql-bin.000001的二進位檔案(每次重啟資料庫或執行flush logs後都會讓檔案流水號增加)，然後對資料表進行增刪改等DML操作的步驟都會北記錄到這裡。

4、然後可通過mysql內建的mysqlbinlog命令來進行管理
su - mysql
直接在終端顯示指定區間的日誌資訊
mysqlbinlog -D /usr/local/mysql/data/binlog/mysql-bin.000001 --start-position=4 --stop-position=1862 --database=aabc
將指定區間的日誌資訊寫到sql檔案
mysqlbinlog -D /usr/local/mysql/data/binlog/mysql-bin.000001 --start-position=4 --stop-position=1862 --database=aabc > oper.sql
將指定區間的日誌資訊在資料庫中再次執行一遍(-D參數為disable-log-bin, 用意為這個再次執行的指令不記錄日誌)
mysqlbinlog -D /usr/local/mysql/data/binlog/mysql-bin.000001 --start-position=4 --stop-position=1862 --database=aabc | mysql -uroot -p

技巧：
  1) 可通過Regex來指定記錄檔，如 mysql-bin.[0-9]*
  2) 可通過在 mysql -uroot -ppwd 後增加參數 --one-database dbname 來恢複指定資料庫

5、日誌的相關指令
su - mysql
mysql -uroot -p

show master status;    -- 查看當前資料庫日誌寫在第幾個記錄檔，以及下個position號
show master logs；     -- 查看二進位檔案列表和檔案大小，也可用 show binary logs
show binlog events in ‘mysql-bin.000001‘;   -- 查看日誌中的event_type以及起始position號, 可用 limit 參數過濾行數
flush logs；   -- 啟動新的記錄檔，一般在Database Backup後使用
reset master;   -- 清除所有記錄檔(也可設定expire_logs_days參數來實現)
purge master logs to ‘mysql-bin.000008‘;            -- 刪除mysql-bin.000008之前的二進位記錄檔
purge master logs before ‘2014-12-10 04:07:00‘      -- 刪除該日期之前的日誌

MySQL叢集安裝、負載平衡及備份恢複