Bo's Oracle Station › 博客首页 ›Oracle技术› Linux-MySQL-Java › 查看内容

【博客文章2024】MySQL NDB Cluster与PHP连接故障暨数据不一致案例处理方法

2024-2-15 12:10| 发布者: admin| 查看: 33| 评论: 0|原作者: Bo Tang

摘要: 本文记录了MySQL ndb cluster 8.0.36与php7.2.24连接故障处理一例。处理内容包含：连接故障描述和解决办法以及数据不一致解决办法。博客仔细分析了出现问题的原因还包括原创的创建表的SQL语句和PHP页面的代码。只要有相似的MySQL NDB环境，读者可以一步一步地跟着重现博客文章里的实验体验。

Author： Bo Tang

1. 环境和架构：

实验环境是一套MySQL NDB Cluster 8.0.36环境，其中的管理节点同时运行着Apache网页服务器（带有PHP扩展和mysqli连接插件，PHP的版本是7.2.24）。所有节点的操作系统都是RedHat Linux8.3（4.18.0-240.el8.x86_64 #1 SMP Wed Sep 23 05:13:10 EDT 2020 x86_64 x86_64 x86_64 GNU/Linux）。

1.1 MySQL数据库部分：
NDB集群的SQL节点上的MySQL的版本是：

[root@node1 mysql]# mysqladmin version -u root -p
Enter password:
mysqladmin Ver 8.0.36-cluster for Linux on x86_64 (MySQL Cluster Community Server - GPL)
Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version        8.0.36-cluster
Protocol version    10
Connection        Localhost via UNIX socket
UNIX socket        /var/lib/mysql/mysql.sock
Uptime:            1 day 15 hours 30 min 9 sec

Threads: 3 Questions: 6867 Slow queries: 0 Opens: 614 Flush tables: 4 Open tables: 391 Queries per second avg: 0.048

NDB集群的示意图是：

NDB集群的管理节点（Management Server）是node0，数据节点（ndbd）是node3/node4/node5/node6，SQL节点（mysqld）是node1/node2：

[root@node0 ~]# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]    4 node(s)
id=4    @172.25.250.203 (mysql-8.0.36 ndb-8.0.36, Nodegroup: 0, *)
id=5    @172.25.250.204 (mysql-8.0.36 ndb-8.0.36, Nodegroup: 0)
id=6    @172.25.250.205 (mysql-8.0.36 ndb-8.0.36, Nodegroup: 0)
id=7    @172.25.250.206 (mysql-8.0.36 ndb-8.0.36, Nodegroup: 0)

[ndb_mgmd(MGM)]    1 node(s)
id=1    @172.25.250.200 (mysql-8.0.36 ndb-8.0.36)

[mysqld(API)]    2 node(s)
id=2    @172.25.250.201 (mysql-8.0.36 ndb-8.0.36)
id=3    @172.25.250.202 (mysql-8.0.36 ndb-8.0.36)

1.2 MySQL数据库的表结构和数据：

create database contacts;

use contacts;

create table product(
id int,
name varchar(30),
price double,
stock int,
id_category int,
id_manufacture int);

insert into product values ( 1,'ThinkServer TS140', 539.88, 20, 2, 4);
insert into product values (2,'ThinkServer TS440', 1736.00, 10, 2,4) ;
insert into product values (3,'RT-AC68U',219.99,10,1,3);
insert into product values (4,'X110 64GB',73.84, 100, 3,1);

create table category(
id int,
name varchar(20));

insert into category values (1,'Networking');
insert into category values (2,'Servers');
insert into category values (3,'Ssd');

create table manufacturer(
id int,
name varchar(20),
seller varchar(20),
phone_number varchar(30));

insert into manufacturer values (1,'SanDisk','John Miller', '+1(941)329-8855');
insert into manufacturer values (2,'Kingston','Mike Taylor','+1(341)375-9999');
insert into manufacturer values (3,'Asus','Wilson Jackson','+1(432)367-8899');
insert into manufacturer values (4,'Lenovo','Allen Scott','+1(876)213-4439');

1.3 网页服务器和PHP：

网页服务器由NDB集群的管理节点兼任，上面安装着RedHat Linux8.3发行版本自带的Apache网页服务器和PHP组件。在Apache网页服务器上访问以下PHP测试页面：

< ?php

echo phpinfo();
?>

可以看到以下内容：

要提供给用户访问的页面示意如下，要求查出数据库中product、category和manufacture三张表的所有内容（/var/www/html/mysql.php）：

< ?php

$servername = "172.25.250.201";
$username = "root";
$password = "oracle_4U";
$db = "contacts";

// Make connection
$conn = new mysqli($servername, $username, $password, $db);

// Test
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "MySQL Connected.";
mysqli_select_db($conn, $db);
echo "

";
$result = mysqli_query($conn, 'set names utf8');
$result = mysqli_query($conn, 'select * from product');

echo "Rows in product:";
echo "
";
if (mysqli_num_rows($result) > 0) {
  while($row = mysqli_fetch_assoc($result)) {
  echo $row["id"]."    ".$row["name"]."    ".$row["price"]."    ".$row["stock"]."    ".$row["id_category"]."    ".$row["id_manufacture"]."
";
  }
 } 
 else {
  echo "0 result.";
 }

echo "
";

$result = mysqli_query($conn, 'select * from category');

echo "Rows in category:";
echo "
";
if (mysqli_num_rows($result) > 0) {
  while($row = mysqli_fetch_assoc($result)) {
  echo $row["id"]."     ".$row["name"]."
";
  }
 } 
 else {
  echo "0 result.";
 }

echo "
";

$result = mysqli_query($conn, 'select * from manufacturer');

echo "Rows in manufacturer:";
echo "
";
if (mysqli_num_rows($result) > 0) {
  while($row = mysqli_fetch_assoc($result)) {
  echo $row["id"]."     ".$row["name"]."        ".$row["seller"]."        ".$row["phone_number"]."
";
  }
 }
 else {
  echo "0 result.";
 }


echo "

";
?>

从上面的代码可以看出：该php页面连接的是MySQL NDB集群的第1个SQL节点（172.25.250.201）。

2. 连接故障描述和解决办法：

2.1 连接故障描述：

客户端使用URL：http://node0/mysql.php时，看到的页面是：

Connection failed: The server requested authentication method unknown to the client

2.2 故障原因：

mysqli连接MySQL数据库时使用的密码验证模块是mysql_native_password，而我们的数据库里用户的密码验证模块却是caching_sha2_password，这是出现连接故障的原因。使用mysql客户端或者mysqlsh连接SQL节点node1，执行如下的查询：

2.3 处理方法：

使用mysql客户端或者mysqlsh连接SQL节点node1，执行如下的修改：

MySQL node1:3306 ssl SQL > alter user root@'%' identified with mysql_native_password;
Query OK, 0 rows affected (0.0301 sec)
MySQL node1:3306 ssl SQL > set password='oracle_4U';
Query OK, 0 rows affected (0.0248 sec)

客户端使用URL：http://node0/mysql.php时，看到的页面说明连接成功，但是数据明显不一致。表中应该有数据，但是却查询不到：

MySQL Connected.

Rows in product:
0 result.
Rows in category:
0 result.
Rows in manufacturer:
0 result.

3. 数据不一致解决办法：

使用mysql客户端或者mysqlsh连接SQL节点node1，执行如下的查询：

mysql> use contacts;

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> show tables;
+--------------------+
| Tables_in_contacts |
+--------------------+
| category           |
| manufacturer       |
| product            |
+--------------------+
3 rows in set (0.01 sec)

mysql> show create table category;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                            |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| category | CREATE TABLE `category` (
`id` int DEFAULT NULL,
`name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table manufacturer ;
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| manufacturer | CREATE TABLE `manufacturer` (
`id` int DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`seller` varchar(20) DEFAULT NULL,
`phone_number` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table product;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| product | CREATE TABLE `product` (
`id` int DEFAULT NULL,
`name` varchar(30) DEFAULT NULL,
`price` double DEFAULT NULL,
`stock` int DEFAULT NULL,
`id_category` int DEFAULT NULL,
`id_manufacture` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

发现问题可能出现在存储引擎上。而且应该是这样的情况，这三个表的数据是在MySQL NDB集群的别的SQL节点（node2）上用默认的innodb存储引擎创建并插入数据的。使用mysql客户端或者mysqlsh连接SQL节点node1，执行如下的查询：

mysql> use contacts;
Database changed
mysql> select * from product;
ERROR 1146 (42S02): Table 'contacts.product' doesn't exist
mysql> select * from category;
ERROR 1146 (42S02): Table 'contacts.category' doesn't exist
mysql> select * from manufacturer;
ERROR 1146 (42S02): Table 'contacts.manufacturer' doesn't exist

使用mysql客户端或者mysqlsh连接SQL节点node2，执行如下的查询：

mysql> use contacts;
Database changed
mysql> select * from product;
+------+-------------------+--------+-------+-------------+----------------+
| id   | name              | price | stock | id_category | id_manufacture |
+------+-------------------+--------+-------+-------------+----------------+
|    1 | ThinkServer TS140 | 539.88 |    20 |           2 |              4 |
|    4 | X110 64GB         | 73.84 |   100 |           3 |              1 |
|    3 | RT-AC68U          | 219.99 |    10 |           1 |              3 |
|    2 | ThinkServer TS440 |   1736 |    10 |           2 |              4 |
+------+-------------------+--------+-------+-------------+----------------+
4 rows in set (0.00 sec)

mysql> select * from category;
+------+------------+
| id   | name       |
+------+------------+
|    3 | Ssd        |
|    2 | Servers    |
|    1 | Networking |
+------+------------+
3 rows in set (0.00 sec)

mysql> select * from manufacturer;
+------+----------+----------------+-----------------+
| id   | name     | seller         | phone_number    |
+------+----------+----------------+-----------------+
|    2 | Kingston | Mike Taylor    | +1(341)375-9999 |
|    3 | Asus     | Wilson Jackson | +1(432)367-8899 |
|    1 | SanDisk | John Miller    | +1(941)329-8855 |
|    4 | Lenovo   | Allen Scott    | +1(876)213-4439 |
+------+----------+----------------+-----------------+
4 rows in set (0.00 sec)

通过上面的两个查询确认了上面的猜想。使用mysql客户端或者mysqlsh连接SQL节点node2，执行如下的修改：

mysql> alter table category engine ndbcluster;
Query OK, 3 rows affected (0.77 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> alter table manufacturer engine ndbcluster;
Query OK, 4 rows affected (0.93 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> alter table product engine ndbcluster;
Query OK, 4 rows affected (0.65 sec)
Records: 4 Duplicates: 0 Warnings: 0

使用mysql客户端或者mysqlsh连接SQL节点node1，执行如下的查询：

mysql> use contacts;
Database changed
mysql> select * from product;
+------+-------------------+--------+-------+-------------+----------------+
| id   | name              | price | stock | id_category | id_manufacture |
+------+-------------------+--------+-------+-------------+----------------+
|    3 | RT-AC68U          | 219.99 |    10 |           1 |              3 |
|    4 | X110 64GB         | 73.84 |   100 |           3 |              1 |
|    1 | ThinkServer TS140 | 539.88 |    20 |           2 |              4 |
|    2 | ThinkServer TS440 |   1736 |    10 |           2 |              4 |
+------+-------------------+--------+-------+-------------+----------------+
4 rows in set (0.00 sec)

mysql> select * from category;
+------+------------+
| id   | name       |
+------+------------+
|    1 | Networking |
|    2 | Servers    |
|    3 | Ssd        |
+------+------------+
3 rows in set (0.00 sec)

mysql> select * from manufacturer;
+------+----------+----------------+-----------------+
| id   | name     | seller         | phone_number    |
+------+----------+----------------+-----------------+
|    1 | SanDisk | John Miller    | +1(941)329-8855 |
|    3 | Asus     | Wilson Jackson | +1(432)367-8899 |
|    2 | Kingston | Mike Taylor    | +1(341)375-9999 |
|    4 | Lenovo   | Allen Scott    | +1(876)213-4439 |
+------+----------+----------------+-----------------+
4 rows in set (0.00 sec)

客户端使用URL：http://node0/mysql.php时，看到的页面说明连接成功，但是数据也一致了：

MySQL Connected.

Rows in product:
3	RT-AC68U	219.99	10	1	3
4	X110 64GB	73.84	100	3	1
1	ThinkServer TS140	539.88	20	2	4
2	ThinkServer TS440	1736	10	2	4

Rows in category:
1     Networking
2     Servers
3     Ssd

Rows in manufacturer:
1     SanDisk        John Miller        +1(941)329-8855
3     Asus        Wilson Jackson        +1(432)367-8899
2     Kingston        Mike Taylor        +1(341)375-9999
4     Lenovo        Allen Scott        +1(876)213-4439

路过

雷人

握手

鲜花

鸡蛋

收藏邀请

【博客文章2024】MySQL NDB Cluster与PHP连接故障暨数据不一致案例处理方法

相关分类