如何搭建Percona XtraDB Cluster（PXC）集群

最新推荐文章于 2024-09-06 11:04:50 发布

薛定谔的003号猫

最新推荐文章于 2024-09-06 11:04:50 发布

阅读量881

点赞数

分类专栏： Java MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/qq_26402707/article/details/107786504

版权

Java 同时被 2 个专栏收录

3 篇文章 0 订阅

订阅专栏

MySQL

1 篇文章 0 订阅

订阅专栏

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

[root@localhost ~]# rpm -e --nodeps mariadb  mariadb-server  mariadb-libs marisa    --卸载

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

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

如果成功，应该看到以下内容：

Installed:
  percona-release.noarch 0:0.1-6

Complete!

3、确保Percona包可用：

sudo yum list | grep percona

输出以下内容：

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
...

4、安装Percona XtraDB Cluster软件包：

sudo yum install Percona-XtraDB-Cluster-57 -y

软件安装在默认路径 /var/lib/mysql

遇到的问题：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!

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;

/var/log/mysqld.log 中找不到临时密码

1.删除原来安装过的mysql残留的数据（这一步非常重要，问题就出在这）
rm -rf /var/lib/mysql
2.重启mysqld服务
systemctl restart mysqld
3.再去找临时密码
grep 'temporary password' /var/log/mysqld.log

7、修改/etc/my.cnf配置文件

在修改配置文件之前，需要先停止mysqld服务
[root@mysql-pxc-02_120 ~]#systemctl stop mysqld
[root@mysql-pxc-02_120 ~]#vi /etc/my.cnf


#
# 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严格模式，建议开启

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

启动不了可能是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

代码如下 复制代码
/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

安装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;