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.使用情境

資料庫軟體：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預設沒有密碼，我們需要設定root密碼。

7.1修改/etc/profile檔案，在檔案末尾添加

vi + /etc/profile

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

source /etc/profile

現在，我們可以在終端內直接輸入mysql進入，mysql的環境了

執行下面的命令修改root密碼

–7.2修改mysql管理員密碼

1.修改目前使用者密碼
mysql -uroot -p

SET PASSWORD = PASSWORD(‘pass01’);

flush privileges;

2.給所有的root使用者設定密碼：
第一種方式(推薦)
# 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.刪除所有匿名使用者(不要刪除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.cnfnode01

[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.gzcd socat-2.0.0-b8./configuremake & 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%’;

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。