Galera 10.0.20 on CentOS 6.6, galeracentos

Galera 10.0.20 on CentOS 6.6, galeracentos

Galera 10.0.20 on CentOS 6.6

0. Use Cases

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

Install galera to obtain libgalera_smm.so;
Xtrabackup is installed to configure the synchronization method xtrabackup, which is used for node data synchronization;
Socat is installed because the socket mode is used for end-to-end data exchange for synchronous data;

Database node:
127.0.0.1 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. Delete postfix

Yum remove postfix

--- 1. System Installation

See: Linux for Mysql system installation specifications

--- 2 configure yum Source

Cd/etc/yum. repos. d

Mv CentOS-Base.repo CentOS-Base.repo.old

Alibaba Image

Wget-O/etc/yum. repos. d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-6.repo

163 Images

Wget http://mirrors.163.com/.help/CentOS6-Base-163.repo

Yum makecache

Check the updatable rpm package

Yum check-update

Update all rpm packages

Yum update

-3. Disable the 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 the hosts of each node

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 Installation

I. Uninstall the old version

Run the following command to check whether MySQL Server is installed.
Rpm-qa | grep mysql

If yes, run the following command to uninstall it:
Rpm-e mysql // normal deletion Mode
Rpm-e-nodeps mysql
Rpm-e-nodeps mysql-devel
Rpm-e-nodeps mysql-libs

// Strong deletion mode. If the preceding command is used to delete a file, other dependent files are displayed,
You can use this command to forcibly delete it.

Yum remove mysql-libs

Ii. Install MySQL

1. Install the package required for compiling code

Yum-y install cmake gcc-c ++ autoconf automake zlib * libxml *\
Ncurses-devel libtool-ltdl-devel * make bison-devel \
Openssl-devel libevent-devel \
Libaio-devel pam-devel boost-devel valgrind-devel \
Libnl-devel popt-static bzr

2. Create a group and user for mysql

Groupadd mysql
Useradd-s/sbin/nologin-g mysql-M mysql
Passwd mysql
Mysql01!

Id mysql

3. Set system resource limits for users

Vi/etc/security/limits. conf

Mysql soft nproc 2047
Hard nproc 16384
Mysql soft nofile 1024
Mysql hard nofile 65536

4. Compile and install

Tar xf mariadb-galera-10.0.20.tar.gz

Cd mariadb-10.0.20

Compile and install

/*
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

During re-compilation, the old object files and cache information must be cleared.

# Make clean

# Rm-f CMakeCache.txt

**/

Cmake \
-DCMAKE_INSTALL_PREFIX =/data/mysql \
-DMYSQL_UNIX_ADDR =/data/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

Iii. Configure MySQL

1. Create data files and temporary files, and 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. Modify parameters

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

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

Ll/etc/my. cnf
Lrwxrwxrwx. 1 root 23 Nov 18 17: 30/etc/my. cnf->/data/mysql/my. cnf

Chown-R mysql/data/mysql
Chgrp-R mysql/data/mysql

5. initialize 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 secure startup
/Data/mysql/bin/mysqld_safe &

/Data/mysql/bin/mysqld_safe-datadir = '/data/mysql/data '&

-6.2 Add a service, copy the service script to the init. d directory, and set the startup

Cd/data/mysql/

Cp support-files/mysql. server/etc/init. d/mysql

Chkconfig mysql on

7. configure a mysql user

After MySQL is started successfully, the root user has no password by default. We need to set the root password.

7.1 modify the/etc/profile file and add it at the end of the file

Vi +/etc/profile

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

Source/etc/profile

Now, we can directly enter mysql in the terminal to enter the mysql environment.

Run the following command to change the root password:

-7.2 modify the mysql administrator password

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

Set password = PASSWORD ('pass01 ');

Flush privileges;

2. Set a password for all root users:
Method 1 (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 root @ localhost)
Drop user "@ 'localhost'; delete 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 remote access for root users

-Method 1: Authorization

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 on to mysql

Mysql-hlocalhost-uroot-ppass01

-- 7.5 set the linux script

Vi ++ ~ /. Bash_profile

Ocpyang set (only Intranet IP addresses can be written to bind mysql to the Intranet IP address)

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 '@ % root@mysql.ocp.com' ignored in-skip-name-resolve mode.

Solution:

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 bind directory for soft connection to mysql

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

3. galera Configuration

-- 3.1 install galera (see galera installation configuration)

-3.2 Create a wsrep user

Grant reload, lock tables, REPLICATION CLIENT ON.TO 'wsrep '@' % '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 install 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 modify 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 conflicts

Innodb_autoinc_lock_mode = 2
Innodb_locks_unsafe_for_binlog = 1
Innodb_flush_log_at_trx_commit = 2 # It can improve performance and ensure that galera does not lose data
Query_cache_size = 0
Query_cache_type = 0
Wsrep_provider =/data/mysql/lib/plugin/libgalera_smm.so # modify
Wsrep_cluster_name = "a_a_cluster" # modify
Wsrep_cluster_address = "gcomm: // 192.168.50.10, 192.168.50.20, 192.168.50.30" # modify
Wsrep_node_address = 192.168.50.10 # modify
Wsrep_node_name = db01 # modify
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_policy_cmd =
Wsrep_sst_method = xtrabackup-v2 # can be modified # rsync, xtrabackup, mysqldump
Wsrep_sst_auth = root: pass01 # modify

Node02

[Mysqld]
Wsrep_on = ON
Binlog_format = ROW
Default-storage-engine = innodb

To reduce conflicts

Innodb_autoinc_lock_mode = 2
Innodb_flush_log_at_trx_commit = 2 # It can improve performance and ensure that galera does not lose data
Query_cache_size = 0
Query_cache_type = 0
Wsrep_provider =/data/mysql/lib/plugin/libgalera_smm.so # modify
Wsrep_cluster_name = "a_a_cluster" # modify
Wsrep_cluster_address = "gcomm: // 192.168.50.10, 192.168.50.20, 192.168.50.30" # modify
Wsrep_node_address = 192.168.50.20 # modify
Wsrep_node_name = db02 # modify
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_policy_cmd =
Wsrep_sst_method = xtrabackup-v2 # can be modified # rsync, xtrabackup, mysqldump, xtrabackup-v2
Wsrep_sst_auth = root: pass01 # modify

Node03

[Mysqld]
Wsrep_on = ON
Binlog_format = ROW
Default-storage-engine = innodb

To reduce conflicts

Innodb_autoinc_lock_mode = 2
Innodb_locks_unsafe_for_binlog = 1
Innodb_flush_log_at_trx_commit = 2 # It can improve performance and ensure that galera does not lose data
Query_cache_size = 0
Query_cache_type = 0
Wsrep_provider =/data/mysql/lib/plugin/libgalera_smm.so # modify
Wsrep_cluster_name = "a_a_cluster" # modify
Wsrep_cluster_address = "gcomm: // 192.168.50.10, 192.168.50.20, 192.168.50.30" # modify
Wsrep_node_address = 192.168.50.30 # modify
Wsrep_node_name = db03 # modify
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_policy_cmd =
Wsrep_sst_method = xtrabackup-v2 # can be modified # rsync, xtrabackup, mysqldump, xtrabackup-v2
Wsrep_sst_auth = root: pass01 # modify

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

-4. Use rsync, xtrabackup, and mydupm

-4.1 Use rsync

Yum-y install rsync

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

-We strongly recommend that you add xtrabackup and socat 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 Use 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 my. cnf and set wsrep_cluster_address = gcomm: //. Then modify the configuration after startup.

-Method 2: (recommended)
Run the following command to start mysql (RedHat and CentOS systems are supported)

The start of the first node is somewhat different from that of the other two nodes.
/Data/mysql/bin/mysqld_safe-datadir = '/data/mysql/data'-wsrep-cluster-address = "gcomm ://"&

Start other nodes:
/Data/mysql/bin/mysqld_safe-datadir = '/data/mysql/data '&

-Node1: View logs

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

-- 6. startup of each node

Netstat-lntp | grep 33306

Tcp 0 0: 33306: * LISTEN 3072/mysqld

Pkill mysqld

NODE1 is started first, and other nodes are unordered.

-- 7. view Cluster connection information

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 Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.