部署Percona XtraDB Cluster高可用和多Master集群

最新推荐文章于 2021-02-11 07:42:18 发布

weixin_33695082

最新推荐文章于 2021-02-11 07:42:18 发布

阅读量128

点赞数

文章标签： python 数据库

原文链接：https://my.oschina.net/anthonyyau/blog/276775

版权

2019独角兽企业重金招聘Python工程师标准>>>

环境情况：

系统：CentOS release 6.4 (Final)，2.6.32-358.el6.x86_64

PXC版本：二进制版本，5.5.37-35.0-25.10 Percona XtraDB Cluster binary (GPL) 5.5.37-25.10, Revision 756, wsrep_25.10.r3985

集群节点：

在每个节点安装PXC：

tar -xvf Percona-XtraDB-Cluster-5.5.37-rel35.0-25.10.756.Linux.x86_64.tar.gz -C /opt
ln -s /opt/Percona-XtraDB-Cluster-5.5.37-rel35.0-25.10.756.Linux.x86_64 /opt/PXC

配置节点mariadb01：

简单my.cnf配置文件

[mysqld]
datadir=/opt/PXC/data
binlog_format=ROW
#存储引擎设置为innodb，myisam仅仅测试支持
default-storage-engine=innodb
#指定innodb 自增长列锁模式，2为交叉锁模式， 多个语句能同时执行
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0
#指定Galera库文件，PXC自带该库文件，mariadb galera需要安装galera
wsrep_provider=/opt/PXC/lib/libgalera_smm.so
wsrep_cluster_name="my_wsrep_cluster"
#指定集群每个节点的连接地址
wsrep_cluster_address="gcomm://10.0.60.104,10.0.60.105,10.0.60.106"
wsrep_slave_threads=1
wsrep_certify_nonPK=1
wsrep_max_ws_rows=131072
wsrep_max_ws_size=1073741824
wsrep_debug=1
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=
#指定SST方式，支持rsync(最快，需要锁表)，mysqldump和xtrabackup，从5.5.33-23.7.6起支持xtrabackup-v2
wsrep_sst_method=xtrabackup-v2
#SST的认证方式
wsrep_sst_auth=sstuser:sstuser

启动mariadb01节点：

./bin/mysqld_safe --defaults-file=my.cnf --wsrep-cluster-address="gcomm://" &

创建SST用户和进行相应的授权：

DELETE FROM mysql.user WHERE user='';
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost' IDENTIFIED BY 'sstuser';;
FLUSH PRIVILEGES;

配置节点mariadb02：

mysql> \! cat /opt/PXC/my.cnf |grep -v ^#|grep -v ^$

[mysqld]
datadir=/opt/PXC/data
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0
wsrep_provider=/opt/PXC/lib/libgalera_smm.so
wsrep_cluster_name="my_wsrep_cluster"
wsrep_cluster_address="gcomm://10.0.60.104,10.0.60.105,10.0.60.106"
wsrep_slave_threads=1
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
wsrep_sst_auth=sstuser:sstuser

启动mariadb02节点：

./bin/mysqld_safe --defaults-file=my.cnf &

配置节点mariadb03：

mysql> \! cat /opt/PXC/my.cnf |grep -v ^#|grep -v ^$

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0
wsrep_provider=/opt/PXC/lib/libgalera_smm.so
wsrep_cluster_name="my_wsrep_cluster"
wsrep_cluster_address="gcomm://10.0.60.104,10.0.60.105,10.0.60.106"
wsrep_slave_threads=1
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
wsrep_sst_auth=sstuser:sstuser

启动mariadb03节点：

./bin/mysqld_safe --defaults-file=my.cnf &

检查状态：

mariadb01>show status like 'wsrep%'; +----------------------------+----------------------------------------------------+ | Variable_name | Value | +----------------------------+----------------------------------------------------+ | wsrep_local_state_uuid | 961de113-ed93-11e3-95a0-934e3df4463f | | wsrep_protocol_version | 4 | | wsrep_last_committed | 8 | | wsrep_replicated | 1 | | wsrep_replicated_bytes | 180 | | wsrep_received | 5 | | wsrep_received_bytes | 798 | | wsrep_local_commits | 1 | | wsrep_local_cert_failures | 0 | | wsrep_local_replays | 0 | | wsrep_local_send_queue | 0 | | wsrep_local_send_queue_avg | 0.000000 | | wsrep_local_recv_queue | 0 | | wsrep_local_recv_queue_avg | 0.000000 | | wsrep_flow_control_paused | 0.000000 | | wsrep_flow_control_sent | 0 | | wsrep_flow_control_recv | 0 | | wsrep_cert_deps_distance | 1.000000 | | wsrep_apply_oooe | 0.000000 | | wsrep_apply_oool | 0.000000 | | wsrep_apply_window | 0.000000 | | wsrep_commit_oooe | 0.000000 | | wsrep_commit_oool | 0.000000 | | wsrep_commit_window | 0.000000 | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | wsrep_cert_index_size | 4 | | wsrep_causal_reads | 0 | | wsrep_incoming_addresses | 10.0.60.104:3306,10.0.60.105:3306,10.0.60.106:3306 | | wsrep_cluster_conf_id | 3 | | wsrep_cluster_size | 3 | | wsrep_cluster_state_uuid | 961de113-ed93-11e3-95a0-934e3df4463f | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | wsrep_local_bf_aborts | 0 | | wsrep_local_index | 0 | | wsrep_provider_name | Galera | | wsrep_provider_vendor | Codership Oy <info@codership.com> | | wsrep_provider_version | 2.10(r175) | | wsrep_ready | ON | +----------------------------+----------------------------------------------------+ 40 rows in set (0.00 sec)