Multi-instance compilation installation for Percona-xtradb-cluster

First, the environment

CentOS release 6.9Percona-XtraDB-Cluster-5.7.19-29.22.tar.gzboost_1_59_0.tar.gzpercona-xtrabackup-24-2.4.7-1.el6.x86_64.rpm

node	MySQL Service port	Gmcast.listen Port
node01:192.168.1.210:6600	6600	6630
node02:192.168.1.210:6602	6602	6632
Node03:gardb	No	5567

Second, download the dependency package

yum install -y git scons gcc gcc-c++ openssl check cmake bison boost-devel asio-devel libaio-devel ncurses-devel readline-devel pam-devel yum -y install  libtool ncurses-devel libgcrypt-devel  libev-devel git scons  gcc  gcc-c++  openssl  check  cmake  bison boost-devel  asio-devel libaio-devel  ncurses-devel  readline-devel  pam-devel socat libaio  

Third, compile and install: 3.1 Compiling and installing Percona-xtrabackup

cd /usr/local/percona-xtrabackup-2.4.5/cmake ./ -DBUILD_CONFIG=xtrabackup_release -DWITH_MAN_PAGES=OFF -DDOWNLOAD_BOOST=1 -DWITH_BOOST="/usr/local/boost" make -j 4 && make install

3.2 Compiling and installing percona-xtradb-cluster-5.7.19-29.22

It is best to uninstall the local MySQL database before compiling the installation.

 rpm-qa | grep mysql （注意大小写，如果mysql不行就换MySQL） yum -y remove mysql的包名

Data and/ETC/MY.CNF in/var/lib/mysql will not be removed after uninstallation, and will be removed manually if determined to be useless.

 rm -f /etc/my.cnf rm -rf /var/lib/mysql

Create a MySQL user name and folder

mkdir /data/6600 -pmkdir /data/6602 -pmkdir /data/{6600,6602}/{binlog,slow} -puseradd mysqlchown -R mysql:mysql /data/

Compiling garbd,libgalera_smm.so

 cd /usr/local/mysql cd "percona-xtradb-cluster-galera"

Get Revno value

cat GALERA-REVISION#revno="GALERA-REVISION"scons -j4 psi=1 --config=force  revno="8678538"  boost_pool=0 libgalera_smm.soscons -j4 --config=force revno="8678538" garb/garbd

Create the PXC installation directory

mkdir -p /usr/local/mysql/{bin,lib}chown -R mysql:mysql /usr/local/mysql

Copy the compiled results file to the PXC installation directory

 cp garb/garbd /usr/local/mysql/bin cp libgalera_smm.so /usr/local/mysql/lib

Check that the copied files are normal

 ls -l /usr/local/mysql/bin/garbd ls -l /usr/local/mysql/lib/libgalera_smm.so

-dmysql_server_suffix Value Acquisition

cd /usr/local/Percona-XtraDB-Cluster-5.7.19-29.22WSREP_VERSION="$(grep WSREP_INTERFACE_VERSION wsrep/wsrep_api.h | cut -d ‘"‘ -f2).$(grep ‘SET(WSREP_PATCH_VERSION‘  "cmake/wsrep.cmake" | cut -d ‘"‘ -f2)"echo $WSREP_VERSION

-dcompilation_comment Value Acquisition

cd /usr/local/Percona-XtraDB-Cluster-5.7.19-29.22source VERSIONMYSQL_VERSION="$MYSQL_VERSION_MAJOR.$MYSQL_VERSION_MINOR.$MYSQL_VERSION_PATCH"echo $MYSQL_VERSIONREVISION="$(cd "$SOURCEDIR"; grep ‘^short: ‘ Docs/INFO_SRC |sed -e ‘s/short: //‘)"echo "Percona XtraDB Cluster binary (GPL) $MYSQL_VERSION-$WSREP_VERSION Revision $REVISION"

Compiling percona-xtradb-cluster-5.7.16-27.19

cd /usr/local/mysqlcmake ./-DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=RelWithDebInfo -DWITH_EMBEDDED_SERVER=OFF -DFEATURE_SET=community -DENABLE_DTRACE=OFF -DWITH_SSL=system -DWITH_ZLIB=system -DCMAKE_INSTALL_PREFIX="/usr/local/mysql" -DMYSQL_DATADIR="/usr/local/mysql/data" -DMYSQL_SERVER_SUFFIX="29.22" -DWITH_INNODB_DISALLOW_WRITES=ON -DWITH_WSREP=ON -DWITH_UNIT_TESTS=0 -DWITH_READLINE=system -DWITHOUT_TOKUDB=ON -DWITHOUT_ROCKSDB=ON -DCOMPILATION_COMMENT="Percona XtraDB Cluster binary (GPL) 5.7.19-29.22 Revision c10027a" -DWITH_PAM=ON -DWITH_INNODB_MEMCACHED=ON -DDOWNLOAD_BOOST=1 -DWITH_BOOST="/usr/local/boost" -DWITH_SCALABILITY_METRICS=ONmake && make install

3.3 One-click installation script

#!/bin/shhome_dir=/home/admin/toolsintall_dir=/usr/local/mysqldata_dir=/usr/local/mysql/dataexport PATH=/usr/ Local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/binyum-y Install Libtool Ncurses-devel Libgcrypt-devel libev-devel git scons gcc gcc-c++ OpenSSL check cmake bison boost-devel asio-devel libaio-devel N  Curses-devel readline-devel pam-devel socat libaio automake autoconf redhat-lsb check-devel Curl Curl-devel Xinetdyum -y install libnl-devel openssl-devel libnfnetlink-devel ipvsadm popt-devel libnfnetlink kernel-devel popt-static Iptrafy Um-y Install numactl Libev perl-dbd-mysql perl-time-hiresmkdir-p/usr/local/boosttar XF ${home_dir}/boost_1_59_0.tar.g Z-c/usr/local/boost### #安装xtrabackup我用的RPM包rpm-IVH ${home_dir}/percona-xtrabackup-24-2.4.7-1.el6.x86_64.rpmtar XF ${home_dir}/percona-xtradb-cluster-5.7.19-29.22.tar.gzmkdir-p ${intall_dir}/{binlog,slow}mkdir-p ${Intall_Dir}/{ Bin,lib}useradd mysqlchown-r mysql:mysql ${INTALL_DIR}CD ${home_dir}/peRCONA-XTRADB-CLUSTER-5.7.19-29.22CD "Percona-xtradb-cluster-galera" revno= ' cat galera-revision ' scons-j4 psi=1-- Config=force revno= "$Revno" boost_pool=0 libgalera_smm.soscons-j4--config=force revno= "$Revno" GARB/GARBDCP garb/ GARBD ${INTALL_DIR}/BINCP libgalera_smm.so ${INTALL_DIR}/LIBCD ${home_dir}/ Percona-xtradb-cluster-5.7.19-29.22wsrep_version= "$ (grep wsrep_interface_version wsrep/wsrep_api.h | cut-d '" '-f2). $ (grep ' SET (wsrep_patch_version ' "Cmake/wsrep.cmake" | cut-d ' "'-f2)" source./versionmysql_version= "$MYSQL _version_ MAJOR. $MYSQL _version_minor. $MYSQL _version_patch "revision=" $ (cd "$SOURCEDIR"; grep ' ^short: ' docs/info_src |sed-e ' s/ Short://') "Dct= ' echo" Percona XtraDB Cluster binary (GPL) $MYSQL _version-$WSREP _version Revision $REVISION "' CD ${home_ Dir}/percona-xtradb-cluster-5.7.19-29.22cmake./-dbuild_config=mysql_release-dcmake_build_type=relwithdebinfo- Dwith_embedded_server=off-dfeature_set=community-denable_dtrace=off-dwith_ssl=system-dwith_zlib=system-dcmake_install_prefix= "$Intall _dir"-dmysql_datadir= "$DATA _dir"-dmysql_server_suffix= "$WSREP _version"-dwith_innodb _disallow_writes=on-dwith_wsrep=on-dwith_unit_tests=0-dwith_readline=system-dwithout_tokudb=on-dwithout_ Rocksdb=on-dcompilation_comment= "$DCT"-dwith_pam=on-dwith_innodb_memcached=on-ddownload_boost=1-dwith_boost= "/ Usr/local/boost "-dwith_scalability_metrics=onmake && make Install

A detailed explanation of the parameters can be seen in my previous blog post

Iv. compiling the configuration file

4.1 Compiling the configuration file
First Instance: 6600

Vim/data/6600/my.cnf[client]socket=/data/6600/mysql.sockport = 6600default-character-set = utf8[mysqld]socket= /data/6600/mysql.sockbasedir =/datadatadir =/data/6600/dataport = 6600server_id = 16600pid-file =/data/6600/mysql_ 6600.pidkey_buffer_size =128mmax_allowed_packet = 16mtable_open_cache = 512max_connections = 200sort_buffer_size = 20mread_buffer_size = 20mread_rnd_buffer_size = 8mmyisam_sort_buffer_size = 64mthread_cache_size = 8query_cache_size = 32mcharacter-set-server=utf8innodb_buffer_pool_size = 256minnodb_log_file_size = 48Minnodb_log_buffer_size = 8minnodb_flush_log_at_trx_commit = 1innodb_file_per_table=1lower_case_table_names=1innodb_temp_data_file_path = Ibtmp1:12m:autoextend:max:5120mexplicit_defaults_for_timestamp=truesql_mode=no_engine_substitution,strict_ Trans_tables,only_full_group_byskip-name-resolvelog-bin =/data/6600/binlog/mysql-binexpire_logs_days = 7# Binlog-do-db=plateform_stable,finance_stable,location_log_stable#binlog-ignore-db=mysql,informAtion_schema,testbinlog_format=rowlog_slave_updatessymbolic-links=0default_storage_engine=innodbinnodb_autoinc _lock_mode=2log-error=/data/6600/mysqld_6600-error.logwsrep_cluster_name = pxcwsrep_provider=/usr/local/mysql/ lib/libgalera_smm.sowsrep_cluster_address= ' gcomm://192.168.1.210:6630,192.168.1.210:6632 ' wsrep_sst_receive_ Address=192.168.1.210:6620wsrep_node_incoming_address=192.168.1.210:6600wsrep_provider_options = "Gmcast.listen_ addr=tcp://192.168.1.210:6030;ist.recv_addr=192.168.1.210:6631; " wsrep_slave_threads= 4wsrep_log_conflictswsrep_node_address=192.168.1.210:6600#wsrep_node_name= Pxc-cluster-node-wsrep_sst_method=xtrabackup-v2#wsrep_sst_method=mysqldumpwsrep_sst_auth=sstuser:sstpasswd#pxc _strict_mode=enforcingwsrep_log_conflictswsrep_retry_autocommit=8secure_file_priv= "" innodb_page_cleaners=8[ Mysqldump]quickmax_allowed_packet = 16m[mysql]no-auto-rehashport = 6600default-character-set = Utf8socket=/data /6600/mysql.sock[myisamchk]key_buffer_size = 256msort_buffer_siZe = 256mread_buffer = 2mwrite_buffer = 2m[mysqlhotcopy]interactive-timeout 

Second instance: 6602

[Client]socket=/data/6602/mysql.sockport = 6602default-character-set = Utf8[mysqld]socket=/data/6602/mysql.sock Basedir =/datadatadir =/data/6602/dataport = 6602server_id = 6602pid-file =/data/6602/mysql_6602.pidkey_buffer_size =1 28mmax_allowed_packet = 16mtable_open_cache = 512max_connections = 200sort_buffer_size = 20Mread_buffer_size = 20Mread_ Rnd_buffer_size = 8mmyisam_sort_buffer_size = 64mthread_cache_size = 8query_cache_size = 32Mcharacter-set-server= Utf8innodb_buffer_pool_size = 256minnodb_log_file_size = 48minnodb_log_buffer_size = 8Minnodb_flush_log_at_trx_ commit = 1innodb_file_per_table=1lower_case_table_names=1innodb_temp_data_file_path = Ibtmp1:12m:autoextend:max : 5120mexplicit_defaults_for_timestamp=truesql_mode=no_engine_substitution,strict_trans_tables,only_full_group_ Byskip-name-resolvelog-bin =/data/6602/binlog/mysql-binexpire_logs_days = 7#binlog-do-db=plateform_stable,finance _stable,location_log_stable#binlog-ignore-db=mysql,information_schema,testbinloG_format=rowlog_slave_updatessymbolic-links=0default_storage_engine=innodbinnodb_autoinc_lock_mode=2log-error= /data/6602/mysqld_6602-error.logwsrep_cluster_name = Pxcwsrep_provider=/usr/local/mysql/lib/libgalera_ smm.sowsrep_cluster_address= ' gcomm://192.168.1.210:6630,192.168.1.210:6632 ' wsrep_sst_receive_address= 192.168.1.210:6622wsrep_node_incoming_address=192.168.1.210:6602wsrep_provider_options = "Gmcast.listen_addr=tcp ://192.168.1.210:6632;ist.recv_addr=192.168.1.210:6633; " wsrep_slave_threads= 4wsrep_log_conflictswsrep_node_address=192.168.1.210:6602#wsrep_node_name= Pxc-cluster-node-wsrep_sst_method=xtrabackup-v2#wsrep_sst_method=mysqldumpwsrep_sst_auth=sstuser:sstpasswd#pxc _strict_mode=enforcingwsrep_log_conflictswsrep_retry_autocommit=8secure_file_priv= "" innodb_page_cleaners=8[ Mysqldump]quickmax_allowed_packet = 16m[mysql]no-auto-rehashport = 6602default-character-set = Utf8socket=/data /6602/mysql.sock[myisamchk]key_buffer_size = 256msort_buffer_size = 256mread_buffer = 2mwrite_buffer = 2m[mysqlhotcopy]interactive-timeout 

V. Initialize start PXC master node

5.1 Initializing PXC

Initialize 6600

chown -R mysql:mysql /usr/local/mysqlchown -R mysql:mysql /data/6600{,6602}/usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/6600/data --user=mysql  

Initialize 6602

/usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/6602/data --user=mysql  

Initialization error can empty files in msyql/data/directory
Note Record initialization password

5.2 Starting MySQL
Starting 6600 Instances

/usr/local/mysql/bin/mysqld --defaults-file=/data/6602/my.cnf  --wsrep-new-cluster &

Note that the master node starts with--wsrep-new-cluster, and the other nodes do not.

Starting 6600 Instances

/usr/local/mysql/bin/mysqld --defaults-file=/data/6602/my.cnf &

5.3 Changing your password

echo "export .:/usr/local/mysql/bin:$PATH" >>/etc/profilesource /etc/profile

Go in with the password you just initialized. mysql instance 6600

mysql -uroot -p${mypasswd} -S /data/6600/mysql.sock

You'll be prompted to change your password the first time.

 ALTER USER ‘root‘@‘localhost‘ IDENTIFIED BY ‘123456‘;

Go in with the password you just initialized. mysql instance 6602

mysql -uroot -p${mypasswd} -S /data/6602/mysql.sock

You'll be prompted to change your password the first time.

 ALTER USER ‘root‘@‘localhost‘ IDENTIFIED BY ‘123456‘;

5.4 Creating a user for Wsrep_sst_auth transport
Randomly select a node to create

mysql> CREATE USER ‘sstuser‘@‘localhost‘ IDENTIFIED BY ‘sstpassword‘;mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO ‘sstuser‘@‘localhost‘;mysql> FLUSH PRIVILEGES;

Vi. Use of GARBD

PXC cluster is at least 3 units, arbiter GARBD, used to solve

cd /usr/local/mysql/bin/    ./garbd --group=pxc-cluster --address=gcomm://192.168.1.210:6630,192.168.1.210:6632 --option=gmcast.listen_addr=tcp://192.168.1.210:5567 -d -l /tmp/garbd.log    #全部起完了之后就可以看到节点数和集群

VII. Inspection

#查看MySQL 节点数 mysql> show global status like ‘wsrep_cluster_size‘;+--------------------+-------+| Variable_name      | Value |+--------------------+-------+| wsrep_cluster_size | 3     |+--------------------+-------+1 row in set (0.00 sec)

Is three indicates that the PXC cluster is successfully built, a table can be created on one node, and the other nodes can be queried to indicate that the cluster is functioning properly.

To view the number of processes

ps -ef|grep mysqlmysql    14316     1  0 Jun28 ?        00:06:18 /usr/local/mysql/bin/mysqld --defaultsfile=/data/6600/my.cnf --wsrep-new-clustermysql    15611     1  0 Jun28 ?        00:03:37 /usr/local/mysql/bin/garbd --group=pxc --address=gcomm://192.168.1.210:6630,192.168.1.210:6632 --option=gmcast.listen_addr=tcp://192.168.1.210:5567 -d -l /tmp/garbd.logmysql    23001     1  0 Jun29 ?        00:04:55 /usr/local/mysql/bin/mysqld --defaults-file=/data/6602/my.cnf

Multi-instance compilation installation for Percona-xtradb-cluster