Galera 10.0.20 on CentOS 6.6

0.使用场景

数据库软件：mariadb-galera-10.0.20-linux-x86_64.tar.gz
集群管理：galera-3-25.3.10
节点同步方式（xtrabackup,rsync)：percona-xtrabackup-2.2.8-5059.el6.x86_64.rpm
节点同步使用sockt：socat-2.0.0-b7.tar.gz

安装galera是为了获得libgalera_smm.so。
安装xtrabackup是为了配置同步方式xtrabackup，节点数据同步的时候用到。
安装socat是由于同步数据使用socket方式进行端到端的数据交换。

数据库节点:
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.系统安装

——–0.删除postfix

yum remove postfix

——–1.系统安装

參见:Linux for Mysql 系统安装规范

——-2 配置yum源

cd /etc/yum.repos.d

mv CentOS-Base.repo CentOS-Base.repo.old

阿里镜像

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

163镜像

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

yum makecache

检查可更新的rpm包

yum check-update

更新全部的rpm包

yum update

—3.关闭防火墙和selinux

chkconfig –level 123456 iptables off

service iptables stop

/usr/sbin/sestatus -v | grep “SELinux status”

vi /etc/selinux/config

SELINUX=disabled

—4.改动各个节点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安装部分

一：卸载旧版本号

使用以下的命令检查是否安装有MySQL Server
rpm -qa | grep mysql

有的话通过以下的命令来卸载掉
rpm -e mysql //普通删除模式
rpm -e –nodeps mysql
rpm -e –nodeps mysql-devel
rpm -e –nodeps mysql-libs

// 强力删除模式。假设使用上面命令删除时，提示有依赖的其他文件，
则用该命令能够对其进行强力删除

yum remove mysql-libs

二：安装MySQL

1.安装编译代码须要的包

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.为mysql新建组和用户

groupadd mysql
useradd -s /sbin/nologin -g mysql -M mysql
passwd mysql
mysql01!

id mysql

3.设置用户的系统资源限制

vi /etc/security/limits.conf

mysql soft nproc 2047
mysql hard nproc 16384
mysql soft nofile 1024
mysql hard nofile 65536

4.编译安装

tar xf mariadb-galera-10.0.20.tar.gz

cd mariadb-10.0.20

编译安装

/*
cmake . -LH –查看cmake支持的mysql相关參数

shell> cmake . -L # overview
shell> cmake . -LH # overview with help text
shell> cmake . -LAH # all params with help text
shell> ccmake . # interactive display

又一次编译时，须要清除旧的对象文件和缓存信息

# 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

三：配置MySQL

1.新建数据文件、暂时文件,改动/usr/local/mysql权限

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.改动权限

chown -R mysql /data/mysql
chgrp -R mysql /data/mysql

3.改动參数

rm -rf /etc/my.cnf
vi /data/mysql/my.cnf

链接文件

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

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

chown -R mysql /data/mysql
chgrp -R mysql /data/mysql

5.初始化数据库

cd /data/mysql/scripts

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

–datadir=/data/mysql/data

6.启动MySQL

chown -R mysql /data/mysql
chgrp -R mysql /data/mysql

–6.1建议安全启动
/data/mysql/bin/mysqld_safe &

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

–6.2加入服务。拷贝服务脚本到init.d文件夹，并设置开机启动

cd /data/mysql/

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

chkconfig mysql on

7.配置mysql用户

MySQL启动成功后，root默认没有password，我们须要设置rootpassword。

7.1改动/etc/profile文件，在文件末尾加入

vi + /etc/profile

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

source /etc/profile

如今，我们能够在终端内直接输入mysql进入，mysql的环境了

运行以下的命令改动rootpassword

–7.2改动mysql管理员password

1.改动当前用户password
mysql -uroot -p

SET PASSWORD = PASSWORD(‘pass01’);

flush privileges;

2.给全部的root用户设定password：
第一种方式(推荐)
# mysql -uroot -p

update mysql.user set password = password(‘pass01’) where user = ‘root’ limit 100;flush privileges;
flush privileges;

3.删除全部匿名用户(不要删除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设置root用户能够远程訪问

–方法1：授权法

mysql>GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ IDENTIFIED BY ‘pass01’ \
WITH GRANT OPTION;flush privileges;

select host,user,password from mysql.user;

–7.4 登录mysql

mysql -hlocalhost -uroot -ppass01

—-7.5设置linux脚本

vi + ~/.bash_profile

ocpyang set(绑定mysql到内网IP仅仅能写内网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改动权限

chown -R mysql /data/mysql
chgrp -R mysql /data/mysql

—-7.8 常见启动错误或警告

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

Warning] ‘proxies_priv’ entry ‘@% root@mysql.ocp.com’ ignored in –skip-name-resolve mode.

解决的方法:

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 软连接mysql的bin文件夹

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

3.galera相关配置

—–3.1 galera安装(參见galera安装配置)

—3.2 新建wsrep用户

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 安装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 改动mysql的配置文件

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

node01

[mysqld]
wsrep_on=ON
binlog_format=ROW
default-storage-engine=innodb

为了减少冲突

innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
innodb_flush_log_at_trx_commit = 2 #能够提高性能，galera保证不丢数据
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

为了减少冲突

innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit = 2 #能够提高性能，galera保证不丢数据
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

为了减少冲突

innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
innodb_flush_log_at_trx_commit = 2 #能够提高性能，galera保证不丢数据
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.使用rsync、xtrabackup、mydupm

–4.1 使用rsync

yum -y install rsync

tar xvzf socat-2.0.0-b8.tar.gz

cd socat-2.0.0-b8

./configure

make & make install

–强烈建议将xtrabackup和socat加入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 使用xtrabackup(推荐)

tar xf Percona-XtraBackup-2.2.11-r7956d1d-el6-x86_64-bundle.tar

yum localinstall -y percona-*

—5.初始化启动

–方法1:
改动 my.cnf 并设置 wsrep_cluster_address=gcomm:// ，启动后再来改动配置.

–方法2:(推荐)
使用以下这个命令来启动 mysql (支持 RedHat 和 CentOS 系统)

第一个节点启动和其他的两个节点有些不同
/data/mysql/bin/mysqld_safe –datadir=’/data/mysql/data’ –wsrep-cluster-address=”gcomm://” &

其他节点启动:
/data/mysql/bin/mysqld_safe –datadir=’/data/mysql/data’ &

–node1查看日志

cat /data/mysql/data/innobackup.backup.log

—-6.各节点启动相关

netstat -lntp | grep 33306

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

pkill mysqld

NODE1先启动,其他节点无顺序.

—-7.查看群集连接情况

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%’;

本文转自mfrbuaa博客园博客，原文链接：http://www.cnblogs.com/mfrbuaa/p/5333467.html，如需转载请自行联系原作者

Galera 10.0.20 on CentOS 6.6

阿里镜像

163镜像

yum check-update

yum update

vi /etc/hosts

vi /etc/security/limits.conf

链接文件

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

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

vi + /etc/profile

source /etc/profile

mysql -hlocalhost -uroot -ppass01

ocpyang set(绑定mysql到内网IP仅仅能写内网IP)

vi /etc/my.cnf.d/wsrep.cnf

node01

为了减少冲突

node02

为了减少冲突

node03

为了减少冲突

tar xvzf socat-2.0.0-b8.tar.gz

cd socat-2.0.0-b8

./configure

make & make install

vi /etc/profile

cat /data/mysql/data/innobackup.backup.log

netstat -lntp | grep 33306

pkill mysqld

热门文章

最新文章

相关电子书

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

Galera 10.0.20 on CentOS 6.6

阿里镜像

163镜像

yum check-update

yum update

vi /etc/hosts

vi /etc/security/limits.conf

链接文件

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

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

vi + /etc/profile

source /etc/profile

mysql -hlocalhost -uroot -ppass01

ocpyang set(绑定mysql到内网IP仅仅能写内网IP)

vi /etc/my.cnf.d/wsrep.cnf

node01

为了减少冲突

node02

为了减少冲突

node03

为了减少冲突

tar xvzf socat-2.0.0-b8.tar.gz

cd socat-2.0.0-b8

./configure

make & make install

vi /etc/profile

cat /data/mysql/data/innobackup.backup.log

netstat -lntp | grep 33306

pkill mysqld

热门文章

最新文章

相关电子书