12 percona-xtradb-cluster · 王康宁的笔记

1、安装安装地址：https://www.percona.com/downloads/ yum -y remove Percona-Server* yum -y install percona-xtrabackup-24* yum -y install Percona-XtraDB-Cluster-57* 报错：Error: Package: percona-xtrabackup-24-2.4.11-1.el7.x86_64 (percona-cluster) Requires: libev.so.4()(64bit) 解决：安装 libev-4.15-3.el7.x86_64.rpm 报错：Error: Package: Percona-XtraDB-Cluster-server-57-5.7.21-29.26.1.el7.x86_64 (percona-cluster) Requires: qpress 解决：安装 qpress-1.1-8.1.x86_64.rpm 2、启动：service mysql start 失败报错： 2018-05-13T17:56:39.752463Z 0 [ERROR] WSREP: wsrep_load(): dlopen(): /usr/lib64/galera3/libgalera_smm.so: symbol SSL_COMP_free_compression_methods, version libssl.so.10 not defined in file libssl.so.10 with link time reference 2018-05-13T17:56:39.752477Z 0 [ERROR] WSREP: wsrep_load() failed to load the provider('/usr/lib64/galera3/libgalera_smm.so'): Invalid argument (22). Need to abort. 2018-05-13T17:56:39.752480Z 0 [ERROR] Aborting 解决方法：升级openssl 安装 openssl-1.0.2k-8.el7.x86_64.rpm openssl-libs-1.0.2k-8.el7.x86_64.rpm openssl-devel-1.0.2k-8.el7.x86_64.rpm 可以直接force安装，可以将之前版本覆盖。 3、修改root密码可以在/vr/log/mysqld.log中找到root的口令 mysql -u root -p set password=password('123456');flush privileges;quit; 4、修改wsrep.conf 第一个： [root@10-248-56-192 percona-xtradb-cluster.conf.d]# cat wsrep.cnf [mysqld] #Path to Galera library wsrep_provider=/usr/lib64/galera3/libgalera_smm.so #Cluster connection URL contains IPs of nodes #If no IP is found, this implies that a new cluster needs to be created, #in order to do that you need to bootstrap this node wsrep_cluster_address=gcomm://10.248.56.192,10.248.56.195,10.248.56.209 #In order for Galera to work correctly binlog format should be ROW binlog_format=ROW #MyISAM storage engine has only experimental support default_storage_engine=InnoDB #Slave thread to use wsrep_slave_threads= 8 wsrep_log_conflicts #This changes how InnoDB autoincrement locks are managed and is a requirement for Galera innodb_autoinc_lock_mode=2 #Node IP address wsrep_node_address=10.248.56.192 #Cluster name wsrep_cluster_name=pxc-cluster #If wsrep_node_name is not specified, then system hostname will be used wsrep_node_name=pxc-cluster-node-1 #pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER pxc_strict_mode=ENFORCING #SST method wsrep_sst_method=xtrabackup-v2 #Authentication for SST method wsrep_sst_auth="sstuser:s3cretPass" 第二个： [root@10-248-56-195 percona-xtradb-cluster.conf.d]# cat wsrep.cnf [mysqld] #Path to Galera library wsrep_provider=/usr/lib64/galera3/libgalera_smm.so #Cluster connection URL contains IPs of nodes #If no IP is found, this implies that a new cluster needs to be created, #in order to do that you need to bootstrap this node wsrep_cluster_address=gcomm://10.248.56.192,10.248.56.195,10.248.56.209 #In order for Galera to work correctly binlog format should be ROW binlog_format=ROW #MyISAM storage engine has only experimental support default_storage_engine=InnoDB #Slave thread to use wsrep_slave_threads= 8 wsrep_log_conflicts #This changes how InnoDB autoincrement locks are managed and is a requirement for Galera innodb_autoinc_lock_mode=2 #Node IP address wsrep_node_address=10.248.56.195 #Cluster name wsrep_cluster_name=pxc-cluster #If wsrep_node_name is not specified, then system hostname will be used wsrep_node_name=pxc-cluster-node-2 #pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER pxc_strict_mode=ENFORCING #SST method wsrep_sst_method=xtrabackup-v2 #Authentication for SST method wsrep_sst_auth="sstuser:s3cretPass" 第三个： [root@10-248-56-192 percona-xtradb-cluster.conf.d]# cat wsrep.cnf [mysqld] #Path to Galera library wsrep_provider=/usr/lib64/galera3/libgalera_smm.so #Cluster connection URL contains IPs of nodes #If no IP is found, this implies that a new cluster needs to be created, #in order to do that you need to bootstrap this node wsrep_cluster_address=gcomm://10.248.56.192,10.248.56.195,10.248.56.209 #In order for Galera to work correctly binlog format should be ROW binlog_format=ROW #MyISAM storage engine has only experimental support default_storage_engine=InnoDB #Slave thread to use wsrep_slave_threads= 8 wsrep_log_conflicts #This changes how InnoDB autoincrement locks are managed and is a requirement for Galera innodb_autoinc_lock_mode=2 #Node IP address wsrep_node_address=10.248.56.209 #Cluster name wsrep_cluster_name=pxc-cluster #If wsrep_node_name is not specified, then system hostname will be used wsrep_node_name=pxc-cluster-node-3 #pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER pxc_strict_mode=ENFORCING #SST method wsrep_sst_method=xtrabackup-v2 #Authentication for SST method wsrep_sst_auth="sstuser:s3cretPass" 5、启动第一个节点： systemctl restart mysql@bootstrap.service show status like 'wsrep_%'; 查看现在集群状态 6、启动第二个节点：需要将my.cnf中的sever_id与其他节点不一致 service mysql restart [root@10-248-56-195 log]# pwd /var/log/mysqld.log 2018-05-14T09:17:56.600663Z WSREP_SST: [INFO] ............Waiting for SST streaming to complete! 2018-05-14T09:17:57.737279Z 0 [Note] WSREP: (aeddf750, 'tcp://0.0.0.0:4567') turning message relay requesting off 2018-05-14T09:18:06.585389Z WSREP_SST: [ERROR] ******************* FATAL ERROR ********************** 2018-05-14T09:18:06.587149Z WSREP_SST: [ERROR] xtrabackup_checkpoints missing. xtrabackup/SST failed on DONOR. Check DONOR log 2018-05-14T09:18:06.588852Z WSREP_SST: [ERROR] ****************************************************** 2018-05-14T09:18:06.591227Z WSREP_SST: [ERROR] Cleanup after exit with status:2 2018-05-14T09:18:06.602013Z 0 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.248.56.195' --datadir '/data0/mysql/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --parent '27460' '' : 2 (No such file or directory) 主节点日志： [root@10-248-56-192 mysql]# /data0/mysql/innobackup.backup.log 报错： 180514 17:14:44 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock' as 'sstuser' (using password: YES). Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock','sstuser',...) failed: Access denied for user 'sstuser'@'localhost' (using password: YES) at - line 1314. 180514 17:14:44 Connecting to MySQL server host: localhost, user: sstuser, password: set, port: not set, socket: /var/lib/mysql/mysql.sock Failed to connect to MySQL server: Access denied for user 'sstuser'@'localhost' (using password: YES). 解决：在主节点mysql内添加sstuser 用户： create user 'sstuser'@'%' identified by 's3cretPass'; 报错：180514 17:18:06 Executing LOCK TABLES FOR BACKUP... Error: failed to execute query LOCK TABLES FOR BACKUP: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation ----提示：缺少reload权限查看用户权限： select user,host,Reload_priv from user where user='sstuser'; 授权： GRANT ALL PRIVILEGES ON *.* TO 'sstuser'@'localhost' IDENTIFIED BY 's3cretPass' WITH GRANT OPTION; Delete FROM user Where User='sstuser' and Host='%'; 7、启动第三个节点 service mysql restart 8、测试需要每个表建立主键 mysql> insert into kangning values (1,'ddd'),(2,'xxx'),(3,'ssss'); ERROR 1105 (HY000): Percona-XtraDB-Cluster prohibits use of DML command on a table (test.kangning) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER mysql> alter table kangning add primary key(id); mysql> insert into kangning values (1,'ddd'),(2,'xxx'),(3,'ssss'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0