Mysql Galera 集群版的安装部署方法


下文内容主要给大家带来Mysql Galera 集群版的安装部署方法,所讲到的知识,与书籍不同,都是开发云专业技术人员在与用户接触过程中,总结出来的,具有一定的经验分享价值,希望给广大读者带来帮助。Galera 是一个Mysql(Mariadb,Percona)同步多主集群软件,本文主要讲解Galera cluster安装,MySQL Galera集群版的mysql是经过codeship打了wsrep补丁的,不是普通的mysql版本。安装时采用轻量级pssh来进行批量安装。
概况如下:机器IP 结点名192.168.1.42 Es-Search-A
192.168.1.43 Es-Search-B
192.168.1.44 Es-Search-COS版本:CentOS 7
mysql版本:mysql-5.6.43Galera cluster安装包:galera-3-25.3.26-2.el7.x86_64.rpm
mysql-wsrep-5.6-5.6.43-25.25.el7.x86_64.rpm
mysql-wsrep-client-5.6-5.6.43-25.25.el7.x86_64.rpm
mysql-wsrep-devel-5.6-5.6.43-25.25.el7.x86_64.rpm
mysql-wsrep-server-5.6-5.6.43-25.25.el7.x86_64.rpm
mysql-wsrep-shared-5.6-5.6.43-25.25.el7.x86_64.rpm
mysql-wsrep-test-5.6-5.6.43-25.25.el7.x86_64.rpmGalera cluster下载url:
http://galeracluster.com/downloads/注:这里我下载的都是打过wsrep补丁的mysql安装包,不用独立安装mysql数据库,直接安装这些包之后数据库就安装好了。一、准备工作
在每台机器/下创建/data目录,用于mysql数据文件目录。
[elasticsearch@Es-Search-A ~]$ cat hosts.txt
192.168.1.42
192.168.1.43
192.168.1.44
[elasticsearch@Es-Search-A ~]$ pssh -h hosts.txt “sudo mkdir /data/”
[1] 14:37:15 [SUCCESS] 192.168.1.42
[2] 14:37:15 [SUCCESS] 192.168.1.44
[3] 14:37:15 [SUCCESS] 192.168.1.43将mysql源码文件复制到每台机器安装依赖包
[elasticsearch@Es-Search-A ~]$ pssh -h hosts.txt “sudo yum install gcc gcc-c++ perl-devel bison bison-devel ncurses-devel cmake autoconf -y”
[1] 14:51:37 [SUCCESS] 192.16开发云主机域名8.1.42
[2] 14:51:39 [SUCCESS] 192.168.1.44
[3] 14:51:41 [SUCCESS] 192.168.1.43创建用户:[elasticsearch@Es-Search-A ~]$ pssh -h hosts.txt “sudo groupadd mysql”
[1] 14:58:03 [SUCCESS] 192.168.1.43
[2] 14:58:03 [SUCCESS] 192.168.1.44
[3] 14:58:03 [SUCCESS] 192.168.1.42
[elasticsearch@Es-Search-A ~]$ pssh -h hosts.txt “sudo useradd -g mysql mysql -d /home/mysql -s /sbin/nologin”
[1] 14:58:16 [SUCCESS] 192.168.1.42
[2] 14:58:16 [SUCCESS] 192.168.1.43
[3] 14:58:16 [SUCCESS] 192.168.1.44[elasticsearch@Es-Search-A ~]$ pssh -h hosts.txt “sudo mkdir /data/mysql/data -p”
[1] 14:59:00 [SUCCESS] 192.168.1.42
[2] 14:59:00 [SUCCESS] 192.168.1.43
[3] 14:59:00 [SUCCESS] 192.168.1.44
[elasticsearch@Es-Search-A ~]$ pssh -h hosts.txt “sudo chown -R mysql:mysql /data/mysql/data/”
[1] 15:01:13 [SUCCESS] 192.168.1.42
[2] 15:01:13 [SUCCESS] 192.168.1.43
[3] 15:01:13 [SUCCESS] 192.168.1.44将安装包复制到其他两台机器上:
[elasticsearch@Es-Search-A ~]$ pscp.pssh -h hosts.txt -t 0 mysql-wsrep- /home/elasticsearch/
[1] 10:07:35 [SUCCESS] 192.168.1.42
[2] 10:07:38 [SUCCESS] 192.168.1.44
[3] 10:07:38 [SUCCESS] 192.168.1.43
[elasticsearch@Es-Search-A ~]$ pscp.pssh -h hosts.txt -t 0 galera-3-25.3.26-2.el7.x86_64.rpm /home/elasticsearch/
[1] 10:08:01 [SUCCESS] 192.168.1.42
[2] 10:08:02 [SUCCESS] 192.168.1.43
[3] 10:08:03 [SUCCESS] 192.168.1.44
二、安装
批量安装:
[elasticsearch@Es-Search-A ~]$ pssh -h hosts.txt -t 0 “sudo rpm -ivh mysql-wsrep-

[1] 10:09:08 [SUCCESS] 192.168.1.42
[2] 10:09:09 [SUCCESS] 192.168.1.43
[3] 10:09:10 [SUCCESS] 192.168.1.44[elasticsearch@Es-Search-A ~]$ pssh -h hosts.txt -t 0 -P “sudo rpm -ivh galera*”
[1] 10:10:24 [SUCCESS] 192.168.1.44
[2] 10:10:25 [SUCCESS] 192.168.1.43
[3] 10:10:28 [SUCCESS] 192.168.1.42检查是否成功安装:
[elasticsearch@Es-Search-A ~]$ pssh -h hosts.txt -t 0 -P “sudo rpm -qa| grep mysql-wsrep”
192.168.1.42: mysql-wsrep-test-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-server-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-devel-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-client-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-shared-5.6-5.6.43-25.25.el7.x86_64
[1] 15:28:04 [SUCCESS] 192.168.1.42
192.168.1.43: mysql-wsrep-client-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-shared-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-server-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-test-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-devel-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-5.6-5.6.43-25.25.el7.x86_64
[2] 15:28:04 [SUCCESS] 192.168.1.43
192.168.1.44: mysql-wsrep-server-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-devel-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-client-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-shared-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-test-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-5.6-5.6.43-25.25.el7.x86_64
[3] 15:28:04 [SUCCESS] 192.168.1.44
[elasticsearch@Es-Search-A ~]$ pssh -h hosts.txt -t 0 -P “sudo rpm -qa| grep galera”
192.168.1.42: galera-3-25.3.26-2.el7.x86_64
[1] 10:11:25 [SUCCESS] 192.168.1.42
192.168.1.43: galera-3-25.3.26-2.el7.x86_64
[2] 10:11:25 [SUCCESS] 192.168.1.43
192.168.1.44: galera-3-25.3.26-2.el7.x86_64
[3] 10:11:25 [SUCCESS] 192.168.1.44安装结束。三、mysql配置文件第一台:
[elasticsearch@Es-Search-A ~]$ sudo vi /etc/my.cnf#For advice on how to change settings please see
#http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html[mysqld]#Remove leading # and set to the amount of RAM for the most important data
#cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
#innodb_buffer_pool_size = 128M#Remove leading # to turn on a very important data integrity option: logging
#changes to the binary log between backups.
#log_bin#Remove leading # to set options mainly useful for reporting servers.
#The server defaults are faster for transactions and fast SELECTs.
#Adjust sizes as needed, experiment to find the optimal values.
#join_buffer_size = 128M
#sort_buffer_size = 2M
#read_rnd_buffer_size = 2M
#datadir=/usr/local/mysql
#socket=/usr/local/mysql/mysql.sock#Disabling symbolic-links is recommended to prevent assorted security risks
#symbolic-links=0#log-error=/data/mysql/log/error.log
#pid-file=/usr/local/mysql/mysqld.pid
!includedir /etc/my.cnf.d/[elasticsearch@Es-Search-A ~]$ sudo vi /etc/my.cnf.d/wsrep.cnf[mysqld]
datadir=/var/lib/mysql
#basedir=/usr/share/mysql
tmpdir=/tmp ###临时目录
socket=/var/lib/mysql/mysql.sock
#Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#Settings user and group are ignored when systemd is used.
#If you need to run mysqld under a different user or group,
#customize your systemd unit file for mariadb according to the
#instructions in http://fedoraproject.org/wiki/Systemd
character-set-server=utf8 #字符集utf-8
collation-server=utf8_general_ci
skip-name-resolve ##跳过主机名
user=mysql
port=3306 ##端口
binlog_rows_query_log_events=OFF ###这个选项应该关掉,否则会产生内部错误
innodb_buffer_pool_size = 4096M
max_allowed_packet = 500M
max_connections = 600
log-error=/data/mysql/log/error.log
pid-file=/var/lib/mysql/mysql.pidskip-grant-tables ##跳过授权表
binlog_format=ROW
log-bin=mysql-bin#####################################################################################
character-set-server=utf8 #字符集utf-8
collation-server=utf8_general_ci
binlog_rows_query_log_events=OFF ###这个选项应该关掉,否则会产生内部错误wsrep_on=on
innodb_autoinc_lock_mode=2
default_storage_engine=innodb
wsrep_node_name = Es-Search-A
wsrep_node_address=’192.168.1.42′
wsrep_provider = /usr/lib64/galera-3/libgalera_smm.so
wsrep_cluster_address=”gcomm://”
wsrep_cluster_name=’Galera_cluster’
wsrep_sst_method = rsync #xtrabackup
skip-grant-tables ##跳过授权表
wsrep_sst_auth=galera:galera
binlog_format=ROW
log-bin=mysql-bin
server-id=42
log-slave-updates=1
#######################################################################################[mysqld_safe]
log-error=/data/mysql/data/error.log
#pid-file=/usr/local/mysql/mysql.pid#include all files from the config directory#!includedir /etc/my.cnf.d/配置文件各项配置意义:wsrep_sst_method
:state_snapshot_transfer(SST)使用的传输方法,可用方法有mysqldump、rsync和xtrabackup,前两者在传输时都需要对Donor加全局只读锁(
FLUSH TABLES WITH READ LOCK),xtrabackup则不需要(它使用percona自己提供的backup
lock)。强烈建议采用xtrabackup将配置文件复制到其他机器
[elasticsearch@Es-Search-A ~]$ pscp.pssh -h hosts.txt -t 0 /etc/my.cnf /home/elasticsearch/
[1] 11:15:22 [SUCCESS] 192.168.1.42
[2] 11:15:22 [SUCCESS] 192.168.1.43
[3] 11:15:22 [SUCCESS] 192.168.1.44
[elasticsearch@Es-Search-A ~]$ pssh -h hosts.txt -t 0 -P “sudo cp my.cnf /etc/”
[1] 11:15:59 [SUCCESS] 192.168.1.42
[2] 11:15:59 [SUCCESS] 192.168.1.43
[3] 11:15:59 [SUCCESS] 192.168.1.44初始化第一台mysql
[elasticsearch@Es-Search-A ~]$ sudo mysqld –initialize –user=mysql
2019-06-27 15:10:11 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
2019-06-27 15:10:11 0 [Note] mysqld (mysqld 5.6.43-log) starting as process 5321 …[elasticsearch@Es-Search-A ~]$ ll /data/mysql/data
total 241672
-rw-rw—- 1 mysql mysql 134219048 Jun 26 17:38 galera.cache
-rw-rw—- 1 mysql mysql 113 Jun 26 17:38 grastate.dat
-rw-rw—- 1 mysql mysql 12582912 Jun 26 17:38 ibdata1
-rw-rw—- 1 mysql mysql 50331648 Jun 26 17:38 ib_logfile0
-rw-rw—- 1 mysql mysql 50331648 Jun 20 17:53 ib_logfile1
drwx—— 2 mysql mysql 6 Jun 20 18:01 mysql
-rw-rw—- 1 mysql mysql 0 Jun 20 17:53 mysql-bin.index
drwx—— 2 mysql mysql 6 Jun 20 18:01 test启动第一台mysql
sudo service mysql start –wsrep-new-cluster或使用这种方式启动也可以
[elasticsearch@Es-Search-A ~]$ sudo mysqld –wsrep-new-cluster –user=mysql &
[1] 16007
[elasticsearch@Es-Search-A ~]$ 2019-06-20 17:11:56 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
2019-06-20 17:11:56 0 [Note] mysqld (mysqld 5.6.43-log) starting as process 16008 …在另一个终端
[elasticsearch@Es-Search-A ~]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 3
Server version: 5.6.43-log MySQL Community Server (GPL), wsrep_25.25Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input statement.mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| test |
+——————–+
4 rows in set (0.00 sec)查看参数:
mysql> show status like ‘wsrep_%’;
+——————————+———————————————–+
| Variable_name | Value |
+——————————+———————————————–+
| wsrep_local_state_uuid | 73aa66b8-933b-11e9-9578-9a7df8c24dcc |
| wsrep_protocol_version | 9 |
| 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 | 2 |
| wsrep_received_bytes | 147 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_max | 2 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0.500000 |
| 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_open_transactions | 0 |
| wsrep_open_connections | 0 |
| wsrep_incoming_addresses | 192.168.1.42:3306 |
| wsrep_cluster_weight | 1 |
| wsrep_desync_count | 0 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 3.204e-06/6.0466e-06/1.0783e-05/2.89701e-06/5 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | 73a64b76-933b-11e9-99cc-337e6bf5a0f1 |
| wsrep_cluster_conf_id | 1 |
| wsrep_cluster_size | 1 |
| wsrep_cluster_state_uuid | 73aa66b8-933b-11e9-9578-9a7df8c24dcc |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy |
| wsrep_provider_version | 3.26(rff05089) |
| wsrep_ready | ON |
+——————————+———————————————–+
60 rows in set (0.00 sec)
修改密码
mysql> update mysql.user set password=PASSWORD(‘123′) where user=’root’;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0mysql> flush privileges;
Query OK, 0 rows affected (0.17 sec)创建复制用户
mysql> grant all on . to ‘galera’@’%’ identified by ‘galera’;
Query OK, 0 rows affected (0.12 sec)mysql> flush privileges;
Query OK, 0 rows affected (0.28 sec)第一个节点启动成功,再配置启动其他节点。##第二个节点配置:
server-id=43
wsrep_node_name = Es-Search-B
wsrep_node_address=’192.168.1.43′
wsrep_cluster_address=”gcomm://192.168.1.42,192.168.1.44″
与节点1区别就这三个参数启动第二个节点:
[elasticsearch@Es-Search-B ~]$ sudo service mysql start
Starting MySQL…………………… SUCCESS!##第三个节点配置:
server-id=44
wsrep_node_name = Es-Search-C
wsrep_node_address=’192.168.1.44′
wsrep_cluster_address=”gcomm://192.168.1.42,192.168.1.43″启动第三个节点:
[elasticsearch@Es-Search-C data]$ sudo service mysql start
Starting MySQL……………………… SUCCESS!查看集群启动情况:
mysql> SHOW GLOBAL STATUS WHERE Variable_name IN(‘wsrep_ready’,’wsrep_cluster_size’,’wsrep_cluster_status’,’wsrep_connected’);
+———————-+———+
| Variable_name | Value |
+———————-+———+
| wsrep_cluster_size | 3 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_ready | ON |
+———————-+———+
4 rows in set (0.00 sec)可以看到cluster有3台,尝试在其中任一台上创建一个数据库,则在另外节点可以看到也创建了同样的库。mysql> create database mydb;
Query OK, 1 row affected (0.16 sec)mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+——————–+
5 rows in set (0.00 sec)因为刚才第一台机器用来创建集群,启动时跳过了授权表,然后连接到库中修改了密码,创建了用户,之后需要注释掉跳过权限表的参数。安装配置完结。这里仅是使用rpm包安装,basedir与datadir不能很灵活定义,还是比较习惯源码安装。之后会再探索直接在mysql上打补丁,然后组成Galera cluster。对于以上关于Mysql Galera 集群版的安装部署方法,如果大家还有更多需要了解的可以持续关注我们开发云的行业推新,如需获取专业解答,可在官网联系售前售后的,希望该文章可给大家带来一定的知识更新。

相关推荐: mysql导入txt文本数据的方法

这篇文章主要介绍mysql导入txt文本数据开发云主机域名的方法,文中介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们一定要看完!mysql导入txt文本数据的方法:首先创建数据库和表;然后创建表,将数据写入一个【pet.txt】文本中;最后使用命令实现导…

免责声明:本站发布的图片视频文字,以转载和分享为主,文章观点不代表本站立场,本站不承担相关法律责任;如果涉及侵权请联系邮箱:360163164@qq.com举报,并提供相关证据,经查实将立刻删除涉嫌侵权内容。

(0)
打赏 微信扫一扫 微信扫一扫
上一篇 06/07 13:24
下一篇 06/07 13:24

相关推荐