Percona XtraDB Cluster安装配置

  Percona XtraDB Cluster没有 master 和 slave 的概念,而是 multi-master 方式的同步,支持在任意节点上写入数据,同时它能确保写入时不产生一致性冲突问题,它没有通过binlog方式来同步,而是xtrabackup的方式,因为每个节点都是彼此的镜像,像参数 "binlog-do-db" 这类的就没有什么用了。

  它和 Percona-Server-server 是兼容的,可以共用数据文件,但是软件包是冲突的,必须删除原有的软件包之后再安装新的软件包;

安装前的准备:
yum install https://www.percona.com/redir/downloads/percona-release/percona-release-0.0-1.x86_64.rpm  

需要用到epel源

yum install epel-release  
安装Percona XtraDB Cluster第一个Node
rpm -e --nodeps mysql-libs # 删除原系统的mysql库  
yum install Percona-XtraDB-Cluster-server-56 Percona-XtraDB-Cluster-client-56  
#如果要用5.5版本则为:
#yum install Percona-XtraDB-Cluster-server Percona-XtraDB-Cluster-client
mkdir -p /mnt/mysql/{data,tmp,run,binlogs,log} #创建所需要目录  
chown mysql:mysql /mnt/mysql/{data,tmp,run,binlogs,log}  
#初始化数据库
su - mysql  
mysql_install_db --user=mysql --datadir=/mnt/mysql/data/  
exit  
配置 Percona XtraDB Cluster
vim /etc/my.cnf  
[mysqld]
# basic settings
datadir = /mnt/mysql/data  
tmpdir = /mnt/mysql/tmp  
socket = /mnt/mysql/run/mysqld.sock  
port = 3306  
pid-file = /mnt/mysql/run/mysqld.pid  
character-set-server = utf8  
skip-character-set-client-handshake  
skip-name-resolve  
event_scheduler=ON

# innodb settings
default-storage-engine = INNODB  
innodb_file_per_table = 1  
log-bin = /mnt/mysql/binlogs/bin-log-mysqld  
log-bin-index = /mnt/mysql/binlogs/bin-log-mysqld.index  
log-error = /mnt/mysql/binlogs/mysqld.err  
expire_logs_days = 30  
innodb_data_home_dir = /mnt/mysql/data  
innodb_data_file_path = ibdata1:10M:autoextend  
innodb_log_group_home_dir = /mnt/mysql/data  
#binlog-do-db = testdb

#####     Global Buffers     ###########################################
key_buffer_size = 32M  
innodb_buffer_pool_size = 3G  
innodb_log_buffer_size = 16M  
innodb_log_file_size = 512M  
innodb_log_files_in_group = 3  
innodb_lock_wait_timeout = 120  
innodb_additional_mem_pool_size = 64M  
net_buffer_length = 16K

#####     Thread Buffers     ###########################################
sort_buffer_size = 16M  
myisam_sort_buffer_size = 256M  
read_buffer_size = 8M  
join_buffer_size = 16M  
read_rnd_buffer_size = 64M

#####    InnoDB Parameters   ###########################################
innodb_file_per_table  
innodb_flush_method = O_DIRECT  
innodb_flush_log_at_trx_commit = 1  
skip-innodb-doublewrite

max_heap_table_size = 32M  
tmp_table_size = 32M

max_connect_errors = 1000  
max_connections = 3000  
table_open_cache = 512  
thread_cache_size = 16  
wait_timeout = 30  
open_files_limit = 65535

slow-query-log  
long_query_time = 5


# xtradb cluster settings
binlog_format = ROW  
wsrep_cluster_name = mycluster  
wsrep_cluster_address = gcomm://10.46.10.2,10.46.10.3 #其它Node的IP,多个用‘,’分隔  
#首次启动第一个Node时需要设置为空:wsrep_cluster_address = gcomm://
wsrep_node_address = 10.46.10.1 #本机内网ip  
wsrep_provider = /usr/lib64/libgalera_smm.so  
wsrep_sst_method = xtrabackup-v2  
#如果是5.5可以设置为:wsrep_sst_method = xtrabackup
wsrep_provider_options = 'socket.checksum = 1;'

#xtrabackup rsync
wsrep_sst_auth = sst:sstpasswd  # 用于同步的账号和密码  
innodb_locks_unsafe_for_binlog = 1  
innodb_autoinc_lock_mode = 2

# server id
# server-id = 1

# other settings
[mysqld_safe]
log-error = /mnt/mysql/log/mysqld.log  
log-queries-not-using-indexes  = 1  
slow-query-log                 = 1  
slow-query-log-file            = /mnt/mysql/log/mysql-slow.log  
pid-file = /mnt/mysql/run/mysqld.pid  
open-files-limit = 8192

[mysqlhotcopy]
interactive-timeout

[client]
port = 3306  
socket = /mnt/mysql/run/mysqld.sock  
default-character-set = utf8

[mysqldump]
# 不要在将内存中的整个结果写入磁盘之前缓存. 在导出非常巨大的表时需要此项
quick

max_allowed_packet = 32M  
初始化第一个Node:

首次启动第一个Node时需要在my.cnf中设置:wsrep_cluster_address = gcomm://

service mysql start  
mysql -uroot  

在MySQL中创建同步账号并修改root密码

UPDATE user SET Password = PASSWORD('rootpasswd') WHERE user = 'root';  
#创建同步账号
GRANT USAGE ON *.* to sst@'%' IDENTIFIED BY 'sstpasswd';  
GRANT ALL PRIVILEGES on *.* to sst@'%';  
FLUSH PRIVILEGES;  
quit  
#在实际应用中,sst的权限应做限制
添加新的 Node 到Cluster中:

重复上面步骤,注意my.cnf中的wsrep_cluster_address = gcomm://这里要填写第一个Node的IP地址, 然后启动MySQL:

Starting MySQL (Percona XtraDB Cluster)....SST in progress, setting sleep higher  
.. SUCCESS!

会看到同步信息,以此类推安装第三个或者多个Node。数据自动同步了,在 XtraDB Cluster 中,没有主从的概念,它是一个Multi-Master的同步方案,支持在任意Node上写入数据,其它Node自动同步;