【IT Notes】 Ubuntu 安裝 ProxySQL與 Percona-Xtradb-Cluster

 去年安裝過XtraDB，當時用的OS是CentOS 7，後來RedHat宣布不再更新CentOS系列後，我們就轉投靠Ubuntu了，這次除了複習一下XtraDB以外，還要安裝ProxySQL看看，用來優化資料庫的load balance，安裝的過程遇到比上次更多障礙，所以重新再做一遍筆記，順便把MySQL的版本提升到8.0（之前裝的是5.7）。

一‧事前準備 四台VM，基本配備：  

OS選擇Ubuntu 22.04

4核心CPU、4GB RAM、20GB硬碟  
proxysql: 172.25.250.20   proxysql

node1: 172.25.250.21        pxc1
node2: 172.25.250.22        pxc2
node3: 172.25.250.23        pxc3 
（要先確認彼此都能ping或ssh得到）

二‧node下載並用root安裝XtraDB

1.node分別依下列方式安裝XtraDB

root@lab-pxc1:~# sudo ufw allow 3306/tcp

root@lab-pxc1:~# sudo ufw allow 4444/tcp

root@lab-pxc1:~# sudo ufw allow 4567/tcp

root@lab-pxc1:~# sudo ufw allow 4567/udp

root@lab-pxc1:~# sudo ufw allow 4568/tcp

root@lab-pxc1:~# sudo ufw allow 22/tcp

root@lab-pxc1:~# sudo ufw enable

root@lab-pxc1:~# sudo ufw status

root@lab-pxc1:~# sudo apt update -y

root@lab-pxc1:~# sudo apt install wget gnupg2 lsb-release curl -y

root@lab-pxc1:~# wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb

root@lab-pxc1:~# sudo dpkg -i percona-release_latest.generic_all.deb

root@lab-pxc1:~# sudo apt -y update 

root@lab-pxc1:~# sudo percona-release setup pxc80

root@lab-pxc1:~# sudo apt -y install -y percona-xtradb-cluster

root@lab-pxc1:~# sudo service mysql stop

安裝過程會要求輸入資料庫的root密碼，我用P@ssw0rd

確認密碼

選擇預設的密碼加密

在三台node都進行同樣的安裝!後面兩台就不重複了。

2.將node1的通訊憑證傳到node2與node3

root@lab-pxc1:~# scp /var/lib/mysql/server-key.pem root@172.25.250.22:/var/lib/mysql

root@lab-pxc1:~# scp /var/lib/mysql/server-cert.pem root@172.25.250.22:/var/lib/mysql

root@lab-pxc1:~# scp /var/lib/mysql/ca.pem root@172.25.250.22:/var/lib/mysql

root@lab-pxc1:~# scp /var/lib/mysql/server-key.pem root@172.25.250.23:/var/lib/mysql

root@lab-pxc1:~# scp /var/lib/mysql/server-cert.pem root@172.25.250.23:/var/lib/mysql

root@lab-pxc1:~# scp /var/lib/mysql/ca.pem root@172.25.250.23:/var/lib/mysql

root@lab-pxc1:~# sudo vim /etc/mysql/my.cnf

三‧node配置複寫config

1.在node1的my.cnf上添加以下配置

root@lab-pxc1:~# sudo vim /etc/mysql/my.cnf

[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib/libgalera_smm.so

# Cluster connection URL contains the IPs of pxc01, pxc02, and pxc03
wsrep_cluster_address=gcomm://172.25.250.21,172.25.250.22,172.25.250.23

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# Using the MyISAM storage engine is not recommended
default_storage_engine=InnoDB

# This InnoDB autoincrement locking mode is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #1 address
wsrep_node_address=172.25.250.21
# Cluster name
wsrep_cluster_name=pxc_cluster

# SST method
wsrep_sst_method=xtrabackup-v2

wsrep_provider_options="socket.ssl_key=server-key.pem;socket.ssl_cert=server-cert.pem;socket.ssl_ca=ca.pem"

[sst]
encrypt=4
ssl-key=server-key.pem
ssl-ca=ca.pem
ssl-cert=server-cert.pem

2.在第node2的my.cnf上添加以下配置

root@lab-pxc2:~# sudo vim /etc/mysql/my.cnf

[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib/libgalera_smm.so

# Cluster connection URL contains the IPs of pxc01, pxc02, and pxc03
wsrep_cluster_address=gcomm://172.25.250.21,172.25.250.22,172.25.250.23

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# Using the MyISAM storage engine is not recommended
default_storage_engine=InnoDB

# This InnoDB autoincrement locking mode is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #2 address
wsrep_node_address=172.25.250.22

# Cluster name
wsrep_cluster_name=pxc_cluster

# SST method
wsrep_sst_method=xtrabackup-v2

wsrep_provider_options="socket.ssl_key=server-key.pem;socket.ssl_cert=server-cert.pem;socket.ssl_ca=ca.pem"

[sst]
encrypt=4
ssl-key=server-key.pem
ssl-ca=ca.pem
ssl-cert=server-cert.pem

3.在node3的my.cnf上添加以下配置

root@lab-pxc3:~#  sudo vim /etc/mysql/my.cnf

[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib/libgalera_smm.so

# Cluster connection URL contains the IPs of pxc01, pxc02, and pxc03
wsrep_cluster_address=gcomm://172.25.250.21,172.25.250.22,172.25.250.23

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# Using the MyISAM storage engine is not recommended
default_storage_engine=InnoDB

# This InnoDB autoincrement locking mode is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #3 address
wsrep_node_address=172.25.250.23

# Cluster name
wsrep_cluster_name=pxc_cluster

# SST method
wsrep_sst_method=xtrabackup-v2

wsrep_provider_options="socket.ssl_key=server-key.pem;socket.ssl_cert=server-cert.pem;socket.ssl_ca=ca.pem"

[sst]
encrypt=4
ssl-key=server-key.pem
ssl-ca=ca.pem
ssl-cert=server-cert.pem

到目前為止，MySQL8.0和5.7的安裝大致上一樣，只是之前5.7版本我用的是user與password的方式讓cluster相互通訊，8.0版則改用憑證，這是最大的差異。

四‧啟動node複寫

1.node1先帶頭啟動並建立複寫用帳號，再檢查複寫狀態

root@lab-pxc1:~# sudo systemctl start mysql@bootstrap.service

root@lab-pxc1:~# mysql -u root -pP@ssw0rd

mysql> show status like 'wsrep%';

可以看到目前node1加入cluster了

2.node2和node3啟動MySQL，加入cluster

root@lab-pxc2:~# systemctl start mysql

root@lab-pxc3:~# systemctl start mysql

3.回到node1檢視複寫狀態

mysql> show status like 'wsrep%';

可看見另兩台node也都加入了cluster

mysql> show status like 'wsrep_cluster_size';

4.建立資料表測試複寫

在node1上建立資料庫和資料表

mysql> CREATE DATABASE RECIPES;

mysql> USE RECIPES;

mysql> CREATE TABLE TAB1 (CONTACT_ID INT PRIMARY KEY, CONTACT_NAME VARCHAR(20));

mysql> INSERT INTO TAB1 VALUES (1,'sadegh');

mysql> INSERT INTO TAB1 VALUES (2,'nix');

mysql> INSERT INTO TAB1 VALUES (3,'mamad');

mysql> exit

Bye

再到另兩台node查看有無同步

mysql> USE RECIPES;

mysql> SELECT * FROM TAB1;

五‧安裝ProxySQL

ProxySQL為XtraDB作負載平衡的設計，除了監控三台node的狀態外，程式的寫入也統一改ProxySQL，所以最後還要設定的一個帳號是專門用來寫入資料到cluster用的。

1.ProxySQL的基本安裝

root@lab-proxysql:~# sudo ufw allow 3306/tcp

root@lab-proxysql:~# sudo ufw allow 4444/tcp
root@lab-proxysql:~# sudo ufw allow 4567/tcp
root@lab-proxysql:~# sudo ufw allow 4567/udp
root@lab-proxysql:~# sudo ufw allow 4568/tcp
root@lab-proxysql:~# sudo ufw enable
root@lab-proxysql:~# sudo ufw status
root@lab-proxysql:~# sudo apt -y update 
root@lab-proxysql:~# sudo apt install -y wget gnupg2 lsb-release curl
root@lab-proxysql:~# wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
root@lab-proxysql:~# sudo dpkg -i percona-release_latest.generic_all.deb
root@lab-proxysql:~# sudo apt -y update 
root@lab-proxysql:~# sudo percona-release setup pxc80
root@lab-proxysql:~# sudo apt -y update 
root@lab-proxysql:~# sudo apt -y install percona-xtradb-cluster-client　\\ProxySQL這台要安裝的是xtraDB的client版本，跟前面node裝的server版本不同
root@lab-proxysql:~# sudo apt -y install proxysql2

2.ProxySQL監控配置

我覺得最難的是從這裡開始，研究過程參考過很多資料，個人覺得最精準的參考資料如下：https://docs.percona.com/percona-xtradb-cluster/5.7/howtos/proxysql.html

root@lab-proxysql:~# mysql -u admin -padmin -h 127.0.0.1 -P 6032　 \\登入管理帳號admin

mysql> DELETE FROM mysql_group_replication_hostgroups;
mysql> INSERT INTO mysql_group_replication_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) VALUES (30,34,31,36,1,1,1,0);
mysql> INSERT INTO mysql_servers(hostgroup_id, hostname, port,comment) VALUES (0,'172.25.250.21',3306,'pxc1');
mysql> INSERT INTO mysql_servers(hostgroup_id, hostname, port,comment) VALUES (0,'172.25.250.22',3306,'pxc2');
mysql> INSERT INTO mysql_servers(hostgroup_id, hostname, port,comment) VALUES (0,'172.25.250.23',3306,'pxc3');
mysql> SELECT * FROM mysql_servers;

檢視三台node

mysql> LOAD MYSQL VARIABLES TO RUNTIME;    \\將變數參數寫入記憶體
mysql> SAVE MYSQL VARIABLES TO DISK;              \\將變數參數寫入磁碟

mysql> UPDATE global_variables SET variable_value='proxysql' WHERE variable_name='mysql-monitor_username';     \\建立監控cluster的帳號

mysql> UPDATE global_variables SET variable_value='ProxySQLPa55' WHERE variable_name='mysql-monitor_password';       \\建立監控cluster的密碼

mysql> LOAD MYSQL SERVERS TO RUNTIME;    \\將伺服器參數寫入記憶體

mysql> SAVE MYSQL SERVERS TO DISK;     \\將伺服器參數寫入磁碟

3.node1建立ProxySQL監控用的user

除了ProcySQL，別忘記也要回到node1上面建立讓ProxySQL監控cluster的user，帳密是要一樣的

mysql>  CREATE USER 'proxysql'@'%' IDENTIFIED WITH mysql_native_password by 'ProxySQLPa55';
mysql>  GRANT USAGE ON *.* TO 'proxysql'@'%';

4.ProxySQL建立寫入cluster用的user

在PoxySQL上建立一個用來寫入用的user和password (sbuser/sbpass)

mysql> INSERT INTO mysql_users (username,password) VALUES ('sbuser','sbpass');

mysql> LOAD MYSQL USERS TO RUNTIME;　　\\將使用者參數寫入記憶體

mysql> SAVE MYSQL USERS TO DISK;　　　　　\\將使用者參數寫入磁碟　

mysql> LOAD MYSQL SERVERS TO RUNTIME;

mysql> SAVE MYSQL SERVERS TO DISK;

5.node1建立給ProxySQL寫入用的user

別忘記也要回到node1上面建立帳密一樣的使用者

mysql> CREATE USER 'sbuser'@'172.25.250.20' IDENTIFIED BY 'sbpass';

mysql> GRANT ALL ON *.* TO 'sbuser'@'172.25.250.20';

mysql> flusg privileges;

6.ProxySQL驗證cluster監控

root@lab-proxysql:~#  mysql -u admin -padmin -h 127.0.0.1 -P 6032

mysql> SELECT * FROM runtime_mysql_servers;

mysql> select * from mysql_server_group_replication_log order by time_start_us desc  limit 5 ;
mysql> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 6;

若出現訊息又沒有error的話，就表示監控的配置有成功

7.ProxySQL驗證cluster寫入

用來寫入的帳號聽的port是6033

root@lab-proxysql:~#  mysql -u sbuser -psbpass -h 127.0.0.1 -P 6033

mysql> CREATE DATABASE MYPROXY;

回去node1查看

mysql > show databases;

+--------------------+

| Database           |
+--------------------+
| MYPROXY            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

node1檢視到資料成功寫入

費了九牛二虎之力終於設定好了，必須說這個ProxySQL監控Percona XtraDB Cluster滿困難了，花了很多天研究和測試，才順利完成lab，希望這篇研究紀錄有價值。