MySQL集群安装、负载均衡及备份恢复

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) 可通过正则表达式来指定日志文件，如 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；   -- 启动新的日志文件，一般在数据库备份后使用
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‘      -- 删除该日期之前的日志