Mysql Galera 集群版的安装布置方法
发布时间：2023-08-12 14:52:18 所属栏目：MySql教程 来源：
导读：主要给大家带来Mysql Galera 集群版的安装部署方法，所讲到的知识，与书籍不同，都是亿速云专业技术人员在与用户接触过程中，总结出来的，具有一定的经验分享价值，希望给广大读者带来帮助。
Galera 是一个Mysql
Galera 是一个Mysql
|
主要给大家带来Mysql Galera 集群版的安装部署方法，所讲到的知识，与书籍不同，都是亿速云专业技术人员在与用户接触过程中，总结出来的，具有一定的经验分享价值，希望给广大读者带来帮助。 Galera 是一个Mysql(Mariadb，Percona)同步多主集群软件，本文主要讲解Galera cluster安装，MySQL Galera集群版的mysql是经过codeship打了wsrep补丁的，不是普通的mysql版本。安装时采用轻量级pssh来进行批量安装。 Mysql Galera 集群版的安装部署方法 概况如下： 机器IP 结点名 192.168.1.42 Es-Search-A 192.168.1.43 Es-Search-B 192.168.1.44 Es-Search-C OS版本：CentOS 7 mysql版本：mysql-5.6.43 Galera 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.rpm Galera 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.168.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.pid skip-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_provider：指定Galera库的路径 wsrep_cluster_name：Galera集群的名称 wsrep_cluster_address：Galera集群中各节点地址。地址使用组通信协议gcomm://(group communication) wsrep_node_name：本节点在Galera集群中的名称 wsrep_node_address：本节点在Galera集群中的通信地址 wsrep_sst_method ：state_snapshot_transfer(SST)使用的传输方法，可用方法有mysqldump、rsync和xtrabackup，前两者在传输时都需要对Donor加全局只读锁( FLUSH TABLES WITH READ LOCK)，xtrabackup则不需要(它使用percona自己提供的backup lock)。强烈建议采用xtrabackup wsrep_sst_auth：在SST传输时需要用到的认证凭据，格式为："用户：密码" pxc_strict_mode：是否限制PXC启用正在试用阶段的功能，ENFORCING是默认值，表示不启用 binlog_format：二进制日志的格式。Galera只支持row格式的二进制日志 default_storage_engine：指定默认存储引擎。Galera的复制功能只支持InnoDB innodb_autoinc_lock_mode：只能设置为2，设置为0或1时会无法正确处理死锁问题 将配置文件复制到其他机器 [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.25 Copyright (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 <info@codership.com> | | 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: 0 mysql> 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。 （编辑：聊城站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
推荐文章
站长推荐
