Galera 10.0.20 on CentOS 6.6

Galera 10.0.20 on CentOS 6.6

0. Usage Scenarios

Database software: mariadb-galera-10.0.20-linux-x86_64.tar.gz
Cluster Management: galera-3-25.3.10
Node synchronization mode (Xtrabackup,rsync): percona-xtrabackup-2.2.8-5059.el6.x86_64.rpm
Node synchronization using sockt:socat-2.0.0-b7.tar.gz

Installation of Galera is to obtain libgalera_smm.so;
Xtrabackup is installed in order to configure the synchronization mode xtrabackup, node data synchronization when used;
The SOCAT is installed because the synchronous data uses the socket method for end-to-end data exchange;

Database node:
127.0.0.1 localhost localhost
192.168.50.10 DB01 db01.mysql.com
192.168.50.20 DB02 db02.mysql.com
192.168.50.30 db03 db03.mysql.com

1. System Installation

--–0. deleting postfix

Yum Remove Postfix

--–1. System Installation

See also: Linux for Mysql System installation specification

——-2 Configuring the Yum source

Cd/etc/yum.repos.d

MV Centos-base.repo Centos-base.repo.old

Ari image

Wget-o/etc/yum.repos.d/centos-base.repo Http://mirrors.aliyun.com/repo/Centos-6.repo

163 mirroring

wget Http://mirrors.163.com/.help/CentOS6-Base-163.repo

Yum Makecache

Check for updatable RPM packages

Yum Check-update

Update all RPM Packages

Yum Update

-3. Turn off firewall and SELinux

Chkconfig–level 123456 iptables off

Service Iptables Stop

/usr/sbin/sestatus-v | grep "SELinux Status"

Vi/etc/selinux/config

Selinux=disabled

-4. Modify each node hosts

Vi/etc/hosts

192.168.50.10 DB01 db01.mysql.com
192.168.50.20 DB02 db02.mysql.com
192.168.50.30 db03 db03.mysql.com

-4.rz

Yum Install Lrzsz-y

2.mysql Mounting Section

One: Uninstall the old version

Use the following command to check if MySQL Server is installed
Rpm-qa | grep MySQL

Some words are unloaded by the following command
RPM-E MySQL//normal Delete mode
Rpm-e–nodeps MySQL
Rpm-e–nodeps Mysql-devel
Rpm-e–nodeps Mysql-libs

Brute Force Delete mode, if you use the above command to delete, prompted to have other dependent files,
The command can be used to forcefully delete the

Yum Remove Mysql-libs

Two: Install MySQL

1. Install the packages required for compiling the code

Yum-y Install cmake gcc gcc-c++ autoconf automake zlib* libxml* \
ncurses ncurses-devel libtool libtool-ltdl-devel* make bison bison-devel \
Openssl-devel libevent-devel \
Libaio libaio-devel pam-devel boost Boost-devel valgrind-devel \
Libnl-devel Popt-devel popt-static BZR

2. Create a new group and user for MySQL

Groupadd MySQL
Useradd-s/sbin/nologin-g mysql-m MySQL
passwd MySQL
mysql01!

ID MySQL

3. Set the user's system resource limits

Vi/etc/security/limits.conf

MySQL Soft nproc 2047
MySQL Hard Nproc 16384
MySQL Soft nofile 1024
MySQL Hard nofile 65536

4. Compiling the installation

Tar XF mariadb-galera-10.0.20.tar.gz

CD mariadb-10.0.20

Compiling the installation

/*
CMake. -lh– view MySQL-related parameters supported by CMake

Shell> CMake. -L # Overview
Shell> CMake. -LH # Overview with help text
Shell> CMake. -lah # All params with help text
Shell> Ccmake. # Interactive Display

When recompiling, you need to clear the old object file and cache information

# Make Clean

# rm-f CMakeCache.txt

**/

CMake \
-dcmake_install_prefix=/data/mysql \
-dmysql_unix_addr=/data/mysql/mysql.sock \
-dwith_innobase_storage_engine=1 \
-dwith_myisam_storage_engine=1 \
-dwith_sphinx_storage_engine=1 \
-dwith_readline=1 \
-dmysql_datadir=/data/mysql/data \
-dmysql_tcp_port=33306 \
-denabled_local_infile=1 \
-dwith_extra_charsets=all \
-ddefault_charset=utf8 \
-DDEFAULT_COLLATION=UTF8_GENERAL_CI \
-dextra_charsets=all \
-dwith_wsrep=1 \
-dwith_innodb_disallow_writes=1

Make-j4

Make install

Three: Configure MySQL

1. Create new data files, temporary files, modify/usr/local/mysql permissions

Mkdir-p/data/mysql

MKDIR/DATA/MYSQL/{INNODB_DATA,DATA,TMP}-P

Mkdir/data/mysql/mysql_logs/{binary_log,innodb_log,query_log,slow_query_log,error_log}-P

2. Modify Permissions

Chown-r Mysql/data/mysql
Chgrp-r Mysql/data/mysql

3. Modifying parameters

Rm-rf/etc/my.cnf
Vi/data/mysql/my.cnf

Link Files ln-fs/data/mysql/my.cnf/etc/my.cnf

Ll/etc/my.cnf
lrwxrwxrwx. 1 root root 17:30/etc/my.cnf/data/mysql/my.cnf

Chown-r Mysql/data/mysql
Chgrp-r Mysql/data/mysql

5. Initializing the database

Cd/data/mysql/scripts

./mysql_install_db–defaults-file=/etc/my.cnf–user=mysql–basedir=/data/mysql \

–datadir=/data/mysql/data

6. Start MySQL

Chown-r Mysql/data/mysql
Chgrp-r Mysql/data/mysql

–6.1 Recommended Safe Boot
/data/mysql/bin/mysqld_safe &

/data/mysql/bin/mysqld_safe–datadir= '/data/mysql/data ' &

–6.2 Add service, Copy Service script to INIT.D directory, and set boot start

cd/data/mysql/

CP Support-files/mysql.server/etc/init.d/mysql

Chkconfig MySQL on

7. Configure MySQL Users

After MySQL starts successfully, Root does not have a password, we need to set the root password.

7.1 Modify the/etc/profile file to add at the end of the file

VI +/etc/profile

Path=/data/mysql/bin: $PATH
Export PATH

Source/etc/profile

Now, we can enter MySQL in the terminal directly into the MySQL environment

Execute the following command to modify the root password

–7.2 Modifying the MySQL administrator password

1. Modify the current user password
Mysql-uroot-p

SET PASSWORD = PASSWORD (' pass01 ');

Flush privileges;

2. Set the password for all root users:
First Way (recommended)
# mysql-uroot-p

MariaDB [(None)]>
MariaDB [(None)]> select Host,user,password from Mysql.user;
+ ————-+--+ ———-+
| Host | user | password |
+ ————-+--+ ———-+
| localhost | Root | |
| db02.zp.com | Root | |
| 127.0.0.1 | Root | |
| :: 1 | Root | |
| localhost | | |
| db02.zp.com | | |
+ ————-+--+ ———-+
6 rows in Set (0.00 sec)

Update mysql.user Set password = password (' pass01 ') where user = ' root ' limit 100;flush privileges;
Flush privileges;

3. Delete all anonymous users (do not delete [email protected])
DROP USER "@ ' localhost ';d elete from Mysql.user where user=" limit 10;commit;flush privileges;

Delete from Mysql.user where host= ' db01.mysql.com '; flush privileges;
Delete from Mysql.user where host= ' db02.mysql.com '; flush privileges;
Delete from Mysql.user where host= ' db03.mysql.com '; flush privileges;

–7.3 set root user can access remotely

– Method 1: Authorization law

Mysql>grant all privileges on . To ' root ' @ '% ' identified by ' pass01 ' \
with GRANT Option;flush privileges;

Select Host,user,password from Mysql.user;

–7.4 log in to MySQL

Mysql-hlocalhost-uroot-ppass01

--7.5 setting up Linux scripts

VI + ~/.bash_profile

Ocpyang set (bind MySQL to intranet IP only write intranet IP)

Alias mysql= "Mysql-u-hlocalhost-uroot-ppass01–auto-rehash"
Alias errorlog= "Cat/data/mysql/mysql_logs/error_log/error.log"
Alias mycnf= "Cd/data/mysql"
Export path=/data/mysql/scripts: $PATH

SOURCE ~/.bash_profile

--7.7 Modify Permissions

Chown-r Mysql/data/mysql
Chgrp-r Mysql/data/mysql

--7.8 common startup errors or warnings

Cat/data/mysql/mysql_logs/error_log/error.log

Warning] ' proxies_priv ' entry ' @% [email protected] ' ignored In–skip-name-resolve mode.

Workaround:

Delete from Mysql.proxies_priv where host= ' db01.mysql.com '; commit;flush privileges;
Delete from Mysql.proxies_priv where host= ' db02.mysql.com '; commit;flush privileges;
Delete from Mysql.proxies_priv where host= ' db03.mysql.com '; commit;flush privileges;

-–7.9 soft-connect MySQL bin directory

ln-sf/data/mysql/bin/*/usr/bin/

3.galera Related Configurations

-–3.1 Galera installation (see Galera installation configuration)

-3.2 New Wsrep user

GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT on . To ' wsrep ' @ ' percent ' identified by ' wsrep '; flush privileges;

CREATE USER ' wsrep ' @ ' localhost ' identified by ' wsrep ';
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT on . To ' wsrep ' @ ' localhost ';
FLUSH privileges;

-3.2.3 Installing Socat

http://www.dest-unreach.org/socat/

http://www.dest-unreach.org/socat/download/

Tar XF socat-1.7.3.0.tar.gz
CD socat-1.7.3.0
./configure
Make-j4
Make install

-3.2.4 Modifying the MySQL configuration file

Mkdir/etc/my.cnf.d

Cd/soft/mariadb-10.0.20/support-files

CP wsrep.cnf/etc/my.cnf.d/

Vi/etc/my.cnf.d/wsrep.cnfnode01

[Mysqld]
Wsrep_on=on
Binlog_format=row
Default-storage-engine=innodb

To reduce conflict

innodb_autoinc_lock_mode=2
Innodb_locks_unsafe_for_binlog=1
Innodb_flush_log_at_trx_commit = 2 #可以提高性能, Galera guaranteed no data loss
Query_cache_size=0
Query_cache_type=0
Wsrep_provider=/data/mysql/lib/plugin/libgalera_smm.so #修改
Wsrep_cluster_name= "Galera_cluster" #修改
wsrep_cluster_address= "gcomm://192.168.50.10,192.168.50.20,192.168.50.30" #修改
wsrep_node_address=192.168.50.10 #修改
WSREP_NODE_NAME=DB01 #修改
Wsrep_slave_threads=4
Wsrep_certify_nonpk=1
wsrep_max_ws_rows=131072
wsrep_max_ws_size=1073741824
Wsrep_debug=0
Wsrep_convert_lock_to_trx=0
Wsrep_retry_autocommit=1
Wsrep_auto_increment_control=1
Wsrep_drupal_282555_workaround=0
Wsrep_causal_reads=0
Wsrep_notify_cmd=
Wsrep_sst_method=xtrabackup-v2 #可以修改 #rsync,xtrabackup,mysqldump
Wsrep_sst_auth=root:pass01 #修改

Node02

[Mysqld]
Wsrep_on=on
Binlog_format=row
Default-storage-engine=innodb

To reduce conflict

innodb_autoinc_lock_mode=2
Innodb_flush_log_at_trx_commit = 2 #可以提高性能, Galera guaranteed no data loss
Query_cache_size=0
Query_cache_type=0
Wsrep_provider=/data/mysql/lib/plugin/libgalera_smm.so #修改
Wsrep_cluster_name= "Galera_cluster" #修改
wsrep_cluster_address= "gcomm://192.168.50.10,192.168.50.20,192.168.50.30" #修改
wsrep_node_address=192.168.50.20 #修改
WSREP_NODE_NAME=DB02 #修改
Wsrep_slave_threads=4
Wsrep_certify_nonpk=1
wsrep_max_ws_rows=131072
wsrep_max_ws_size=1073741824
Wsrep_debug=0
Wsrep_convert_lock_to_trx=0
Wsrep_retry_autocommit=1
Wsrep_auto_increment_control=1
Wsrep_drupal_282555_workaround=0
Wsrep_causal_reads=0
Wsrep_notify_cmd=
Wsrep_sst_method=xtrabackup-v2 #可以修改 #rsync,xtrabackup,mysqldump,xtrabackup-v2
Wsrep_sst_auth=root:pass01 #修改

Node03

[Mysqld]
Wsrep_on=on
Binlog_format=row
Default-storage-engine=innodb

To reduce conflict

innodb_autoinc_lock_mode=2
Innodb_locks_unsafe_for_binlog=1
Innodb_flush_log_at_trx_commit = 2 #可以提高性能, Galera guaranteed no data loss
Query_cache_size=0
Query_cache_type=0
Wsrep_provider=/data/mysql/lib/plugin/libgalera_smm.so #修改
Wsrep_cluster_name= "Galera_cluster" #修改
wsrep_cluster_address= "gcomm://192.168.50.10,192.168.50.20,192.168.50.30" #修改
wsrep_node_address=192.168.50.30 #修改
WSREP_NODE_NAME=DB03 #修改
Wsrep_slave_threads=4
Wsrep_certify_nonpk=1
wsrep_max_ws_rows=131072
wsrep_max_ws_size=1073741824
Wsrep_debug=0
Wsrep_convert_lock_to_trx=0
Wsrep_retry_autocommit=1
Wsrep_auto_increment_control=1
Wsrep_drupal_282555_workaround=0
Wsrep_causal_reads=0
Wsrep_notify_cmd=
Wsrep_sst_method=xtrabackup-v2 #可以修改 #rsync,xtrabackup,mysqldump,xtrabackup-v2
Wsrep_sst_auth=root:pass01 #修改

Echo '!includedir/etc/my.cnf.d/' >>/etc/my.cnf

-4. Using rsync, Xtrabackup, MYDUPM

–4.1 using rsync

Yum-y Install Rsync

Tar xvzf socat-2.0.0-b8.tar.gzcd socat-2.0.0-b8./configuremake & make Install

– It is strongly recommended that xtrabackup and Socat be added to the path

Vi/etc/profile

Path=/data/mysql/bin:/usr/local/bin/socat: $PATH
Export PATH

ln-sf/usr/local/bin/socat/usr/sbin/

-–4.2 using Xtrabackup (recommended)

Tar XF Percona-xtrabackup-2.2.11-r7956d1d-el6-x86_64-bundle.tar

Yum Localinstall-y percona-*

-5. Initialize Startup

– Method 1:
Modify the MY.CNF and set the wsrep_cluster_address=gcomm://to start and then modify the configuration.

– Method 2: (recommended)
Use the following command to start MySQL (support RedHat and CentOS systems)

The first node starts up and the other two nodes are a little different.
/data/mysql/bin/mysqld_safe–datadir= '/data/mysql/data ' –wsrep-cluster-address= "gcomm://" &

Other nodes start:
/data/mysql/bin/mysqld_safe–datadir= '/data/mysql/data ' &

–node1 Viewing logs

Cat/data/mysql/data/innobackup.backup.log

--6. Start-up correlation for each node

NETSTAT-LNTP | grep 33306

TCP 0 0::: 33306:::* LISTEN 3072/mysqld

Pkill mysqld

NODE1 start first, other nodes are not sequential.

--7. viewing cluster connections

Select Variable_value as "cluster size" from INFORMATION_SCHEMA. Global_status WHERE variable_name= "Wsrep_cluster_size";

SHOW STATUS like ' wsrep_cluster_size ';

SHOW STATUS like ' wsrep% ';

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Galera 10.0.20 on CentOS 6.6