linux下安装MariaDB Galera Cluster
环境:
OS: CentOS 7
DB: mariadb-galera-10.0.31
1.安装步骤
1.1 环境部署
| 节点角色 |
ip |
| Host01 |
192.168.56.11 |
| Host02 |
192.168.56.12 |
| Host03 |
192.168.56.13 |
| nginx |
192.168.56.13 |
1.2 MariaDB Galera Cluster下载
下载介质:
mariadb-galera-10.0.31-linux-x86_64.tar.gz
galera-25.3.19-1.rhel7.el7.centos.x86_64.rpm
下载地址:
http://mirrors.ctyun.cn/MariaDB/
1.3 建立各主机的等效连接
步骤省略
1.3.1 验证等效连接
1.3.1.1 名称节点到各节点的等效性
[root@host01~]# ssh host02;host03 date
Last login:Mon Apr 18 11:27:01 2016 from host01.com
[root@host02~]# ssh host01;host03 date
Last login:Mon Apr 18 10:48:31 2016 from host03.com
[root@host03~]# ssh host01;host02 date
Last login:Mon Apr 18 14:52:30 2016 from host02.com
1.4 关闭防火墙
[root@host01~]# systemctl stop firewalld.service
[root@host01~]# systemctl disable firewalld.service
[root@host01~]# firewall-cmd --state
[root@host02~]# systemctl stop firewalld.service
[root@host02~]# systemctl disable firewalld.service
[root@host02~]# firewall-cmd –state
[root@host03~]# systemctl stop firewalld.service
[root@host03~]# systemctl disable firewalld.service
[root@host03~]# firewall-cmd --state
1.5 在各节点上安装MariaDB-Galera
1.5.1 创建用户和用户组
创建目录
[root@host01opt]# mkdir -p /opt/mariadb-galera
[root@host01opt]# groupadd maria
[root@host01opt]# useradd -g maria -d /opt/mariadb-galera maria
[root@host01opt]# passwd maria
##我这里设置密码跟用户名一样也是maria
1.5.2 安装相应的软件包
[root@hxl01conf]# yum install rsync
[root@hxl01conf]# yum install lsof
[root@host01soft]# rpm -ivh galera-25.3.19-1.rhel7.el7.centos.x86_64.rpm
galera-25.3.19-1.rhel7.el7.centos.x86_64.rpm
[root@host01soft]# rpm -ivh galera-25.3.19-1.rhel7.el7.centos.x86_64.rpm
warning:galera-25.3.19-1.rhel7.el7.centos.x86_64.rpm: Header V4 DSA/SHA1 Signature, keyID 1bb943db: NOKEY
error:Failed dependencies:
libboost_program_options.so.1.53.0()(64bit) is needed bygalera-25.3.19-1.rhel7.el7.centos.x86_64
解决办法:
yum install boost-devel.x86_64
[root@host01soft]# rpm -ivh galera-25.3.19-1.rhel7.el7.centos.x86_64.rpm
warning:galera-25.3.19-1.rhel7.el7.centos.x86_64.rpm: Header V4 DSA/SHA1 Signature, keyID 1bb943db: NOKEY
Preparing... #################################[100%]
Updating/ installing...
1:galera-25.3.19-1.rhel7.el7.centos#################################[100%]
1.5.3 安装数据库
解压安装包并移动到指定目录
[root@host01soft]# tar -zxvf mariadb-galera-10.0.31-linux-x86_64.tar.gz
[root@host01]#mv mariadb-galera-10.0.31-linux-x86_64 /opt/mariadb-galera/mariadb-galera-10031
创建配置文件目录
mkdir -p/opt/mariadb-galera/mariadb-galera-10031/conf ##数据库配置文件目录
mkdir -p/opt/mariadb-galera/mariadb-galera-10031/conf/galera ##集群配置文件目录
数据库初始化参数
[root@host01conf]# more my.cnf
[mysqld]
port=3306
server-id=1 ##相应修改节点2和节点3的值分别为 2和3
basedir=/opt/mariadb-galera/mariadb-galera-10031
datadir=/opt/mariadb-galera/mariadb-galera-10031/data
character-set-server=utf8
max_connections= 1500
[client]
port =3306
socket=/opt/mariadb-galera/mariadb-galera-10031/mysql.sock
default-character-set= utf8
[mysqldump]
quick
max_allowed_packet= 16M
[myisamchk]
key_buffer_size= 8M
sort_buffer_size= 8M
read_buffer= 4M
write_buffer= 4M
修改文件目录权限
[root@host01opt]# chown -R maria:maria ./mariadb-galera
[root@host01opt]# pwd
/opt
初始化数据库
[root@host01scripts]#cd /opt/mariadb-galera/mariadb-galera-10031/scripts
[root@host01scripts]#./mysql_install_db --basedir=/opt/mariadb-galera/mariadb-galera-10031--datadir=/opt/mariadb-galera/mariadb-galera-10031/data --user=maria
要是遇到如下错误
/opt/mariadb-galera/mariadb-galera-10031/bin/mysqld:error while loading shared libraries: libaio.so.1: cannot open shared objectfile: No such file or directory
需要安装
yuminstall libaio*
1.5.4 启动数据库
./mysqld_safe--defaults-file=/opt/mariadb-galera/mariadb-galera-10031/conf/my.cnf--user=maria &
1.5.5 初始化root密码
登录数据库
./mysql-h localhost -uroot
删除账号空的记录,并设置root账号登录密码
deletefrom mysql.user where user='';
updatemysql.user set password=PASSWORD('mysql') where user='root';
flushprivileges;
1.5.6 创建集群同步用户
grantall privileges on *.* to sst@'%' identified by 'mysql';
flushprivileges;
1.5.7 配置Galera Cluster参数
先停掉mysql
./mysqladmin-h localhost -uroot -pmysql shutdown
创建集群配置文件
cd/opt/mariadb-galera/mariadb-galera-10031/support-files
cpwsrep.cnf /opt/mariadb-galera/mariadb-galera-10031/conf/galera/
[root@host01galera]# more wsrep.cnf
[mysqld]
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.56.11,192.168.56.12,192.168.56.13"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_cluster_name='hxl_wsrep_cluster'
wsrep_node_address='192.168.56.11'##按照节点2节点3的ip进行修改
wsrep_node_name='hxl01' ##按照节点2节点3的主机名进行修改
wsrep_sst_method=rsync
wsrep_sst_auth=sst:mysql
修改./conf/my.cnf文件
在my.cnf文件最后面添加
!includedir/opt/mariadb-galera/mariadb-galera-10031/conf/galera/
[root@host01conf]# more my.cnf
[mysqld]
port=3306
server-id=1
basedir=/opt/mariadb-galera/mariadb-galera-10031
datadir=/opt/mariadb-galera/mariadb-galera-10031/data
character-set-server=utf8
max_connections= 1500
[client]
port =3306
socket=/opt/mariadb-galera/mariadb-galera-10031/mysql.sock
default-character-set= utf8
[mysqldump]
quick
max_allowed_packet= 16M
[myisamchk]
key_buffer_size= 8M
sort_buffer_size= 8M
read_buffer= 4M
write_buffer= 4M
!includedir/opt/mariadb-galera/mariadb-galera-10031/conf/galera/
在root账号下添加修改文件,需要想要修改下权限
[root@host02opt]# chown -R maria:maria ./mariadb-galera
1.6 启动第一个节点
./mysqld_safe--defaults-file=/opt/mariadb-galera/mariadb-galera-10031/conf/my.cnf--wsrep-new-cluster --user=maria
每次集群启动的时候需要加上--wsrep-new-cluster选项启动,要启动最后关闭数据库的节点，通过查找每个节点data目录下的grastate.dat文件，查看该文件内容，safe_to_bootstrap值为 1的节点为启动的第一个节点。
1.7 启动第二个节点
./mysqld_safe--defaults-file=/opt/mariadb-galera/mariadb-galera-10031/conf/my.cnf--user=maria
1.8 启动第三个节点
./mysqld_safe--defaults-file=/opt/mariadb-galera/mariadb-galera-10031/conf/my.cnf--user=maria
1.9 添加仲裁节点
# garbd-a gcomm://192.168.56.11 -g hxl_wsrep_cluster –d
1.10 验证
1.10.1 验证wsrep参数
登录任何一个节点查看wsrep参数，主要关注红色部分的参数
[root@hxl03bin]# ./mysql -h localhost -uroot -pmysql
Welcometo the MariaDB monitor. Commands endwith ; or \g.
YourMariaDB connection id is 4
Serverversion: 10.0.31-MariaDB-wsrep MariaDB Server, wsrep_25.19.rc3fc46e
Copyright(c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB[(none)]> show status like '%wsrep%';
+------------------------------+----------------------------------------------------------+
|Variable_name | Value |
+------------------------------+----------------------------------------------------------+
|wsrep_local_state_uuid |43caaa91-54bf-11e7-9321-c60bd8969b24 |
|wsrep_protocol_version | 7 |
|wsrep_last_committed | 0 |
|wsrep_replicated | 0 |
|wsrep_replicated_bytes | 0 |
|wsrep_repl_keys | 0 |
|wsrep_repl_keys_bytes | 0 |
|wsrep_repl_data_bytes | 0 |
|wsrep_repl_other_bytes | 0 |
|wsrep_received | 3 |
|wsrep_received_bytes | 290 |
|wsrep_local_commits | 0 |
|wsrep_local_cert_failures | 0 |
|wsrep_local_replays | 0 |
|wsrep_local_send_queue | 0 |
|wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
|wsrep_local_send_queue_avg |0.000000 |
|wsrep_local_recv_queue | 0 |
|wsrep_local_recv_queue_max | 1 |
|wsrep_local_recv_queue_min | 0 |
|wsrep_local_recv_queue_avg |0.000000 |
|wsrep_local_cached_downto |18446744073709551615 |
|wsrep_flow_control_paused_ns | 0 |
| 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_cert_interval |0.000000 |
|wsrep_incoming_addresses |192.168.56.11:3306,192.168.56.13:3306,192.168.56.12:3306 |
|wsrep_desync_count | 0 |
|wsrep_evs_delayed | |
|wsrep_evs_evict_list | |
|wsrep_evs_repl_latency |0/0/0/0/0 |
|wsrep_evs_state |OPERATIONAL |
|wsrep_gcomm_uuid |75ce45ee-54c3-11e7-9d93-4ef9d5520bd4 |
|wsrep_cluster_conf_id | 9 |
|wsrep_cluster_size | 3 |
|wsrep_cluster_state_uuid |43caaa91-54bf-11e7-9321-c60bd8969b24 |
| wsrep_cluster_status | Primary |
|wsrep_connected | ON |
|wsrep_local_bf_aborts | 0 |
|wsrep_local_index | 1 |
|wsrep_provider_name |Galera |
|wsrep_provider_vendor | CodershipOy <info@codership.com> |
|wsrep_provider_version |25.3.19(r3667) |
|wsrep_ready | ON |
|wsrep_thread_count | 2 |
+------------------------------+----------------------------------------------------------+
58 rowsin set (0.00 sec)
1.10.2 创建数据库和表并写入数据
节点1:
MariaDB[(none)]> CREATE DATABASE `ngoss_dim` /*!40100 DEFAULT CHARACTER SET utf8*/;
QueryOK, 1 row affected (0.02 sec)
MariaDB[(none)]> use ngoss_dim;
Databasechanged
MariaDB[ngoss_dim]> create table tb_t1(id int,name varchar(64)) engine=innodb;
QueryOK, 0 rows affected (0.02 sec)
MariaDB[ngoss_dim]> insert into tb_t1values(1,'name1'),(2,'name2'),(3,'name3'),(4,'name4'),(5,'name5');
QueryOK, 5 rows affected (0.01 sec)
Records:5 Duplicates: 0 Warnings: 0
MariaDB[ngoss_dim]> select * from tb_t1;
+------+-------+
|id | name |
+------+-------+
| 1 | name1 |
| 2 | name2 |
| 3 | name3 |
| 4 | name4 |
| 5 | name5 |
+------+-------+
5 rowsin set (0.00 sec)
在另外两个节点节点1和节点2上查看
[root@host02bin]# ./mysql -h localhost -uroot -pmysql
Welcometo the MariaDB monitor. Commands endwith ; or \g.
YourMariaDB connection id is 4
Serverversion: 10.0.31-MariaDB-wsrep MariaDB Server, wsrep_25.19.rc3fc46e
Copyright(c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB[(none)]> show databases;
+--------------------+
|Database |
+--------------------+
|information_schema |
|mysql |
|ngoss_dim |
|performance_schema |
|test |
+--------------------+
5 rowsin set (0.00 sec)
MariaDB[(none)]> use ngoss_dim;
Readingtable information for completion of table and column names
You canturn off this feature to get a quicker startup with -A
Databasechanged
MariaDB[ngoss_dim]> show tables;
+---------------------+
|Tables_in_ngoss_dim |
+---------------------+
|tb_t1 |
+---------------------+
1 row inset (0.00 sec)
MariaDB[ngoss_dim]> select * from tb_t1;
+------+-------+
|id | name |
+------+-------+
| 1 | name1 |
| 2 | name2 |
| 3 | name3 |
| 4 | name4 |
| 5 | name5 |
+------+-------+
5 rowsin set (0.00 sec)
MariaDB[ngoss_dim]>
节点3:
[root@hxl03bin]# ./mysql -h localhost -uroot -pmysql
Welcometo the MariaDB monitor. Commands endwith ; or \g.
YourMariaDB connection id is 5
Serverversion: 10.0.31-MariaDB-wsrep MariaDB Server, wsrep_25.19.rc3fc46e
Copyright(c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB[(none)]> show databases;
+--------------------+
|Database |
+--------------------+
|information_schema |
|mysql |
|ngoss_dim |
|performance_schema |
|test |
+--------------------+
5 rowsin set (0.00 sec)
MariaDB[(none)]> use ngoss_dim;
Readingtable information for completion of table and column names
You canturn off this feature to get a quicker startup with -A
Databasechanged
MariaDB[ngoss_dim]> show tables;
+---------------------+
| Tables_in_ngoss_dim|
+---------------------+
|tb_t1 |
+---------------------+
1 row inset (0.00 sec)
MariaDB[ngoss_dim]> select * from tb_t1;
+------+-------+
|id | name |
+------+-------+
| 1 | name1 |
| 2 | name2 |
| 3 | name3 |
| 4 | name4 |
| 5 | name5 |
+------+-------+
5 rowsin set (0.00 sec)
1.11 安装nginx
1.11.1 下载
下载地址:
https://nginx.org/en/download.html
我这里下载的是
nginx-1.12.0.tar.gz
1.11.2 安装所需环境
gcc 安装
yuminstall gcc-c++
PCRE pcre-devel安装
yuminstall -y pcre pcre-devel
zlib 安装
yuminstall -y zlib zlib-devel
OpenSSL 安装
yuminstall -y openssl openssl-devel
1.11.3 解压安装
[root@hxl03opt]# tar -zxvf nginx-1.12.0.tar.gz
[root@hxl03opt]# mv nginx-1.12.0 nginx112
[root@hxl03opt]# cd nginx112
我们这里使用的是tcp第四次协议,需要安装stream模块
[root@hxl03nginx112]# ./configure --with-stream --with-stream_ssl_module
[root@hxl03nginx112]# make
[root@hxl03nginx112]# make install
1.11.4 启动、停止nginx
cd/usr/local/nginx/sbin/
./nginx
./nginx-s stop
./nginx-s quit
./nginx-s reload
./nginx-s quit:此方式停止步骤是待nginx进程处理任务完毕进行停止。
./nginx-s stop:此方式相当于先查出nginx进程id再使用kill命令强制杀掉进程。
查询nginx进程：
psaux|grep nginx
在ie栏输入服务器ip地址,可以查看到nginx已经安装成功
1.11.5 重启nginx
1.先停止再启动（推荐）：
对 nginx 进行重启相当于先停止再启动，即先执行停止命令再执行启动命令。如下：
./nginx-s quit
./nginx
2.重新加载配置文件：
当 ngin x的配置文件 nginx.conf 修改后，要想让配置生效需要重启 nginx，使用-s reload不用先停止 ngin x再启动 nginx 即可将配置信息在 nginx 中生效，如下：
./nginx-s reload
1.11.6 配置nginx
配置文件如下
[root@hxl03conf]# more nginx.conf
worker_processesauto;
##error_loglogs/error.stream.log info;
events {
worker_connections 1024;
}
stream {
log_format proxy '$remote_addr[$time_local] '
'$protocol $status $bytes_sent$bytes_received '
'$session_time"$upstream_addr" '
'"$upstream_bytes_sent""$upstream_bytes_received" "$upstream_connect_time"';
access_log/usr/local/nginx/logs/tcp-access.log proxy ;
open_log_file_cache off;
upstream galera {
hash $remote_addr consistent;
server 192.168.56.11:3306;
server 192.168.56.12:3306;
server 192.168.56.13:3306;
}
server {
listen 23306;
proxy_connect_timeout 1s;
proxy_timeout 3s;
proxy_pass galera;
}
}
1.11.7 验证
通过nginx登录集群的方法:
./mysql -h nginx服务器ip –u用户 –p密码 –P端口号
Host01登录：
[root@host01bin]# ./mysql -h 192.168.56.13 -uroot -pmysql -P23306
Welcometo the MariaDB monitor. Commands endwith ; or \g.
YourMariaDB connection id is 4
Serverversion: 10.0.31-MariaDB-wsrep MariaDB Server, wsrep_25.19.rc3fc46e
Copyright(c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB[(none)]>
Host02登录：
[root@host02bin]# ./mysql -h 192.168.56.13 -uroot -pmysql -P23306
Welcometo the MariaDB monitor. Commands endwith ; or \g.
YourMariaDB connection id is 5
Serverversion: 10.0.31-MariaDB-wsrep MariaDB Server, wsrep_25.19.rc3fc46e
Copyright(c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB[(none)]>
Host03登录：
[root@hxl03bin]# ./mysql -h 192.168.56.13 -uroot -pmysql -P23306
Welcometo the MariaDB monitor. Commands endwith ; or \g.
YourMariaDB connection id is 4
Serverversion: 10.0.31-MariaDB-wsrep MariaDB Server, wsrep_25.19.rc3fc46e
Copyright(c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB[(none)]>