PXC Percona-XtraDB-Cluster-5.6.30

 

初始化

 

yum  install -y cmake;
yum  install -y gcc gcc-c++ ; 
yum  nstall -y ncurses-devel.x86_64 ; 
yum  install -y cmake.x86_64 ; 
yum   install -y libaio.x86_64 ;
yum   install -y libaio.i686; 
yum  install -y bison.x86_64 ; 
yum  install -y gcc-c++.x86_64;
yum   install -y perl-Module-Build; 
yum   install -y libtool zilib-devel ;
yum   install -y perl.x86_64;
yum    install -y perl-Module-Pluggable.x86_64;
yum   install -y perl-Pod-Escapes.x86_64;
yum   install -y perl-Pod-Simple.x86_64;
yum    install -y perl-libs.x86_64;
yum   install -y perl-version.x86_64  ;
yum   install –y openssl openssl-devel ncurses ncurses-devel;
yum   install -y readline-devel;
yum install readline-devel readline -y;
yum   install numactl -y ;
yum   install libaio.so.1 libnuma.so.1 -y;

cd /usr/lib64
ln -s libcrypto.so.10  libcrypto.so.6
ln -s  libssl.so.10 libssl.so.6
ln -s libreadline.so libreadline.so.5

 

安装：

 

#安装路径
mkdir  /usr/local/Percona/
groupadd mysql
useradd -r -g mysql -d /data/mysql -s /sbin/nologin mysql

cd /tmp
wget  https://www.percona.com/downloads/Percona-XtraDB-Cluster-56/Percona-XtraDB-Cluster-5.6.30-25.16/binary/tarball/Percona-XtraDB-Cluster-5.6.30-rel76.3-25.16.1.Linux.x86_64.ssl098e.tar.gz
tar -xvf Percona-XtraDB-Cluster-5.6.30-rel76.3-25.16.1.Linux.x86_64.ssl098e.tar.gz
cp -R Percona-XtraDB-Cluster-5.6.30-rel76.3-25.16.1.Linux.x86_64.ssl098e/* /usr/local/Percona/
cp /usr/local/Percona/support-files/mysql.server /etc/init.d/
#数据目录
mkdir /data/mysql/data/engine/innodata -p
mkdir /data/mysql/data/engine/innolog -p
mkdir /data/mysql/data/engine/wsrep -p
mkdir /data/mysql/data -p
mkdir /data/mysql/log -p
chown mysql. /data -R

 

配置：

 

cat >> /etc/my.cnf << EOF
[mysqld] 

wsrep_cluster_address="gcomm://192.168.10.243,192.168.10.244,192.168.10.242"   #集群所有IP
wsrep_provider_options="gcache.size=300M; gcache.page_size=512M"
innodb_autoinc_lock_mode=2
wsrep_cluster_name=my_wsrep_cluster
wsrep_sst_auth=galera:galera123  #集群同步sst账号密码
innodb_flush_log_at_trx_commit=0
wsrep_provider=/usr/local/Percona/lib/libgalera_smm.so
wsrep_node_address=192.168.10.244 #本机IP
wsrep_sst_method=rsync  #数据同步方式 xtrabackup-v2,mysqldump
wsrep_node_name=node1 #当前节点名称 node2 node3 

user=mysql
basedir = /usr/local/Percona
datadir = /data/mysql/data 

innodb_log_group_home_dir=/data/mysql/data/engine/innolog
innodb_log_file_size = 50M
innodb_data_home_dir=/data/mysql/data/engine/innodata
innodb_data_file_path = ibdata1:100M;ibdata2:150M:autoextend
log-bin =  /data/mysql/log/mysql-binlog
log-bin-index = /data/mysql/log/mysql-bin.index
innodb_api_enable_binlog=1
log_bin_trust_function_creators=1

#skip-grant-tables
binlog_cache_size = 32M
max_binlog_size=512M
binlog_format=row
innodb_file_per_table=1

log-error = /data/mysql/mysql-error.log
pid-file = /data/mysql/mysql.pid
skip-name-resolve
socket=/data/mysql/mysql.sock 

############pool#########

#*******skip-innodb*********
innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size=2000M
innodb_write_io_threads = 8
innodb_read_io_threads = 16
innodb_thread_concurrency = 1000
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 64M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 75
innodb_lock_wait_timeout = 120
innodb_buffer_pool_instances=4
port=3306 
server-id=1

# *** global cache ***
read_buffer_size = 32M
read_rnd_buffer_size = 128M
sort_buffer_size = 64M
join_buffer_size = 64M

# *** query cache ***
query_cache_size = 0
#query_cache_limit = 4M

# *** bin log ***
#log_slave_updates
log_warnings=2

###########others#########
#***transaction***
default-storage-engine = INNODB
transaction_isolation = REPEATABLE-READ

# *** thread ***
thread_cache_size = 64
thread_stack = 512K

#*** network ***
back_log = 512
#skip-networking
max_connections = 3000
max_connect_errors = 30
table_open_cache = 4096
max_allowed_packet = 64M
max_heap_table_size = 512M

#*** char set ***
character-set-server = utf8
explicit_defaults_for_timestamp = true

# *** tmp table ***
tmp_table_size = 128M

# *** slow query log ***
slow_query_log
long_query_time = 5

[mysqldump]
quick
max_allowed_packet = 32M

[mysql]
auto-rehash
[myisamchk]
key_buffer_size = 12M
sort_buffer_size = 12M
read_buffer = 32M
write_buffer = 32M
[mysqlhotcopy]
interactive-timeout

[client]
socket=/data/mysql/mysql.sock 
port=3306 
EOF

 

初始化：

 

#初始化数据库
cd /usr/local/Percona/
./scripts/mysql_install_db --user=mysql --basedir=/usr/local/Percona/ --datadir=/data/mysql/data/ --defaults-file=/etc/my.cnf
#重置默认root密码
/usr/local/Percona//bin/mysqladmin -u root password '123.com';
#集群首台启动必须这种方式 其他使用start
/etc/init.d/mysql.server bootstrap-pxc
#授权和清理账户
 mysql -u root -p123.com -e "GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* to galera@'%' IDENTIFIED BY 'galera123'"
 mysql -u root -p123.com -e "GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* to galera@'localhost' IDENTIFIED BY 'galera123'"
 mysql -u root -p123.com -e  "delete from mysql.user where user='';";
 mysql -u root -p123.com -e "flush privileges;"

 

检测：

 

mysql> show global status like '%wsrep%'

 

| wsrep_incoming_addresses | 192.168.10.242:3306,192.168.10.243:3306,192.168.10.244:3306 |

| wsrep_cluster_size | 3 |