1、CentOS 7.9.x
2、Percona XtraDB Cluster 8.0.x
3、Navicat Premium 15.0.27
4、准备 3 台 CentOS 7.9.x 服务器
IP_1:192.168.101.111
IP_2:192.168.101.112
IP_3:192.168.101.113
在3台服务器上参考以下步骤依次安装
2.1 安装 percona-release
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y
2.2 配置 percona 存储库
percona-release enable-only ps-80 release percona-release enable-only pxb-80 release percona-release enable-only pdps-8.0.23 release percona-release enable-only pdpxc-8.0.23 release
2.3 安装 pxc 和 创建远程连接用户
# 安装 pxc yum install percona-xtradb-cluster -y # 启动MySQL service mysql start # 查看初始密码 grep 'temporary password' /var/log/mysqld.log # 登录 MySQL mysql -u root -p # 修改密码 ALTER USER 'root'@'localhost' IDENTIFIED BY 'root' ; # 查看 MySQL 版本 select @@version ; # 创建 远程连接用户 'ymalls' create user 'ymalls'@'%' identified by 'ymalls' ; # 授予权限 grant all privileges on *.* to 'ymalls'@'%' ; flush privileges;
2.4 修改 my.cnf
vim /etc/my.cnf # 修改或加入 # ------------------------ [mysqld] default_storage_engine=InnoDB skip-name-resolve pxc-encrypt-cluster-traffic=OFF [sst] pxc-encrypt-cluster-traffic=OFF # ------------------------------ # 重启 MySQL service mysql restart
3.1 停止3台服务器上的 MySQL 服务 service mysql stop
3.2 在 192.168.101.111 配置第1个(初始)节点 node1
vim /etc/my.cnf # ---------------- [mysqld] server-id=1 binlog_expire_logs_seconds=604800 wsrep_provider=/usr/lib64/galera4/libgalera_smm.so wsrep_cluster_address=gcomm:// binlog_format=ROW wsrep_slave_threads=8 wsrep_log_conflicts innodb_autoinc_lock_mode=2 wsrep_cluster_name=pxc-cluster wsrep_node_address=192.168.101.111 wsrep_node_name=node1 pxc_strict_mode=ENFORCING wsrep_sst_method=xtrabackup-v2 # --------------- # 保存后 启动 第1个节点 service mysql start # 查看集群是否初始化 mysql -u root -p'root' show status like 'wsrep%'; # 如下 +----------------------------+--------------------------------------+ | Variable_name | Value | +----------------------------+--------------------------------------+ | wsrep_local_state_uuid | c2883338-834d-11e2-0800-03c9c68e41ec | | ... | ... | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | ... | ... | | wsrep_cluster_size | 1 | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | ... | ... | | wsrep_ready | ON | +----------------------------+--------------------------------------+
3.3 在 192.168.101.112 配置第2个节点 node2
vim /etc/my.cnf # ---------------- [mysqld] server-id=2 binlog_expire_logs_seconds=604800 wsrep_provider=/usr/lib64/galera4/libgalera_smm.so wsrep_cluster_address=gcomm://192.168.101.111,192.168.101.112,192.168.101.113 binlog_format=ROW wsrep_slave_threads=8 wsrep_log_conflicts innodb_autoinc_lock_mode=2 wsrep_cluster_name=pxc-cluster wsrep_node_address=192.168.101.112 wsrep_node_name=node2 pxc_strict_mode=ENFORCING wsrep_sst_method=xtrabackup-v2 # --------------- # 保存后 启动 第 2 个节点 service mysql start
3.3 在 192.168.101.113 配置第3个节点 node3
vim /etc/my.cnf # ---------------- [mysqld] server-id=3 binlog_expire_logs_seconds=604800 wsrep_provider=/usr/lib64/galera4/libgalera_smm.so wsrep_cluster_address=gcomm://192.168.101.111,192.168.101.112,192.168.101.113 binlog_format=ROW wsrep_slave_threads=8 wsrep_log_conflicts innodb_autoinc_lock_mode=2 wsrep_cluster_name=pxc-cluster wsrep_node_address=192.168.101.113 wsrep_node_name=node3 pxc_strict_mode=ENFORCING wsrep_sst_method=xtrabackup-v2 # --------------- # 保存后 启动 第 3 个节点 service mysql start
3.4 修改 192.168.101.111 上的初始节点配置
vim /etc/my.cnf # 修改 "wsrep_cluster_address=gcomm://" 如下: wsrep_cluster_address=gcomm://192.168.101.111,192.168.101.112,192.168.101.113 # 保存 并重启 此节点 service mysql restart
四、使用 Navicat Premium 15.0.27 连接PXC集群
4.1 在 pxc_node1 创建数据库 db_ymalls
4.2 刷新 pxc-node2和pxc-node3 连接,验证是否同步创建
4.3 如果是老版的 Navicat 连不上集群,可修改 ymalls 用户密码的加密方式
# 使用 mysql_native_password 加密方式 alter user 'ymalls'@'%' identified with mysql_native_password by 'ymalls'; # 刷新权限 flush privileges;