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