Galera Cluster for MySQL集群搭建

Galera Cluster for MySQL集群搭建

要架设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的部署

安装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

或者apt-get:

$ apt-get -y update

$ apt-get -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 https://www.360docs.net/doc/d611570251.html, 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/https://www.360docs.net/doc/d611570251.html,f [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/https://www.360docs.net/doc/d611570251.html,f，

并且将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/mysqlrestart

要怎么验证设置成功呢？可以透过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 |

| 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 | +-----------------------+-------------------------------------------------+

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

MariaDB [(none)]> SHOW VARIABLES LIKE'wsrep_cluster_address';

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

| Variable_name | VALUE |

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

| wsrep_cluster_address | gcomm://192.168.1.100:4567|

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

最后需要注意的地方是，由于我们每一台机器都互相设置，如果要关闭全部Node机器,请务必将第一台重新设置gcomm://为空值，让后续重新启动的机器可以先连上此机器进行同步，

这次把MySQL Replication 安装设置完成，也就是完成架构图的最下面部份

可能出现的问题

1.问题1：无法启动、停止或查询数据库

# service mysql start

Starting MySQL database server: mysqld.

Checking for corrupt, not cleanly closed and upgrade needing tables..

ERROR 1045 (28000): Access denied for user 'debian-sys-maint'@'localhost' (using password:

YES)

解决办法：

$ cat /etc/mysql/https://www.360docs.net/doc/d611570251.html,f

# Automatically generated for Debian scripts. DO NOT TOUCH!

[client]

host = localhost

user = debian-sys-maint

password = 9oFlmkyBeCHz7Pcm

socket = /var/run/mysqld/mysqld.sock

[mysql_upgrade]

host = localhost

user = debian-sys-maint

password = 9oFlmkyBeCHz7Pcm

socket = /var/run/mysqld/mysqld.sock

basedir = /usr

得到password（假定是9oFlmkyBeCHz7Pcm），然后在MySQL Qery Browser里执行：

$ mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 32

Server version: 5.5.34-MariaDB-1~precise-wsrep-log https://www.360docs.net/doc/d611570251.html, binary distribution, wsrep_23.7.6

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

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY '9oFlmkyBeCHz7Pcm' WITH GRANT OPTION;

Query OK, 0 rows affected (0.00 sec)

2.问题2：无法远程访问数据库

假定用户为keystone，密码123123。赋予权限：

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

任然无法访问该数据库。

解决办法：

修改/etc/mysql/https://www.360docs.net/doc/d611570251.html,f

root@cn13447:~# vim /etc/mysql/https://www.360docs.net/doc/d611570251.html,f

# MariaDB database server configuration file.

#

# You can copy this file to one of:

# - "/etc/mysql/https://www.360docs.net/doc/d611570251.html,f" to set global options,

# - "~/https://www.360docs.net/doc/d611570251.html,f" to set user-specific options.

#

# One can use all long options that the program supports.

# Run program with --help to get a list of available options and with

# --print-defaults to see which it would actually understand and use.

#

# For explanations see

# https://www.360docs.net/doc/d611570251.html,/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients

# It has been reported that passwords should be enclosed with ticks/quotes # escpecially if they contain "#" chars...

# Remember to edit /etc/mysql/https://www.360docs.net/doc/d611570251.html,f when changing the socket location. [client]

port = 3306

socket = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs

# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed. [mysqld_safe]

socket = /var/run/mysqld/mysqld.sock

nice = 0

[mysqld]

#

# * Basic Settings

#

user = mysql

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

port = 3306

basedir = /usr

datadir = /var/lib/mysql

tmpdir = /tmp

lc_messages_dir = /usr/share/mysql

lc_messages = en_US

skip-external-locking

#

# Instead of skip-networking the default is now to listen only on

# localhost which is more compatible and is not less secure.

# bind-address = 127.0.0.1 bind-address = 0.0.0.0重启数据库即可。