ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > MySQL > PXC（Percona XtraDB Cluster）集群的安装

PXC（Percona XtraDB Cluster）集群的安装

原创 MySQL 作者：小亮520cl 时间：2015-11-12 10:19:11 0 删除 编辑

1规划

node1
192.168.6.119
node2
192.168.6.121
node3
192.168.6.122


2 安装（所有节点上都做）
  1. 设置selinux为disable
  2. SELINUX=disabled
  3. SELINUXTYPE=targeted
 $ setenforce 0 立即生效
  1. 测试时可以关闭防火墙
  2. 生产环境需要打开端口：3306, 4444, 4567 and 4568
 安装percona yum源
[root@localhost ~]# rpm -Uhv http://www.percona.com/downloads ... se-0.0-1.x86_64.rpm
Retrieving http://www.percona.com/downloads ... se-0.0-1.x86_64.rpm
Preparing...                ########################################### [100%]
1:percona-release        ########################################### [100%]

安装epel yum源
。。。。。。

安装percona包
[root@localhost ~]# rpm -aq | grep -i percona
Percona-XtraDB-Cluster-galera-3-3.12.2-1.rhel5
percona-xtrabackup-2.3.2-1.el5
Percona-XtraDB-Cluster-client-55-5.5.41-25.11.853.el5
Percona-XtraDB-Cluster-server-55-5.5.41-25.11.853.el5
Percona-Server-shared-compat-5.1.68-rel14.6.551.rhel5
percona-release-0.1-3


3 编辑参数文件
编辑第一个节点
  1. [root@localhost ~]# more /etc/my.cnf
  2. [mysqld]

  4. # GENERAL
  5. datadir=/var/lib/mysql
  6. socket=/var/lib/mysql/mysql.sock
  7. pid_file=/var/lib/mysql/mysql.pid
  8. user=mysql
  9. port=3306
  10. default_storage_engine=InnoDB
  11. character_set_server=utf8
  12. log_bin=mysqlbin
  13. # INNODB/XtraDB
  14. innodb_buffer_pool_size=1610612736    ----这个地方要注意啊。在别的节点上不要注释掉，不然别的节点启动的时候一直报错
  15. innodb_log_file_size=104857600

  17. #PXC
  18. wsrep_provider=/usr/lib64/libgalera_smm.so
  19. wsrep_cluster_address=gcomm://192.168.6.119,192.168.6.121,192.168.6.121
  20. binlog_format=ROW
  21. innodb_autoinc_lock_mode=2
  22. wsrep_node_address=192.168.6.119
  23. wsrep_sst_receive_address=192.168.6.119     ---第二个，第三个节点一样的，只是这个ip分别换了
  24. server_id=000119
  25. wsrep_sst_method=xtrabackup-v2
  26. wsrep_cluster_name=my_mysql_cluster
  27. wsrep_sst_auth="sstuser:sstuser"            ----集群用户

  29. # LOGGING
  30. log-error=/var/lib/mysql/mysql-error.log
  31. slow_query_log=1
  32. slow_query_log_file=/var/lib/mysql/mysql-slow.log

  34. # OTHER

  36. [client]
  37. socket=/var/lib/mysql/mysql.sock
  38. port=3306
  39. # END

启动
[root@localhost ~]# /etc/init.d/mysql start --wsrep-cluster-address="gcomm://"
Starting MySQL (Percona XtraDB Cluster).. SUCCESS! 
或者使用这种启动
[root@localhost ~]/etc/init.d/mysql bootstrap-pxc
mysql> show status like 'wsrep%'

点击(此处)折叠或打开

  1. mysql> show status like 'wsrep%';
  2. +------------------------------+---------------------------------------+
  3. | Variable_name | Value |
  4. +------------------------------+---------------------------------------+
  5. | wsrep_local_state_uuid | a36a6abc-8829-11e5-bd81-fa6fd7dadaec |
  6. | wsrep_protocol_version | 7 |
  7. | wsrep_last_committed | 38253 |
  8. | wsrep_replicated | 38246 |
  9. | wsrep_replicated_bytes | 16341882 |
  10. | wsrep_repl_keys | 114381 |
  11. | wsrep_repl_keys_bytes | 1794706 |
  12. | wsrep_repl_data_bytes | 12099432 |
  13. | wsrep_repl_other_bytes | 0 |
  14. | wsrep_received | 400 |
  15. | wsrep_received_bytes | 7131 |
  16. | wsrep_local_commits | 37932 |
  17. | wsrep_local_cert_failures | 104 |
  18. | wsrep_local_replays | 0 |
  19. | wsrep_local_send_queue | 0 |
  20. | wsrep_local_send_queue_max | 228 |
  21. | wsrep_local_send_queue_min | 0 |
  22. | wsrep_local_send_queue_avg | 10.807747 |
  23. | wsrep_local_recv_queue | 0 |
  24. | wsrep_local_recv_queue_max | 2 |
  25. | wsrep_local_recv_queue_min | 0 |
  26. | wsrep_local_recv_queue_avg | 0.012500 |
  27. | wsrep_local_cached_downto | 7 |
  28. | wsrep_flow_control_paused_ns | 19690538000 |
  29. | wsrep_flow_control_paused | 0.001397 |
  30. | wsrep_flow_control_sent | 0 |
  31. | wsrep_flow_control_recv | 524 |
  32. | wsrep_cert_deps_distance | 7.398894 |
  33. | wsrep_apply_oooe | 0.827072 |
  34. | wsrep_apply_oool | 0.044831 |
  35. | wsrep_apply_window | 141.161812 |
  36. | wsrep_commit_oooe | 0.000000 |
  37. | wsrep_commit_oool | 0.000000 |
  38. | wsrep_commit_window | 140.036573 |
  39. | wsrep_local_state | 4 |
  40. | wsrep_local_state_comment | Synced |
  41. | wsrep_cert_index_size | 13 |
  42. | wsrep_cert_bucket_count | 210 |
  43. | wsrep_gcache_pool_size | 17954373 |
  44. | wsrep_causal_reads | 0 |
  45. | wsrep_cert_interval | 145.261227 |
  46. | wsrep_incoming_addresses | 192.168.6.119:3306,192.168.6.121:3306 |
  47. | wsrep_evs_delayed | |
  48. | wsrep_evs_evict_list | |
  49. | wsrep_evs_repl_latency | 0/0/0/0/0 |
  50. | wsrep_evs_state | OPERATIONAL |
  51. | wsrep_gcomm_uuid | 0636fa24-8844-11e5-83bd-9bdf3f03c13f |
  52. | wsrep_cluster_conf_id | 13 |
  53. | wsrep_cluster_size | 2 |
  54. | wsrep_cluster_state_uuid | a36a6abc-8829-11e5-bd81-fa6fd7dadaec |
  55. | wsrep_cluster_status | Primary |
  56. | wsrep_connected | ON |
  57. | wsrep_local_bf_aborts | 0 |
  58. | wsrep_local_index | 0 |
  59. | wsrep_provider_name | Galera |
  60. | wsrep_provider_vendor | Codership Oy <info@codership.com> |
  61. | wsrep_provider_version | 3.12.2(rf3e626d) |
  62. | wsrep_ready | ON |
  63. | wsrep_thread_count | 2 |
  64. +------------------------------+---------------------------------------+
  65. 59 rows in set (0.00 sec)

进入数据库为其他节点做准备工作
mysql> UPDATE mysql.user SET password=PASSWORD("Passw0rd") where user='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0
#修改root用户密码(也可以不修改)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'sstuser';
Query OK, 0 rows affected (0.00 sec)
#增加用户sstuser
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
#赋予权限
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

第二个节点
编辑my.cnf配置文件
启动mysql：/etc/init.d/mysql start    ---启动方式和上面有区别
Starting MySQL (Percona XtraDB Cluster)...SST in progress, setting sleep higher
.. SUCCESS!


d第三个节点
编辑my.cnf配置文件
启动mysql：/etc/init.d/mysql start   ---启动方式和上面有区别
Starting MySQL (Percona XtraDB Cluster)...SST in progress, setting sleep higher
.. SUCCESS!


到此搭建完毕：不管在哪个节点进行操作，所有的操作会复制到整个集群！

补充：上面的方式搭建好麻烦还总是报错，按照Mariadb Galar cluster搭建方式简单容易

  1. 环境
  2. 121
  3. 119
  4. 122

121：
  1. [root@node4 mysql]# more /etc/my.cnf
  2. [mysqld]
  3. datadir=/var/lib/mysql
  4. socket=/var/lib/mysql/mysql.sock
  5. pid_file=/var/lib/mysql/mysql.pid
  6. user=mysql
  7. port=3306
  8. character_set_server=utf8
  9. log_bin=mysqlbin
  10. innodb_buffer_pool_size=200M
  11. innodb_log_file_size=50M
  12. binlog_format=ROW
  13. innodb_autoinc_lock_mode=2
  14. server_id=000121
  15. log-error=/var/lib/mysql/mysql-error.log
  16. slow_query_log=1
  17. slow_query_log_file=/var/lib/mysql/mysql-slow.log



  21. wsrep_provider=/usr/lib64/libgalera_smm.so
  22. wsrep_cluster_address="gcomm://"
  23. wsrep_sst_auth="sstuser:sstuser"
  24. wsrep_sst_method=rsync


  27. [client]
  28. socket=/var/lib/mysql/mysql.sock
  29. port=3306

122：
  1. [mysqld]

  3. # GENERAL
  4. datadir=/var/lib/mysql
  5. socket=/var/lib/mysql/mysql.sock
  6. pid_file=/var/lib/mysql/mysql.pid
  7. user=mysql
  8. port=3306
  9. character_set_server=utf8
  10. log_bin=mysqlbin
  11. innodb_buffer_pool_size=200M
  12. innodb_log_file_size=50M
  13. binlog_format=ROW
  14. innodb_autoinc_lock_mode=2
  15. server_id=000121
  16. log-error=/var/lib/mysql/mysql-error.log
  17. slow_query_log=1
  18. slow_query_log_file=/var/lib/mysql/mysql-slow.log





  24. wsrep_provider=/usr/lib64/libgalera_smm.so
  25. wsrep_cluster_address="gcomm://192.168.6.121:4567"
  26. wsrep_sst_auth="sstuser:sstuser"
  27. wsrep_sst_method=rsync

  29. [client]
  30. socket=/var/lib/mysql/mysql.sock
  31. port=3306

119：
  1. [mysqld]

  3. # GENERAL
  4. datadir=/var/lib/mysql
  5. socket=/var/lib/mysql/mysql.sock
  6. pid_file=/var/lib/mysql/mysql.pid
  7. user=mysql
  8. port=3306
  9. character_set_server=utf8
  10. log_bin=mysqlbin
  11. innodb_buffer_pool_size=200M
  12. innodb_log_file_size=50M
  13. binlog_format=ROW
  14. innodb_autoinc_lock_mode=2
  15. server_id=000121
  16. log-error=/var/lib/mysql/mysql-error.log
  17. slow_query_log=1
  18. slow_query_log_file=/var/lib/mysql/mysql-slow.log





  24. wsrep_provider=/usr/lib64/libgalera_smm.so
  25. wsrep_cluster_address="gcomm://192.168.6.122:4567"
  26. wsrep_sst_auth="sstuser:sstuser"
  27. wsrep_sst_method=rsync

  29. [client]
  30. socket=/var/lib/mysql/mysql.sock
  31. port=3306



1.122关闭mysql，自动从集群中被踢出
[root@localhost ~]# /etc/init.d/mysql stop
mysql> show status like 'wsrep%';
+----------------------------+----------------------------------------------------------+
| Variable_name              | Value                                                    |
+----------------------------+----------------------------------------------------------+
| wsrep_incoming_addresses   | 192.168.6.119:3306,192.168.6.121:3306 |



2.模拟此时集群还有数据写入，如创建一个bvoid数据库



3.恢复122mysql，自动加入集群

mysql> show status like 'wsrep%';
+----------------------------+----------------------------------------------------------+
| Variable_name              | Value                                                    |
+----------------------------+----------------------------------------------------------+
| wsrep_incoming_addresses   | 192.168.6.119:3306,192.168.6.122:3306,192.168.6.121:3306 |

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bvoid              |  关闭阶段产生的数据也会自动同步到122上，是通过rsync的方式同步的！



来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29096438/viewspace-1831964/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 【MySQL】计算 TPS,QPS 的方式
下一篇： 【Mysql】mysql5.7新特性之-并行复制实现原理与调优
请登录后发表评论 登录
全部评论
小亮520cl
专家姓名：陈亮
毕业以后专业任职数据库工程师职位，itpub一直作为自己的笔记记录的地方,blog写的不详细，草书笔记，仅供参考！

注册时间：2013-09-12

  • 博文量
    529
  • 访问量
    1126940

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593