Shulou(Shulou.com)06/01 Report--

The following content mainly brings you the installation and deployment methods of Mysql Galera Cluster Edition. Unlike books, the knowledge mentioned is summarized by professional and technical personnel in the process of contact with users, and has a certain value of experience sharing. I hope to bring help to the majority of readers.

Galera is a Mysql (Mariadb,Percona) synchronous multi-master cluster software, this article focuses on Galera cluster installation, MySQL Galera cluster version of mysql is patched by codeship wsrep, not the ordinary mysql version. Lightweight pssh is used for bulk installation.

The overview is as follows:

Machine IP node roll call

192.168.1.42 Es-Search-A

192.168.1.43 Es-Search-B

192.168.1.44 Es-Search-C

OS version: CentOS 7

Mysql version: mysql-5.6.43

Galera cluster installation package:

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

Download url for Galera cluster:

Http://galeracluster.com/downloads/

Note: what I download here are all wsrep patched mysql installation packages. There is no need to install the mysql database independently. After installing these packages directly, the database is installed.

I. preparatory work

Create a / data directory under each machine / for the mysql data file directory.

[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

Copy the mysql source files to each machine

Install dependency packages

[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

Create a user:

[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

Copy the installation package to the other two machines:

[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

II. Installation

Batch installation:

[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

Check that the installation was successful:

[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

The installation is complete.

III. Mysql configuration file

Number one:

[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 # temporary directory

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 # character set utf-8

Collation-server=utf8_general_ci

Skip-name-resolve # # Skip hostname

User=mysql

Port=3306 # # Port

Binlog_rows_query_log_events=OFF # # this option should be turned off, otherwise an internal error will occur

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 # # skip the authorization table

Binlog_format=ROW

Log-bin=mysql-bin

#

Character-set-server=utf8 # character set utf-8

Collation-server=utf8_general_ci

Binlog_rows_query_log_events=OFF # # this option should be turned off, otherwise an internal error will occur

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 # # skip the authorization table

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/

Configuration meaning of each configuration file:

Wsrep_provider: specify the path to the Galera library, the name of the wsrep_cluster_name:Galera cluster, the address of each node in the wsrep_cluster_address:Galera cluster. Address uses group communication protocol gcomm:// (group communication) wsrep_node_name: name of this node in Galera cluster wsrep_node_address: communication address of this node in Galera cluster

Wsrep_sst_method

The transmission methods used by state_snapshot_transfer (SST) include mysqldump, rsync and xtrabackup, both of which need to add a global read-only lock to Donor (

FLUSH TABLES WITH READ LOCK), but not for xtrabackup (it uses backup provided by percona itself

Lock). Xtrabackup is strongly recommended

Wsrep_sst_auth: authentication credentials to be used when transferring SST. Format: "user: password" pxc_strict_mode: whether to restrict PXC to enable the function in the trial phase. ENFORCING is the default value, which means that the format of binlog_format: binary log is not enabled. Galera only supports binary log default _ storage_engine in row format: specifies the default storage engine. Galera's replication function only supports InnoDBinnodb_autoinc_lock_mode: it can only be set to 2. If it is set to 0 or 1, the deadlock problem cannot be handled correctly.

Copy the configuration file to another machine

[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

Initialize the first 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

Start the first mysql

Sudo service mysql start-wsrep-new-cluster

Or you can start it this way.

[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.

At the other terminal.

[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)

View parameters:

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)

Modify the password

Mysql > update mysql.user set password=PASSWORD ('123') where user='root'

Query OK, 4 rows affected (0.01sec)

Rows matched: 4 Changed: 4 Warnings: 0

Mysql > flush privileges

Query OK, 0 rows affected (0.17 sec)

Create replication user

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)

The first node starts successfully, and then configure to start the other nodes.

# # second node configuration:

Server-id=43

Wsrep_node_name = Es-Search-B

Wsrep_node_address='192.168.1.43'

Wsrep_cluster_address= "gcomm://192.168.1.42192.168.1.44"

There are only three parameters that are different from node 1.

Start the second node:

[elasticsearch@Es-Search-B ~] $sudo service mysql start

Starting MySQL.... SUCCESS!

# # third node configuration:

Server-id=44

Wsrep_node_name = Es-Search-C

Wsrep_node_address='192.168.1.44'

Wsrep_cluster_address= "gcomm://192.168.1.42192.168.1.43"

Start the third node:

[elasticsearch@Es-Search-C data] $sudo service mysql start

Starting MySQL.. SUCCESS!

Check the cluster startup:

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)

You can see that there are three cluster, try to create a database on any one of them, and you can see that the same library has been created on the other node.

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)

Because the first machine was used to create a cluster, the authorization table was skipped at startup, and then connected to the library to change the password and create a user, and then you need to comment out the parameters of the skip permission table.

The installation configuration is complete. Here only use rpm package installation, basedir and datadir can not be very flexible definition, or more accustomed to source code installation. After that, I will explore patching directly on mysql and then composing Galera cluster.

For the above installation and deployment methods of Mysql Galera Cluster Edition, if you need to know more, you can continue to pay attention to the innovation of our industry. If you need professional solutions, you can contact the pre-sales and after-sales on the official website. I hope this article can bring you some knowledge updates.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.