热门标签
热门文章
- 1还在用手画太阳花，太out了，python教你如何轻松画一朵！_idle如何画一朵花
- 2“Git：不容错过的现代化版本控制系统” 基础入门篇_初探git,理解和使用版本控制的魔法
- 3c++ STL less 的视角
- 4Java正则表达式汇总_java正则表达式,不能输入引号(单引号和双引号)以及特殊字符<、>、#和&
- 5C语言的printf的介绍_printf("%g%+gi\n", a.rp, a.ip);
- 6【蓝桥杯选拔赛真题50】python充电器 青少年组蓝桥杯python 选拔赛STEMA比赛真题解析_蓝桥杯青少组stema
- 7框架技术 ---- SPA和Vue的组件化开发_vue和spa
- 8git命令（使用git review）
- 9【Django】Django实现对IP地址访问过滤_django 限制 国内 ip 访问
- 10图像处理： 马赛克艺术_马赛克是什么纹理
当前位置: article > 正文
如何搭建Percona XtraDB Cluster（PXC）集群_perconor cluster
作者：Gausst松鼠会 | 2024-02-16 00:03:24
赞
踩
perconor cluster
my.cnf中关闭sql_mode=ONLY_FULL_GROUP_BY
1、关闭防火墙或者允许3306, 4444, 4567和4568四个端口的连接
3306 数据库对外提供服务的端口
4444 镜像数据传输SST，集群数据同步端口，全量同步，新节点加入时起作用
4567 集群节点间相互通信的端口
4568 增量数据同步IST，节点下线、重启后使用该端口，增量同步数据。
#开放3306, 4444, 4567和4568四个端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --zone=public --add-port=4444/tcp --permanent
firewall-cmd --zone=public --add-port=4567/tcp --permanent
firewall-cmd --zone=public --add-port=4568/tcp --permanent
firewall-cmd --reload
firewall-cmd --list-ports
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
[root@localhost ~]# rpm -e --nodeps mariadb mariadb-server mariadb-libs marisa --卸载
- 1
- 2
1、3个节点安装依赖包
yum install -y git scons gcc* gcc-c++ openssl* check cmake bison \
boost-devel asio-devel libaio-devel ncurses-devel readline-devel pam-devel \
socat perl-time-hires perl-io-socket-ssl.noarch perl-dbd-mysql.x86_64
- 1
- 2
- 3
- 4
2、CentOS上配置Percona存储库
1、安装存储库包：
sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm
软件安装在默认路径：/var/lib/mysql
- 1
- 2
- 3
- 4
- 5
如果成功，应该看到以下内容：
Installed:
percona-release.noarch 0:0.1-6
Complete!
- 1
- 2
- 3
- 4
- 5
3、确保Percona包可用：
sudo yum list | grep percona
- 1
- 2
输出以下内容：
percona-release.noarch 0.1-6 @/percona-release-0.1-6.noarch
Percona-Server-55-debuginfo.x86_64 5.5.54-rel38.7.el7 percona-release-x86_64
Percona-Server-56-debuginfo.x86_64 5.6.35-rel81.0.el7 percona-release-x86_64
Percona-Server-57-debuginfo.x86_64 5.7.17-13.1.el7 percona-release-x86_64
...
- 1
- 2
- 3
- 4
- 5
- 6
4、安装Percona XtraDB Cluster软件包：
sudo yum install Percona-XtraDB-Cluster-57 -y
软件安装在默认路径 /var/lib/mysql
- 1
- 2
- 3
- 4
遇到的问题：yum安装失败
报错信息如下： Transaction check error: file /etc/my.cnf conflicts between attempted installs of Percona-XtraDB-Cluster-server-57-5.7.21-29.26.1.el7.x86_64 and MariaDB-common-10.2.11-1.el7.centos.x86_64 file /usr/lib64/mysql/plugin/dialog.so conflicts between attempted installs of Percona-XtraDB-Cluster-server-57-5.7.21-29.26.1.el7.x86_64 and MariaDB-common-10.2.11-1.el7.centos.x86_64 有两文件存在冲突，应该是前面安装mariadb的残留文件。 于是删除两文件，再次yum安装： [root@node1 ~]#ls /etc/my.cnf my.cnf my.cnf.d/ [root@node1 ~]#ls /etc/my.cnf.d/mysql-clients.cnf /etc/my.cnf.d/mysql-clients.cnf [root@node1 ~]#rm -rf /etc/my.cnf [root@node1 ~]#rm -rf /etc/my.cnf.d/ 再次报错，信息如下： Transaction check error: file /etc/my.cnf conflicts between attempted installs of Percona-XtraDB-Cluster-server-57-5.7.21-29.26.1.el7.x86_64 and MariaDB-common-10.2.11-1.el7.centos.x86_64 file /usr/lib64/mysql/plugin/dialog.so conflicts between attempted installs of Percona-XtraDB-Cluster-server-57-5.7.21-29.26.1.el7.x86_64 and MariaDB-common-10.2.11-1.el7.centos.x86_64 卸载mariadb root@node1 ~]#yum remove "mariadb*" …… …… Erasing : 2:postfix-2.10.1-6.el7.x86_64 1/2 Erasing : 1:mariadb-libs-5.5.56-2.el7.x86_64 2/2 Verifying : 1:mariadb-libs-5.5.56-2.el7.x86_64 1/2 Verifying : 2:postfix-2.10.1-6.el7.x86_64 2/2 Removed: mariadb-libs.x86_64 1:5.5.56-2.el7 Dependency Removed: postfix.x86_64 2:2.10.1-6.el7 Complete!
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
6、修改默认密码：
[root@mysql-pxc-02_120 ~]#systemctl start mysqld
[root@mysql-pxc-02_120 ~]# grep 'temporary password' /var/log/mysqld.log
2018-08-23T04:10:00.459951Z 1 [Note] A temporary password is generated for root@localhost: HPemhef0sot<I
mysql> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
/var/log/mysqld.log 中找不到临时密码
1.删除原来安装过的mysql残留的数据（这一步非常重要，问题就出在这）
rm -rf /var/lib/mysql
2.重启mysqld服务
systemctl restart mysqld
3.再去找临时密码
grep 'temporary password' /var/log/mysqld.log
- 1
- 2
- 3
- 4
- 5
- 6
- 7
7、修改/etc/my.cnf配置文件
在修改配置文件之前，需要先停止mysqld服务
[root@mysql-pxc-02_120 ~]#systemctl stop mysqld
[root@mysql-pxc-02_120 ~]#vi /etc/my.cnf
- 1
- 2
- 3
- 4
# # The Percona XtraDB Cluster 5.7 configuration file. # # # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # Please make any edits and changes to the appropriate sectional files # included below. # # # The Percona XtraDB Cluster 5.7 configuration file. # # # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # Please make any edits and changes to the appropriate sectional files # included below. # [mysqld] !includedir /etc/my.cnf.d/ !includedir /etc/percona-xtradb-cluster.conf.d/ wsrep_provider=/usr/lib64/galera3/libgalera_smm.so #Galera库文件路径 wsrep_cluster_name=pxc-cluster #集群的逻辑名称，各节点应该统一 wsrep_cluster_address=gcomm://192.168.111.119,192.168.111.120,192.168.111.121 #列出集群内所有节点的IP wsrep_node_name=PXC-01 #当前节点的逻辑名称 wsrep_node_address=192.168.111.119 #当前节点的IP wsrep_sst_method=xtrabackup-v2 #全量同步（SST）方式 wsrep_sst_auth=harson:123456 binlog_format=ROW #binlog格式，PXC只支持格式为ROW的binlog default_storage_engine=InnoDB #PXC对InnoDB存储引擎有最好的支持 innodb_autoinc_lock_mode=2 #在向有auto_increment 列的表插入数据时，PXC只支持interleaved(2)交错锁 pxc_strict_mode=ENFORCING #PXC严格模式，建议开启
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
PXC 5.7集群参数具体描述:
wsrep_cluster_name 指定您的群集的逻辑名称。对于群集中的所有节点，它必须相同。 wsrep_cluster_address 指定群集中节点的IP地址。节点加入集群至少需要一个，但建议列出所有节点的地址。这样，如果列表中的第一个节点不可用，则加入节点可以使用其他地址。 注意：群集中的初始节点不需要地址。但是，建议指定它们并正确引导第一个节点。这将确保节点将来能够重新加入集群。 wsrep_node_name 指定每个单独节点的逻辑名称。如果未指定此变量，则将使用主机名称。 wsrep_node_address 指定该特定节点的IP地址。 wsrep_sst_method 默认情况下，Percona XtraDB集群使用Percona XtraBackup进行状态快照传输（SST）。 强烈建议设置wsrep_sst_method=xtrabackup-v2。 这种方法需要用户在初始节点上建立SST。用wsrep_sst_auth变量提供SST用户凭据。 wsrep_sst_auth 指定认证凭证SST 作为:。您必须在引导第一个节点时创建此用户 并为其提供必要的权限： 1 mysql> CREATE USER 'hanson'@'localhost' IDENTIFIED BY 'passw0rd'; 2 mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'hanson'@'localhost'; 3 mysql> FLUSH PRIVILEGES; pxc_strict_mode PXC严格模式在默认情况下ENFORCING处于启用状态，并设置为阻止在Percona XtraDB集群中使用实验和不支持的功能。 为确保数据强一致性，建议至少启用PERMISSIVE模式 关于这个参数的具体描述可以参考：Percona XtraDB Cluster Strict Mode(PXC 5.7 ) binlog_format Galera只支持行级复制，所以设置binlog_format=ROW。 default_storage_engine Galera完全支持InnoDB存储引擎。它不能与MyISAM或任何其他非事务性存储引擎正常工作。将此变量设置为default_storage_engine=InnoDB。 innodb_autoinc_lock_mode Galera仅支持InnoDB的交错（2）锁定模式。设置传统（0）或连续（1）锁定模式会导致复制失败，因为未解决的死锁。将此变量设置为innodb_autoinc_lock_mode=2
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
启动不了 可能是my.cnf权限不够
初始化集群
[root@mysql-pxc-01_119 ~]# systemctl start mysql@bootstrap.service [root@mysql-pxc-01_119 ~]# systemctl status mysql@bootstrap.service ● mysql@bootstrap.service - Percona XtraDB Cluster with config /etc/sysconfig/mysql.bootstrap Loaded: loaded (/usr/lib/systemd/system/mysql@.service; disabled; vendor preset: disabled) Active: active (running) since Thu 2018-08-23 19:11:17 CST; 1min 29s ago Process: 4959 ExecStartPost=/usr/bin/mysql-systemd start-post $MAINPID (code=exited, status=0/SUCCESS) Process: 4918 ExecStartPre=/usr/bin/mysql-systemd start-pre (code=exited, status=0/SUCCESS) Main PID: 4958 (mysqld_safe) CGroup: /system.slice/system-mysql.slice/mysql@bootstrap.service ├─4958 /bin/sh /usr/bin/mysqld_safe --basedir=/usr --wsrep-new-cluster └─5520 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --wsrep-provider=/usr/lib64/galera3/libgalera_smm.... Aug 23 19:11:07 mysql-pxc-01_119 systemd[1]: Starting Percona XtraDB Cluster with config /etc/sysconfig/mysql.bootstrap... Aug 23 19:11:07 mysql-pxc-01_119 mysql-systemd[4959]: State transfer in progress, setting sleep higher Aug 23 19:11:08 mysql-pxc-01_119 mysqld_safe[4958]: 2018-08-23T11:11:08.126278Z mysqld_safe Logging to '/var/log/mysqld.log'. Aug 23 19:11:08 mysql-pxc-01_119 mysqld_safe[4958]: 2018-08-23T11:11:08.129528Z mysqld_safe Logging to '/var/log/mysqld.log'. Aug 23 19:11:08 mysql-pxc-01_119 mysqld_safe[4958]: 2018-08-23T11:11:08.156421Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql Aug 23 19:11:08 mysql-pxc-01_119 mysqld_safe[4958]: 2018-08-23T11:11:08.169802Z mysqld_safe Skipping wsrep-recover for 1d40208d-a6b7-11e8-8115-2e1d6e4b6ea1:5 pair Aug 23 19:11:08 mysql-pxc-01_119 mysqld_safe[4958]: 2018-08-23T11:11:08.171468Z mysqld_safe Assigning 1d40208d-a6b7-11e8-8115-2e1d6e4b6ea1:5 to wsrep_start_position Aug 23 19:11:17 mysql-pxc-01_119 mysql-systemd[4959]: SUCCESS! Aug 23 19:11:17 mysql-pxc-01_119 systemd[1]: Started Percona XtraDB Cluster with config /etc/sysconfig/mysql.bootstrap. 监听端口3306和4567： [root@mysql-pxc-01_119 ~]# ss -ntl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* LISTEN 0 128 *:4567 *:* LISTEN 0 128 :::22 :::* LISTEN 0 80 :::3306 :::* #在初始化启动节点1的mysql后，需要创建用于节点直接同步数据的账户。 mysql> GRANT PROCESS,RELOAD,LOCK TABLES,REPLICATION CLIENT ON *.* TO 'harson'@'192.168.%.%' IDENTIFIED BY '123456'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.05 sec) #show status like 'wsrep%'查看PXC集群当前状态 mysql> show status like 'wsrep%'; +----------------------------------+--------------------------------------+ | Variable_name | Value | +----------------------------------+--------------------------------------+ | wsrep_local_state_uuid | 1869fabe-6145-11e8-8589-9740e28b11bb | | wsrep_protocol_version | 8 | | wsrep_last_applied | 4 | | wsrep_last_committed | 4 | | wsrep_replicated | 2 | | wsrep_replicated_bytes | 504 | | wsrep_gcomm_uuid | 3d64d29d-6199-11e8-982a-8fce70c54ebc | | wsrep_cluster_conf_id | 1 | | wsrep_cluster_size | 1 #集群中的节点数 | | …… | …… | | 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 | 3.26(rac090bc) | | wsrep_ready | ON | +----------------------------------+--------------------------------------+ 68 rows in set (0.01 sec) #开启MySQL远程访问权限 允许远程连接 mysql> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select host,user from user; +-------------+---------------+ | host | user | +-------------+---------------+ | 192.168.%.% | harson | | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-------------+---------------+ 4 rows in set (0.00 sec) mysql> update user set host='%' where user='root'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.02 sec) #开启另外两台： systemctl start mysqld [root@mysql-pxc-01_119 ~]# systemctl status mysql.service ● mysql.service - Percona XtraDB Cluster Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; vendor preset: disabled) Active: failed (Result: exit-code) since Fri 2018-09-07 14:25:30 CST; 30s ago Process: 6546 ExecStopPost=/usr/bin/mysql-systemd stop-post (code=exited, status=0/SUCCESS) Process: 6517 ExecStop=/usr/bin/mysql-systemd stop (code=exited, status=2) Process: 5784 ExecStartPost=/usr/bin/mysql-systemd start-post $MAINPID (code=exited, status=1/FAILURE) Process: 5783 ExecStart=/usr/bin/mysqld_safe --basedir=/usr (code=exited, status=1/FAILURE) Process: 5743 ExecStartPre=/usr/bin/mysql-systemd start-pre (code=exited, status=0/SUCCESS) Main PID: 5783 (code=exited, status=1/FAILURE) Sep 07 14:25:29 mysql-pxc-01_119 mysql-systemd[5784]: ERROR! mysqld_safe with PID 5783 has already exited: FAILURE Sep 07 14:25:29 mysql-pxc-01_119 systemd[1]: mysql.service: control process exited, code=exited status=1 Sep 07 14:25:29 mysql-pxc-01_119 mysql-systemd[6517]: WARNING: mysql pid file /var/run/mysqld/mysqld.pid empty or not readable Sep 07 14:25:29 mysql-pxc-01_119 mysql-systemd[6517]: ERROR! mysql already dead Sep 07 14:25:29 mysql-pxc-01_119 systemd[1]: mysql.service: control process exited, code=exited status=2 Sep 07 14:25:30 mysql-pxc-01_119 mysql-systemd[6546]: WARNING: mysql pid file /var/run/mysqld/mysqld.pid empty or not readable Sep 07 14:25:30 mysql-pxc-01_119 mysql-systemd[6546]: WARNING: mysql may be already dead Sep 07 14:25:30 mysql-pxc-01_119 systemd[1]: Failed to start Percona XtraDB Cluster. Sep 07 14:25:30 mysql-pxc-01_119 systemd[1]: Unit mysql.service entered failed state. Sep 07 14:25:30 mysql-pxc-01_119 systemd[1]: mysql.service failed. [root@mysql-pxc-03_121 ~]# systemctl start mysqld Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details. [root@mysql-pxc-03_121 ~]# systemctl status mysql.service ● mysql.service - Percona XtraDB Cluster Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; vendor preset: disabled) Active: failed (Result: exit-code) since Sat 2018-09-08 01:59:55 CST; 2s ago Process: 1802 ExecStartPre=/usr/bin/mysql-systemd start-pre (code=exited, status=1/FAILURE) Sep 08 01:59:55 mysql-pxc-03_121 systemd[1]: Starting Percona XtraDB Cluster... Sep 08 01:59:55 mysql-pxc-03_121 mysql-systemd[1802]: 2018-09-07T17:59:55.838705Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp s...ore details). Sep 08 01:59:55 mysql-pxc-03_121 mysql-systemd[1802]: 2018-09-07T17:59:55.838817Z 0 [Warning] WSREP: Node is running in bootstrap/initialize mode. Disabling pxc_strict_mode checks Sep 08 01:59:55 mysql-pxc-03_121 mysql-systemd[1802]: 2018-09-07T17:59:55.841859Z 0 [ERROR] Could not open file '/var/log/mysqld.log' for error logging: Permission denied Sep 08 01:59:55 mysql-pxc-03_121 mysql-systemd[1802]: 2018-09-07T17:59:55.841885Z 0 [ERROR] Aborting Sep 08 01:59:55 mysql-pxc-03_121 systemd[1]: mysql.service: control process exited, code=exited status=1 Sep 08 01:59:55 mysql-pxc-03_121 systemd[1]: Failed to start Percona XtraDB Cluster. Sep 08 01:59:55 mysql-pxc-03_121 systemd[1]: Unit mysql.service entered failed state. Sep 08 01:59:55 mysql-pxc-03_121 systemd[1]: mysql.service failed. Hint: Some lines were ellipsized, use -l to show in full. [root@mysql-pxc-03_121 ~]# sudo touch /var/log/mysqld.log [root@mysql-pxc-03_121 ~]# sudo chown mysql:mysql /var/log/mysqld.log [root@mysql-pxc-03_121 ~]# sudo chcon system_u:object_r:mysqld_log_t:s0 /var/log/mysqld.log [root@mysql-pxc-03_121 ~]# systemctl start mysqld
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
代码如下 复制代码 /etc/init.d/mysqld stop 移动数据: mv /var/lib/mysql/* /home/mysql/ 创建软连接: ln -s /home/mysql/ /var/lib/mysql/ 启动mysql: ln -s /home/mysql/ /var/lib/mysql/ 启动mysql: /etc/init.d/mysqld stop mv /var/lib/mysql/* /data/local/percona-xtradb-cluster/data ln -s /data/local/percona-xtradb-cluster/data /var/lib/mysql/ /etc/init.d/mysqld start 【开机自启】Linux下设置MySql自动启动 1、将服务文件拷贝到init.d下， cp /usr/share/percona-xtradb-cluster/mysql.server /etc/init.d/ 2、赋予可执行权限 chmod +x /etc/init.d/mysql.server 3、添加服务 chkconfig --add mysql.server 4、显示服务列表 chkconfig --list chkconfig --level 345 mysql.server on reboot
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
安装ProxySQL
#开放3306, 4444, 4567和4568四个端口 firewall-cmd --zone=public --add-port=3306/tcp --permanent firewall-cmd --zone=public --add-port=4444/tcp --permanent firewall-cmd --zone=public --add-port=4567/tcp --permanent firewall-cmd --zone=public --add-port=4568/tcp --permanent firewall-cmd --reload firewall-cmd --list-ports 首先要安装一些依赖的软件包，配置好 Yum 源进行安装即可。 [root@proxysql-01_117 ~]#yum -y install perl-DBD-MySQL [root@proxysql-01_117 ~]#yum -y install perl-DBI [root@proxysql-01_117 ~]#yum -y install perl-Time-HiRes [root@proxysql-01_117 ~]#yum -y install perl-IO-Socket-SSL [root@proxysql-01_117 ~]#yum -y install proxysql-1.4.9-1.1.el7.x86_64.rpm [root@proxysql-01_117 ~]# service proxysql start Starting ProxySQL: DONE! [root@proxysql-01_117 ~]# mysql -uadmin -padmin -h 127.0.0.1 -P6032 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2009-2018 Percona LLC and/or its affiliates Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> show databases; +-----+---------------+-------------------------------------+ | seq | name | file | +-----+---------------+-------------------------------------+ | 0 | main | | | 2 | disk | /var/lib/proxysql/proxysql.db | | 3 | stats | | | 4 | monitor | | | 5 | stats_history | /var/lib/proxysql/proxysql_stats.db | +-----+---------------+-------------------------------------+ 5 rows in set (0.00 sec) mysql> show tables; +--------------------------------------------+ | tables | +--------------------------------------------+ | global_variables | | mysql_collations | | mysql_group_replication_hostgroups | | mysql_query_rules | | mysql_query_rules_fast_routing | | mysql_replication_hostgroups | | mysql_servers | | mysql_users | | proxysql_servers | | runtime_checksums_values | | runtime_global_variables | | runtime_mysql_group_replication_hostgroups | | runtime_mysql_query_rules | | runtime_mysql_query_rules_fast_routing | | runtime_mysql_replication_hostgroups | | runtime_mysql_servers | | runtime_mysql_users | | runtime_proxysql_servers | | runtime_scheduler | | scheduler | +--------------------------------------------+ 20 rows in set (0.00 sec) 添加pxc到ProxySQL的 mysql_servers 表 mysql> INSERT INTO mysql_servers (hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment)VALUES(100,'192.168.111.119',3306,1,1000,10,'pxc-01'),(100,'192.168.111.120',3306,1,1000,10,'pxc-02'),(100,'192.168.111.121',3306,1,1000,10,'pxc-03'); Query OK, 3 rows affected (0.00 sec) mysql> select * from mysql_servers; +--------------+-----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+-----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 100 | 192.168.111.119 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | pxc-01 | | 100 | 192.168.111.120 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | pxc-02 | | 100 | 192.168.111.121 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | pxc-03 | +--------------+-----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 6 rows in set (0.00 sec) mysql> UPDATE global_variables SET variable_value='monitor' where variable_name='mysql-monitor_username'; Query OK, 1 row affected (0.01 sec) mysql> UPDATE global_variables SET variable_value='monitor' where variable_name='mysql-monitor_password'; Query OK, 1 row affected (0.00 sec) mysql> select * from global_variables where variable_name in ('mysql-monitor_username','mysql-monitor_password'); +------------------------+----------------+ | variable_name | variable_value | +------------------------+----------------+ | mysql-monitor_password | monitor | | mysql-monitor_username | monitor | +------------------------+----------------+ 2 rows in set (0.00 sec) 以下sql是目标数据库中执行的: CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor'; GRANT USAGE ON *.* TO 'monitor'@'%'; CREATE USER 'proxysql'@'%' IDENTIFIED BY 'proxysql'; GRANT ALL ON *.* TO 'proxysql'@'%'; FLUSH PRIVILEGES; SELECT user,host FROM mysql.user; 回到proxysql set mysql-monitor_username=''proxysql01'; set mysql-monitor_password=''proxysql01'; load mysql variables to runtime; save mysql variables to disk; 设置ProxySQL连接后端PXC的用户，即配置 mysql_users 表 在pxc上执行(pxc1、pxc2、pxc3任意一个)创建连接用户，这里假设ProxySQL全都使用root用户连接PXC，以及接受app的SQL请求。 create user root@'192.168.%.%' identified by 'proxysql01'; grant all on *.* to root@'192.168.%.%'; 然后回到ProxySQL，配置mysql_users表，将刚才的用户添加到该表中。 insert into mysql_users(username,password,default_hostgroup,transaction_persistent) values('root','proxysql01',1,1); load mysql users to runtime; save mysql users to disk; 添加Galera支持 INSERT INTO scheduler(id,interval_ms,filename,arg1,arg2,arg3,arg4) VALUES (1,'10000','/var/lib/proxysql/proxysql_galera_checker.sh','127.0.0.1','6032','10', '/tmp/proxysql_galera_checker.log'); LOAD SCHEDULER TO RUNTIME; SAVE SCHEDULER TO DISK;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
声明：本文内容由网友自发贡献，转载请注明出处：【wpsshop博客】
推荐阅读
相关标签
